DBUtils及数据库连接池

DBUtil

查看一条记录

• BeanHandler
• ArrayHandler
• MapHandler
• ScalarHandler

查看集合

• BeanListHandler
• ArrayListHandler
• MapListHandler

DBUtilTest.java

public class DBUtilTest {

    public static Connection connection;

    /**
     * BeanHandler 查看一条记录
     * @throws SQLException
     */
    public static void testQuery() throws SQLException {
        connection = MySQLDButil.getConnection();
        String sql = "select * from emp where empno=?";
        QueryRunner runner = new QueryRunner();
        Emp query = runner.query(connection, sql, new BeanHandler<Emp>(Emp.class), 7369);
        System.out.println(query);
        connection.close();
    }

    /**
     * BeanListHandler 查看集合
     * @throws SQLException
     */
    public static void testList() throws SQLException {
        connection = MySQLDButil.getConnection();
        String sql = "select * from emp";
        QueryRunner runner = new QueryRunner();
        List<Emp> query = runner.query(connection, sql, new BeanListHandler<Emp>(Emp.class));
        for (Emp emp : query) {
            System.out.println(emp);
        }
        connection.close();
    }

    /**
     *  ArrayHandler 查看一条记录
     *  每一行的值会组成一个数组对象放进去
     * @throws SQLException
     */
    public static void testArray() throws SQLException {
        connection = MySQLDButil.getConnection();
        String sql = "select * from emp";
        QueryRunner runner = new QueryRunner();
        Object[] query = runner.query(connection, sql, new ArrayHandler());
        for (Object o: query) {
            System.out.println(o); //查看的是第一条记录
        }
        connection.close();
    }

    /**
     * ArrayListHandler 查看集合
     * 把表中的所有记录的值一条条放到List中，而每条具体值是放到Object[]中的
     * 7369--SMITH
     * 7499--ALLEN
     * ...
     * @throws SQLException
     */
    public static void testArrayList() throws SQLException {
        connection = MySQLDButil.getConnection();
        String sql = "select * from emp";
        QueryRunner runner = new QueryRunner();
        List<Object[]> query = runner.query(connection, sql, new ArrayListHandler());
        for (Object[] o: query) {
            System.out.println(o[0] +"--"+o[1]);
        }
        connection.close();
    }

    /**
     * MapHandler 查看一条记录
     * 将一条记录中列名和对应的值作为一整个键值对元素放到Map中
     * EMPNO----7369
     * ENAME----SMITH
     * ...
     * @throws SQLException
     */
    public static void tesMap() throws SQLException {
        connection = MySQLDButil.getConnection();
        String sql = "select * from emp";
        QueryRunner runner = new QueryRunner();
        Map<String, Object> query = runner.query(connection, sql, new MapHandler());
        Set<Map.Entry<String,Object>> entries = query.entrySet();
        for (Map.Entry<String ,Object> entry : entries) {
            System.out.println(entry.getKey()+"----"+entry.getValue());
        }
        connection.close();
    }

    /**
     * MapListHandler
     * @throws SQLException
     */
    public static void tesMapList() throws SQLException {
        connection = MySQLDButil.getConnection();
        String sql = "select * from emp";
        QueryRunner runner = new QueryRunner();
        List<Map<String, Object>> query = runner.query(connection, sql, new MapListHandler());
        for (Map<String, Object> q: query) {
            Set<Map.Entry<String,Object>> entries = q.entrySet();
            for (Map.Entry<String,Object> entry : entries) {
                System.out.println(entry.getKey()+"----"+entry.getValue());
            }
            System.out.println("----------");
        }
        connection.close();
    }

    /**
     * ScalarHandler 单值查询
     * 15
     * @throws SQLException
     */
    public static void tesScalar() throws SQLException {
        connection = MySQLDButil.getConnection();
        String sql = "select count(*) from emp";
        QueryRunner runner = new QueryRunner();
        Object query = runner.query(connection, sql, new ScalarHandler<>());
        System.out.println(query);
        connection.close();
    }   
     /**
     * 自定义handler对象
     * Emp{empno=7369, ename='SMITH', job='CLERK', mrg=7902, hiredate=1980-12-17, sal=800.0, comm=0.0, deptno=20}
     * @throws SQLException
     */
    public static void tesMyHandler() throws SQLException {
        connection = MySQLDButil.getConnection();
        String sql = "select * from emp where empno=?";
        QueryRunner runner = new QueryRunner();
        Emp query = runner.query(connection, sql, new ResultSetHandler<Emp>() {
            @Override
            public Emp handle(ResultSet resultSet) throws SQLException {
                Emp e = null;
                if (resultSet.next()) {
                    e = new Emp();
                    e.setEmpno(resultSet.getInt("empno"));
                    e.setEname(resultSet.getString("ename"));
                    e.setJob(resultSet.getString("job"));
                    e.setMrg(resultSet.getInt("mgr"));
                    e.setHiredate(resultSet.getString("hiredate"));
                    e.setSal(resultSet.getDouble("sal"));
                    e.setComm(resultSet.getDouble("comm"));
                    e.setDeptno(resultSet.getInt("deptno"));
                }
                return e;
            }
        },7369);
        System.out.println(query);
        connection.close();
    }

    /**
     * 插入数据
     */
    public static void insert() throws SQLException {
        String sql = "insert into emp(empno,ename) values(?,?)";
        connection = MySQLDButil.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        queryRunner.update(connection,sql,1234,"msb");
        connection.close();
    }

    /**
     * 修改数据
     */
    public static void update() throws SQLException {
        String sql = "update emp set ename=? where empno=?";
        connection = MySQLDButil.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        queryRunner.update(connection,sql,"msb123",1234);
        connection.close();
    }

    /**
     * 删除数据
     */
    public static void delete() throws SQLException {
        String sql = "delete from emp where empno=?";
        connection = MySQLDButil.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        queryRunner.update(connection,sql,1234);
        connection.close();
    }
    public static void main(String[] args) throws SQLException {
//        testQuery();
        testList();
//        testArray();
//        testArrayList();
//        tesMap();
//        tesMapList();
//        tesScalar();
//        tesMyHandler();
//        insert();
//        update();
        delete();
    }
}

数据库连接池

数据库连接池的目的是为了减少频繁开关连接的时间，提高整个系统的响应能力。
通过分析发现应该具备几个属性值：
1、初始大小
2、每次扩容的大小
3、连接池的最大个数
3、空闲连接的死亡时间

各种数据库连接池：
DBCP
C3P0
Druid
hikariCP

DBCP(几乎不用)

https://commons.apache.org/proper/commons-dbcp/configuration.html

Parameter	Default	desc
initialSize	0
maxTotal	8	可以同时从此池分配的活动连接的最大数量，或者为无限制的最大数量。
maxIdle	8	池中可以保持空闲（不释放多余连接）的最大连接数，或者为无限制的最大连接数。
minIdle	0
maxWaitMillis	indefinitely（不明确）

c3p0

c3p0的配置方式分为三种，分别是
1.直接在类的方法中设置连接参数，一般没人使用，最好使用配置文件
2.类路径下提供一个c3p0.properties文件
3.类路径下提供一个c3p0-config.xml文件

方式一

public static void main(String[] args) throws Exception {
    ComboPooledDataSource cpds = new ComboPooledDataSource();
    cpds.setDriverClass("com.mysql.cj.jdbc.Driver"); // load the jdbc driver
    cpds.setJdbcUrl("jdbc:mysql://localhost:3306/demo");
    cpds.setUser("root");
    cpds.setPassword("512");
    Connection connection = cpds.getConnection();
    System.out.println(connection);
    connection.close();
}

方式二 properties

1. 文件必须放到src目录。
2. 文件必须叫做c3p0.properties （源码层面C3P0Config规定好了 否则无法识别）

# c3p0.properties
c3p0.driverClass=com.mysql.cj.jdbc.Driver
c3p0.jdbcUrl=jdbc:mysql://localhost:3306/demo
c3p0.user=root
c3p0.password=512

c3p0.driverClass写错，找不到驱动类，也能把结果打印出来。
原因：JDBC具有自己的规范。JDBC4之前，驱动必须要写名字的，JDBC4及以后不写名称也会自动进行查找（SPI机制）

方式三 xml

1. 文件必须放到src目录。
2. 文件必须叫做c3p0-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!-- 方式三 -->
<c3p0-config>
    <default-config>
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/demo</property>
        <property name="user">root</property>
        <property name="password">512</property>
    </default-config>
</c3p0-config>

获取连接池和获取连接:

1. 使用ComboPooledDataSource
2. 通过工厂方法来完成操作
   静态工厂类DataSources，这个类可以创建未池化的数据源对象，可以将未池化的数据源池化,当然，这种方式也会去自动加载配置文件
3. 通过JNDI获取数据源。在C3P0基础上，自己定义自己的DataSource的源，来满足自己要求。

C3P0Test.java

package pool.c3p0;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @Author : LiuYan
 * @create 2021/2/16 10:01
 * 
 * 第一种 使用ComboPooledDataSource
 */
public class C3P0Test {

    public static Connection connection;
    public static ComboPooledDataSource dataSource;

    public static void getConnection() {
        dataSource = new ComboPooledDataSource();
    }

    public static void queryData() {
        try {
            connection=dataSource.getConnection();
            String sql = "select * from emp";
            PreparedStatement pstmt = connection.prepareStatement(sql);
            ResultSet resultSet = pstmt.executeQuery();
            while (resultSet.next()) {
                System.out.println(resultSet.getString("ename"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    public static void main(String[] args) throws Exception {
        /**
         * 方式一 直接在**类的方法中**设置连接参数，一般没人使用，最好使用配置文件
         */
//        ComboPooledDataSource cpds = new ComboPooledDataSource();
//        cpds.setDriverClass("com.mysql.cj.jdbc.Driver"); // load the jdbc driver
//        cpds.setJdbcUrl("jdbc:mysql://localhost:3306/demo");
//        cpds.setUser("root");
//        cpds.setPassword("512");
//        Connection connection = cpds.getConnection();
//        System.out.println(connection);
//        connection.close();
        getConnection();
        queryData();
    }
}

C3P0Test2.java

package pool.c3p0;

import com.mchange.v2.c3p0.DataSources;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

/**
 * @Author : LiuYan
 * @create 2021/2/16 11:04
 *
 * 第二种 通过工厂方法来完成操作
 */
public class C3P0Test2 {
    public static void main(String[] args) throws SQLException {
        // 1.获取未池化数据源对象
        DataSource ds_unpooled = DataSources.unpooledDataSource("jdbc:mysql://localhost:3306/demo",
                "root",
                "512");
        // 2.将未池化数据源对象进行池化
        //不能搭参数
//        DataSource ds_pooled = DataSources.pooledDataSource(ds_unpooled);
        // 用map可以写入参数
        Map overrides = new HashMap();
        overrides.put("maxStatements","200");
        overrides.put("maxPoolSize",new Integer(50));
        DataSource ds_pooled = DataSources.pooledDataSource(ds_unpooled,overrides);
        // 3.获取连接
        Connection connection = ds_pooled.getConnection();
        System.out.println(connection);
        connection.close();
    }
}

配置

Basic Pool Configuration

# 当没有空闲连接可用时，批量创建连接的个数
# 默认3
c3p0.acquireIncrement=3

# 初始化连接个数
# 默认3
c3p0.initialPoolSize=3

# 最大连接个数
# 默认15
c3p0.maxPoolSize=15

# 最小连接个数
# 默认3
c3p0.minPoolSize=3

其他配置

# 最大空闲时间。超过将被释放
# 默认0，即不限制。单位秒
c3p0.maxIdleTime=0

# 失败重试时间
# 默认30。如果非正数，则将一直阻塞地去获取连接。单位毫秒。
c3p0.acquireRetryAttempts=30

# 最大存活时间。超过将被释放
# 默认0，即不限制。单位秒
c3p0.maxConnectionAge=1800