JDBC (事务、隔离性、数据库连接池(C3P0,DBCP,Druid)、DBUtils)

事务

引入事务的必要性

未考虑事务的情况

现在想对如下表进行转账操作，AA给BB转100，代码如下：

public class TransactionTest { // 未考虑事务的情况 @Test public void test() throws Exception { String sql1 = "update user_table set balance = balance - 100 where user = ?"; update(sql1, "AA"); String sql2 = "update user_table set balance = balance + 100 where user = ?"; update(sql2, "BB"); } public void update(String sql, Object ... args) { Connection connection = null; PreparedStatement ps = null; try { connection = JDBCUtils.getConnection(); ps = connection.prepareStatement(sql); for (int i = 0; i < args.length; i++) { ps.setObject(i+1, args[i]); } ps.execute(); } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(connection, ps); } } }

结果：

这样看起来目的也达到了，但如果在AA转出钱之后程序出现了异常就会导致BB收不到钱()下面代码模拟了异常：

@Test public void test() throws Exception { String sql1 = "update user_table set balance = balance - 100 where user = ?"; update(sql1, "AA"); // 异常出现 System.out.println(1/0); String sql2 = "update user_table set balance = balance + 100 where user = ?"; update(sql2, "BB"); }

我们再来查看数据库，显然钱对不上了：

所以，要引入事务

也就是说，我们希望故障发生时这些数据能回滚到初始状态(即二人钱都是1000)，想让AA转钱，BB收钱之后再把数据提交，而不是AA转钱，BB收钱之间提交，因为这时即使回滚也回不到他们两人都是1000的状态了(此时AA:900，BB:1000)。

回滚只能回到最近一次提交的状态。

哪些操作会导致数据库的提交

因此，要想避免自动提交就要避免上述三点：

因为DML操作居多，所以不用太在意 直接设置即可 在AA转出之后，先不要关闭连接，而是接着用这个连接去操作BB

考虑到事务的情况

// 考虑到事务的情况 @Test public void test2() { Connection connection = null; try { connection = JDBCUtils.getConnection(); // 先不让它自动提交 connection.setAutoCommit(false); String sql1 = "update user_table set balance = balance - 100 where user = ?"; updateTx(connection, sql1, "AA"); // 异常出现 System.out.println(1/0); String sql2 = "update user_table set balance = balance + 100 where user = ?"; updateTx(connection, sql2, "BB"); // 都执行完了再提交 connection.commit(); } catch (Exception e) { e.printStackTrace(); try { connection.rollback(); } catch (SQLException throwables) { throwables.printStackTrace(); } } finally { JDBCUtils.closeResource(connection, null); } } public void updateTx(Connection connection, String sql, Object ... args) { PreparedStatement ps = null; try { ps = connection.prepareStatement(sql); for (int i = 0; i < args.length; i++) { ps.setObject(i+1, args[i]); } ps.execute(); } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(null, ps); } }

当异常出现时，可以看到数据库并没有更改：

数据库的并发问题

几种事务隔离级别

命令行验证不同隔离性

https://www.bilibili.com/video/BV1eJ411c7rf?p=40

Java代码验证不同隔离性

https://www.bilibili.com/video/BV1eJ411c7rf?p=41

通常只需保证避免脏读就行。

DAO及其实现类

BaseDAO (database access object)

public abstract class BaseDAO { // 考虑事务之后的更新操作 public void update(Connection connection, String sql, Object ... args) { PreparedStatement ps = null; try { ps = connection.prepareStatement(sql); for (int i = 0; i < args.length; i++) { ps.setObject(i+1, args[i]); } ps.execute(); } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(null, ps); } } // 考虑事务之后的查询操作 public <T> T queryMulti(Connection connection, Class<T> clazz, String sql, Object ... args) { PreparedStatement ps = null; ResultSet rs = null; try { ps = connection.prepareStatement(sql); for (int i = 0; i < args.length; i++) { ps.setObject(i+1, args[i]); } rs = ps.executeQuery(); ResultSetMetaData metaData = rs.getMetaData(); int columnCount = metaData.getColumnCount(); if (rs.next()) { T t = clazz.newInstance(); for (int i = 0; i < columnCount; i++) { Object columnValue = rs.getObject(i + 1); String columnLable = metaData.getColumnLabel(i + 1); Field field = clazz.getDeclaredField(columnLable); field.setAccessible(true); field.set(t, columnValue); } return t; } } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(null, ps, rs); } return null; } // 查询结果包含多条记录 public <T> List<T> queryMultiItems(Connection connection, Class<T> clazz, String sql, Object ... args) { PreparedStatement ps = null; ResultSet rs = null; try { ps = connection.prepareStatement(sql); for (int i = 0; i < args.length; i++) { ps.setObject(i+1, args[i]); } rs = ps.executeQuery(); ResultSetMetaData metaData = rs.getMetaData(); int columnCount = metaData.getColumnCount(); ArrayList<T> list = new ArrayList<>(); while (rs.next()) { T t = clazz.newInstance(); for (int i = 0; i < columnCount; i++) { Object columnValue = rs.getObject(i + 1); String columnLable = metaData.getColumnLabel(i + 1); Field field = clazz.getDeclaredField(columnLable); field.setAccessible(true); field.set(t, columnValue); } list.add(t); } return list; } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(null, ps, rs); } return null; } // 用于其它的查询，如count(*)、max等 public <E> E getValue(Connection connection, String sql, String ... args) { PreparedStatement ps = null; ResultSet rs = null; try { ps = connection.prepareStatement(sql); for (int i = 0; i < args.length; i++) { ps.setObject(i+1, args[i]); } rs = ps.executeQuery(); if (rs.next()) { return (E) rs.getObject(1); } } catch (SQLException throwables) { throwables.printStackTrace(); } finally { JDBCUtils.closeResource(null, ps, rs); } return null; } }

所需类的接口(这里只写了Customer)

public interface CustomerDAO { // 向Customer表中插入数据 void insert(Connection connection, Customer cust); // 根据id删除信息 void deleteById(Connection connection, int id); // 根据传来的对象去修改数据库中的数据 void update(Connection connection, Customer cust); // 根据指定id查询Customer对象(某一条) Customer getCustomerById(Connection connection, int id); // 查询所有数据 List<Customer> getAll(Connection connection); // 返回表中的记录数 long getCount(Connection connection); // 查询最大的生日 Date getMaxBirth(Connection conn); }

实现类(Customer)

public class customerDAOImpl extends BaseDAO implements CustomerDAO { @Override public void insert(Connection connection, Customer cust) { String sql = "insert into customers(name, email, birth) values(?,?,?)"; update(connection, sql, cust.getName(), cust.getEmail(), cust.getBirth()); } @Override public void deleteById(Connection connection, int id) { String sql = "delete from customers where id=?"; update(connection, sql, id); } @Override public void update(Connection connection, Customer cust) { String sql = "update customers set name = ?, email = ?, birth = ? where id = ?"; update(connection, sql, cust.getName(), cust.getEmail(), cust.getBirth(), cust.getId()); } @Override public Customer getCustomerById(Connection connection, int id) { String sql = "select id, name, email, birth from customers where id = ?"; Customer customer = queryMulti(connection, Customer.class, sql, id); return customer; } @Override public List<Customer> getAll(Connection connection) { String sql = "select id, name, email, birth from customers"; List<Customer> customerList = queryMultiItems(connection, Customer.class, sql); return customerList; } @Override public long getCount(Connection connection) { String sql = "select count(*) from customers"; return getValue(connection, sql); } @Override public Date getMaxBirth(Connection conn) { String sql = "select max(birth) from customers"; return getValue(conn, sql); } }

升级版(使用反射)

在Cusotmer查询的时候，传了Customer.class这个参数，但我们操作Customer表，再传这个参数就有些多余了，我们希望能不传参也能实现查询的功能：

@Override public Customer getCustomerById(Connection connection, int id) { String sql = "select id, name, email, birth from customers where id = ?"; Customer customer = queryMulti(connection, Customer.class, sql, id); return customer; }

该如何做呢？

先给BaseDAO加上泛型：

然后让Impl继承父类并指明泛型：

再把之前的泛型方法修改：

我们的目的是不同的类继承BaseDAO就传不同的泛型，在不同的类(如Customer、Order等)调用update等方法之前，clazz就已经获得了类型，因此在BaseDAO中设置了属性，并希望在它的子类(也就是Customer、Order等)创建对象时就把这个clazz的类型确定下来，这要如何做呢？：

在这个时机赋值有如下几种方法：

显式赋值，但不可行，因为一行代码搞不定代码块赋值，可行，但要是非静态构造器，可行

这里选用代码块。

使用反射

在new子类对象的时候，会调用super()加载父类结构，因而会加载父类的构造器和代码块，所以在BaseDAO中添加如下代码块：

{ Type genericSuperclass = this.getClass().getGenericSuperclass(); System.out.println(genericSuperclass); ParameterizedType paramType = (ParameterizedType) genericSuperclass; System.out.println(paramType); Type[] TypeArguments = paramType.getActualTypeArguments();// 获取父类的泛型参数 System.out.println(TypeArguments); clazz = (Class<T>) TypeArguments[0]; // 泛型的第一个参数 当new Customer对象时，它为Customer System.out.println("clazz: " + clazz); }

当程序运行时：

@Test public void testSelect() { Connection connection = null; try { connection = JDBCUtils.getConnection(); Customer customer = CustomerTest.getCustomerById(connection, 20); System.out.println(customer); } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(connection, null); } }

结果：

https://www.bilibili.com/video/BV1eJ411c7rf?p=45

数据库连接池

使用连接池的必要性

连接池技术

几种连接池技术

C3P0数据库连接池的两种实现方式

使用这个需要把相应的驱动导入并build path。

方式1 直接操作

// 方式一 直接操作 @Test public void test1() throws Exception { ComboPooledDataSource cpds = new ComboPooledDataSource(); cpds.setDriverClass("com.mysql.jdbc.Driver"); cpds.setJdbcUrl("jdbc:mysql://localhost:3306/test"); cpds.setUser("root"); cpds.setPassword("root"); // 设置初始数据库连接池中的连接数 cpds.setInitialPoolSize(10); Connection conn = cpds.getConnection(); System.out.println(conn); }

方式2 读取配置文件

这种方式要求我们在src下新建一个文件：c3p0-config.xml，文件名一定要是这个。

然后在里面写配置信息，例如：

<?xml version="1.0" encoding="UTF-8" ?> <c3p0-config> <named-config name="hc3p0"> <!-- 连接所需的4个信息--> <property name="driverClass">com.mysql.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property> <property name="user">root</property> <property name="password">root</property> <!-- 数据库连接池管理的信息--> <!-- 当连接池中的连接数不够时c3p0向服务器一次性申请的连接数--> <property name="acquireIncrement">5</property> <!-- 数据库连接池中初始化的连接数--> <property name="initialPoolSize">10</property> <!-- c3p0数据库连接池维护的最少连接数--> <property name="minPoolSize">10</property> <!-- c3p0数据库连接池维护的最多连接数--> <property name="maxPoolSize">100</property> <!-- 维护的最多Statement个数--> <property name="maxStatements">50</property> <!-- 每个连接中可以最多使用的Statement的个数--> <property name="maxStatementsPerConnection">2</property> </named-config> </c3p0-config> // 方式二 使用配置文件 @Test public void test2() throws SQLException { ComboPooledDataSource cpds = new ComboPooledDataSource("hc3p0"); // 这里的参数要和上面的xml文件<named-config name="hc3p0">保持一致 Connection conn = cpds.getConnection(); System.out.println(conn); }

结果：

使用连接池的连接

现在就可以使用连接池的连接来执行之前的操作了：

将之前用的JDBCUtils拷贝一份到c3p0包下，然后对获取连接的操作做如下更改：

public static Connection getConnection() throws Exception{ Connection conn = cpds.getConnection(); System.out.println("使用了连接池"); return conn; }

之后测试功能，这里就简写了，没有关闭连接：

@Test public void testSelectC3p0() throws Exception { Connection connection = JDBCUtils.getConnection(); Customer customer = CustomerTest.getCustomerById(connection, 20); System.out.println(customer); }

结果：

DBCP数据库连接池的两种实现方式

同样，需要把驱动导入并build path：

常用配置

dbcp连接池常用基本配置属性 1.initialSize ：连接池启动时创建的初始化连接数量（默认值为0） 2.maxActive ：连接池中可同时连接的最大的连接数（默认值为8，调整为20，高峰单机器在20并发左右，自己根据应用场景定） 3.maxIdle：连接池中最大的空闲的连接数，超过的空闲连接将被释放，如果设置为负数表示不限制（默认为8个，maxIdle不能设置太小，因为假如在高负载的情况下，连接的打开时间比关闭的时间快，会引起连接池中idle的个数 上升超过maxIdle，而造成频繁的连接销毁和创建，类似于jvm参数中的Xmx设置) 4.minIdle：连接池中最小的空闲的连接数，低于这个数量会被创建新的连接（默认为0，调整为5，该参数越接近maxIdle，性能越好，因为连接的创建和销毁，都是需要消耗资源的；但是不能太大，因为在机器很空闲的时候，也会创建低于minidle个数的连接，类似于jvm参数中的Xmn设置） 5.maxWait ：最大等待时间，当没有可用连接时，连接池等待连接释放的最大时间，超过该时间限制会抛出异常，如果设置-1表示无限等待（默认为无限，调整为60000ms，避免因线程池不够用，而导致请求被无限制挂起） 6.poolPreparedStatements：开启池的prepared（默认是false，未调整，经过测试，开启后的性能没有关闭的好。） 7.maxOpenPreparedStatements：开启池的prepared 后的同时最大连接数（默认无限制，同上，未配置） 8.minEvictableIdleTimeMillis ：连接池中连接，在时间段内一直空闲， 被逐出连接池的时间 9.removeAbandonedTimeout ：超过时间限制，回收没有用(废弃)的连接（默认为 300秒，调整为180） 10.removeAbandoned ：超过removeAbandonedTimeout时间后，是否进 行没用连接（废弃）的回收（默认为false，调整为true)

方式1 直接操作

@Test public void test1() throws SQLException { BasicDataSource source = new BasicDataSource(); source.setDriverClassName("com.mysql.jdbc.Driver"); source.setUrl("jdbc:mysql:///test"); source.setUsername("root"); source.setPassword("root"); // 设置其他的一些参数 source.setInitialSize(10); source.setMaxActive(10); Connection conn = source.getConnection(); System.out.println(conn); } 结果： jdbc:mysql:///test, UserName=root@localhost, MySQL Connector Java

方式2 读取配置文件

需要写properties文件：

driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql:///test username=root password=root initialSize=10 @Test public void test2() throws Exception { Properties pros = new Properties(); // 方式 1 InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("dbcp.properties"); // 方式 2 // FileInputStream is = new FileInputStream(new File("src/dbcp.properties")); pros.load(is); DataSource source = BasicDataSourceFactory.createDataSource(pros); Connection conn = source.getConnection(); System.out.println(conn); } 结果： jdbc:mysql:///test, UserName=root@localhost, MySQL Connector Java

测试dbcp连接池

@Test public void testSelectDbcp() throws Exception { Connection conn = JDBCUtils.getConnection(); Customer customer = CustomerTest.getCustomerById(conn, 20); System.out.println(customer); } 结果： Customer{id=20, name='秦岚', email='qinlan@gmail.com', birth=1979-07-17} public class JDBCUtils { private static DataSource source = null; static { try { Properties pros = new Properties(); InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("dbcp.properties"); pros.load(is); source = BasicDataSourceFactory.createDataSource(pros); } catch (Exception e) { e.printStackTrace(); } } public static Connection getConnection() throws Exception{ Connection conn = source.getConnection(); return conn; } }

https://www.bilibili.com/video/BV1eJ411c7rf?p=49

Druid数据库连接池的两种实现方式

老规矩，先加载驱动：

这里就不演示方法1了，直接上读取配置文件的代码：

driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql:///test username=root password=root initialSize=8 @Test public void test1() throws Exception { Properties pros = new Properties(); pros.load(ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties")); DataSource source = DruidDataSourceFactory.createDataSource(pros); Connection conn = source.getConnection(); System.out.println(conn); } 结果： com.mysql.jdbc.JDBC4Connection@243c4f91

DBUtils的使用

连接池解决的是获取数据库的连接，而增删改查(CURD)是很常见的操作，因此Java将这些操作做了封装，只需调用相应的方法就可以了。

insert操作

package dbutils; import dbcp.JDBCUtils; import org.apache.commons.dbutils.QueryRunner; import org.junit.Test; import java.sql.Connection; // insert操作的测试 @Test public void test1() { Connection conn = null; try { QueryRunner runner = new QueryRunner(); conn = JDBCUtils.getConnection(); String sql = "insert into customers(name, birth, email) values(?,?,?)"; runner.update(conn, sql, "索隆", "1988-9-23", "sl@gmail.com"); } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(conn, null); } }

说明：

这里的连接池随便选哪个都行，这里使用的是dbcp在插入中文的时候数据库里出现了??，解决方案是在properties文件中的url后面加上characterEncoding=utf-8，即：url=jdbc:mysql:///test?characterEncoding=utf-8其实这个update的源码和我们之前写的源码核心部分是差不多的，只是人家的代码健壮性好一些update方法的返回值也是被影响的行数

关于QueryRunner的有参和无参构造器

我们能发现这个QueryRunner主要有有无参的和有参的两种构造器，有参的需要传一个DataSource接口的实现类，下面来看看都有什么区别：

无参构造器：就像上面的insert操作一样，无参构造器是这样的：QueryRunner runner = new QueryRunner();这时如果要调用update或者query方法需要把Connection传进去，并且执行完之后不会帮你把Connection关闭，你得自己关闭，但是结果集和Statement会帮你关闭，通常用这种方式来完成和事务相关的操作；有参构造器：QueryRunner runner = new QueryRunner(DataSource);这时如果要调用update或者query方法就不需要再把Connection传进去，因为DataSource就能获取到Connection，并且执行完之后会帮你把Connection、结果集和Statement一并关闭，这种方式可以用来完成一次操作就结束的业务。

delete操作

// delete操作的测试 @Test public void test2() { Connection conn = null; try { QueryRunner runner = new QueryRunner(); conn = JDBCUtils.getConnection(); String sql = "delete from customers where id = ?"; runner.update(conn, sql, 24); } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(conn, null); } }

select操作

为了进行select操作，我们发现了几个重载的query方法：

现在推荐使用的就那么几个，这里选择上图蓝色行的那个。

分析：

第一个参数是Connection的实现类的对象，这个不论是我们自己写的还是调用连接池都能获得第二个参数就是sql语句第四个参数是与sql配套的可变参数第三个参数点进去一看是个接口： 于是查看其实现类，有好几个！：

BeanHandler

由于查询的时候需求是不同的，根据不同的需求来选用不同的实现类，如果想让他返回一个对象，就可以用BeanHandler，注意它的构造器要求传一个你想把数据保存到那个表的类的class：

BeanHandler beanHandler = new BeanHandler<>(Customer.class); // select测试 public void test3() { Connection conn = null; try { QueryRunner runner = new QueryRunner(); conn = JDBCUtils.getConnection(); String sql = "select name, email from cUstomers where id = ?"; BeanHandler<Customer> beanHandler = new BeanHandler<>(Customer.class); Customer customer = runner.query(conn, sql, beanHandler, 10); System.out.println(customer); } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(conn, null); } } 结果： Customer{id=0, name='周杰伦', email='zhoujl@sina.com', birth=null}

以上是返回一条记录，那么返回多条记录也是同理：

可以使用BeanListHandler。

@Test public void test4() { Connection conn = null; try { QueryRunner runner = new QueryRunner(); conn = JDBCUtils.getConnection(); String sql = "select name, email from cUstomers where id < ?"; BeanListHandler<Customer> beanListHandler = new BeanListHandler<>(Customer.class); List<Customer> customerList = runner.query(conn, sql, beanListHandler, 10); customerList.forEach(s -> System.out.println(s)); } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(conn, null); } } 结果： Customer{id=0, name='汪峰', email='wf@126.com', birth=null} Customer{id=0, name='王菲', email='wangf@163.com', birth=null} Customer{id=0, name='林志玲', email='linzl@gmail.com', birth=null} Customer{id=0, name='汤唯', email='tangw@sina.com', birth=null} Customer{id=0, name='成龙', email='Jackey@gmai.com', birth=null} Customer{id=0, name='迪丽热巴', email='reba@163.com', birth=null} Customer{id=0, name='刘亦菲', email='liuyifei@qq.com', birth=null} Customer{id=0, name='陈道明', email='bdf@126.com', birth=null}

其实这个BeanHandler内部的实现和我们之前写的差不多，见下图：

MapHandler

以这个作为query的参数来保存查询结果是将属性和值以键值对的形式存储的：

// select测试 使用MapHandler @Test public void test5() { Connection conn = null; try { QueryRunner runner = new QueryRunner(); conn = JDBCUtils.getConnection(); String sql = "select name, email from cUstomers where id = ?"; MapHandler handler = new MapHandler(); Map<String, Object> query = runner.query(conn, sql, handler, 10); System.out.println(query); } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(conn, null); } } 结果： {name=周杰伦, email=zhoujl@sina.com}

聚合函数

有这种需求的时候可以使用ScalarHandler ：

// select 特殊表达式，如count等 @Test public void test6() { Connection conn = null; try { QueryRunner runner = new QueryRunner(); conn = JDBCUtils.getConnection(); String sql = "select count(*) from cUstomers"; ScalarHandler handler = new ScalarHandler(); long rs = (long) runner.query(conn, sql, handler); System.out.println(rs); } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(conn, null); } } 结果： 16

自定义ResultSetHandler

如果已提供的ResultSetHandler的实现类无法满足现有的要求，那么可以自己定义一个实现类。

我们自定义这样一个实现类，将count查到的结果*10(没有实际意义，只是演示一下)：

// 自定义ResultSetHandler的实现类 @Test public void test7() { Connection conn = null; try { QueryRunner runner = new QueryRunner(); conn = JDBCUtils.getConnection(); String sql = "select count(*) from cUstomers"; ResultSetHandler<Long> handler = new ResultSetHandler<>(){ @Override public Long handle(ResultSet rs) throws SQLException { long count = 0; if (rs.next()) count = (long) rs.getObject(1); return count*10; } }; long rs = (long) runner.query(conn, sql, handler); System.out.println(rs); } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(conn, null); } } 结果： 160

关闭资源

在org.apache.commons.dbutils.DbUtils;下可以关闭资源：

public static void closeResource(Connection conn, PreparedStatement ps, ResultSet result) { DbUtils.closeQuietly(conn); DbUtils.closeQuietly(ps); DbUtils.closeQuietly(result); }

这个就比较简单了，不必多说。