学习Java的日子 Day56 数据库连接池，Druid连接池

Day56

1.数据库连接池

理解：池就是容器，容器中存放了多个连接对象

使用原因：

1.优化创建和销毁连接的时间（在项目启动时创建连接池，项目销毁时关闭连接池）

2.提高连接对象的复用率

3.有效控制项目中连接的个数（连接对象占内存资源）

数据库连接池负责分配、管理和释放数据库连接对象，它允许应用程序重复使用一个现有的数据库连接，而

不是再重新建立一个

1.1 自定义连接池

自己写的连接池类 FastConnectionPool

public class FastConnectionPool {private String driverClassName;private String url;private String username;private String password;private int maxActive;//存储connection连接对象的资源容器(removeFirst)private LinkedList<Connection> list;//get,set方法(没有list的get、set方法)//初始化listpublic void init() throws SQLException {list = new LinkedList<>();try {Class.forName(driverClassName);//创建连接对象，并添加到连接池容器中for (int i = 0; i < maxActive; i++) {Connection connection = DriverManager.getConnection(url, username, password);list.add(connection);}} catch (ClassNotFoundException e) {throw new RuntimeException(e);}}public Connection getConnection() throws SQLException {if(list == null){init();}Connection connection = null;if(!list.isEmpty()){connection = list.removeFirst();}return connection;}//回收connection对象，使用完了不用close()方法关闭，而是回收到list中继续使用，提高复用率public void recovery(Connection connection){list.add(connection);}}

测试类

public class Test01 {public static void main(String[] args) throws SQLException {//创建数据库连接池FastConnectionPool pool = new FastConnectionPool();//设置参数pool.setDriverClassName("com.mysql.cj.jdbc.Driver");pool.setUrl("jdbc:mysql://localhost:3306/2403javaee?characterEncoding=utf8&serverTimezone=UTC&rewriteBatchedStatements=true");pool.setUsername("root");pool.setPassword("123456");pool.setMaxActive(20);//jdbc的查询代码Connection connection = pool.getConnection();String sql = "select * from student";PreparedStatement statement = connection.prepareStatement(sql);ResultSet resultSet = statement.executeQuery();while(resultSet.next()){int id = resultSet.getInt("id");String name = resultSet.getString("name");String sex = resultSet.getString("sex");int age = resultSet.getInt("age");float salary = resultSet.getFloat("salary");String course = resultSet.getString("course");System.out.println(id + " -- " + name + " -- " + sex + " -- " + age + " -- " + salary + " -- " + course);}resultSet.close();statement.close();pool.recovery(connection);//回收connection对象}
}

1.2 升级自定义连接池(重点重点关注)

DataSource：连接池各有各的实现方式，所以sun公司定义了一个标准，DataSource

注意：市面上有众多的数据库连接池（Druid、C3P0、DBCP），他们都实现Java提供的DataSource接口(简称数据源)

使用过程，重点是要实现close方法，连接资源 (connection对象) 怎么关闭：装饰模式来帮忙

装饰设计模式（包装模式）：目的：改写已存在的类的某个方法或某些方法

数据库连接的包装类（需要实现很多方法）

定义Connection connection对象

定义LinkedList pool;

通过构造方法对上述两个值进行初始化

重写close方法

Public void close(){

pool.addLast(connection);

}

public class MyConnectionWrapper implements Connection {private Connection connection;private LinkedList<Connection> list;//连接池里的容器public MyConnectionWrapper(Connection connection, LinkedList<Connection> list) {this.connection = connection;this.list = list;}@Overridepublic Statement createStatement() throws SQLException {return connection.createStatement();}@Overridepublic PreparedStatement prepareStatement(String sql) throws SQLException {return connection.prepareStatement(sql);}@Overridepublic CallableStatement prepareCall(String sql) throws SQLException {return connection.prepareCall(sql);}@Overridepublic String nativeSQL(String sql) throws SQLException {return connection.nativeSQL(sql);}@Overridepublic void setAutoCommit(boolean autoCommit) throws SQLException {connection.setAutoCommit(autoCommit);}@Overridepublic boolean getAutoCommit() throws SQLException {return connection.getAutoCommit();}@Overridepublic void commit() throws SQLException {connection.commit();}@Overridepublic void rollback() throws SQLException {connection.rollback();}@Overridepublic void close() throws SQLException {System.out.println("将连接包装类对象回收到连接池里的容器");list.add(this);}@Overridepublic boolean isClosed() throws SQLException {return connection.isClosed();}@Overridepublic DatabaseMetaData getMetaData() throws SQLException {return connection.getMetaData();}@Overridepublic void setReadOnly(boolean readOnly) throws SQLException {connection.setReadOnly(readOnly);}@Overridepublic boolean isReadOnly() throws SQLException {return connection.isReadOnly();}@Overridepublic void setCatalog(String catalog) throws SQLException {connection.setCatalog(catalog);}@Overridepublic String getCatalog() throws SQLException {return connection.getCatalog();}//省略一些方法
}

自定义连接池类，需要实现DataSource接口

public class FastConnectionPool implements DataSource {private String driverClassName;private String url;private String username;private String password;private int maxActive;private LinkedList<Connection> list;//get,set方法(没有list的get、set方法)public void init() throws SQLException {list = new LinkedList<>();try {Class.forName(driverClassName);} catch (ClassNotFoundException e) {throw new RuntimeException(e);}for (int i = 0; i < maxActive; i++) {Connection connection = DriverManager.getConnection(url, username, password);//获取连接的方法需要将原本的connection对象进行包装MyConnectionWrapper connectionWrapper = new MyConnectionWrapper(connection, list);list.add(connectionWrapper);}}@Overridepublic Connection getConnection() throws SQLException {if(list == null){init();}Connection connection = null;if(!list.isEmpty()){connection = list.removeFirst();}return connection;}@Overridepublic Connection getConnection(String username, String password) throws SQLException {return null;}@Overridepublic <T> T unwrap(Class<T> iface) throws SQLException {return null;}@Overridepublic boolean isWrapperFor(Class<?> iface) throws SQLException {return false;}@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;}
}

测试类，和上面的一样

public class Test01 {public static void main(String[] args) throws SQLException {//创建连接池对象FastConnectionPool pool = new FastConnectionPool();//设置参数//设置参数pool.setDriverClassName("com.mysql.cj.jdbc.Driver");pool.setUrl("jdbc:mysql://localhost:3306/2403javaee?characterEncoding=utf8&serverTimezone=UTC&rewriteBatchedStatements=true");pool.setUsername("root");pool.setPassword("123456");pool.setMaxActive(20);Connection connection = pool.getConnection();String sql = "select * from student";PreparedStatement statement = connection.prepareStatement(sql);ResultSet resultSet = statement.executeQuery();while(resultSet.next()){int id = resultSet.getInt("id");String name = resultSet.getString("name");String sex = resultSet.getString("sex");int age = resultSet.getInt("age");float salary = resultSet.getFloat("salary");String course = resultSet.getString("course");System.out.println(id + " -- " + name + " -- " + sex + " -- " + age + " -- " + salary + " -- " + course);}resultSet.close();statement.close();connection.close();}
}

1.3 Druid (德鲁伊) 连接池

通常我们把DataSource的实现，按其英文含义称之为数据源，数据源中都包含了数据库连接池的实现。
也有一些开源组织提供了数据源的独立实现：

· DBCP 数据库连接池

· C3P0 数据库连接池

· Druid德鲁伊)数据库连接池

注意：市面上有众多的数据库连接池（Druid、C3P0、DBCP），他们都实现Java提供的DataSource接口(简称数据源)

在使用了数据库连接池之后，在项目的实际开发中就不需要编写连接数据库的代码了，直接从数据源获得数据库的连接。

需要导包

public class Test01 {public static void main(String[] args) throws SQLException {//创建连接池对象DruidDataSource pool = new DruidDataSource();//就只变化了这一句话//设置参数pool.setDriverClassName("com.mysql.cj.jdbc.Driver");pool.setUrl("jdbc:mysql://localhost:3306/2403javaee?characterEncoding=utf8&serverTimezone=UTC&rewriteBatchedStatements=true");pool.setUsername("root");pool.setPassword("123456");pool.setMaxActive(20);Connection connection = pool.getConnection();String sql = "select * from student";PreparedStatement statement = connection.prepareStatement(sql);ResultSet resultSet = statement.executeQuery();while(resultSet.next()){int id = resultSet.getInt("id");String name = resultSet.getString("name");String sex = resultSet.getString("sex");int age = resultSet.getInt("age");float salary = resultSet.getFloat("salary");String course = resultSet.getString("course");System.out.println(id + " -- " + name + " -- " + sex + " -- " + age + " -- " + salary + " -- " + course);}resultSet.close();statement.close();connection.close();}
}

1.4 Druid封装DBUtil

DBConfig.properties

driverClassName=com.mysql.cj.jdbc.Driverurl=jdbc:mysql://localhost:3306/2403javaee?characterEncoding=utf8&serverTimezone=UTC&rewriteBatchedStatements=trueuser
name=root
password=123456
maxActive=20

maxActive=20：最大连接对象个数

数据库工具类，修改DBUtil

package com.qf.utils;public class DBUtil {private static DruidDataSource pool;private static ThreadLocal<Connection> local;static{Properties properties = new Properties();try {properties.load(DBUtil.class.getClassLoader().getResourceAsStream("DBConfig.properties"));} catch (IOException e) {throw new RuntimeException(e);}String driverClassName = properties.getProperty("driverClassName");String url = properties.getProperty("url");String username = properties.getProperty("username");String password = properties.getProperty("password");int maxActive = Integer.parseInt(properties.getProperty("maxActive"));//初始化数据库连接池pool = new DruidDataSource();//设置参数pool.setDriverClassName(driverClassName);pool.setUrl(url);pool.setUsername(username);pool.setPassword(password);pool.setMaxActive(maxActive);local = new ThreadLocal<>();}/*** 获取连接对象*/public static Connection getConnection() throws SQLException {Connection connection = local.get();//获取当前线程的Connection对象if(connection == null){connection = pool.getConnection();//获取数据库连接池里的连接对象local.set(connection);//将Connection对象添加到local中}return connection;}/*** 关闭资源*/public static void close(Connection connection, Statement statement, ResultSet resultSet){if(resultSet != null){try {resultSet.close();} catch (SQLException e) {throw new RuntimeException(e);}}if(statement != null){try {statement.close();} catch (SQLException e) {throw new RuntimeException(e);}}if(connection != null){try {if(connection.getAutoCommit()){connection.close();local.set(null);}} catch (SQLException e) {throw new RuntimeException(e);}}}/*** 开启事务*/public static void startTransaction() throws SQLException {Connection connection = getConnection();connection.setAutoCommit(false);}/*** 提交事务*/public static void commit() throws SQLException {Connection connection = local.get();if(connection != null){connection.commit();connection.close();local.set(null);}}public static void rollback() throws SQLException {Connection connection = local.get();if(connection != null){connection.rollback();connection.close();local.set(null);}}/*** 更新数据（添加、删除、修改）*/public static int commonUpdate(String sql,Object... params) throws SQLException {Connection connection = null;PreparedStatement statement = null;try {connection = getConnection();statement = connection.prepareStatement(sql);paramHandler(statement,params);int num = statement.executeUpdate();return num;}finally {close(connection,statement,null);}}/*** 添加数据 - 主键回填(主键是int类型可以返回)*/public static int commonInsert(String sql,Object... params) throws SQLException {Connection connection = null;PreparedStatement statement = null;ResultSet resultSet = null;try {connection = getConnection();statement = connection.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);paramHandler(statement,params);statement.executeUpdate();resultSet = statement.getGeneratedKeys();int primaryKey = 0;if(resultSet.next()){primaryKey = resultSet.getInt(1);}return primaryKey;}finally {close(connection,statement,resultSet);}}/*** 查询多个数据*/public static <T> List<T> commonQueryList(Class<T> clazz,String sql, Object... params) throws SQLException, InstantiationException, IllegalAccessException {Connection connection = null;PreparedStatement statement = null;ResultSet resultSet = null;try {connection = getConnection();statement = connection.prepareStatement(sql);paramHandler(statement,params);resultSet = statement.executeQuery();//获取表数据对象ResultSetMetaData metaData = resultSet.getMetaData();//获取字段个数int count = metaData.getColumnCount();List<T> list = new ArrayList<>();while(resultSet.next()){T t = clazz.newInstance();//获取字段名及数据for (int i = 1; i <= count; i++) {String fieldName = metaData.getColumnName(i);Object fieldVal = resultSet.getObject(fieldName);setField(t,fieldName,fieldVal);}list.add(t);}return list;} finally {DBUtil.close(connection,statement,resultSet);}}/*** 查询单个数据*/public static <T> T commonQueryObj(Class<T> clazz,String sql, Object... params) throws SQLException, InstantiationException, IllegalAccessException {Connection connection = null;PreparedStatement statement = null;ResultSet resultSet = null;try {connection = getConnection();statement = connection.prepareStatement(sql);paramHandler(statement,params);resultSet = statement.executeQuery();//获取表数据对象ResultSetMetaData metaData = resultSet.getMetaData();//获取字段个数int count = metaData.getColumnCount();if(resultSet.next()){T t = clazz.newInstance();//获取字段名及数据for (int i = 1; i <= count; i++) {String fieldName = metaData.getColumnName(i);Object fieldVal = resultSet.getObject(fieldName);setField(t,fieldName,fieldVal);}return t;}} finally {DBUtil.close(connection,statement,resultSet);}return null;}/*** 处理statement对象参数数据的处理器*/private static void paramHandler(PreparedStatement statement,Object... params) throws SQLException {for (int i = 0; i < params.length; i++) {statement.setObject(i+1,params[i]);}}/*** 获取当前类及其父类的属性对象* @param clazz class对象* @param name 属性名* @return 属性对象*/private static Field getField(Class<?> clazz,String name){for(Class<?> c = clazz;c != null;c = c.getSuperclass()){try {Field field = c.getDeclaredField(name);return field;} catch (NoSuchFieldException e) {} catch (SecurityException e) {}}return null;}/*** 设置对象中的属性* @param obj 对象* @param name 属性名* @param value 属性值*/private static void setField(Object obj,String name,Object value){Field field = getField(obj.getClass(), name);if(field != null){field.setAccessible(true);try {field.set(obj, value);} catch (IllegalArgumentException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();}}}
}

封装学生类

public class Student {private int id;private String name;private String sex;private int age;private float salary;private String course;//无参构造，有参构造，get，set，toString方法省略
}

测试类

public class Test01 {public static void main(String[] args) throws SQLException, InstantiationException, IllegalAccessException {String sql = "select * from student";List<Student> students = DBUtil.commonQueryList(Student.class, sql);//打印集合for (Student stu : students) {System.out.println(stu);}}
}

总结

1.连接池

​ 概念

​ 自定义连接池（两个版本，重点关注第二个版本）

​ Druid连接池