第1章:事务处理
课时1:事务的四大特性(ACID)
课时2:MySQL中开启和关闭事务
课时3:JDBC中完成事务处理
- 在JDBC中处理事务,都是通过Connection完成的。
- 同一事务中的所有操作,都需要使用同一个Connection对象!!!
-
示例:
-
在数据库中创建account表并填充数据:
CREATE TABLE `account` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `balance` decimal(12,2) NOT NULL DEFAULT '0.00', PRIMARY KEY (`id`), UNIQUE KEY `name_UNIQUE` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('张三', '1000.00'); INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('李四', '1000.00'); INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('王五', '1000.00');
-
编写AccountDao文件:
package cn.pjh.jdbc.demo.demo1; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; public class AccountDao { /** * 更新账户余额 * @param conn 数据库连接 * @param name 账户名称 * @param fee 账户变动余额 */ public void updateAccount(Connection conn, String name, Double fee) { PreparedStatement pstmt = null; try { String sql = "UPDATE `account` SET `balance` = `balance` + ? WHERE `name` = ?"; pstmt = conn.prepareStatement(sql); pstmt.setDouble(1, fee); pstmt.setString(2, name); pstmt.executeUpdate(); } catch (SQLException e) { throw new RuntimeException(e); } finally { try { if (null != pstmt) pstmt.close(); } catch (SQLException e) { throw new RuntimeException(e); } } } }
-
编写主类:
package cn.pjh.jdbc.demo.demo1; import cn.pjh.jdbc.utils.JdbcUtils; import java.sql.Connection; import java.sql.SQLException; public class JDBCDemo1 { /** * 转账 * @param fromName 转出账户名称 * @param toName 转入账户名称 * @param fee 转账金额 */ public static void transfer(String fromName, String toName, Double fee) { Connection conn = null; try { conn = JdbcUtils.getConnection(); conn.setAutoCommit(false); AccountDao acctDao = new AccountDao(); acctDao.updateAccount(conn, fromName, -fee); acctDao.updateAccount(conn, toName, fee); conn.commit(); } catch (SQLException e) { try { conn.rollback(); } catch (SQLException e1) { throw new RuntimeException(e); } } finally { try { if (null != conn) conn.close(); } catch (SQLException e) { throw new RuntimeException(e); } } } public static void main(String[] args) { transfer("张三", "李四", 100.0); } }
-
课时4:事务的隔离级别
-
MySQL的事务隔离级别:
-
查看
-
mysql5:
-
方式一:
SELECT @@tx_isolation;
-
方式二:
SHOW variables LIKE 'tx_isolation';
-
-
mysql8:
-
方式一:
SELECT @@transaction_isolation;
-
方式二:
SHOW variables LIKE 'transaction_isolation';
-
-
-
修改(在重启mysql服务后生效):
-
修改为 读已提交:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-
修改为 可重复读:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-
-
第2章:连接池
课时5:dbcp连接池
-
池参数:
- 初始连接数
- 最小空闲连接数
- 增量(一次创建的最小单位)
- 最大空闲连接数
- 最大连接数
- 最大等待时间
-
连接池必须实现:javax.sql.DataSource接口
- DataSource接口方法:
- Connection getConnection() throws SQLException
- Connection getConnection(String username, String password) throws SQLException
- 连接池返回Connection对象的close()方法,并不是关闭连接,而是把连接归还给连接池。
- DataSource接口方法:
-
工具包下载(请根据自己的java版本进行下载):
- dbcp包:https://commons.apache.org/proper/commons-dbcp/download_dbcp.cgi
- pool包:https://commons.apache.org/proper/commons-pool/download_pool.cgi
- logging包:http://commons.apache.org/proper/commons-logging/download_logging.cgi
-
连接池的配置参数:
- 最大活动连接数:
- mysql8:maxTotal
- mysql5:maxActive
- 最大空闲时间:
- mysql8:maxWaitMillis
- mysql5:maxWait
- 最大活动连接数:
-
示例:
package cn.pjh.jdbc.demo.dbcp; import org.apache.commons.dbcp2.BasicDataSource; import java.sql.Connection; import java.sql.SQLException; public class dbcpDemo { public static void main(String[] args) throws SQLException { // 1、创建连接池对象 BasicDataSource dataSource = new BasicDataSource(); // 2、配置四大参数 dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver"); dataSource.setUrl("jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai"); dataSource.setUsername("root"); dataSource.setPassword("root"); // 3、配置池参数 dataSource.setMaxTotal(20); // 最大活动连接数 dataSource.setMinIdle(3); // 最小空闲连接数 dataSource.setMaxWaitMillis(1000); // 最大等待秒数 // 4、获取连接对象 Connection conn = dataSource.getConnection(); System.out.println(conn.getClass().getName()); // 打印实际连接的类名 // 输出:org.apache.commons.dbcp2.PoolingDataSource$PoolGuardConnectionWrapper /** * PoolGuardConnectionWrapper是一个包装类,所以该连接池使用了装饰者模式 * 连接池内部使用四大参数创建了连接对象,即使用了mysql驱动提供的Connection * 连接池使用mysql的连接对象进行了装饰,只对close()方法进行了增强 * 装饰之后的Connection的close()方法,用来把连接归还给连接池 */ // 5、把连接归还给连接池 conn.close(); } }
课时6:装饰者模式
-
对象增强手段:
- 继承:
- 被增强的对象:固定
- 增强的内容:固定
- 装饰者模式:
- 被增强的对象:可以切换
- 增强的内容:固定
- 动态代理(AOP):
- 被增强的对象:可以切换(Service)
- 增强的内容:可以切换(事务处理)
- 继承:
-
装饰者模式:
-
口诀:是你,还有你,一切拜托你!
// is a // has a // use a class MyConnection implements Connection { // 是你(实现/继承) // 还有你(作为属性) private Connection conn; // 底层对象,被增强的对象 public MyConnection(Connection conn) { // 通过构造传递底层对象 this.conn = conn; } // 一切拜托你(直接使用父类方法) public Statement createStatement() { return con.createStatement; } // 增强的内容 public void close() { // 把当前连接归还给连接池 } }
-
课时7:c3p0连接池的基本使用方式
-
c3p0下载地址:https://sourceforge.net/projects/c3p0/
-
示例:
package cn.pjh.jdbc.demo.c3p0; import com.mchange.v2.c3p0.ComboPooledDataSource; import java.beans.PropertyVetoException; import java.sql.Connection; import java.sql.SQLException; public class c3p0Demo { public static void main(String[] args) throws PropertyVetoException, SQLException { { // 不使用配置文件 // 创建连接池对象 // ComboPooledDataSource dataSource = new ComboPooledDataSource(); // // 对连接池进行四大参数的配置 // dataSource.setDriverClass("com.mysql.cj.jdbc.Driver"); // dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai"); // dataSource.setUser("root"); // dataSource.setPassword("root"); // // 对连接池进行池参数的配置 // dataSource.setAcquireIncrement(5); // 设置增量 // dataSource.setInitialPoolSize(20); // 设置初始化大小 // dataSource.setMinPoolSize(2); // 设置最小连接数 // dataSource.setMaxPoolSize(50); // 设置最大连接数 // // 获取连接 // Connection conn = dataSource.getConnection(); // System.out.println(conn); // // 归还连接 // conn.close(); } { // 使用配置文件的默认配置 // 创建连接池对象 // ComboPooledDataSource dataSource = new ComboPooledDataSource(); // // 直接获取连接 // Connection conn = dataSource.getConnection(); // System.out.println(conn); // // 归还连接 // conn.close(); } { // 使用配置文件的命名配置 // 创建连接池对象 ComboPooledDataSource dataSource = new ComboPooledDataSource("mysql-config"); // 直接获取连接 Connection conn = dataSource.getConnection(); System.out.println(conn); // 归还连接 conn.close(); } } }
课时8:c3p0连接的配置文件使用
-
配置文件要求:
- 文件名称:必须为c3p0-config.xml
- 文件位置:必须在src目录下
-
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/test?serverTimeZone=Asia/Shanghai</property> <property name="user">root</property> <property name="password">root</property> <!--池参数的配置--> <property name="acquireIncrement">3</property> <property name="initialPoolSize">10</property> <property name="minPoolSize">2</property> <property name="maxPoolSize">10</property> </default-config> <!--专门为mysql数据库的配置--> <named-config name="mysql-config"> <!--四大参数的配置--> <property name="driverClass">com.mysql.cj.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql://localhost:3306/test?serverTimeZone=Asia/Shanghai</property> <property name="user">root</property> <property name="password">root</property> <!--池参数的配置--> <property name="acquireIncrement">3</property> <property name="initialPoolSize">10</property> <property name="minPoolSize">2</property> <property name="maxPoolSize">10</property> </named-config> </c3p0-config>
第3章:JDBC工具类
课时9:JdbcUtils使用连接池
-
示例:
package cn.pjh.jdbc.utils; import com.mchange.v2.c3p0.ComboPooledDataSource; import javax.sql.DataSource; import java.sql.Connection; import java.sql.SQLException; public class JdbcUtils { // 必须给出c3p0-config.xml配置文件,使用配置文件的默认配置 private static ComboPooledDataSource dataSource = new ComboPooledDataSource(); /** * 使用连接池返回一个连接对象 * @throws SQLException */ public static Connection getConnection() throws SQLException { return dataSource.getConnection(); } /** * 返回连接池 * @return */ public static DataSource getDataSource() { return dataSource; } }
课时10:JdbcUtils增加事务处理
-
示例:
-
修改JdbcUtils工具类:
package cn.pjh.jdbc.utils; import com.mchange.v2.c3p0.ComboPooledDataSource; import javax.sql.DataSource; import java.sql.Connection; import java.sql.SQLException; public class JdbcUtils { private static ComboPooledDataSource dataSource = new ComboPooledDataSource(); private static Connection connTx = null; // 事务专用连接 // 获取连接 public static Connection getConnection() throws SQLException { if (null != connTx) return connTx; else return dataSource.getConnection(); } // 获取连接池 public static DataSource getDataSource() { return dataSource; } // 开启事务 public static void beginTransaction() throws SQLException { if (null != connTx) throw new SQLException("事务已开启,不可重复开启!"); connTx = getConnection(); connTx.setAutoCommit(false); } // 提交事务 public static void commitTransaction() throws SQLException { if (null == connTx) throw new SQLException("事务未开启,无法提交!"); connTx.commit(); connTx.close(); connTx = null; } // 回滚事务 public static void rollbackTransaction() throws SQLException { if (null == connTx) throw new SQLException("事务未开启,无法回滚!"); connTx.rollback(); connTx.close(); connTx = null; } }
-
编写AccountDao类:
(代码中使用的QueryRunner类需要下载DBUtils包,下载地址:http://commons.apache.org/proper/commons-dbutils/download_dbutils.cgi)
package cn.pjh.jdbc.demo.c3p0; import cn.pjh.jdbc.utils.JdbcUtils; import org.apache.commons.dbutils.QueryRunner; import java.sql.Connection; import java.sql.SQLException; public class AccountDao { /** * 更新账户余额 * @param name 账户名称 * @param fee 账户变动余额 */ public void updateAccount(String name, Double fee) throws SQLException { QueryRunner queryRunner = new QueryRunner(); String sql = "UPDATE `account` SET `balance` = `balance` + ? WHERE `name` = ?"; Object[] params = {fee, name}; Connection conn = JdbcUtils.getConnection(); // 要使用JdbcUtils的Connection queryRunner.update(conn, sql, params); } }
-
编写主类:
package cn.pjh.jdbc.demo.c3p0; import cn.pjh.jdbc.utils.JdbcUtils; import java.sql.SQLException; public class c3p0Demo { /** * 转账 * @param fromName 转出账户名称 * @param toName 转入账户名称 * @param fee 转账金额 */ public static void transfer(String fromName, String toName, Double fee) { AccountDao acctDao = new AccountDao(); try { JdbcUtils.beginTransaction(); acctDao.updateAccount(fromName, -fee); acctDao.updateAccount(toName, fee); JdbcUtils.commitTransaction(); } catch (SQLException e) { try { JdbcUtils.rollbackTransaction(); } catch (SQLException e1) { throw new RuntimeException(); } } } public static void main(String[] args) { transfer("张三", "李四", 100.0); } }
-
课时11:JNDI配置
- JNDI(Java Naming and Directory Interface):Java命名和目录接口。
- JNDI的作用:在服务器上配置资源(连接池),然后通过同一的方式来获取配置的资源。
课时12:ThreadLocal内部实现分析
-
示例:
package cn.pjh.demo; import java.util.HashMap; import java.util.Map; public class ThreadLocalDemo { public static void main(String[] args) { ThreadLocal<String> thread = new ThreadLocal<>(); thread.set("hello"); // 存 String str = thread.get(); // 取 thread.remove(); // 删 System.out.println(str); } } /** * ThreadLocal类内部实现分析 * @param <T> */ class TL<T> { private Map<Thread, T> map = new HashMap<>(); public void set(T data) { map.put(Thread.currentThread(), data); } public T get() { return map.get(Thread.currentThread()); } public void remove() { map.remove(Thread.currentThread()); } }
课时13:dbutils原理
-
示例:
-
编写Account类:
package cn.pjh.jdbc.demo.demo2; public class Account { private int id; private String name; private Double balance; public Account() {} public Account(String name, Double balance) { this.name = name; this.balance = balance; } public int getId() { return this.id; } public void setId(int id) { this.id = id; } public String getName() { return this.name; } public void setName(String name) { this.name = name; } public Double getBalance() { return this.balance; } public void setBalance(Double balance) { this.balance = balance; } public String toString() { return this.id + " - " + this.name + " - " + this.balance; } }
-
编写DbUtils工具类(模拟apache提供的dbutils):
package cn.pjh.jdbc.demo.demo2; import javax.sql.DataSource; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class DbUtils<T> { private DataSource dataSource = null; public DbUtils(DataSource dataSource) { this.dataSource = dataSource; } /** * 更新方法,包括insert、update、delete * @param sql 更新语句的SQL模板 * @param params SQL模板的参数 * @return 更新成功记录数 */ public int update(String sql, Object... params) { Connection conn = null; PreparedStatement pstmt = null; try { conn = this.dataSource.getConnection(); pstmt = conn.prepareStatement(sql); initParams(pstmt, params); return pstmt.executeUpdate(); } catch (SQLException e) { throw new RuntimeException(e); } finally { try { if (null != pstmt) pstmt.close(); if (null != conn) conn.close(); } catch (SQLException e) { throw new RuntimeException(e); } } } private void initParams(PreparedStatement pstmt, Object... params) throws SQLException { for (int i = 0; i < params.length; i++) { pstmt.setObject(i+1, params[i]); } } /** * 查询方法(select) * @param sql 查询语句的SQL模板 * @param rsh 结果集处理类 * @param params SQL模板的参数 * @return 调用时指定的类型 */ public T query(String sql, IResultSetHandle rsh, Object... params) { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; try { conn = this.dataSource.getConnection(); pstmt = conn.prepareStatement(sql); initParams(pstmt, params); rs = pstmt.executeQuery(); return (T) rsh.handle(rs); } catch (SQLException e) { throw new RuntimeException(e); } finally { try { if (null != rs) rs.close(); if (null != pstmt) pstmt.close(); if (null != conn) conn.close(); } catch (SQLException e) { throw new RuntimeException(e); } } } } /** * 处理结果集的类,具体处理由调用者实现 * @param <T> */ interface IResultSetHandle<T> { T handle(ResultSet rs) throws SQLException; }
-
编写主类:
-
使用我们自己编写的DbUtils工具类:
package cn.pjh.jdbc.demo.demo2; import cn.pjh.jdbc.utils.JdbcUtils; import java.sql.ResultSet; import java.sql.SQLException; public class Demo2 { public static void main(String[] args) { // Account acct = new Account("皮卡丘", 10000.0); // addAccount(acct); Account acct = getAccount("皮卡丘"); System.out.println(acct); } public static void addAccount(Account acct) { DbUtils dbUtils = new DbUtils(JdbcUtils.getDataSource()); String sql = "INSERT INTO `account` VALUES(NULL,?,?)"; Object[] params = {acct.getName(), acct.getBalance()}; int num = dbUtils.update(sql, params); System.out.println("更新成功记录数:" + num); } public static Account getAccount(String name) { DbUtils dbUtils = new DbUtils(JdbcUtils.getDataSource()); String sql = "SELECT * FROM `account` WHERE `name` = ?"; Object[] params = {name}; IResultSetHandle<Account> rsh = new IResultSetHandle<>() { @Override public Account handle(ResultSet rs) throws SQLException { if (!rs.next()) return null; Account acct = new Account(); acct.setId(rs.getInt("id")); acct.setName(rs.getString("name")); acct.setBalance(rs.getDouble("balance")); return acct; } }; return (Account) dbUtils.query(sql, rsh, params); } }
-
使用apache提供的dbutils(只要将DbUtils替换成QueryRunner即可):
package cn.pjh.jdbc.demo.demo2; import cn.pjh.jdbc.utils.JdbcUtils; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.ResultSetHandler; import java.sql.ResultSet; import java.sql.SQLException; public class Demo2 { public static void main(String[] args) throws SQLException { // Account acct = new Account("皮卡丘2", 10000.0); // addAccount(acct); Account acct = getAccount("皮卡丘2"); System.out.println(acct); } public static void addAccount(Account acct) throws SQLException { QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource()); String sql = "INSERT INTO `account` VALUES(NULL,?,?)"; Object[] params = {acct.getName(), acct.getBalance()}; int num = queryRunner.update(sql, params); System.out.println("更新成功记录数:" + num); } public static Account getAccount(String name) throws SQLException { QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource()); String sql = "SELECT * FROM `account` WHERE `name` = ?"; Object[] params = {name}; ResultSetHandler<Account> rsh = new ResultSetHandler<>() { @Override public Account handle(ResultSet rs) throws SQLException { if (!rs.next()) return null; Account acct = new Account(); acct.setId(rs.getInt("id")); acct.setName(rs.getString("name")); acct.setBalance(rs.getDouble("balance")); return acct; } }; return (Account) queryRunner.query(sql, rsh, params); } }
-
结果集可以直接使用BeanHandle:
public static Account getAccount(String name) throws SQLException { QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource()); String sql = "SELECT * FROM `account` WHERE `name` = ?"; Object[] params = {name}; // 可直接使用BeanHandler,但要求数据库表的字段名和类的属性名一致 return (Account) queryRunner.query(sql, new BeanHandler<Account>(Account.class), params); }
-
-
课时14:dbutils结果处理器介绍
-
示例:
package cn.pjh.jdbc.demo.demo2; import cn.pjh.jdbc.utils.JdbcUtils; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.*; import java.sql.SQLException; import java.util.List; import java.util.Map; public class ResultDemo { public static void main(String[] args) throws SQLException { resultBeanHandle("皮卡丘"); resultBeanListHandle(); resultMapHandle("皮卡丘"); resultMapListHandle(); resultScalarHandle(); } /** * BeanHandler:单行处理器 * 结果类型:指定类对象 */ public static void resultBeanHandle(String name) throws SQLException { QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource()); String sql = "SELECT * FROM `account` WHERE `name` = ?"; Object[] params = {name}; Account acct = (Account) queryRunner.query(sql, new BeanHandler<Account>(Account.class), params); System.out.println("BeanHandler:" + acct); } /** * BeanListHandler:多行处理器 * 结果类型:指定类对象的List */ public static void resultBeanListHandle() throws SQLException { QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource()); String sql = "SELECT * FROM `account`"; List<Account> listAcct = queryRunner.query(sql, new BeanListHandler<Account>(Account.class)); System.out.println("BeanListHandler:" + listAcct); } /** * MapHandler:单行处理器 * 结果类型:字段名与字段值的Map */ public static void resultMapHandle(String name) throws SQLException { QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource()); String sql = "SELECT * FROM `account` WHERE `name` = ?"; Object[] params = {name}; Map<String, Object> acct = queryRunner.query(sql, new MapHandler(), params); System.out.println("MapHandler:" + acct); } /** * MapListHandler:多行处理器 * 结果类型:字段名与字段值Map的List */ public static void resultMapListHandle() throws SQLException { QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource()); String sql = "SELECT * FROM `account`"; List<Map<String, Object>> listAcct = queryRunner.query(sql, new MapListHandler()); System.out.println("MapListHandler:" + listAcct); } /** * ScalarHandler:单行单列处理器 * 结果类型:字段的类型 */ public static void resultScalarHandle() throws SQLException { QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource()); String sql = "SELECT COUNT(*) cnt FROM `account`"; // COUNT()的类型,在不同数据库不同驱动包中,可能会不一样 // 可能类型:Integer、Long、BigInteger // 所以最好用它们的父类Number,然后再通过intValue()、longValue()进行转换 Number cnt = (Number) queryRunner.query(sql, new ScalarHandler()); System.out.println("ScalarHandler:" + cnt); } }
课时15:编写TxQueryRunner配合JdbcUtils来处理事务
-
利用JdbcUtils进行数据库处理时,如果不使用事务,那么也需要关闭连接,而这个连接的关闭就需要在主类中进行。我们可以不需要在主类中处理连接(获取连接、关闭连接),下面我们就来实现这样的需求。
-
实现:
-
修改JdbcUtils工具类:增加 releaseConnection() 方法:
// 释放非事务专用连接 public static void releaseConnection(Connection conn) throws SQLException { /** * 判断是否为事务专用连接 * 是:不关闭连接,由commitTransaction()和rollbackTransaction()关闭 * 否:关闭连接 */ // 如果事务专用连接connTx为null,那么入参的conn肯定不是事务专用连接 if (null == connTx) conn.close(); // 如果 事务专用连接connTx 与 入参的conn 不一样,则conn非事务专用连接 else if (connTx != conn) conn.close(); }
-
编写TxQueryRunner类:
package cn.pjh.jdbc.demo.c3p0; import cn.pjh.jdbc.utils.JdbcUtils; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.ResultSetHandler; import java.sql.Connection; import java.sql.SQLException; import java.util.List; public class TxQueryRunner extends QueryRunner { @Override public int[] batch(String sql, Object[][] params) throws SQLException { Connection conn = JdbcUtils.getConnection(); int[] result = super.batch(conn, sql, params); JdbcUtils.releaseConnection(conn); return result; } @Override public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException { Connection conn = JdbcUtils.getConnection(); T result = super.query(conn, sql, rsh, params); JdbcUtils.releaseConnection(conn); return result; } @Override public <T> T query(String sql, ResultSetHandler<T> rsh) throws SQLException { Connection conn = JdbcUtils.getConnection(); T result = super.query(conn, sql, rsh); JdbcUtils.releaseConnection(conn); return result; } @Override public int update(String sql) throws SQLException { Connection conn = JdbcUtils.getConnection(); int result = super.update(conn, sql); JdbcUtils.releaseConnection(conn); return result; } @Override public int update(String sql, Object param) throws SQLException { Connection conn = JdbcUtils.getConnection(); int result = super.update(conn, sql, param); JdbcUtils.releaseConnection(conn); return result; } @Override public int update(String sql, Object... params) throws SQLException { Connection conn = JdbcUtils.getConnection(); int result = super.update(conn, sql, params); JdbcUtils.releaseConnection(conn); return result; } @Override public <T> T insert(String sql, ResultSetHandler<T> rsh) throws SQLException { Connection conn = JdbcUtils.getConnection(); T result = super.insert(conn, sql, rsh); JdbcUtils.releaseConnection(conn); return result; } @Override public <T> T insert(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException { Connection conn = JdbcUtils.getConnection(); T result = super.insert(conn, sql, rsh, params); JdbcUtils.releaseConnection(conn); return result; } @Override public <T> T insertBatch(String sql, ResultSetHandler<T> rsh, Object[][] params) throws SQLException { Connection conn = JdbcUtils.getConnection(); T result = super.insertBatch(conn, sql, rsh, params); JdbcUtils.releaseConnection(conn); return result; } @Override public int execute(String sql, Object... params) throws SQLException { Connection conn = JdbcUtils.getConnection(); int result = super.execute(conn, sql, params); JdbcUtils.releaseConnection(conn); return result; } @Override public <T> List<T> execute(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException { Connection conn = JdbcUtils.getConnection(); List<T> result = super.execute(conn, sql, rsh, params); JdbcUtils.releaseConnection(conn); return result; } }
-
修改AccountDao类:将 “new QueryRunner()” 改成 “new TxQueryRunner()” :
QueryRunner queryRunner = new TxQueryRunner();
-
课时16:JdbcUtils处理多线程并发访问问题
-
修改JdbcUtils工具类:
package cn.pjh.jdbc.utils; import com.mchange.v2.c3p0.ComboPooledDataSource; import javax.sql.DataSource; import java.sql.Connection; import java.sql.SQLException; public class JdbcUtils { private static ComboPooledDataSource dataSource = new ComboPooledDataSource(); // 将事务专用连接放入线程中,需要使用连接时则从线程中取 private static ThreadLocal<Connection> threadLocalConn = new ThreadLocal<>(); // 获取连接 public static Connection getConnection() throws SQLException { Connection connTx = threadLocalConn.get(); if (null != connTx) return connTx; else return dataSource.getConnection(); } // 获取连接池 public static DataSource getDataSource() { return dataSource; } // 开启事务 public static void beginTransaction() throws SQLException { Connection connTx = threadLocalConn.get(); // 从线程中取连接 if (null != connTx) throw new SQLException("事务已开启,不可重复开启!"); connTx = getConnection(); connTx.setAutoCommit(false); threadLocalConn.set(connTx); // 将连接存入线程中 } // 提交事务 public static void commitTransaction() throws SQLException { Connection connTx = threadLocalConn.get(); // 从线程中取连接 if (null == connTx) throw new SQLException("事务未开启,无法提交!"); connTx.commit(); connTx.close(); threadLocalConn.remove(); // 关闭连接后,则将移除线程中的连接 } // 回滚事务 public static void rollbackTransaction() throws SQLException { Connection connTx = threadLocalConn.get(); // 从线程中取连接 if (null == connTx) throw new SQLException("事务未开启,无法回滚!"); connTx.rollback(); connTx.close(); threadLocalConn.remove(); // 关闭连接后,则将移除线程中的连接 } // 释放非事务专用连接 public static void releaseConnection(Connection conn) throws SQLException { Connection connTx = threadLocalConn.get(); // 从线程中取连接 if (null == connTx) conn.close(); else if (connTx != conn) conn.close(); } }
第4章:分页
课时17:分页准备工作
-
分页数据:
页面的数据都是由Servlet传递过来的。
- 当前页数:currPage
- 如果页面没有传递过来,那么默认为第一页
- 总页数:totalPage
- 总记录数 / 每页记录数
- 总记录数:totalCnt
- 通过dao来获取:SELECT COUNT(*) FROM …
- 每页记录数:业务数据或系统数据
- 当前页数据:beanList
- 当前页数:currPage
-
bean的实现:
package cn.pjh.jdbc.demo.pageDemo.domain; import java.util.List; public class PageBean<T> { private int currPage; // 当前页数 private int totalCnt; // 总记录数 private int pageSize; // 每页记录数 private List<T> beanList; // 当前页数据 // 计算总页数 public int getTotalPage() { int totalPage = totalCnt/pageSize; return 0 == totalCnt%pageSize ? totalPage : totalPage+1; } public int getCurrPage() { return this.currPage; } public void setCurrPage(int currPage) { this.currPage = currPage; } public int getTotalCnt() { return this.totalCnt; } public void setTotalCnt(int totalCnt) { this.totalCnt = totalCnt; } public int getPageSize() { return this.pageSize; } public void setPageSize(int pageSize) { this.pageSize = pageSize; } public List<T> getBeanList() { return this.beanList; } public void setBeanList(List<T> beanList) { this.beanList = beanList; } }
课时18:处理分页各层分工
课时19:分页处理第一阶段完成
略(无视频中的源代码,无法修改)
课时20:分页之页面页码列表计算
课时21:分页之查询条件丢失问题
略(无视频中的源代码,无法修改)
课时22:分页之查询条件保存到PageBean的url中
略(无视频中的源代码,无法修改)
————————————————————————————————————