努力尽今夕，少年犹可夸。这篇文章主要讲述MySQL自定义数据库连接池和开源数据库连接池的使用相关的知识，希望能为你提供帮助。
数据库连接池的概念

• 数据库连接背景
  
  • 数据库连接是一种关键的、有限的、昂贵的资源，这一点在多用户的网页应用程序中体现得尤为突出。对数据库连接的管理能显著影响到整个应用程序的伸缩性和健壮性，影响到程序的性能指标。数据库连接池正是针对这个问题提出来的。
• 数据库连接池
  
  • 数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个。这项技术能明显提高对数据库操作的性能。
• 数据库连接池的原理
  
  • 没有使用数据库连接池：一个访问创建一个连接，使用完关闭连接。而频繁的创建和关闭连接非常耗时
    
    文章图片
    
    
  • 使用数据库连接池之后：提前准备一些数据库连接，使用时从池中取出，用完归还连接池
    
    文章图片
    

自定义连接池 初探连接池 自定义JDBC工具类
配置文件 config.properties

driverClass=com.mysql.jdbc.Driver url=jdbc:mysql://主机名:3306/数据库名 username=用户名 password=密码

PROPERTIES
driverClass=com.mysql.jdbc.Driver< br/> url=jdbc:mysql://主机名:3306/数据库名< br/> username=用户名< br/> password=密码 < /pre>
JDBCUtils工具类

public class JDBCUtils { private JDBCUtils() {}//构造函数私有化private static String driverClass; private static String url; private static String username; private static String password; private static Connection con; static { try { InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("config.properties"); Properties properties = new Properties(); properties.load(is); driverClass = properties.getProperty("driverClass"); url = properties.getProperty("url"); username = properties.getProperty("username"); password = properties.getProperty("password"); Class.forName(driverClass); } catch (IOException e) { e.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } }public static Connection getConnection() {//获取连接对象 try { con = DriverManager.getConnection(url, username, password); } catch (SQLException e) { e.printStackTrace(); } return con; } //关闭连接（有查询结果集） public static void close(Connection con, Statement stat, ResultSet res) { if (con != null) { try { con.close(); } catch (SQLException e) { e.printStackTrace(); } } if (stat != null) { try { stat.close(); } catch (SQLException e) { e.printStackTrace(); } } if (res != null) { try { res.close(); } catch (SQLException e) { e.printStackTrace(); } } } //关闭连接（无查询结果集） public static void close(Connection con, Statement stat) { close(con, stat, null); } }

实现连接池类
定义一个连接池类并实现java.sql.DataSource 接口。

Connection getConnection(); //获取数据库连接对象

public class MyDataSource implements DataSource{ //定义集合容器，用于保存多个数据库连接对象 //使用Collections 工具类实现集合的线程同步 private static List< Connection> pool = Collections.synchronizedList(new ArrayList< Connection> ()); //静态代码块，生成10个数据库连接保存到集合中 static { for (int i = 0; i < 10; i++) { Connection con = JDBCUtils.getConnection(); pool.add(con); } }//返回连接池的大小 public int getSize() { return pool.size(); }//从池中返回一个数据库连接 @Override public Connection getConnection() { 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; } }

自定义连接池的测试

public class MyDataSourceTest { public static void main(String[] args) throws SQLException { MyDataSource dataSource = new MyDataSource(); System.out.println("使用前连接池数量：" + dataSource.getSize()); Connection con = dataSource.getConnection(); String sql = "select * from emp"; PreparedStatement pst = con.prepareStatement(sql); ResultSet res = pst.executeQuery(); while (res.next()) { String ename = res.getString("ename"); String job = res.getString("job"); String hiredate = res.getString("hiredate"); System.out.println("ename:" + ename + "\\t job:" + job + "\\t hiredate:" + hiredate); }res.close(); pst.close(); con.close(); System.out.println("使用后连接池数量：" + dataSource.getSize()); } }

输出：

使用前连接池数量：10 ename:SMITHjob:CLERKhiredate:1980-12-17 ename:ALLENjob:SALESMANhiredate:1981-02-20 ename:WARDjob:SALESMANhiredate:1981-02-22 ename:JONESjob:MANAGERhiredate:1981-04-02 ename:MARTINjob:SALESMANhiredate:1981-09-28 ename:BLAKEjob:MANAGERhiredate:1981-05-01 ename:CLARKjob:MANAGERhiredate:1981-06-09 ename:SCOTTjob:ANALYSThiredate:1987-04-19 ename:KINGjob:PRESIDENThiredate:1981-11-17 ename:TURNERjob:SALESMANhiredate:1981-09-08 ename:ADAMSjob:CLERKhiredate:1987-05-23 ename:JAMESjob:CLERKhiredate:1981-12-03 ename:FORDjob:ANALYSThiredate:1981-12-03 ename:MILLERjob:CLERKhiredate:1982-01-23 使用后连接池数量：9

问题：虽然我们自定义了数据库连接池，但是连接关闭以后并没有归还给数据库连接池，还需要改进归还连接的问题
继承方式改进连接池

System.out.println(JDBCUtils.getConnection()); //com.mysql.jdbc.JDBC4Connection@470e2030

通过输出Connection的地址发现Connection类的实现类是JDBC4Connection，是否能够通过编写一个类继承JDBC4Connection，然后重写close()方法，在关闭连接的同时归还连接？

/* 自定义Connection类 */ public class MyConnection1 extends JDBC4Connection { //声明连接对象和连接池集合对象 private Connection con; private List< Connection> pool; //通过构造方法给成员变量赋值 public MyConnection1(String hostToConnectTo, int portToConnectTo, Properties info, String databaseToConnectTo, String url,Connection con,List< Connection> pool) throws SQLException { super(hostToConnectTo, portToConnectTo, info, databaseToConnectTo, url); this.con = con; this.pool = pool; }//重写close()方法，将连接归还给池中 @Override public void close() throws SQLException { pool.add(con); } }

但是这种方式行不通，通过查看JDBC工具类获取连接的方法我们发现：我们虽然自定义了一个子类，完成了归还连接的操作。但是DriverManager获取的还是JDBC4Connection这个对象，并不是我们的子类对象。而我们又不能整体去修改驱动包中类的功能！

//将之前的连接对象换成自定义的子类对象 private static MyConnection1 con; public static Connection getConnection() { try { //等效于：MyConnection1 con = new JDBC4Connection(); 子类引用指向父类对象，语法错误！ con = DriverManager.getConnection(url,username,password); } catch (SQLException e) { e.printStackTrace(); }return con; }

装饰设计模式改进连接池自定义Connection类。通过装饰设计模式，实现和mysql驱动包中的Connection实现类相同的功能！
实现步骤：

• 定义一个类，实现Connection接口
  
• 定义Connection连接对象和连接池容器对象的变量
  
• 提供有参构造方法，接收连接对象和连接池对象，对变量赋值
  
• 在close()方法中，完成连接的归还
  
• 剩余方法，只需要调用mysql驱动包的连接对象完成即可
  

  public class MyConnection2 implements Connection {//2.定义Connection连接对象和连接池容器对象的变量 private Connection con; private List< Connection> pool; //3.提供有参构造方法，接收连接对象和连接池对象，对变量赋值 public MyConnection2(Connection con,List< Connection> pool) { this.con = con; this.pool = pool; }//4.在close()方法中，完成连接的归还 @Override public void close() throws SQLException { pool.add(con); }@Override public Statement createStatement() throws SQLException { return con.createStatement(); }@Override public PreparedStatement prepareStatement(String sql) throws SQLException { return con.prepareStatement(sql); }@Override public CallableStatement prepareCall(String sql) throws SQLException { return con.prepareCall(sql); }@Override public String nativeSQL(String sql) throws SQLException { return con.nativeSQL(sql); }@Override public void setAutoCommit(boolean autoCommit) throws SQLException { con.setAutoCommit(autoCommit); }@Override public boolean getAutoCommit() throws SQLException { return con.getAutoCommit(); }@Override public void commit() throws SQLException { con.commit(); }@Override public void rollback() throws SQLException { con.rollback(); }@Override public boolean isClosed() throws SQLException { return con.isClosed(); }@Override public DatabaseMetaData getMetaData() throws SQLException { return con.getMetaData(); }@Override public void setReadOnly(boolean readOnly) throws SQLException { con.setReadOnly(readOnly); }@Override public boolean isReadOnly() throws SQLException { return con.isReadOnly(); }@Override public void setCatalog(String catalog) throws SQLException { con.setCatalog(catalog); }@Override public String getCatalog() throws SQLException { return con.getCatalog(); }@Override public void setTransactionIsolation(int level) throws SQLException { con.setTransactionIsolation(level); }@Override public int getTransactionIsolation() throws SQLException { return con.getTransactionIsolation(); }@Override public SQLWarning getWarnings() throws SQLException { return con.getWarnings(); }@Override public void clearWarnings() throws SQLException { con.clearWarnings(); }@Override public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException { return con.createStatement(resultSetType,resultSetConcurrency); }@Override public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException { return con.prepareStatement(sql,resultSetType,resultSetConcurrency); }@Override public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException { return con.prepareCall(sql,resultSetType,resultSetConcurrency); }@Override public Map< String, Class< ?> > getTypeMap() throws SQLException { return con.getTypeMap(); }@Override public void setTypeMap(Map< String, Class< ?> > map) throws SQLException { con.setTypeMap(map); }@Override public void setHoldability(int holdability) throws SQLException { con.setHoldability(holdability); }@Override public int getHoldability() throws SQLException { return con.getHoldability(); }@Override public Savepoint setSavepoint() throws SQLException { return con.setSavepoint(); }@Override public Savepoint setSavepoint(String name) throws SQLException { return con.setSavepoint(name); }@Override public void rollback(Savepoint savepoint) throws SQLException { con.rollback(savepoint); }@Override public void releaseSavepoint(Savepoint savepoint) throws SQLException { con.releaseSavepoint(savepoint); }@Override public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException { return con.createStatement(resultSetType,resultSetConcurrency,resultSetHoldability); }@Override public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException { return con.prepareStatement(sql,resultSetType,resultSetConcurrency,resultSetHoldability); }@Override public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException { return con.prepareCall(sql,resultSetType,resultSetConcurrency,resultSetHoldability); }@Override public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException { return con.prepareStatement(sql,autoGeneratedKeys); }@Override public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException { return con.prepareStatement(sql,columnIndexes); }@Override public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException { return con.prepareStatement(sql,columnNames); }@Override public Clob createClob() throws SQLException { return con.createClob(); }@Override public Blob createBlob() throws SQLException { return con.createBlob(); }@Override public NClob createNClob() throws SQLException { return con.createNClob(); }@Override public SQLXML createSQLXML() throws SQLException { return con.createSQLXML(); }@Override public boolean isValid(int timeout) throws SQLException { return con.isValid(timeout); }@Override public void setClientInfo(String name, String value) throws SQLClientInfoException { con.setClientInfo(name,value); }@Override public void setClientInfo(Properties properties) throws SQLClientInfoException { con.setClientInfo(properties); }@Override public String getClientInfo(String name) throws SQLException { return con.getClientInfo(name); }@Override public Properties getClientInfo() throws SQLException { return con.getClientInfo(); }@Override public Array createArrayOf(String typeName, Object[] elements) throws SQLException { return con.createArrayOf(typeName,elements); }@Override public Struct createStruct(String typeName, Object[] attributes) throws SQLException { return con.createStruct(typeName,attributes); }@Override public void setSchema(String schema) throws SQLException { con.setSchema(schema); }@Override public String getSchema() throws SQLException { return con.getSchema(); }@Override public void abort(Executor executor) throws SQLException { con.abort(executor); }@Override public void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException { con.setNetworkTimeout(executor,milliseconds); }@Override public int getNetworkTimeout() throws SQLException { return con.getNetworkTimeout(); }@Override public < T> T unwrap(Class< T> iface) throws SQLException { return con.unwrap(iface); }@Override public boolean isWrapperFor(Class< ?> iface) throws SQLException { return con.isWrapperFor(iface); } }

  
  

自定义连接池类

public class MyDataSource implements DataSource{ //定义集合容器，用于保存多个数据库连接对象 private static List< Connection> pool = Collections.synchronizedList(new ArrayList< Connection> ()); //静态代码块，生成10个数据库连接保存到集合中 static { for (int i = 0; i < 10; i++) { Connection con = JDBCUtils.getConnection(); pool.add(con); } }//返回连接池的大小 public int getSize() { return pool.size(); }//从池中返回一个数据库连接 @Override public Connection getConnection() { if(pool.size() > 0) { //从池中获取数据库连接 Connection con = pool.remove(0); //通过自定义连接对象进行包装 MyConnection2 mycon = new MyConnection2(con,pool); //返回包装后的连接对象 return mycon; }else { throw new RuntimeException("连接数量已用尽"); } } }

缺点：Connection 接口中要实现的方法太多了，代码繁杂
适配器设计模式改进连接池提供一个适配器类，实现Connection接口，将所有功能进行实现(除了close()方法)，作为中间类。自定义连接类只需要继承这个适配器类，重写需要改进的close()方法即可！
适配器类不需要实现close()方法，所以定义为抽象类

public abstract class MyAdapter implements Connection {// 定义数据库连接对象的变量 private Connection con; // 通过构造方法赋值 public MyAdapter(Connection con) { this.con = con; }// 所有的方法，均调用mysql的连接对象实现 @Override public Statement createStatement() throws SQLException { return con.createStatement(); }@Override public PreparedStatement prepareStatement(String sql) throws SQLException { return con.prepareStatement(sql); }@Override public CallableStatement prepareCall(String sql) throws SQLException { return con.prepareCall(sql); }@Override public String nativeSQL(String sql) throws SQLException { return con.nativeSQL(sql); }@Override public void setAutoCommit(boolean autoCommit) throws SQLException { con.setAutoCommit(autoCommit); }@Override public boolean getAutoCommit() throws SQLException { return con.getAutoCommit(); }@Override public void commit() throws SQLException { con.commit(); }@Override public void rollback() throws SQLException { con.rollback(); }@Override public boolean isClosed() throws SQLException { return con.isClosed(); }@Override public DatabaseMetaData getMetaData() throws SQLException { return con.getMetaData(); }@Override public void setReadOnly(boolean readOnly) throws SQLException { con.setReadOnly(readOnly); }@Override public boolean isReadOnly() throws SQLException { return con.isReadOnly(); }@Override public void setCatalog(String catalog) throws SQLException { con.setCatalog(catalog); }@Override public String getCatalog() throws SQLException { return con.getCatalog(); }@Override public void setTransactionIsolation(int level) throws SQLException { con.setTransactionIsolation(level); }@Override public int getTransactionIsolation() throws SQLException { return con.getTransactionIsolation(); }@Override public SQLWarning getWarnings() throws SQLException { return con.getWarnings(); }@Override public void clearWarnings() throws SQLException { con.clearWarnings(); }@Override public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException { return con.createStatement(resultSetType,resultSetConcurrency); }@Override public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException { return con.prepareStatement(sql,resultSetType,resultSetConcurrency); }@Override public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException { return con.prepareCall(sql,resultSetType,resultSetConcurrency); }@Override public Map< String, Class< ?> > getTypeMap() throws SQLException { return con.getTypeMap(); }@Override public void setTypeMap(Map< String, Class< ?> > map) throws SQLException { con.setTypeMap(map); }@Override public void setHoldability(int holdability) throws SQLException { con.setHoldability(holdability); }@Override public int getHoldability() throws SQLException { return con.getHoldability(); }@Override public Savepoint setSavepoint() throws SQLException { return con.setSavepoint(); }@Override public Savepoint setSavepoint(String name) throws SQLException { return con.setSavepoint(name); }@Override public void rollback(Savepoint savepoint) throws SQLException { con.rollback(savepoint); }@Override public void releaseSavepoint(Savepoint savepoint) throws SQLException { con.releaseSavepoint(savepoint); }@Override public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException { return con.createStatement(resultSetType,resultSetConcurrency,resultSetHoldability); }@Override public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException { return con.prepareStatement(sql,resultSetType,resultSetConcurrency,resultSetHoldability); }@Override public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException { return con.prepareCall(sql,resultSetType,resultSetConcurrency,resultSetHoldability); }@Override public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException { return con.prepareStatement(sql,autoGeneratedKeys); }@Override public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException { return con.prepareStatement(sql,columnIndexes); }@Override public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException { return con.prepareStatement(sql,columnNames); }@Override public Clob createClob() throws SQLException { return con.createClob(); }@Override public Blob createBlob() throws SQLException { return con.createBlob(); }@Override public NClob createNClob() throws SQLException { return con.createNClob(); }@Override public SQLXML createSQLXML() throws SQLException { return con.createSQLXML(); }@Override public boolean isValid(int timeout) throws SQLException { return con.isValid(timeout); }@Override public void setClientInfo(String name, String value) throws SQLClientInfoException { con.setClientInfo(name,value); }@Override public void setClientInfo(Properties properties) throws SQLClientInfoException { con.setClientInfo(properties); }@Override public String getClientInfo(String name) throws SQLException { return con.getClientInfo(name); }@Override public Properties getClientInfo() throws SQLException { return con.getClientInfo(); }@Override public Array createArrayOf(String typeName, Object[] elements) throws SQLException { return con.createArrayOf(typeName,elements); }@Override public Struct createStruct(String typeName, Object[] attributes) throws SQLException { return con.createStruct(typeName,attributes); }@Override public void setSchema(String schema) throws SQLException { con.setSchema(schema); }@Override public String getSchema() throws SQLException { return con.getSchema(); }@Override public void abort(Executor executor) throws SQLException { con.abort(executor); }@Override public void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException { con.setNetworkTimeout(executor,milliseconds); }@Override public int getNetworkTimeout() throws SQLException { return con.getNetworkTimeout(); }@Override public < T> T unwrap(Class< T> iface) throws SQLException { return con.unwrap(iface); }@Override public boolean isWrapperFor(Class< ?> iface) throws SQLException { return con.isWrapperFor(iface); } }

自定义连接类
通过适配器设计模式。完成close()方法的重写

• 定义一个类，继承适配器父类
  
• 定义Connection连接对象和连接池容器对象的变量
  
• 提供有参构造方法，接收连接对象和连接池对象，对变量赋值
  
• 在close()方法中，完成连接的归还
  

  public class MyConnection3 extends MyAdapter { //2.定义Connection连接对象和连接池容器对象的变量 private Connection con; private List< Connection> pool; //3.提供有参构造方法，接收连接对象和连接池对象，对变量赋值 public MyConnection3(Connection con,List< Connection> pool) { super(con); // 将接收的数据库连接对象给适配器父类传递 this.con = con; this.pool = pool; }//4.在close()方法中，完成连接的归还 @Override public void close() throws SQLException { pool.add(con); } }

  
  

自定义连接池类

public class MyDataSource implements DataSource{ //定义集合容器，用于保存多个数据库连接对象 private static List< Connection> pool = Collections.synchronizedList(new ArrayList< Connection> ()); //静态代码块，生成10个数据库连接保存到集合中 static { for (int i = 0; i < 10; i++) { Connection con = JDBCUtils.getConnection(); pool.add(con); } }//返回连接池的大小 public int getSize() { return pool.size(); }//从池中返回一个数据库连接 @Override public Connection getConnection() { if(pool.size() > 0) { //从池中获取数据库连接 Connection con = pool.remove(0); //通过自定义连接对象进行包装 MyConnection3 mycon = new MyConnection3(con,pool); //返回包装后的连接对象 return mycon; }else { throw new RuntimeException("连接数量已用尽"); } } }

缺点：自定义连接类中的方法已经很简洁了。剩余所有的方法已经抽取到了适配器类中。但是适配器这个类还是我们自己编写的，也比较麻烦！所以可以使用动态代理的方式来改进。
动态代理

public class MyDataSource implements DataSource{ //定义集合容器，用于保存多个数据库连接对象 private static List< Connection> pool = Collections.synchronizedList(new ArrayList< Connection> ()); //静态代码块，生成10个数据库连接保存到集合中 static { for (int i = 0; i < 10; i++) { Connection con = JDBCUtils.getConnection(); pool.add(con); } }//返回连接池的大小 public int getSize() { return pool.size(); }//动态代理方式 @Override public 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方法，则将连接还回池中 如果不是，直接执行实现类的原有方法 */ @Override public 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("连接数量已用尽"); } }//从池中返回一个数据库连接 /*@Override public Connection getConnection() { if(pool.size() > 0) { //从池中获取数据库连接 Connection con = pool.remove(0); //通过自定义连接对象进行包装 //MyConnection2 mycon = new MyConnection2(con,pool); MyConnection3 mycon = new MyConnection3(con,pool); //返回包装后的连接对象 return mycon; }else { throw new RuntimeException("连接数量已用尽"); } }*/ }

开源连接池的使用 C3P0连接池

• 导入jar包
  
  文章图片
  
  
  文章图片
  
  
• 导入配置文件到src目录下
  
• 创建c3p0连接池对象
  
• 获取数据库连接进行使用

配置文件 c3p0-config.xml 注意该配置文件的名字是固定的不要改，否则无法识别

• initialPoolSize：初始化连接数量
  
• maxPoolSize：最大连接数量，当连接数量超过初始化连接数量时，会在连接池内继续创建连接，直到达到数据库连接池所能容纳的最大连接数量
  
• checkoutTimeout：超过时间。如果使用的连接数量超过最大连接数量，编译器会在checkoutTimeout时间以后报错并终止程序。
  

  < c3p0-config> < !-- 使用默认的配置读取连接池对象 --> < default-config> < !--连接参数 --> < property name="driverClass"> com.mysql.jdbc.Driver< /property> < property name="jdbcUrl"> jdbc:mysql://主机名:3306/数据库名< /property> < property name="user"> 用户名< /property> < property name="password"> 密码< /property> < !-- 连接池参数 --> < !-- 初始化连接数量 --> < property name="initialPoolSize"> 5< /property> < !--最大连接数量--> < property name="maxPoolSize"> 10< /property> < !--超时时间--> < property name="checkoutTimeout"> 3000< /property> < /default-config> < !--创建数据库连接池时指定名称--> < named-config name="otherc3p0"> < !--连接参数 --> < property name="driverClass"> com.mysql.jdbc.Driver< /property> < property name="jdbcUrl"> jdbc:mysql://主机名:3306/数据库名< /property> < property name="user"> 用户名< /property> < property name="password"> 密码< /property> < !-- 连接池参数 --> < property name="initialPoolSize"> 5< /property> < property name="maxPoolSize"> 8< /property> < property name="checkoutTimeout"> 1000< /property> < /named-config> < /c3p0-config>

  
  C3P0数据库连接池的使用
  

  public static void main(String[] args) throws SQLException { //创建c3p0连接池对象 DataSource dataSource = new ComboPooledDataSource(); //获取数据库连接进行使用 Connection con = dataSource.getConnection(); String s = "select *from emp"; PreparedStatement pst = con.prepareStatement(s); ResultSet rs = pst.executeQuery(); while (rs.next()) { String ename = rs.getString("ename"); String job = rs.getString("job"); String hiredate = rs.getString("hiredate"); System.out.println("ename:" + ename + " job:" + job + " hiredate:" + hiredate); } rs.close(); pst.close(); con.close(); // 将连接对象归还池中 }

  
  

Druid连接池

• 导入jar包
  
  文章图片
  
  
• 编写配置文件，放在src目录下
  
• 通过Properties集合加载配置文件
  
• 通过Druid连接池工厂类获取数据库连接池对象
  
• 获取数据库连接，进行使用

配置文件druid.properties

driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://主机名:3306/数据库名 username:用户名 password:密码 # 初始连接数量 initialSize=5 # 最大连接数量 maxActive=10 # 最长等待时间 maxWait=3000

Druid数据库的使用

public static void main(String[] args) throws Exception { //通过Properties集合加载配置文件 InputStream is = demo01.class.getClassLoader().getResourceAsStream("druid.properties"); Properties prop = new Properties(); prop.load(is); //通过Druid连接池工厂类获取数据库连接池对象 DataSource dataSource = DruidDataSourceFactory.createDataSource(prop); //获取数据库连接，进行使用 Connection con = dataSource.getConnection(); PreparedStatement pst = con.prepareStatement("select *from emp"); ResultSet rs = pst.executeQuery(); while (rs.next()) { String ename = rs.getString("ename"); String job = rs.getString("job"); String hiredate = rs.getString("hiredate"); System.out.println("ename:" + ename + " job:" + job + " hiredate:" + hiredate); } rs.close(); pst.close(); con.close(); }

抽取工具类

/* 数据库连接池工具类 */ public class DataSourceUtils { //1.私有构造方法 private DataSourceUtils(){}//2.定义DataSource数据源变量 private static DataSource dataSource; //3.提供静态代码块，完成配置文件的加载和获取连接池对象 static { try{ //加载配置文件 InputStream is = DruidDemo1.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); }}

【MySQL自定义数据库连接池和开源数据库连接池的使用】工具类的使用

public static void main(String[] args) throws SQLException { //利用工具类获取DataSoure DataSource dataSource = DataSourceUtils.getDataSource(); //获取连接，并使用 Connection con = dataSource.getConnection(); String s = "select *from emp"; PreparedStatement pst = con.prepareStatement(s); ResultSet rs = pst.executeQuery(); while (rs.next()) { String ename = rs.getString("ename"); String job = rs.getString("job"); String hiredate = rs.getString("hiredate"); System.out.println("ename:" + ename + " job:" + job + " hiredate:" + hiredate); } DataSourceUtils.close(con, pst, rs); }

推荐阅读

• 

  汽车积碳是什么意思
• 

  redis多节点同步
• 

  27岁有稳定工作,想用20万存款当做第二职业来投资,投资什么好呢？
• 

  上海戏剧学院分数线 2019年上海戏剧学院分数线
• 

  去加拿大必买清单 去加拿大买什么最划算
• 

  室内阳台养什么花最好 室内阳台适合养什么花好
• 

  火理财什么时间可以申请债权转让？债权转让收费吗
• 

  塑料菜板发黑怎么办
• 

  癌细胞|癌细胞已经转移，为什么人的精神依旧很好，而且很能吃？是好事？
• 

  详细步骤及注意事项 g2810加墨水后如何操作
• 

  佳能相机7100 佳能7100单反
• 

  有什么好看的书推荐？
• 

  你喜欢看军事小说吗？
• 

  空气阻尼器时间继电器 空气阻尼器结构图
• 

  2023春节坐高铁需要全程戴口罩吗 2021春节能坐高铁吗
• 

  台高官被问“你有接受过性招待吗
• 

  农村籍独生子女补贴如何领取？四类人无法领取有你吗？
• 

  甲氰菊酯有效是多久 甲氰菊酯药效是几天，水里多久失效
• 

  c语言连接数据库函数 c语言数据库连接池
• 

  有宠app怎么关闭资讯推送通知？有宠app关闭资讯推送通知的方法

• centos7 防火墙_李孟_新浪博客
• C语言 三子棋游戏
• Java ASM系列（（066）Exception处理）
• Thymeleaf模板使用
• centos7网络配置一些问题
• Spring（IOC是啥（如何推导和理解？））
• Python实现批量压缩文件/文件夹——zipfile
• centos7网络配置一些问题_李孟_新浪博客
• SpringCloud升级之路2020.0.x版-23.订制Spring Cloud LoadBal