Database Connection Pool
Introduction to Database Connection Pool
实际开发中“获得连接”或“释放资源”是非常消耗系统资源的两个过程，为了解决此类性能问题，通常情况我们采用连接池技术，来共享连接 Connection。这样我们就不需要每次都创建连接、释放连接了，因为这些操作都交给了连接池。
连接池的好处：使用池来管理 Connection，这样可以重复使用 Connection。当使用完 Connection 后，调用 Connection 的 close() 方法也不会真的关闭 Connection，而是把 Connection “归还”给池。
How to Use Database Connection Pool?
Java 为数据库连接池提供了公共的接口：javax.sql.DataSource，各个厂商需要让自己的连接池实现这个接口，这样应用程序可以方便的切换不同厂商的连接池。
常见的连接池有 DBCP 连接池，C3P0 连接池，Druid 连接池。
想了解更多，欢迎关注我的微信公众号：Renda_Zhang
Data Preparation
在 MySQL 中准备好以下数据
# 创建数据库CREATE DATABASE db5 CHARACTER SET utf8;# 使用数据库USE db5;# 创建员工表CREATE TABLE employee (eid INT PRIMARY KEY AUTO_INCREMENT,ename VARCHAR (20), -- 员工姓名age INT, -- 员工年龄sex VARCHAR (6), -- 员工性别salary DOUBLE,-- 薪水empdate DATE -- 入职日期);# 插入数据INSERT INTO employee (eid, ename, age, sex, salary, empdate) VALUES(NULL,'李清照',22,'女',4000,'-11-12');INSERT INTO employee (eid, ename, age, sex, salary, empdate) VALUES(NULL,'林黛玉',20,'女',5000,'-03-14');INSERT INTO employee (eid, ename, age, sex, salary, empdate) VALUES(NULL,'杜甫',40,'男',6000,'-01-01');INSERT INTO employee (eid, ename, age, sex, salary, empdate) VALUES(NULL,'李白',25,'男',3000,'-10-01');
DBCP 连接池
DBCP 是一个开源的连接池，是 Apache 成员之一，在企业开发中比较常见，Tomcat 内置的连接池。
创建项目并导入 jar包
首先将commons-dbcp和commons-pool两个 jar 包添加到myJar文件夹中，然后添加myJar库到项目的依赖中。
编写工具类
连接数据库表的工具类，采用 DBCP 连接池的方式来完成。
在 DBCP 包中提供了DataSource接口的实现类，我们要用的具体的连接池BasicDataSource类。
public class DBCPUtils {// 定义常量 保存数据库连接的相关信息public static final String DRIVERNAME = "com.mysql.jdbc.Driver";public static final String URL = "jdbc:mysql://localhost:3306/db5?characterEncoding=UTF-8";public static final String USERNAME = "root";public static final String PASSWORD = "root";// 创建连接池对象 (有 DBCP 提供的实现类)public static BasicDataSource dataSource = new BasicDataSource();// 使用静态代码块进行配置static{dataSource.setDriverClassName(DRIVERNAME);dataSource.setUrl(URL);dataSource.setUsername(USERNAME);dataSource.setPassword(PASSWORD);}// 获取连接的方法public static Connection getConnection() throws SQLException {// 从连接池中获取连接Connection connection = dataSource.getConnection();return connection;}// 释放资源方法public static void close(Connection con, Statement statement){if(con != null && statement != null){try {statement.close();// 归还连接con.close();} catch (SQLException e) {e.printStackTrace();}}}public static void close(Connection con, Statement statement, ResultSet resultSet){if(con != null && statement != null && resultSet != null){try {resultSet.close();statement.close();// 归还连接con.close();} catch (SQLException e) {e.printStackTrace();}}}}
测试工具类
/** 查询所有员工的姓名**/public class TestDBCP {public static void main(String[] args) throws SQLException {// 从 DBCP 连接池中拿到连接Connection con = DBCPUtils.getConnection();// 获取 Statement 对象Statement statement = con.createStatement();// 查询所有员工的姓名String sql = "select ename from employee";ResultSet resultSet = statement.executeQuery(sql);// 处理结果集while(resultSet.next()){String ename = resultSet.getString("ename");System.out.println("员工姓名: " + ename);}// 释放资源DBCPUtils.close(con, statement, resultSet);}}
C3P0 连接池
C3P0 是一个开源的 JDBC 连接池，支持 JDBC 3 规范和 JDBC 2 的标准扩展。目前使用它的开源项目有 Hibernate、Spring 等。
导入 jar 包及配置文件
首先将c3p0和mchange-commons-java两个 jar 包复制到myJar文件夹即可，IDEA 会自动导入。
然后导入配置文件c3p0-config.xml。c3p0-config.xml文件名不可更改，可以直接放到src下，也可以放到到资源文件夹中。
最后在项目下创建一个 resource 文件夹（专门存放资源文件），将配置文件放在 resource 目录下即可，创建连接池对象的时候会自动加载这个配置文件。
<c3p0-config><!--默认配置--><default-config><property name="driverClass">com.mysql.jdbc.Driver</property><property name="jdbcUrl">jdbc:mysql://localhost:3306/db5?characterEncoding=UTF-8</property><property name="user">root</property><property name="password">root</property><!-- initialPoolSize：初始化时获取三个连接，取值在 minPoolSize 与 maxPoolSize 之间。--><property name="initialPoolSize">3</property><!-- maxIdleTime：最大空闲时间，60 秒内未使用则连接被丢弃。若为 0 则永不丢弃。--><property name="maxIdleTime">60</property><!-- maxPoolSize：连接池中保留的最大连接数 --><property name="maxPoolSize">100</property><!-- minPoolSize: 连接池中保留的最小连接数 --><property name="minPoolSize">10</property></default-config><!--配置连接池 mysql--><named-config name="mysql"><property name="driverClass">com.mysql.jdbc.Driver</property><property name="jdbcUrl">jdbc:mysql://localhost:3306/db5</property><property name="user">root</property><property name="password">root</property><property name="initialPoolSize">10</property><property name="maxIdleTime">30</property><property name="maxPoolSize">100</property><property name="minPoolSize">10</property></named-config><!--可以配置多个连接池--></c3p0-config>
编写 C3P0 工具类
C3P0 提供的核心工具类ComboPooledDataSource，如果想使用连接池，就必须创建该类的对象。
使用默认配置：new ComboPooledDataSource();
使用命名配置：new ComboPooledDataSource("mysql");
public class C3P0Utils {// 使用指定的配置public static ComboPooledDataSource dataSource = new ComboPooledDataSource("mysql");// 获取连接的方法public static Connection getConnection() throws SQLException {return dataSource.getConnection();}// 释放资源public static void close(Connection con, Statement statement){if(con != null && statement != null){try {statement.close();// 归还连接con.close();} catch (SQLException e) {e.printStackTrace();}}}public static void close(Connection con, Statement statement, ResultSet resultSet){if(con != null && statement != null && resultSet != null){try {resultSet.close();statement.close();// 归还连接con.close();} catch (SQLException e) {e.printStackTrace();}}}}
测试工具类
// 查询姓名为李白的记录public class TestC3P0 {public static void main(String[] args) throws SQLException {// 获取连接Connection con = C3P0Utils.getConnection();// 获取预处理对象String sql = "select * from employee where ename = ?";PreparedStatement ps = con.prepareStatement(sql);// 设置占位符的值ps.setString(1,"李白");ResultSet resultSet = ps.executeQuery();// 处理结果集while (resultSet.next()){int eid = resultSet.getInt("eid");String ename = resultSet.getString("ename");int age = resultSet.getInt("age");String sex = resultSet.getString("sex");double salary = resultSet.getDouble("salary");Date date = resultSet.getDate("empdate");System.out.println(eid+" "+ename+" "+age+" "+sex+" "+salary+" "+date);}// 释放资源C3P0Utils.close(con, ps, resultSet);}}
Druid 连接池
Druid（德鲁伊）是阿里巴巴开发的为监控而生的数据库连接池，Druid 是目前最好的数据库连接池。在功能、性能、扩展性方面，都超过其他数据库连接池，同时加入了日志监控，可以很好的监控 DB 池连接和 SQL 的执行情况。
导入 jar 包及配置文件
首先导入druidjar 包。然后导入 properties 配置文件，可以叫任意名称，可以放在任意目录下，但是这里统一放到 resources 资源目录。
driverClassName=com.mysql.jdbc.Driverurl=jdbc:mysql://127.0.0.1:3306/db5?characterEncoding=UTF-8username=rootpassword=rootinitialSize=5maxActive=10maxWait=3000
编写 Druid 工具类
通过工厂来来获取DruidDataSourceFactory类的createDataSource(Properties p)方法，其参数可以是一个属性集对象。
public class DruidUtils {// 定义成员变量public static DataSource dataSource;// 静态代码块static{try {// 创建属性集对象Properties p = new Properties();// 加载配置文件 Druid 连接池不能够主动加载配置文件，需要指定文件InputStream inputStream = DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties");// 使用 Properties 对象的 load 方法从字节流中读取配置信息p.load(inputStream);// 通过工厂类获取连接池对象dataSource = DruidDataSourceFactory.createDataSource(p);} catch (Exception e) {e.printStackTrace();}}// 获取连接的方法public static Connection getConnection(){try {return dataSource.getConnection();} catch (SQLException e) {e.printStackTrace();return null;}}// 释放资源public static void close(Connection con, Statement statement){if(con != null && statement != null){try {statement.close();// 归还连接con.close();} catch (SQLException e) {e.printStackTrace();}}}public static void close(Connection con, Statement statement, ResultSet resultSet){if(con != null && statement != null && resultSet != null){try {resultSet.close();statement.close();// 归还连接con.close();} catch (SQLException e) {e.printStackTrace();}}}}
测试工具类
// 查询薪资在 3000 - 5000 元之间的员工姓名public class TestDruid {public static void main(String[] args) throws SQLException {// 获取连接Connection con = DruidUtils.getConnection();// 获取 Statement 对象Statement statement = con.createStatement();// 执行查询ResultSet resultSet = statement.executeQuery("select ename from employee where salary between 3000 and 5000");// 处理结果集while(resultSet.next()){String ename = resultSet.getString("ename");System.out.println(ename);}// 释放资源DruidUtils.close(con,statement,resultSet);}}
Commons DbUtils
Introduction toDbUtils
Commons DbUtils是 Apache 组织提供的一个对 JDBC 进行简单封装的开源工具类库，使用它能够简化 JDBC 应用程序的开发，同时也不会影响程序的性能。
DbUtils就是 JDBC 的简化开发工具包，需要在项目导入commons-dbutilsjar 包。
DbUtils核心功能：
QueryRunner中提供对 SQL 语句操作的 API。ResultSetHandler接口用于定义 select 操作后封装结果集。DbUtils类是一个定义了关闭资源与事务处理相关方法的工具类。
相关知识
表和类之间的关系
整个表可以看做是一个类。
表中的一列，对应类中的一个成员属性。
表中的一行记录，对应一个类的实例（对象）。
JavaBean 组件
JavaBean 是一个开发中通常用于封装数据的类：
需要实现序列化接口，Serializable（暂时可以省略）提供私有字段：private 类型变量名提供 getter 和 setter提供空参构造
创建一个 entity 包，专门用来存放 JavaBean 类，然后在 entity 包中创建一个和数据库的 Employee 表对应的 Employee 类。
public class Employee implements Serializable {private int eid;private String ename;private int age;private String sex;private double salary;private Date empdate;// getter setter...}
UseDbUtilsfor CRUD Operations
QueryRunner的创建
手动模式
// 创建 QueryRunner 对象QueryRunner qr = new QueryRunner();
自动模式
// 传入数据库连接池对象QueryRunner qr2 = new QueryRunner(DruidUtils.getDataSource());
自动模式需要传入连接池对象
// 获取连接池对象public static DataSource getDataSource(){return dataSource;}
QueryRunner实现增、删、改操作
步骤：
创建QueryRunner（手动或自动）占位符方式编写SQL设置占位符参数执行
添加：
@Testpublic void testInsert() throws SQLException {// 手动模式创建 QueryRunnerQueryRunner qr = new QueryRunner();// 编写占位符方式 SQLString sql = "insert into employee values(?,?,?,?,?,?)";// 设置占位符的参数Object[] param = {null,"布莱尔",20,"女",10000,"1990-12-26"};// 执行 update 方法Connection con = DruidUtils.getConnection();int i = qr.update(con, sql, param);// 释放资源DbUtils.closeQuietly(con);}
修改：
@Testpublic void testUpdate() throws SQLException {// 自动模式创建 QueryRunner 对象，传入数据库连接池QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());// 编写 SQLString sql = "update employee set salary = ? where ename = ?";// 设置占位符参数Object[] param = {0, "布莱尔"};// 执行 update，不需要传入连接对象qr.update(sql, param);}
删除：
@Testpublic void testDelete() throws SQLException {QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());String sql = "delete from employee where eid = ?";//只有一个参数，不需要创建数组qr.update(sql, 1);}
QueryRunner实现查询操作
ResultSetHandler可以对查询出来的ResultSet结果集进行处理，达到一些业务上的需求。
query方法的返回值都是泛型，具体的返回值类型会根据结果集的处理方式发生变化。
query(String sql, ResultSetHandler rsh, Object[] param)-- 自动模式创建QueryRunner, 执行查询。
query(Connection con, String sql, ResultSetHandler rsh, Object[] param)-- 手动模式创建QueryRunner, 执行查询。
/** 查询 id 为 5 的记录，封装到数组中** ArrayHandler：* 将结果集的第一条数据封装到 Object[] 数组中，* 数组中的每一个元素就是这条记录中的每一个字段的值**/@Testpublic void testFindById() throws SQLException {// 创建 QueryRunnerQueryRunner qr = new QueryRunner(DruidUtils.getDataSource());// 编写 SQLString sql = "select * from employee where eid = ?";// 执行查询Object[] query = qr.query(sql, new ArrayHandler(), 5);// 获取数据System.out.println(Arrays.toString(query));}/*** 查询所有数据，封装到 List 集合中** ArrayListHandler：* 可以将每条数据先封装到 Object[] 数组中,* 再将数组封装到集合中*/@Testpublic void testFindAll() throws SQLException {//1.创建QueryRunnerQueryRunner qr = new QueryRunner(DruidUtils.getDataSource());//2.编写SQLString sql = "select * from employee";//3.执行查询List<Object[]> query = qr.query(sql, new ArrayListHandler());//4.遍历集合获取数据for (Object[] objects : query) {System.out.println(Arrays.toString(objects));}}/*** 查询 id 为 3 的记录,封装到指定 JavaBean 中** BeanHandler：* 将结果集的第一条数据封装到 JavaBean 中**/@Testpublic void testFindByIdJavaBean() throws SQLException {QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());String sql = "select * from employee where eid = ?";Employee employee = qr.query(sql, new BeanHandler<Employee>(Employee.class), 3);System.out.println(employee);}/** 查询薪资大于 3000 的所员工信息，* 封装到 JavaBean 中再封装到 List 集合中** BeanListHandler：* 将结果集的每一条和数据封装到 JavaBean 中，* 再将 JavaBean 放到 List 集合中* */@Testpublic void testFindBySalary() throws SQLException {QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());String sql = "select * from employee where salary > ?";List<Employee> list = qr.query(sql, new BeanListHandler<Employee>(Employee.class), 3000);for (Employee employee : list) {System.out.println(employee);}}/** 查询姓名是布莱尔的员工信息，* 将结果封装到 Map 集合中** MapHandler：* 将结果集的第一条记录封装到 Map 中，* key 对应的是列名，* value 对应的是列的值**/@Testpublic void testFindByName() throws SQLException {QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());String sql = "select * from employee where ename = ?";Map<String, Object> map = qr.query(sql, new MapHandler(), "布莱尔");Set<Map.Entry<String, Object>> entries = map.entrySet();for (Map.Entry<String, Object> entry : entries) {System.out.println(entry.getKey() +" = " +entry.getValue());}}/** 查询所有员工的薪资总额** ScalarHandler：* 用于封装单个的数据**/@Testpublic void testGetSum() throws SQLException {QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());String sql = "select sum(salary) from employee";Double sum = (Double) qr.query(sql, new ScalarHandler<>());System.out.println("员工薪资总额: " + sum);}
Database Batch Process
What is Database Batch Process？
批处理操作数据库：批处理指的是一次操作中执行多条 SQL 语句，批处理相比于一次一次执行效率会提高很多。当向数据库中添加大量的数据时，需要用到批处理。
Implement Batch Processing
Statement和PreparedStatement都支持批处理操作。
MySQL批处理是默认关闭的，所以需要加一个参数才打开MySQL数据库批处理，在url中添加rewriteBatchedStatements=true。
url=jdbc:mysql://127.0.0.1:3306/db5?characterEncoding=UTF-8&rewriteBatchedStatements=true
创建一张表
CREATE TABLE testBatch (id INT PRIMARY KEY AUTO_INCREMENT,uname VARCHAR(50))
测试向表中插入一万条数据
public class TestBatch {public static void main(String[] args) {try {// 获取连接Connection con = DruidUtils.getConnection();// 获取预处理对象String sql ="insert into testBatch(uname) values(?)";PreparedStatement ps = con.prepareStatement(sql);// 创建 for 循环来设置占位符参数for (int i = 0; i < 10000 ; i++) {ps.setString(1, "小明"+i);// 将 SQL 添加到批处理列表ps.addBatch();}long start = System.currentTimeMillis();// 统一批量执行ps.executeBatch();long end = System.currentTimeMillis();System.out.println("插入10000条数据使用: "+(end-start)+" 毫秒!");} catch (SQLException e) {e.printStackTrace();}}}
MySQL Metadata
What is Metadata in MySQL
除了表之外的数据都是元数据，可以分为三类：
查询结果信息，UPDATE 或 DELETE语句 受影响的记录数。数据库和数据表的信息，包含了数据库及数据表的结构信息。MySQL服务器信，包含了数据库服务器的当前状态，版本号等。
常用命令
select version();获取 MySQL 服务器的版本信息
show status;查看服务器的状态信息
show columns from table_name;显示表的字段信息等，和desc table_name一样
show index from table_name;显示数据表的详细索引信息，包括主键
show databases:列出所有数据库
show tables;显示当前数据库的所有表
select database();获取当前的数据库名
使用 JDBC 获取元数据
通过 JDBC 也可以获取到元数据，比如，数据库的相关信息，或者，使用程序查询一个不熟悉的表时，可以通过获取元素据信息来了解表中有多少个字段、字段的名称、字段的类型。
DatabaseMetaData描述数据库的元数据对象
ResultSetMetaData描述结果集的元数据对象
public class TestMetaData {// 获取数据库相关的元数据信息@Testpublic void testDataBaseMetaData() throws SQLException {Connection connection = DruidUtils.getConnection();// 获取代表数据库的元数据对象DatabaseMetaData metaData = connection.getMetaData();// 获取数据库相关的元数据信息String url = metaData.getURL();System.out.println("数据库URL: " + url);String userName = metaData.getUserName();System.out.println("当前用户: " + userName );String productName = metaData.getDatabaseProductName();System.out.println("数据库产品名: " + productName);String version = metaData.getDatabaseProductVersion();System.out.println("数据库版本: " + version);String driverName = metaData.getDriverName();System.out.println("驱动名称: " + driverName);// 判断当前数据库是否只允许只读boolean b = metaData.isReadOnly();if(b){System.out.println("当前数据库只允许读操作!");}else{System.out.println("不是只读数据库");}connection.close();}// 获取结果集中的元数据信息@Testpublic void testResultSetMetaData() throws SQLException {Connection con = DruidUtils.getConnection();PreparedStatement ps = con.prepareStatement("select * from employee");ResultSet resultSet = ps.executeQuery();// 获取结果集元素据对象ResultSetMetaData metaData = ps.getMetaData();// 获取当前结果集共有多少列int count = metaData.getColumnCount();System.out.println("当前结果集中共有: "+count+"列");// 获结果集中列的名称和类型for (int i = 1; i <= count; i++) {String columnName = metaData.getColumnName(i);System.out.println("列名: "+columnName);String columnTypeName = metaData.getColumnTypeName(i);System.out.println("类型: "+columnTypeName);}DruidUtils.close(con, ps, resultSet);}}
