1 事务的概念
事务（TRANSACTION）是作为单个逻辑工作单元执行的一系列操作。
这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行。
2 事务的特点
1、原子性（Atomicity）
事务是一个完整的操作。
2、一致性（Consistency）
当事务完成时，数据必须处于一致状态。
3、隔离性（Isolation）
对数据修改的所有并发事务是彼此隔离的。
业务员 A：张三-100 ； 李四+100
业务员 B：张三-100 ； 李四+100当业务员 A 操作时，业务员 B 要等待
同一时间对数据库的操作，要保持一个事务的锁定
4、永久性（Durability）
事务完成后，它对数据库的修改被永久保持
3 JDBC对事务管理的支持
1、我们通过提交commit() 或是 回退rollback() 来管理事务的操作
2、事务的操作是默认自动提交的
3、可以调用setAutoCommit(false)来禁止自动提交
4 JDBC实现事务的管理
模拟一个客户和一个商家之间的交易，客户购买商品付款，商家收款的一个过程。
1、数据库部分
MySQL 数据库有两种表：
account_info 表，记录商家和客户的账户信息
trans_info 表，记录交易的数据，交易金额，交易双方，交易时间
SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for account_info-- ----------------------------DROP TABLE IF EXISTS `account_info`;CREATE TABLE `account_info` ( `id` int(11) NOT NULL AUTO_INCREMENT, `account` varchar(20) NOT NULL, `amount` double(18,2) NOT NULL DEFAULT '0.00', `create_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;-- ------------------------------ Records of account_info-- ----------------------------INSERT INTO `account_info` VALUES ('1', 'b', '250.00', '2016-10-06 20:17:37');INSERT INTO `account_info` VALUES ('2', 'market', '0.00', '2016-10-06 10:23:05');-- ------------------------------ Table structure for trans_info-- ----------------------------DROP TABLE IF EXISTS `trans_info`;CREATE TABLE `trans_info` ( `id` int(11) NOT NULL AUTO_INCREMENT, `source_id` int(11) NOT NULL, `source_account` varchar(20) NOT NULL, `destination_id` int(11) NOT NULL, `destination_account` varchar(20) NOT NULL, `amount` double(18,2) NOT NULL DEFAULT '0.00', `create_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;-- ------------------------------ Records of trans_info-- ----------------------------2、Java Project
项目目录结构：
com.peng.db 包
DBUtil.java
//package com.peng.db;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;public class DBUtil { private static final String URL = "jdbc:mysql://127.0.0.1:3306/imooc_db?characterEncoding=utf8&useSSL=false"; private static final String USER = "root"; private static final String PASSWORD = "root"; private static Connection conn = null; static {// 加载类时会执行这些静态的代码块 try { // 1.加载驱动程序 Class.forName("com.mysql.jdbc.Driver"); // 2.获得数据库连接 conn = DriverManager.getConnection(URL, USER, PASSWORD); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } } public static Connection getConn() { return conn; }}com.peng.model 包
Account.java
//package com.peng.model;import java.util.Date;public class Account { private Integer id; private String account; private Double amount; private Date createAt; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getAccount() { return account; } public void setAccount(String account) { this.account = account; } public Double getAmount() { return amount; } public void setAmount(Double amount) { this.amount = amount; } public Date getCreateAt() { return createAt; } public void setCreateAt(Date createAt) { this.createAt = createAt; } @Override public String toString() { return "Account [id=" + id + ", account=" + account + ", amount=" + amount + ", createAt=" + createAt + "]"; }}TransInfo.java
//package com.peng.model;import java.util.Date;public class TransInfo { private Integer id; private Integer sourceId; private String sourceeAccount; private Integer destinationId; private String destinationAccount; private Double amount; private Date createAt; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public Integer getSourceId() { return sourceId; } public void setSourceId(Integer sourceId) { this.sourceId = sourceId; } public String getSourceeAccount() { return sourceeAccount; } public void setSourceeAccount(String sourceeAccount) { this.sourceeAccount = sourceeAccount; } public Integer getDestinationId() { return destinationId; } public void setDestinationId(Integer destinationId) { this.destinationId = destinationId; } public String getDestinationAccount() { return destinationAccount; } public void setDestinationAccount(String destinationAccount) { this.destinationAccount = destinationAccount; } public Double getAmount() { return amount; } public void setAmount(Double amount) { this.amount = amount; } public Date getCreateAt() { return createAt; } public void setCreateAt(Date createAt) { this.createAt = createAt; } @Override public String toString() { return "TransInfo [id=" + id + ", sourceId=" + sourceId + ", sourceeAccount=" + sourceeAccount + ", destinationId=" + destinationId + ", destinationAccount=" + destinationAccount + ", amount=" + amount + ", createAt=" + createAt + "]"; }}com.peng.dao包
AccountDao.java
//package com.peng.dao;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;//import com.peng.db.DBUtil;//import com.peng.model.Account;public class AccountDao { public void insert(Account account) throws SQLException { Connection conn = DBUtil.getConn(); String sql = "INSERT INTO account_info (account,amount) VALUES (?,?); "; PreparedStatement ptmt = conn.prepareStatement(sql); ptmt.setString(1, account.getAccount()); ptmt.setDouble(2, account.getAmount()); ptmt.execute(); } public void update(Account account) throws SQLException { Connection conn = DBUtil.getConn(); String sql = "UPDATE account_info SET account=?,amount=? WHERE id =?; "; PreparedStatement ptmt = conn.prepareStatement(sql); ptmt.setString(1, account.getAccount()); ptmt.setDouble(2, account.getAmount()); ptmt.setInt(3, account.getId()); ptmt.execute(); } public void delete(Account account) throws SQLException { Connection conn = DBUtil.getConn(); String sql = "delete from account_info WHERE id =?; "; PreparedStatement ptmt = conn.prepareStatement(sql); ptmt.setInt(1, account.getId()); ptmt.execute(); } public List query(Integer id) throws SQLException { Connection conn = DBUtil.getConn(); List list = new ArrayList(); Account account = null; String sql = "select * from account_info WHERE id =?; "; PreparedStatement ptmt = conn.prepareStatement(sql); ptmt.setInt(1, id); ResultSet rs = ptmt.executeQuery(); while (rs.next()) { // SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd // hh:mm:ss"); // String date = sdf.format(rs.getDate("create_at")); // System.out.println(rs.getString("account")+" // "+rs.getDouble("amount")+" " // +rs.getTimestamp("create_at")); account = new Account(); account.setAccount(rs.getString("account")); account.setAmount(rs.getDouble("amount")); account.setId(rs.getInt("id")); // getTimestamp能得到时分秒的时间数据 account.setCreateAt(rs.getTimestamp("create_at")); list.add(account); } return list; } public List query(Account account) throws SQLException{ List list = new ArrayList<>(); Connection conn = DBUtil.getConn(); StringBuffer sb = new StringBuffer(); sb.append("select * from account_info "); sb.append(" where account like ?"); PreparedStatement ptmt = conn.prepareStatement(sb.toString()); ptmt.setString(1, "%"+account.getAccount()+"%"); ResultSet rs = ptmt.executeQuery(); while(rs.next()){ account = new Account(); account.setAccount(rs.getString("account")); account.setAmount(rs.getDouble("amount")); account.setId(rs.getInt("id")); // getTimestamp能得到时分秒的时间数据 account.setCreateAt(rs.getTimestamp("create_at")); list.add(account); } return list; } public Account get(Integer id) throws SQLException { Account a = null; Connection conn = DBUtil.getConn(); String sql = " select * from account_info " + " where id =? "; PreparedStatement ptmt = conn.prepareStatement(sql); ptmt.setInt(1, id); ResultSet rs = ptmt.executeQuery(); while (rs.next()) { a = new Account(); a.setAccount(rs.getString("account")); a.setAmount(rs.getDouble("amount")); a.setId(rs.getInt("id")); // getTimestamp能得到时分秒的时间数据 a.setCreateAt(rs.getTimestamp("create_at")); } return a; }}TransInfoDao.java
//package com.peng.dao;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;//import com.peng.db.DBUtil;//import com.peng.model.Account;//import com.peng.model.TransInfo;public class TransInfoDao { public void insert(TransInfo transInfo) throws SQLException { Connection conn = DBUtil.getConn(); String sql = "INSERT INTO trans_info (source_id,source_account,destination_id,destination_account,amount)" + " VALUES (?,?,?,?,?); "; PreparedStatement ptmt = conn.prepareStatement(sql); ptmt.setInt(1, transInfo.getSourceId()); ptmt.setString(2, transInfo.getSourceeAccount()); ptmt.setInt(3, transInfo.getDestinationId()); ptmt.setString(4, transInfo.getDestinationAccount()); ptmt.setDouble(5, transInfo.getAmount()); ptmt.execute(); } public void delete(TransInfo transInfo) throws SQLException { Connection conn = DBUtil.getConn(); String sql = "delete from trans_info WHERE id =?; "; PreparedStatement ptmt = conn.prepareStatement(sql); ptmt.setInt(1, transInfo.getId()); ptmt.execute(); } public void update(TransInfo transInfo) throws SQLException { Connection conn = DBUtil.getConn(); String sql = "UPDATE trans_info SET source_account=?," + "destination_id=?,destination_account=?,amount=? where id=? ; "; PreparedStatement ptmt = conn.prepareStatement(sql); ptmt.setString(1, transInfo.getSourceeAccount()); ptmt.setInt(2, transInfo.getDestinationId()); ptmt.setString(3, transInfo.getDestinationAccount()); ptmt.setDouble(4, transInfo.getAmount()); ptmt.setInt(5, transInfo.getId()); ptmt.execute(); } public List query(TransInfo transInfo) throws SQLException{ List list = new ArrayList<>(); Connection conn = DBUtil.getConn(); StringBuffer sb = new StringBuffer(); sb.append("select * from trans_info "); sb.append(" where source_account like ?"); PreparedStatement ptmt = conn.prepareStatement(sb.toString()); ptmt.setString(1, "%"+transInfo.getSourceeAccount()+"%"); ResultSet rs = ptmt.executeQuery(); while(rs.next()){ transInfo = new TransInfo(); transInfo.setId(rs.getInt("id")); transInfo.setSourceId(rs.getInt("source_id")); transInfo.setSourceeAccount(rs.getString("source_account")); transInfo.setDestinationId(rs.getInt("destination_id")); transInfo.setDestinationAccount(rs.getString("destination_account")); transInfo.setCreateAt(rs.getTimestamp("create_at")); transInfo.setAmount(rs.getDouble("amount")); list.add(transInfo); } return list; }} com.peng.test 包
TestDBUtil.java
//package com.peng.test;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.Statement;public class TestDBUtil { private static final String URL = "jdbc:mysql://127.0.0.1:3306/imooc_db?characterEncoding=utf8&useSSL=false"; private static final String USER = "root"; private static final String PASSWORD = "root"; private static Connection conn = null; public static void main(String[] args) throws Exception { // 1.加载驱动程序 Class.forName("com.mysql.jdbc.Driver"); // 2.获得数据库连接 conn = DriverManager.getConnection(URL, USER, PASSWORD); // 3.通过数据库的连接操作数据库，实现增删改查 Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("select * from account_info"); while (rs.next()) { System.out.println(rs.getString("account") + "," + rs.getString("amount")); } }}TestDao.java
//package com.peng.test;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;//import com.peng.dao.AccountDao;//import com.peng.dao.TransInfoDao;//import com.peng.model.Account;//import com.peng.model.TransInfo;public class TestDao { // public static void main(String[] args) { // List result = new ArrayList<>(); // AccountDao dao = new AccountDao(); // try { // result = dao.query(2); // for(int i =0;i result = new ArrayList<>();// account.setAccount("a");// try {// result = dao.query(account);// for (int i = 0; i < result.size(); i++) {//// System.out.println(result.get(i).getId() + " " + result.get(i).getAccount() + " "// + result.get(i).getAmount() + " " + result.get(i).getCreateAt());// }// } catch (SQLException e) {// e.printStackTrace();// }// }// public static void main(String[] args) {// AccountDao dao = new AccountDao();// Account account = new Account();// account.setAmount(2500.00);// account.setAccount("b");// account.setId(1);// try {// dao.update(account);// } catch (SQLException e) {// // TODO Auto-generated catch block// e.printStackTrace();// }// } public static void main(String[] args) { TransInfo ts = new TransInfo(); TransInfoDao tdao = new TransInfoDao(); List result = new ArrayList<>(); ts.setSourceeAccount("b"); try { result = tdao.query(ts); for (int i = 0; i < result.size(); i++) { System.out.println(result.get(i).toString()); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } }} com.peng.service 包
TransService.java
//package com.peng.service;import java.sql.Connection;import java.sql.SQLException;//import com.peng.dao.AccountDao;//import com.peng.dao.TransInfoDao;//import com.peng.db.DBUtil;//import com.peng.model.Account;//import com.peng.model.TransInfo;public class TransService { public String trans(Account from,Account to,Double amount) throws SQLException{ AccountDao accountDao = new AccountDao(); TransInfoDao transInfoDao = new TransInfoDao(); from.setAmount(from.getAmount()-amount); // 客户账户余额减去交易金额 accountDao.update(from); // 更新客户账户信息 //人为报错代码 String a = null; a.split("1"); to.setAmount(to.getAmount()+amount); // 商家账户余额加上交易金额 accountDao.update(to); // 更新商家账户信息 TransInfo info = new TransInfo(); info.setSourceeAccount(from.getAccount()); info.setDestinationAccount(to.getAccount()); info.setAmount(amount); info.setSourceId(from.getId()); info.setDestinationId(to.getId()); transInfoDao.insert(info); // 保存交易信息 return "success"; } public String transacation(Account from,Account to,Double amount) throws SQLException{ Connection conn = DBUtil.getConn(); conn.setAutoCommit(false); // 关闭SQL语句自动提交 try { AccountDao accountDao = new AccountDao(); TransInfoDao transInfoDao = new TransInfoDao(); from.setAmount(from.getAmount()-amount); accountDao.update(from); //人为错误代码// String a = null;// a.split("1"); to.setAmount(to.getAmount()+amount); accountDao.update(to); TransInfo info = new TransInfo(); info.setSourceeAccount(from.getAccount()); info.setDestinationAccount(to.getAccount()); info.setAmount(amount); info.setSourceId(from.getId()); info.setDestinationId(to.getId()); transInfoDao.insert(info); connmit();//手动提交 return "success"; } catch (Exception e) { conn.rollback();//回滚 e.printStackTrace(); return "fail"; } }}com.peng.action 包
TransAction.java
//package com.peng.action;import java.sql.SQLException;//import com.peng.dao.AccountDao;//import com.peng.model.Account;//import com.peng.service.TransService;public class TransAction { public static void main(String[] args) { String res; try { res = trans(); System.out.println(res); } catch (SQLException e) { e.printStackTrace(); } } public static String trans() throws SQLException{ AccountDao accountDao = new AccountDao(); TransService transService = new TransService(); Account from = null; // 商家对象 Account to = null; // 商家对象 from = accountDao.get(1); // 得到 id 为 1 的客户的所有信息 to = accountDao.get(2); // 得到 id 为 2 的商家的所有信息 return transService.transacation(from, to, 20d); //有事务处理的交易 }}连接池
连接池是创建和管理一个连接的缓冲池的技术，这些连接准备好被任何需要它们的线程使用。
在实际应用开发中，特别是在WEB应用系统中，如果JSP、Servlet或EJB使用JDBC直接访问数据库中的数据，每一次数据访问请求都必须经历建立数据库连接、打开数据库、存取数据和关闭数据库连接等步骤，而连接并打开数据库是一件既消耗资源又费时的工作，如果频繁发生这种数据库操作，系统的性能必然会急剧下降，甚至会导致系统崩溃。数据库连接池技术是解决这个问题最常用的方法
dbcp
c3p0
《TODO》2016 .10.7
源码
JDB C事务管理项目下载：
download.csdn.net/detail/peng_hong_fu/9646873