数据库连接池的概念

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

 

• 使用数据库连接池之后：提前准备一些数据库连接，使用时从池中取出，用完归还连接池

 

自定义连接池

初探连接池

自定义JDBC工具类

配置文件 config.properties

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

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 pool = Collections.synchronizedList(new ArrayList());    //静态代码块，生成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 unwrap(Class 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());    }}

输出：

使用前连接池数量：10ename:SMITH job:CLERK hiredate:1980-12-17ename:ALLEN job:SALESMAN hiredate:1981-02-20ename:WARD job:SALESMAN hiredate:1981-02-22ename:JONES job:MANAGER hiredate:1981-04-02ename:MARTIN job:SALESMAN hiredate:1981-09-28ename:BLAKE job:MANAGER hiredate:1981-05-01ename:CLARK job:MANAGER hiredate:1981-06-09ename:SCOTT job:ANALYST hiredate:1987-04-19ename:KING job:PRESIDENT hiredate:1981-11-17ename:TURNER job:SALESMAN hiredate:1981-09-08ename:ADAMS job:CLERK hiredate:1987-05-23ename:JAMES job:CLERK hiredate:1981-12-03ename:FORD job:ANALYST hiredate:1981-12-03ename:MILLER job:CLERK hiredate: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 pool;        //通过构造方法给成员变量赋值      public MyConnection1(String hostToConnectTo, int portToConnectTo, Properties info, String databaseToConnectTo, String url,Connection con,List 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 pool;        //3.提供有参构造方法，接收连接对象和连接池对象，对变量赋值      public MyConnection2(Connection con,List 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> getTypeMap() throws SQLException {          return con.getTypeMap();      }        @Override      public void setTypeMap(Map> 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 unwrap(Class 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 pool = Collections.synchronizedList(new ArrayList());        //静态代码块，生成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> getTypeMap() throws SQLException {          return con.getTypeMap();      }        @Override      public void setTypeMap(Map> 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 unwrap(Class 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 pool;        //3.提供有参构造方法，接收连接对象和连接池对象，对变量赋值      public MyConnection3(Connection con,List 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 pool = Collections.synchronizedList(new ArrayList());        //静态代码块，生成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 pool = Collections.synchronizedList(new ArrayList());        //静态代码块，生成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.Driverproperty>    <property name="jdbcUrl">jdbc:mysql://主机名:3306/数据库名property>    <property name="user">用户名property>    <property name="password">密码property>                <property name="initialPoolSize">5property>        <property name="maxPoolSize">10property>        <property name="checkoutTimeout">3000property>  default-config>    <named-config name="otherc3p0">         <property name="driverClass">com.mysql.jdbc.Driverproperty>    <property name="jdbcUrl">jdbc:mysql://主机名:3306/数据库名property>    <property name="user">用户名property>    <property name="password">密码property>            <property name="initialPoolSize">5property>    <property name="maxPoolSize">8property>    <property name="checkoutTimeout">1000property>  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.Driverurl=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);      }    }

工具类的使用

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);    }