1 数据库连接池的原理
![JDBC (三) JDBC (三)](/default/index/img?u=aHR0cHM6Ly9iYnNtYXguaWthZmFuLmNvbS9zdGF0aWMvTDNCeWIzaDVMMmgwZEhCekwybHRZV2RsY3pJd01UY3VZMjVpYkc5bmN5NWpiMjB2WW14dlp5OHhNVEk0T0RBMEx6SXdNVGN4TVM4eE1USTRPREEwTFRJd01UY3hNVEV4TURZMU16VTVPVFV3TFRFeE5ERXlORGN6TURJdWFuQm4uanBn)
- 缺点:用户每次请求都需要向数据库获取连接,而数据库创建连接通常需要消耗相对较大的资源,创建的时间较长。如果一个网站一天访问量是10万,数据库服务器就需要创建至少10万个连接,极大的浪费数据库的资源,并且非常容易造成数据库服务器内存溢出、宕机等。
![JDBC (三) JDBC (三)](/default/index/img?u=aHR0cHM6Ly9iYnNtYXguaWthZmFuLmNvbS9zdGF0aWMvTDNCeWIzaDVMMmgwZEhCekwybHRZV2RsY3pJd01UY3VZMjVpYkc5bmN5NWpiMjB2WW14dlp5OHhNVEk0T0RBMEx6SXdNVGN4TVM4eE1USTRPREEwTFRJd01UY3hNVEV4TURjd01qQXdOamcwTFRRME9UQTRNREV5TUM1cWNHYz0uanBn)
- 使用数据库连接池的优点:可以大大的降低对数据库的连接、关闭,提高了系统的性能。
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/jdbc
jdbc.user=root
jdbc.password=root
package util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* 2017/11/8
* 说明:JDBC的工具类
*/
public class JDBCUtil {
private static String driverClass;
private static String url;
private static String user;
private static String password;
static {
InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pro = new Properties();
try {
pro.load(is);
driverClass = pro.getProperty("jdbc.driver");
url= pro.getProperty("jdbc.url");
user=pro.getProperty("jdbc.user");
password=pro.getProperty("jdbc.password");
Class.forName(driverClass);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
/**
* 获取数据库连接
* @return
* @throws ClassNotFoundException
* @throws SQLException
*/
public static Connection getConnection() throws ClassNotFoundException, SQLException {
//2 获取数据库的连接
Connection conn = DriverManager.getConnection(url,user,password);
return conn;
}
/**
* 释放资源
* @param rs
* @param stmt
* @param conn
*/
public static void release(ResultSet rs, Statement stmt ,Connection conn){
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
package util;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
* 2017/11/11
* 说明:模拟数据库连接池
*/
public class ConnectionPoolDemo {
private static List<Connection> pool = new ArrayList<>();
static {
for(int x =0;x<10;x++){
try {
Connection conn = JDBCUtil.getConnection();
pool.add(conn);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 从连接池中获取一个连接
* @return
*/
public synchronized static Connection getConnection(){
if(pool.size() >0){
return pool.remove(0);
}else{
throw new RuntimeException("服务器很忙");
}
}
/**
* 释放资源,就是将连接放到连接池里面
* @param conn
*/
public static void close(Connection conn){
pool.add(conn);
}
}
2 编写数据源
- 需要实现javax.sql.DataSource接口。
package util;
import javax.sql.DataSource;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.logging.Logger;
/**
* 2017/11/11
* 说明:
*/
public class MyDataSource implements DataSource {
private static List<Connection> pool = Collections.synchronizedList(new ArrayList<>());
static {
for(int x =0;x<10;x++){
try {
Connection conn = JDBCUtil.getConnection();
pool.add(conn);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
@Override
public Connection getConnection() throws SQLException {
if(pool.size() >0){
return pool.remove(0);
}else{
throw new RuntimeException("服务器很忙");
}
}
@Override
public Connection getConnection(String username, String password) throws SQLException {
return null;
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
return null;
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return false;
}
@Override
public PrintWriter getLogWriter() throws SQLException {
return null;
}
@Override
public void setLogWriter(PrintWriter out) throws SQLException {
}
@Override
public void setLoginTimeout(int seconds) throws SQLException {
}
@Override
public int getLoginTimeout() throws SQLException {
return 0;
}
@Override
public Logger getParentLogger() throws SQLFeatureNotSupportedException {
return null;
}
}
- 示例:使用自定义数据源--MyDataSouceTest.java
package util;
import java.sql.Connection;
import java.sql.SQLException;
/**
* 2017/11/11
* 说明:
*/
public class MyDataSourceTest {
private MyDataSource myDataSource;
public MyDataSourceTest(MyDataSource myDataSource){
this.myDataSource = myDataSource;
}
public void save() throws SQLException {
Connection conn = myDataSource.getConnection();
//操作的逻辑
conn.close();//但是当用户调用Collection的close方法的时候,我们却发现将连接给关闭了,不好
}
}
- 缺点:使用自定义数据源,当用户调用Collection的close()方法的时候,是将连接给关闭了,这样违背了我们设计数据源的初衷。换句话说,用户得到Connection的实现是:是数据库驱动对Connection接口的实现。所以,调用的close()方法都是数据库驱动的,它会把连接关闭的,恰恰相反,这却不是我们所想要的,我们想要的是当用户调用close()方法的时候,是将连接放回到连接池里。
- 解决方案:
- ①继承:不行
- 原因:
- a)如果继承,我们需要继承很多驱动的实现类,很麻烦。
- b)数据库驱动对Connection接口的实现类不允许继承。
- 3)丢失了原有对象的信息。
- ②装饰(包装、静态代理)设计模式
- 原因:保证被保证对象的原有信息,又可以对某个/某些方法进行改写。
- 步骤:
- a)编写一个类,实现与被包装类同样的接口。
- b)定义一个变量,引用被保证类的实例。
- c)定义构造方法,传入被包装类的实例。
- d)对于要改写的方法,编写自己所需要的代码。
- e)对于不需要改写的方法,调用原有哦对象的对应方法。
3 装饰设计模式
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/jdbc
jdbc.user=root
jdbc.password=root
package util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* 2017/11/8
* 说明:JDBC的工具类
*/
public class JDBCUtil {
private static String driverClass;
private static String url;
private static String user;
private static String password;
static {
InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pro = new Properties();
try {
pro.load(is);
driverClass = pro.getProperty("jdbc.driver");
url= pro.getProperty("jdbc.url");
user=pro.getProperty("jdbc.user");
password=pro.getProperty("jdbc.password");
Class.forName(driverClass);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
/**
* 获取数据库连接
* @return
* @throws ClassNotFoundException
* @throws SQLException
*/
public static Connection getConnection() throws ClassNotFoundException, SQLException {
//2 获取数据库的连接
Connection conn = DriverManager.getConnection(url,user,password);
return conn;
}
/**
* 释放资源
* @param rs
* @param stmt
* @param conn
*/
public static void release(ResultSet rs, Statement stmt ,Connection conn){
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
package util;
import java.sql.*;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.Executor;
/**
* 2017/11/11
* 说明:对Connection的实现类进行静态代理
*/
public class MyConnection implements Connection {
private Connection conn;
private List<Connection> pool;
public MyConnection(Connection conn,List<Connection> pool){
this.conn = conn;
this.pool = pool;
}
@Override
public void close() throws SQLException {
pool.add(conn);
}
@Override
public Statement createStatement() throws SQLException {
return conn.createStatement();
}
@Override
public PreparedStatement prepareStatement(String sql) throws SQLException {
return conn.prepareStatement(sql);
}
@Override
public CallableStatement prepareCall(String sql) throws SQLException {
return conn.prepareCall(sql);
}
@Override
public String nativeSQL(String sql) throws SQLException {
return conn.nativeSQL(sql);
}
@Override
public void setAutoCommit(boolean autoCommit) throws SQLException {
conn.setAutoCommit(autoCommit);
}
@Override
public boolean getAutoCommit() throws SQLException {
return conn.getAutoCommit();
}
@Override
public void commit() throws SQLException {
conn.commit();
}
@Override
public void rollback() throws SQLException {
conn.rollback();
}
@Override
public boolean isClosed() throws SQLException {
return conn.isClosed();
}
@Override
public DatabaseMetaData getMetaData() throws SQLException {
return conn.getMetaData();
}
@Override
public void setReadOnly(boolean readOnly) throws SQLException {
conn.setReadOnly(readOnly);
}
@Override
public boolean isReadOnly() throws SQLException {
return conn.isReadOnly();
}
@Override
public void setCatalog(String catalog) throws SQLException {
conn.setCatalog(catalog);
}
@Override
public String getCatalog() throws SQLException {
return conn.getCatalog();
}
@Override
public void setTransactionIsolation(int level) throws SQLException {
conn.setTransactionIsolation(level);
}
@Override
public int getTransactionIsolation() throws SQLException {
return conn.getTransactionIsolation();
}
@Override
public SQLWarning getWarnings() throws SQLException {
return conn.getWarnings();
}
@Override
public void clearWarnings() throws SQLException {
conn.clearWarnings();
}
@Override
public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {
return conn.createStatement(resultSetType,resultSetConcurrency);
}
@Override
public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
return conn.prepareStatement(sql,resultSetType,resultSetConcurrency);
}
@Override
public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
return conn.prepareCall(sql,resultSetType,resultSetConcurrency);
}
@Override
public Map<String, Class<?>> getTypeMap() throws SQLException {
return conn.getTypeMap();
}
@Override
public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
conn.setTypeMap(map);
}
@Override
public void setHoldability(int holdability) throws SQLException {
conn.setHoldability(holdability);
}
@Override
public int getHoldability() throws SQLException {
return conn.getHoldability();
}
@Override
public Savepoint setSavepoint() throws SQLException {
return conn.setSavepoint();
}
@Override
public Savepoint setSavepoint(String name) throws SQLException {
return conn.setSavepoint(name);
}
@Override
public void rollback(Savepoint savepoint) throws SQLException {
conn.rollback(savepoint);
}
@Override
public void releaseSavepoint(Savepoint savepoint) throws SQLException {
conn.releaseSavepoint(savepoint);
}
@Override
public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
return conn.createStatement(resultSetType,resultSetConcurrency,resultSetHoldability);
}
@Override
public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
return conn.prepareStatement(sql,resultSetType,resultSetConcurrency,resultSetHoldability);
}
@Override
public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
return conn.prepareCall(sql,resultSetType,resultSetConcurrency,resultSetHoldability);
}
@Override
public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {
return conn.prepareStatement(sql,autoGeneratedKeys);
}
@Override
public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {
return conn.prepareStatement(sql,columnIndexes);
}
@Override
public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {
return conn.prepareStatement(sql,columnNames);
}
@Override
public Clob createClob() throws SQLException {
return conn.createClob();
}
@Override
public Blob createBlob() throws SQLException {
return conn.createBlob();
}
@Override
public NClob createNClob() throws SQLException {
return conn.createNClob();
}
@Override
public SQLXML createSQLXML() throws SQLException {
return conn.createSQLXML();
}
@Override
public boolean isValid(int timeout) throws SQLException {
return conn.isValid(timeout);
}
@Override
public void setClientInfo(String name, String value) throws SQLClientInfoException {
conn.setClientInfo(name,value);
}
@Override
public void setClientInfo(Properties properties) throws SQLClientInfoException {
conn.setClientInfo(properties);
}
@Override
public String getClientInfo(String name) throws SQLException {
return conn.getClientInfo(name);
}
@Override
public Properties getClientInfo() throws SQLException {
return conn.getClientInfo();
}
@Override
public Array createArrayOf(String typeName, Object[] elements) throws SQLException {
return conn.createArrayOf(typeName,elements);
}
@Override
public Struct createStruct(String typeName, Object[] attributes) throws SQLException {
return conn.createStruct(typeName,attributes);
}
@Override
public void setSchema(String schema) throws SQLException {
conn.setSchema(schema);
}
@Override
public String getSchema() throws SQLException {
return conn.getSchema();
}
@Override
public void abort(Executor executor) throws SQLException {
conn.abort(executor);
}
@Override
public void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException {
conn.setNetworkTimeout(executor,milliseconds);
}
@Override
public int getNetworkTimeout() throws SQLException {
return conn.getNetworkTimeout();
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
return conn.unwrap(iface);
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return conn.isWrapperFor(iface);
}
}
package util;
import javax.sql.DataSource;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.logging.Logger;
/**
* 2017/11/11
* 说明:
*/
public class MyDataSource implements DataSource {
private static List<Connection> pool = Collections.synchronizedList(new ArrayList<>());
static {
for(int x =0;x<10;x++){
try {
Connection conn = JDBCUtil.getConnection();
pool.add(conn);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
@Override
public Connection getConnection() throws SQLException {
if(pool.size() >0){
return new MyConnection(pool.remove(0),pool);
}else{
throw new RuntimeException("服务器很忙");
}
}
@Override
public Connection getConnection(String username, String password) throws SQLException {
return null;
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
return null;
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return false;
}
@Override
public PrintWriter getLogWriter() throws SQLException {
return null;
}
@Override
public void setLogWriter(PrintWriter out) throws SQLException {
}
@Override
public void setLoginTimeout(int seconds) throws SQLException {
}
@Override
public int getLoginTimeout() throws SQLException {
return 0;
}
@Override
public Logger getParentLogger() throws SQLFeatureNotSupportedException {
return null;
}
}
4 动态代理
4.1 基于接口的动态代理
package com;
public interface Human {
/**
* 跳舞
* @param money
*/
public void dance(float money);
/**
* 唱歌
* @param money
*/
public void sing(float money);
}
package com;
public class SpringBrother implements Human {
@Override
public void dance(float money) {
System.out.println("拿到"+money+"元,跳舞");
}
@Override
public void sing(float money) {
System.out.println("拿到"+money+"元,唱歌");
}
}
package com;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.util.Arrays;
public class Client1 {
public static void main(String[] args){
final Human sb = new SpringBrother();
/**
* 通过声明和SpringBrother拥有相同的类加载器,以及实现相同的接口,那么此类就声明是代理类
*/
Human proxy = (Human) Proxy.newProxyInstance(sb.getClass().getClassLoader(), sb.getClass().getInterfaces(), new InvocationHandler() {
/**
*
* @param proxy 代理对象的引用
* @param method 当前执行的方法
* @param args 当前方法执行的参数
* @return
* @throws Throwable
*/
@Override
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
if("sing".equals(method.getName())){
//唱歌
float money = (float) args[0];
if(money >=10000){
return method.invoke(sb,money /2);
}
}
if("dance".equals(method.getName())){
//跳舞
float money = (float) args[0];
if(money > 20000){
return method.invoke(sb,args);
}
}
return null;
}
});
proxy.sing(10000);
proxy.dance(200);
}
}
- 示例:使用动态代理实现自定义数据源(链接池)
- jdbc.properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/jdbc
jdbc.user=root
jdbc.password=root
package com.util;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JDBCUtil {
private static String driverClass;
private static String url;
private static String user;
private static String password;
static {
InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pro = new Properties();
try {
pro.load(is);
driverClass = pro.getProperty("jdbc.driver");
url= pro.getProperty("jdbc.url");
user=pro.getProperty("jdbc.user");
password=pro.getProperty("jdbc.password");
Class.forName(driverClass);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
/**
* 获取数据库连接
* @return
* @throws ClassNotFoundException
* @throws SQLException
*/
public static Connection getConnection() throws ClassNotFoundException, SQLException {
//2 获取数据库的连接
Connection conn = DriverManager.getConnection(url,user,password);
return conn;
}
/**
* 释放资源
* @param rs
* @param stmt
* @param conn
*/
public static void release(ResultSet rs, Statement stmt , Connection conn){
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
package com;
import com.util.JDBCUtil;
import javax.sql.DataSource;
import java.io.PrintWriter;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.logging.Logger;
public class MyDataSource implements DataSource {
private static List<Connection> pool = Collections.synchronizedList(new ArrayList<>());
static {
for(int x =0;x<10;x++){
try {
Connection conn = JDBCUtil.getConnection();
pool.add(conn);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
@Override
public Connection getConnection() throws SQLException {
if(pool.size() >0){
Connection conn = pool.remove(0);
Connection proxy = (Connection) Proxy.newProxyInstance(conn.getClass().getClassLoader(), conn.getClass().getInterfaces(), new InvocationHandler() {
@Override
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
if(method.getName().equals("close")){
//放到池子里
pool.add(conn);
}
return method.invoke(conn,args);
}
});
return proxy;
}else{
throw new RuntimeException("服务器很忙");
}
}
@Override
public Connection getConnection(String username, String password) throws SQLException {
return null;
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
return null;
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return false;
}
@Override
public PrintWriter getLogWriter() throws SQLException {
return null;
}
@Override
public void setLogWriter(PrintWriter out) throws SQLException {
}
@Override
public void setLoginTimeout(int seconds) throws SQLException {
}
@Override
public int getLoginTimeout() throws SQLException {
return 0;
}
@Override
public Logger getParentLogger() throws SQLFeatureNotSupportedException {
return null;
}
}
4.2 基于子类的动态代理 (CGLIB)
package com;
public class SpringBrother {
public void dance(float money) {
System.out.println("拿到"+money+"元,跳舞");
}
public void sing(float money) {
System.out.println("拿到"+money+"元,唱歌");
}
}
package com;
import net.sf.cglib.proxy.Enhancer;
import net.sf.cglib.proxy.MethodInterceptor;
import net.sf.cglib.proxy.MethodProxy;
import java.lang.reflect.Method;
public class Client2 {
public static void main(String[] args){
SpringBrother sb = new SpringBrother();
SpringBrother proxy = (SpringBrother) Enhancer.create(sb.getClass(), new MethodInterceptor() {
@Override
public Object intercept(Object o, Method method, Object[] objects, MethodProxy methodProxy) throws Throwable {
if(method.getName().equals("dance")){
float money = (float) objects[0];
if(money >= 10000){
method.invoke(sb,objects);
}
}
if(method.getName().equals("sing")){
float money = (float) objects[0];
if(money >= 20000){
method.invoke(sb,objects);
}
}
return null;
}
});
proxy.dance(10000);
proxy.sing(25000);
}
}
5 开源数据源的使用
5.1 DBCP
commons-dbcp-1.4.jar
commons-pool-1.5.6.jar
- ②导入配置文件 dbcpconfig.properties
#\u8FDE\u63A5\u8BBE\u7F6E
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
username=root
password=root
initialSize=10
maxActive=50
maxIdle=20
minIdle=5
maxWait=60000
connectionProperties=useUnicode=true;characterEncoding=utf8
defaultAutoCommit=true
defaultReadOnly=
defaultTransactionIsolation=REPEATABLE_READ
package com.util;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
public class DBCPUtil {
private static DataSource dataSource;
static{
try {
InputStream in = DBCPUtil.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
Properties pro = new Properties();
pro.load(in);
dataSource = BasicDataSourceFactory.createDataSource(pro);
}catch (Exception e){
throw new RuntimeException(e);
}
}
public static DataSource getDataSource(){
return dataSource;
}
public static Connection getConnection(){
try {
return dataSource.getConnection();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
5.2 C3P0
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql:///test</property>
<property name="user">root</property>
<property name="password">root</property>
<property name="initialPoolSize">10</property>
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">100</property>
<property name="minPoolSize">10</property>
<property name="maxStatements">200</property>
</default-config>
</c3p0-config>
package com.util;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
public class C3P0util {
private static ComboPooledDataSource dataSource = new ComboPooledDataSource();
public static DataSource getDataSource(){
return dataSource;
}
public static Connection getConnection(){
try {
return dataSource.getConnection();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
package com.test;
import com.util.C3P0util;
import java.sql.Connection;
import java.sql.SQLException;
public class Test {
public static void main(String[] args){
Connection conn = C3P0util.getConnection();
System.out.println(conn);
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
6 编写自己的JDBC框架
6.1 数据库元信息的获取
- Connection中的方法:获取DatabaseMeta对象包含元数据的数据库,
DatabaseMetaData getMetaData() throws SQLException
package com;
import com.util.C3P0util;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
public class DatabaseMetaDataDemo {
public static void main(String[] args) throws SQLException {
Connection conn = C3P0util.getConnection();
DatabaseMetaData databaseMetaData = conn.getMetaData();
System.out.println("DatabaseMetaData对象"+databaseMetaData);
System.out.println("数据库的连接:"+databaseMetaData.getURL());
System.out.println("当前连接数据库管理系统的用户名:"+databaseMetaData.getUserName());
System.out.println("数据库的产品名称:"+databaseMetaData.getDatabaseProductName());
System.out.println("数据库的版本号:"+databaseMetaData.getDatabaseProductVersion());
System.out.println("驱动程序的名称:"+databaseMetaData.getDriverName());
System.out.println("驱动程序的版本号:"+databaseMetaData.getDriverVersion());
System.out.println("数据库是否可读:"+databaseMetaData.isReadOnly()); conn.close();
}
}
- PreparedStatement中的方法:获取参数的的类型和属性的标记信息
ParameterMetaData getParameterMetaData() throws SQLException
package com;
import com.util.C3P0util;
import java.sql.*;
public class DatabaseMetaDataDemo {
public static void main(String[] args) throws SQLException {
Connection conn = C3P0util.getConnection();
PreparedStatement psmt = conn.prepareStatement("select * from user where name = ? and password = ?");
psmt.setString(1,"aa");
psmt.setString(2,"bb");
ParameterMetaData parameterMetaData = psmt.getParameterMetaData();
System.out.println("参数的个数:"+parameterMetaData.getParameterCount());
pstmt.close(); conn.close();
}
}
- PreparedStatement中的方法:获取ResultSet对象列的类型和属性信息的对象。
ResultSetMetaData getMetaData() throws SQLException
package com;
import com.util.C3P0util;
import java.sql.*;
public class DatabaseMetaDataDemo {
public static void main(String[] args) throws SQLException {
Connection conn = C3P0util.getConnection();
PreparedStatement psmt = conn.prepareStatement("select * from user ");
ResultSet rs = psmt.executeQuery();
ResultSetMetaData resultSetMetaData = rs.getMetaData();
int count = resultSetMetaData.getColumnCount();
System.out.println("结果集的列的数量:"+count);
for(int x = 0;x<count;x++){
String columnName = resultSetMetaData.getCatalogName(x+1);
int columnType = resultSetMetaData.getColumnType(x+1);
System.out.println("列的名称:"+columnName+",列的类型:"+columnType);
}
rs.close();
psmt.close();
conn.close();
}
}
6.2 编写自己的JDBC框架
package com.util;
import java.sql.ResultSet;
public interface ResultSetHandler {
public Object handle(ResultSet rs);
}
package com;
import com.util.ResultSetHandler;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class MyDbutil {
private DataSource dataSource;
public MyDbutil(DataSource dataSource){
this.dataSource = dataSource;
}
/**
* 执行更新方法
* @param sql 插入的sql
* @param params 参数
*/
public void update(String sql,Object...params){
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = dataSource.getConnection();
pstmt = conn.prepareStatement(sql);
//获取sql中的占位符数量
int paramCounts = pstmt.getParameterMetaData().getParameterCount();
if(paramCounts >0){
if(params == null){
throw new RuntimeException("传入的参数为空");
}
if(params.length != paramCounts){
throw new RuntimeException("传入的参数的长度和占位符的个数不匹配");
}
for(int x =0;x<paramCounts;x++){
pstmt.setObject((x+1),params[x]);
}
}
int count = pstmt.executeUpdate();
System.out.println("更新的记录数:"+count);
}catch (Exception e){
throw new RuntimeException(e);
}finally {
close(conn,pstmt,null);
}
}
public Object query(String sql,ResultSetHandler rsh,Object...params){
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
conn = dataSource.getConnection();
pstmt= conn.prepareStatement(sql);
//得到sql中的占位符的个数
int paramCounts = pstmt.getParameterMetaData().getParameterCount();
if(paramCounts > 0){
if(params == null){
throw new RuntimeException("传入的参数为空");
}
if(params.length != paramCounts){
throw new RuntimeException("传入的参数的长度和占位符的个数不匹配");
}
for(int x =0;x<paramCounts;x++){
pstmt.setObject((x+1),params[x]);
}
}
rs = pstmt.executeQuery();
return rsh.handle(rs);
}catch (Exception e){
throw new RuntimeException(e);
}finally {
close(conn,pstmt,rs);
}
}
/**
* 释放资源
* @param conn
* @param pstmt
* @param rs
*/
private void close(Connection conn, PreparedStatement pstmt, ResultSet rs) {
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(pstmt != null){
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}