JDBC 06: 连接池 / 数据源
0. 什么是连接池 / 数据源?
连接池是一个集合, 用来保存多个连接(Connection)
1. 为什么需要连接池/数据源?
<1> 为了避免重复地创建Connection
< 2 > 节约资源
2. 创建连接池 / 数据源
public class JDBCUtils { private static final String connectionURL = "jdbc:mysql://localhost:3306/web01?useUnicode=true&characterEncoding=UTF8&useSSL=false"; private static final String username = "root"; private static final String password = "root"; private static ArrayList<Connection> conlist = new ArrayList<Connection>(); static { for(int i=0;i<5;i++) { // 连接池的容量为5 Connection con = createConnection(); conlist.add(con); } } public static Connection getConnection() { if(conlist.isEmpty() == false) { Connection con = conlist.get(0); conlist.remove(con); return con; // 连接借用 }else { return createConnection(); } } private static Connection createConnection() { try { Class.forName("com.mysql.jdbc.Driver"); return DriverManager.getConnection(connectionURL,username,password); }catch(Exception e) { e.printStackTrace(); } return null; } private static void closeConnection(Connection con) { conlist.add(con); // 连接归还 } }
3. 第三方连接池 - dbcp
public class DOCPDataSource { private static final String connectionURL = "jdbc:mysql://localhost:3306/web01?useUnicode=true&characterEncoding=UTF8&useSSL=false"; private static final String username = "root"; private static final String password = "root"; private static BasicDataSource ds; static { // 初始化dbcp 数据源 BasicDataSource ds = new BasicDataSource(); ds.setDriverClassName("com.mysql.jdbc.Driver"); ds.setUrl(connectionURL); ds.setUsername(username); ds.setPassword(password); ds.setInitialSize(5); // 初始连接个数: 5 ds.setMaxTotal(20); // 允许最大连接个数: 20 ds.setMinIdle(3); // 设置最少连接个数: 3 } public Connection getConnection() { try { return ds.getConnection(); // 通过dbcp得到的连接,不需要归还,直接close就可以 }catch(SQLException e) { e.printStackTrace(); } return null; } private static void closeConnection(Connection con) { try { if(con!=null) { con.close(); // 这里会把连接归还給dbcp连接池,并不是真正的断开连接 } } catch(SQLException e) { e.printStackTrace(); } } }
4. 第三方连接池 - C3P0
package com.Jasper2003.jdbc01; import java.beans.PropertyVetoException; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import com.mchange.v2.c3p0.ComboPooledDataSource; public class C3P0DataSource { private static final String connectionURL = "jdbc:mysql://localhost:3306/web01?useUnicode=true&characterEncoding=UTF8&useSSL=false"; private static final String username = "root"; private static final String password = "root"; private static ComboPooledDataSource ds; static { try { ds = new ComboPooledDataSource(); ds.setDriverClass("com.mysql.jdbc.Driver"); ds.setJdbcUrl(connectionURL); ds.setUser(username); ds.setPassword(password); ds.setInitialPoolSize(5); // 初始连接个数: 5 ds.setMaxPoolSize(20); // 允许最大连接个数: 20 }catch(PropertyVetoException e) { e.printStackTrace(); } } public static Connection getConnection() { try { return ds.getConnection(); }catch(SQLException e) { e.printStackTrace(); } return null; } private static void closeConnection(Connection con) { try { if(con!=null) { con.close(); } } catch(SQLException e) { e.printStackTrace(); } } public static void close(ResultSet rs, Statement stmt, Connection con) { closeResultSet(rs); closeStatement(stmt); closeConnection(con); } public static void close(Statement stmt1,Statement stmt2, Connection con) { closeStatement(stmt1); closeStatement(stmt2); closeConnection(con); } private static void closeResultSet(ResultSet rs) { try { if(rs!=null) rs.close(); } catch (SQLException e) { e.printStackTrace(); } } private static void closeStatement(Statement stmt) { try { if(stmt!=null) stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } }