网站导航条专门做页面跳转,长沙市建设网站平台的公司,网站建设客户来源,asp. net 做网站数据库连接池的概念 数据库连接是一种关键的、有限的、昂贵的资源#xff0c;这一点在多用户的网页应用程序中体现得尤为突出。对数据库连接的管理能显著影响到整个应用程序的伸缩性和健壮性#xff0c;影响到程序的性能指标。数据库连接池正是针对这个问题提出来的。数据库连…数据库连接池的概念 数据库连接是一种关键的、有限的、昂贵的资源这一点在多用户的网页应用程序中体现得尤为突出。对数据库连接的管理能显著影响到整个应用程序的伸缩性和健壮性影响到程序的性能指标。数据库连接池正是针对这个问题提出来的。数据库连接池负责分配、管理和释放数据库连接它允许应用程序重复使用一个现有的数据库连接而不是再重新建立一个。这项技术能明显提高对数据库操作的性能。 自定义连接池
java.sql.DataSource接口数据源(数据库连接池)。java官方提供的数据库连接池规范(接口) 获取数据库连接对象Connection getConnection();
工具类
package com.dataSource;import java.io.InputStream;
import java.sql.*;
import java.util.Properties;public class JDBCUtils {/*** 1.私有构造方法*/private JDBCUtils() {}// 2.声明配置信息变量private static String driverClass;private static String url;private static String username;private static String password;private static Connection con;// 3.静态代码块中实现加载配置文件和注册驱动static {try {// 通过类加载器返回配置文件的字节流// getClassLoader().getResourceAsStream:通过给定名称查找资源查询资源的规则由给定的类的class load来实现这个方法由类的loader来执行如果这个类由bootstrap加载那么方法由ClassLoader.getSystemResourceAsStream代理执行。InputStream is JDBCUtils.class.getClassLoader().getResourceAsStream(config.properties);// 创建Properties集合加载流对象的信息Properties prop new Properties();prop.load(is);// 获取信息为变量赋值driverClass prop.getProperty(driverClass);url prop.getProperty(url);username prop.getProperty(username);password prop.getProperty(password);// 注册驱动Class.forName(driverClass);} catch (Exception e) {e.printStackTrace();}}//4.获取数据库连接的方法public static Connection getConnection() {try {con DriverManager.getConnection(url, username, password);} catch (SQLException e) {e.printStackTrace();}return con;}//5.释放资源的方法public static void close(Connection con, Statement stat, ResultSet rs) {if (con ! null) {try {con.close();} catch (SQLException e) {e.printStackTrace();}}if (stat ! null) {try {stat.close();} catch (SQLException e) {e.printStackTrace();}}if (rs ! null) {try {rs.close();} catch (SQLException e) {e.printStackTrace();}}}public static void close(Connection con, Statement stat) {close(con, stat, null);}
}自定义连接池
package com.dataSource;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;public class MyDataSource implements DataSource {// 定义集合容器用于保存多个数据库连接对象private static final ListConnection POOP Collections.synchronizedList(new ArrayList());// 静态代码块生成10个数据库连接保存到集合中static {for (int i 0; i 10; i) {Connection coon JDBCUtils.getConnection();POOP.add(coon);}}Overridepublic Connection getConnection() throws SQLException {if (POOP.size() 0) {Connection remove POOP.remove(0);return remove;} else {throw new RuntimeException(连接数量耗尽);}}/**定义getSize方法获取连接池容器的大小*/public int getSize() {return POOP.size();}Overridepublic Connection getConnection(String username, String password) throws SQLException {return null;}Overridepublic PrintWriter getLogWriter() throws SQLException {return null;}Overridepublic void setLogWriter(PrintWriter out) throws SQLException {}Overridepublic void setLoginTimeout(int seconds) throws SQLException {}Overridepublic int getLoginTimeout() throws SQLException {return 0;}Overridepublic Logger getParentLogger() throws SQLFeatureNotSupportedException {return null;}Overridepublic T T unwrap(ClassT iface) throws SQLException {return null;}Overridepublic boolean isWrapperFor(Class? iface) throws SQLException {return false;}
}测试类
package com.dataSource;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;public class DataSourceTest {public static void main(String[] args) throws SQLException {// 创建连接池对象MyDataSource dataSource new MyDataSource();System.out.println(使用之前连接池数量 dataSource.getSize());// 通过连接池对象获取连接对象Connection conn dataSource.getConnection();// 查看DriverManager连接的实现类发现是JDBC4ConnectionSystem.out.println(conn.getClass());// 查询学生表的全部信息String sql SELECT * FROM student;PreparedStatement ps conn.prepareStatement(sql);// 执行sql语句接收结果集ResultSet rs ps.executeQuery();while (rs.next()) {System.out.println(rs.getInt(sid) \t rs.getString(name) \t rs.getInt(age) \t rs.getDate(birthday));}// 释放资源conn.close();ps.close();rs.close();System.out.println(使用之后连接池数量 dataSource.getSize());}
}上面的代码是有问题的因为最后释放了资源以后通过查看使用后的连接池的数量发现没有归还操作那连接池存在就没有意义了连接池存在就是为了重复使用的。
连接池归还连接
归还连接_继承
通过打印连接对象发现DriverManager获取的连接实现类是JDBC4Connection。 自定义一个类继承JDBC4Connection这个类定义连接对象和连接池容器对象的成员变量通过有参构造对成员变量赋值重写close()方法将连接对象添加到池中 定义了子类完成了归还但是DriverManager获取的还是JDBC4Connection这个对象不是定义的子类所以继承搞不了 归还连接_装饰设计模式 自定义一个类实现Connection接口这样就和JDBC4Connection有相同的方法重写close方法剩余方法只需要调用mysql驱动包的连接对象完成即可但是这个设计模式需要重写很多的方法比较麻烦 自定义类连接池
package com.dataSource;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;public class MyDataSource implements DataSource {// 定义集合容器用于保存多个数据库连接对象private static final ListConnection pool Collections.synchronizedList(new ArrayList());// 静态代码块生成10个数据库连接保存到集合中static {for (int i 0; i 10; i) {Connection coon JDBCUtils.getConnection();pool.add(coon);}}// 从连接池返回一个数据库连接Overridepublic Connection getConnection() {if (pool.size() 0) {// 从池中获取数据库连接Connection con pool.remove(0);// 通过自定义连接对象进行包装MyConnection mycon new MyConnection(con, pool);// 返回包装后的连接对象return mycon;} else {throw new RuntimeException(连接数量耗尽);}}/**定义getSize方法获取连接池容器的大小*/public int getSize() {return pool.size();}Overridepublic Connection getConnection(String username, String password) throws SQLException {return null;}Overridepublic PrintWriter getLogWriter() throws SQLException {return null;}Overridepublic void setLogWriter(PrintWriter out) throws SQLException {}Overridepublic void setLoginTimeout(int seconds) throws SQLException {}Overridepublic int getLoginTimeout() throws SQLException {return 0;}Overridepublic Logger getParentLogger() throws SQLFeatureNotSupportedException {return null;}Overridepublic T T unwrap(ClassT iface) throws SQLException {return null;}Overridepublic boolean isWrapperFor(Class? iface) throws SQLException {return false;}
}自定义连接类
package com.dataSource;import java.sql.*;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.Executor;/*自定义Connection类。通过装饰设计模式实现和mysql驱动包中的Connection实现类相同的功能实现步骤1.定义一个类实现Connection接口2.定义Connection连接对象和连接池容器对象的变量3.提供有参构造方法接收连接对象和连接池对象对变量赋值4.在close()方法中完成连接的归还5.剩余方法还是只需要调用mysql驱动包的连接对象完成即可*/public class MyConnection implements Connection {private Connection con;private ListConnection pool;public MyConnection(Connection con, ListConnection pool) {this.con con;this.pool pool;}Overridepublic void close() throws SQLException {pool.add(con);}Overridepublic Statement createStatement() throws SQLException {return con.createStatement();}Overridepublic PreparedStatement prepareStatement(String sql) throws SQLException {return con.prepareStatement(sql);}Overridepublic CallableStatement prepareCall(String sql) throws SQLException {return con.prepareCall(sql);}Overridepublic String nativeSQL(String sql) throws SQLException {return con.nativeSQL(sql);}Overridepublic void setAutoCommit(boolean autoCommit) throws SQLException {con.setAutoCommit(autoCommit);}Overridepublic boolean getAutoCommit() throws SQLException {return con.getAutoCommit();}Overridepublic void commit() throws SQLException {con.commit();}Overridepublic void rollback() throws SQLException {con.rollback();}Overridepublic boolean isClosed() throws SQLException {return con.isClosed();}Overridepublic DatabaseMetaData getMetaData() throws SQLException {return con.getMetaData();}Overridepublic void setReadOnly(boolean readOnly) throws SQLException {con.setReadOnly(readOnly);}Overridepublic boolean isReadOnly() throws SQLException {return con.isReadOnly();}Overridepublic void setCatalog(String catalog) throws SQLException {con.setCatalog(catalog);}Overridepublic String getCatalog() throws SQLException {return con.getCatalog();}Overridepublic void setTransactionIsolation(int level) throws SQLException {con.setTransactionIsolation(level);}Overridepublic int getTransactionIsolation() throws SQLException {return con.getTransactionIsolation();}Overridepublic SQLWarning getWarnings() throws SQLException {return con.getWarnings();}Overridepublic void clearWarnings() throws SQLException {con.clearWarnings();}Overridepublic Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {return con.createStatement(resultSetType,resultSetConcurrency);}Overridepublic PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {return con.prepareStatement(sql,resultSetType,resultSetConcurrency);}Overridepublic CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {return con.prepareCall(sql,resultSetType,resultSetConcurrency);}Overridepublic MapString, Class? getTypeMap() throws SQLException {return con.getTypeMap();}Overridepublic void setTypeMap(MapString, Class? map) throws SQLException {con.setTypeMap(map);}Overridepublic void setHoldability(int holdability) throws SQLException {con.setHoldability(holdability);}Overridepublic int getHoldability() throws SQLException {return con.getHoldability();}Overridepublic Savepoint setSavepoint() throws SQLException {return con.setSavepoint();}Overridepublic Savepoint setSavepoint(String name) throws SQLException {return con.setSavepoint(name);}Overridepublic void rollback(Savepoint savepoint) throws SQLException {con.rollback(savepoint);}Overridepublic void releaseSavepoint(Savepoint savepoint) throws SQLException {con.releaseSavepoint(savepoint);}Overridepublic Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {return con.createStatement(resultSetType,resultSetConcurrency,resultSetHoldability);}Overridepublic PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {return con.prepareStatement(sql,resultSetType,resultSetConcurrency,resultSetHoldability);}Overridepublic CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {return con.prepareCall(sql,resultSetType,resultSetConcurrency,resultSetHoldability);}Overridepublic PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {return con.prepareStatement(sql,autoGeneratedKeys);}Overridepublic PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {return con.prepareStatement(sql,columnIndexes);}Overridepublic PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {return con.prepareStatement(sql,columnNames);}Overridepublic Clob createClob() throws SQLException {return con.createClob();}Overridepublic Blob createBlob() throws SQLException {return con.createBlob();}Overridepublic NClob createNClob() throws SQLException {return con.createNClob();}Overridepublic SQLXML createSQLXML() throws SQLException {return con.createSQLXML();}Overridepublic boolean isValid(int timeout) throws SQLException {return con.isValid(timeout);}Overridepublic void setClientInfo(String name, String value) throws SQLClientInfoException {con.setClientInfo(name,value);}Overridepublic void setClientInfo(Properties properties) throws SQLClientInfoException {con.setClientInfo(properties);}Overridepublic String getClientInfo(String name) throws SQLException {return con.getClientInfo(name);}Overridepublic Properties getClientInfo() throws SQLException {return con.getClientInfo();}Overridepublic Array createArrayOf(String typeName, Object[] elements) throws SQLException {return con.createArrayOf(typeName,elements);}Overridepublic Struct createStruct(String typeName, Object[] attributes) throws SQLException {return con.createStruct(typeName,attributes);}Overridepublic void setSchema(String schema) throws SQLException {con.setSchema(schema);}Overridepublic String getSchema() throws SQLException {return con.getSchema();}Overridepublic void abort(Executor executor) throws SQLException {con.abort(executor);}Overridepublic void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException {con.setNetworkTimeout(executor,milliseconds);}Overridepublic int getNetworkTimeout() throws SQLException {return con.getNetworkTimeout();}Overridepublic T T unwrap(ClassT iface) throws SQLException {return con.unwrap(iface);}Overridepublic boolean isWrapperFor(Class? iface) throws SQLException {return con.isWrapperFor(iface);}
}归还连接_适配器设计模式 通过装饰设计模式有很多个需要实现的方法。这个时候就可以使用适配器设计模式了。提供一个适配器类实现Connection接口将所有功能进行实现(除了close方法)。自定义连接类只需要继承这个适配器类重写需要改进的close()方法即可 实现步骤 定义一个适配器类实现Connection接口定义Connection连接对象的成员变量通过有参构造方法完成对成员变量的赋值重写所有方法除close调用mysql驱动包的连接对象完成定义一个类继承适配器父类定义Connection连接对象和连接池容器对象的变量通过有参构造进行赋值重写close()方法中完成归还连接在自定义连接池中将获取的连接对象通过自定义连接对象进行包装 适配器类
package com.dataSource;import java.sql.*;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.Executor;/* 定义一个适配器类实现Connection接口定义Connection连接对象的成员变量通过有参构造方法完成对成员变量的赋值重写所有方法除close调用mysql驱动包的连接对象完成
*/public abstract class MyAdapter implements Connection {// 因为所有的方法还是要调用要有的连接对象所以需要有参把原有的对象接收进来// 一般适配器类都是抽象类可以有抽象方法也可以有普通方法和构造private Connection con;public MyAdapter(Connection con) {this.con con;}Overridepublic Statement createStatement() throws SQLException {return con.createStatement();}Overridepublic PreparedStatement prepareStatement(String sql) throws SQLException {return con.prepareStatement(sql);}Overridepublic CallableStatement prepareCall(String sql) throws SQLException {return con.prepareCall(sql);}Overridepublic String nativeSQL(String sql) throws SQLException {return con.nativeSQL(sql);}Overridepublic void setAutoCommit(boolean autoCommit) throws SQLException {con.setAutoCommit(autoCommit);}Overridepublic boolean getAutoCommit() throws SQLException {return con.getAutoCommit();}Overridepublic void commit() throws SQLException {con.commit();}Overridepublic void rollback() throws SQLException {con.rollback();}Overridepublic boolean isClosed() throws SQLException {return con.isClosed();}Overridepublic DatabaseMetaData getMetaData() throws SQLException {return con.getMetaData();}Overridepublic void setReadOnly(boolean readOnly) throws SQLException {con.setReadOnly(readOnly);}Overridepublic boolean isReadOnly() throws SQLException {return con.isReadOnly();}Overridepublic void setCatalog(String catalog) throws SQLException {con.setCatalog(catalog);}Overridepublic String getCatalog() throws SQLException {return con.getCatalog();}Overridepublic void setTransactionIsolation(int level) throws SQLException {con.setTransactionIsolation(level);}Overridepublic int getTransactionIsolation() throws SQLException {return con.getTransactionIsolation();}Overridepublic SQLWarning getWarnings() throws SQLException {return con.getWarnings();}Overridepublic void clearWarnings() throws SQLException {con.clearWarnings();}Overridepublic Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {return con.createStatement(resultSetType, resultSetConcurrency);}Overridepublic PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {return con.prepareStatement(sql, resultSetType, resultSetConcurrency);}Overridepublic CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {return con.prepareCall(sql, resultSetType, resultSetConcurrency);}Overridepublic MapString, Class? getTypeMap() throws SQLException {return con.getTypeMap();}Overridepublic void setTypeMap(MapString, Class? map) throws SQLException {con.setTypeMap(map);}Overridepublic void setHoldability(int holdability) throws SQLException {con.setHoldability(holdability);}Overridepublic int getHoldability() throws SQLException {return con.getHoldability();}Overridepublic Savepoint setSavepoint() throws SQLException {return con.setSavepoint();}Overridepublic Savepoint setSavepoint(String name) throws SQLException {return con.setSavepoint(name);}Overridepublic void rollback(Savepoint savepoint) throws SQLException {con.rollback(savepoint);}Overridepublic void releaseSavepoint(Savepoint savepoint) throws SQLException {con.releaseSavepoint(savepoint);}Overridepublic Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {return con.createStatement(resultSetType, resultSetConcurrency, resultSetHoldability);}Overridepublic PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {return con.prepareStatement(sql, resultSetType, resultSetConcurrency, resultSetHoldability);}Overridepublic CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {return con.prepareCall(sql, resultSetType, resultSetConcurrency, resultSetHoldability);}Overridepublic PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {return con.prepareStatement(sql, autoGeneratedKeys);}Overridepublic PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {return con.prepareStatement(sql, columnIndexes);}Overridepublic PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {return con.prepareStatement(sql, columnNames);}Overridepublic Clob createClob() throws SQLException {return con.createClob();}Overridepublic Blob createBlob() throws SQLException {return con.createBlob();}Overridepublic NClob createNClob() throws SQLException {return con.createNClob();}Overridepublic SQLXML createSQLXML() throws SQLException {return con.createSQLXML();}Overridepublic boolean isValid(int timeout) throws SQLException {return con.isValid(timeout);}Overridepublic void setClientInfo(String name, String value) throws SQLClientInfoException {con.setClientInfo(name, value);}Overridepublic void setClientInfo(Properties properties) throws SQLClientInfoException {con.setClientInfo(properties);}Overridepublic String getClientInfo(String name) throws SQLException {return con.getClientInfo(name);}Overridepublic Properties getClientInfo() throws SQLException {return con.getClientInfo();}Overridepublic Array createArrayOf(String typeName, Object[] elements) throws SQLException {return con.createArrayOf(typeName, elements);}Overridepublic Struct createStruct(String typeName, Object[] attributes) throws SQLException {return con.createStruct(typeName, attributes);}Overridepublic void setSchema(String schema) throws SQLException {con.setSchema(schema);}Overridepublic String getSchema() throws SQLException {return con.getSchema();}Overridepublic void abort(Executor executor) throws SQLException {con.abort(executor);}Overridepublic void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException {con.setNetworkTimeout(executor, milliseconds);}Overridepublic int getNetworkTimeout() throws SQLException {return con.getNetworkTimeout();}Overridepublic T T unwrap(ClassT iface) throws SQLException {return con.unwrap(iface);}Overridepublic boolean isWrapperFor(Class? iface) throws SQLException {return con.isWrapperFor(iface);}
}继承适配器类
public class MyConnection2 extends MyAdapter {private Connection con;private ListConnection pool;public MyConnection2(Connection con, ListConnection pool) {super(con);this.con con;this.pool pool;}Overridepublic void close() throws SQLException {pool.add(con);}
}自定义连接池类
public class MyDataSource implements DataSource {// 定义集合容器用于保存多个数据库连接对象private static final ListConnection pool Collections.synchronizedList(new ArrayList());// 静态代码块生成10个数据库连接保存到集合中static {for (int i 0; i 10; i) {Connection coon JDBCUtils.getConnection();pool.add(coon);}}// 从连接池返回一个数据库连接Overridepublic Connection getConnection() {if (pool.size() 0) {// 从池中获取数据库连接Connection con pool.remove(0);// 通过自定义连接对象进行包装MyConnection mycon new MyConnection(con, pool);// 返回包装后的连接对象return mycon;} else {throw new RuntimeException(连接数量耗尽);}}/**定义getSize方法获取连接池容器的大小*/public int getSize() {return pool.size();}Overridepublic Connection getConnection(String username, String password) throws SQLException {return null;}测试类
public class DataSourceTest {public static void main(String[] args) throws SQLException {// 创建连接池对象MyDataSource dataSource new MyDataSource();System.out.println(使用之前连接池数量 dataSource.getSize());// 通过连接池对象获取连接对象Connection conn dataSource.getConnection();// 查看DriverManager连接的实现类System.out.println(conn.getClass());// 查询学生表的全部信息String sql SELECT * FROM student;PreparedStatement ps conn.prepareStatement(sql);// 执行sql语句接收结果集ResultSet rs ps.executeQuery();while (rs.next()) {System.out.println(rs.getInt(sid) \t rs.getString(name) \t rs.getInt(age) \t rs.getDate(birthday));}// 释放资源conn.close();ps.close();rs.close();System.out.println(使用之后连接池数量 dataSource.getSize());}
}适配器模式也可以实现但是也很麻烦说白了就是把装饰设计模式拆分了而已
连接池归还_动态代理 经过适配器模式的改进自定义连接类中的方法已经很简洁了。剩余所有的方法已经抽取到了适配器类中。但是适配器这个类还是我们自己编写的也比较麻烦所以可以使用动态代理的方式来改进。 自定义数据库连接池类
public class MyDataSource implements DataSource{//定义集合容器用于保存多个数据库连接对象private static ListConnection pool Collections.synchronizedList(new ArrayListConnection());//静态代码块生成10个数据库连接保存到集合中static {for (int i 0; i 10; i) {Connection con JDBCUtils.getConnection();pool.add(con);}}//返回连接池的大小public int getSize() {return pool.size();}//动态代理方式Overridepublic Connection getConnection() {if(pool.size() 0) {//从池中获取数据库连接Connection con pool.remove(0);Connection proxyCon (Connection)Proxy.newProxyInstance(con.getClass().getClassLoader(), new Class[]{Connection.class}, new InvocationHandler() {/*执行Connection实现类所有方法都会经过invoke如果是close方法则将连接还回池中如果不是直接执行实现类的原有方法*/Overridepublic Object invoke(Object proxy, Method method, Object[] args) throws Throwable {if(method.getName().equals(close)) {pool.add(con);return null;}else {return method.invoke(con,args);}}});return proxyCon;}else {throw new RuntimeException(连接数量已用尽);}}
}C3P0 C3P0是一个开源的JDBC连接池简化了很多步骤 使用步骤 导入jar包导入配置文件到src目录下创建c3p0连接池对象获取数据库连接进行使用 注意C3p0的配置文件会自动加载但是必须叫c3p0-config.xml或c3p0-config.properties C3p0测试类
public class C3p0Test {public static void main(String[] args) throws Exception {// 这里是空参的话加载的就是默认的C3p0如果指定名称就可以用自定义的DataSource dataSource new ComboPooledDataSource();// 通过连接池对象获取数据库连接Connection con dataSource.getConnection();// 查询学生表的全部信息String sql SELECT * FROM student;PreparedStatement ps con.prepareStatement(sql);// 执行sql语句接收结果集ResultSet rs ps.executeQuery();while (rs.next()) {System.out.println(rs.getInt(sid) \t rs.getString(name) \t rs.getInt(age) \t rs.getDate(birthday));}// 释放资源con.close();ps.close();rs.close();}
}测试连接池归还情况
public class C3P0Test02 {public static void main(String[] args) throws Exception {DataSource dataSource new ComboPooledDataSource();// 定的是10个连接池如果是115个应该会在3秒以后去申请新的然后报错for (int i 0; i 15; i) {Connection con dataSource.getConnection();System.out.println(i con);// 执行到第六次的时候关闭如果处理了close 地址值肯定是有两个重复的if (i 6) {con.close();}}// 第6个的连接执行到的时候就关闭掉可以看到打印结果第6、7次的地址值是一样的有十一个地址值说明是做了归还处理}
}配置类c3p0-config.xml
c3p0-config!-- 使用默认的配置读取连接池对象 --default-config!-- 连接参数 --property namedriverClasscom.mysql.cj.jdbc.Driver/propertyproperty namejdbcUrljdbc:mysql://localhost/db2/propertyproperty nameuserroot/propertyproperty namepassworditzhuzhu/property!-- 连接池参数 --!--初始化的连接数量--property nameinitialPoolSize5/property!--最大连接数量 初始化为5最大10意思就是默认给5个不够再要但是最多10个--property namemaxPoolSize10/property!--超时时间 毫秒--property namecheckoutTimeout3000/property/default-config!--这里是和上面一样的配置但是c3p0默认的是上面这个也可以自己加name不指定的时候默认使用上面的--named-config nameotherc3p0 !-- 连接参数 --property namedriverClasscom.mysql.cj.jdbc.Driver/propertyproperty namejdbcUrljdbc:mysql://localhost:3306/db2/propertyproperty nameuserroot/propertyproperty namepassworditzhuzhu/property!-- 连接池参数 --property nameinitialPoolSize5/propertyproperty namemaxPoolSize8/propertyproperty namecheckoutTimeout1000/property/named-config
/c3p0-configDruid: 阿里开发的数据库连接池工具不会自动加载配置文件需要手动去读取文件名称可以随意取连接池也不会释放。 使用步骤 导入jar包编写配置文件放在src目录下通过Properties集合加载配置文件通过Druid连接池工厂类获取数据库连接池对象获取数据库连接进行使用 工具类
public class DataSourceUtils {//1.私有构造方法private DataSourceUtils() {}//2.定义DataSource数据源变量private static DataSource dataSource;//3.提供静态代码块完成配置文件的加载和获取连接池对象static {try {//加载配置文件InputStream is DataSourceUtils.class.getClassLoader().getResourceAsStream(druid.properties);Properties prop new Properties();prop.load(is);//获取数据库连接池对象dataSource DruidDataSourceFactory.createDataSource(prop);} catch (Exception e) {e.printStackTrace();}}//4.提供获取数据库连接的方法public static Connection getConnection() {Connection con null;try {con dataSource.getConnection();} catch (SQLException e) {e.printStackTrace();}return con;}//5.提供获取数据库连接池的方法public static DataSource getDataSource() {return dataSource;}//6.提供释放资源的方法public static void close(Connection con, Statement stat, ResultSet rs) {if (con ! null) {try {con.close();} catch (SQLException e) {e.printStackTrace();}}if (stat ! null) {try {stat.close();} catch (SQLException e) {e.printStackTrace();}}if (rs ! null) {try {rs.close();} catch (SQLException e) {e.printStackTrace();}}}public static void close(Connection con, Statement stat) {close(con, stat, null);}
}配置文件
driverClassNamecom.mysql.cj.jdbc.Driver
urljdbc:mysql://localhost/db2
usernameroot
passworditzhuzhu#初始化连接数量
initialSize5#最大连接数量
maxActive10#超时时间
maxWait3000测试类
public class DruidTest {public static void main(String[] args) throws Exception {Connection con DataSourceUtils.getConnection();// 查询学生表的全部信息String sql SELECT * FROM student;PreparedStatement ps con.prepareStatement(sql);// 执行sql语句接收结果集ResultSet rs ps.executeQuery();while (rs.next()) {System.out.println(rs.getInt(sid) \t rs.getString(name) \t rs.getInt(age) \t rs.getDate(birthday));}// 释放资源DataSourceUtils.close(con, ps, rs);}
}
