对数据修改的所有并发事务是彼此隔离的。喎�"/kf/ware/vc/" target="_blank" class="keylink">vcD4NCgk8cD7Stc7x1LEgQaO61cXI/S0xMDAgo7sgwO7LxCsxMDA8YnIgLz4NCgnStc7x1LEgQqO61cXI/S0xMDAgo7sgwO7LxCsxMDA8L3A+DQoJPHA+tbHStc7x1LEgQSCy2df3yrGjrNK1zvHUsSBCINKqtci0/TxiciAvPg0KCc2s0rvKsbzkttTK/b7dv+K1xLLZ1/ejrNKqsaOz1tK7uPbKws7xtcTL+LaoPC9wPg0KPC9ibG9ja3F1b3RlPg0KPHA+NKGiPHN0cm9uZz7TwL7D0NSjqER1cmFiaWxpdHmjqTwvc3Ryb25nPjwvcD4NCjxibG9ja3F1b3RlPg0KCTxwPsrCzvHN6rPJuvOjrMv8ttTK/b7dv+K1xNDeuMSxu9PAvsOxo7PWPC9wPg0KPC9ibG9ja3F1b3RlPg0KPGgxIGlkPQ=="3-jdbc对事务管理的支持">3 JDBC对事务管理的支持
1、我们通过提交commit() 或是 回退rollback() 来管理事务的操作
2、事务的操作是默认自动提交的
3、可以调用setAutoCommit(false)来禁止自动提交
4 JDBC实现事务的管理
模拟一个客户和一个商家之间的交易,客户购买商品付款,商家收款的一个过程。
数据库部分">1、数据库部分
MySQL 数据库有两种表:
account_info 表,记录商家和客户的账户信息
trans_info 表,记录交易的数据,交易金额,交易双方,交易时间
/*
Navicat MySQL Data Transfer
Source Server : peng
Source Server Version : 50712
Source Host : localhost:3306
Source Database : imooc_db
Target Server Type : MYSQL
Target Server Version : 50712
File Encoding : 65001
Date: 2016-10-07 16:18:25
*/
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 {
/**
* 增
*
* @param account
* @throws SQLException
*/
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();
}
/**
* 改
*
* @param account
* @throws SQLException
*/
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();
}
/**
* 删
*
* @param account
* @throws SQLException
*/
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();
}
/**
* 查 根据id查找数据
* @param id
* @return
* @throws SQLException
*/
public Listquery(Integer id) throws SQLException {
Connection conn = DBUtil.getConn();
Listlist = 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;
}
/**
* 查 根据用户名匹配查询
* @param account
* @return
* @throws SQLException
*/
public Listquery(Account account) throws SQLException{
Listlist = 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 {
/**
* 增
* @param transInfo
* @throws SQLException
*/
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();
}
/**
* 删
* @param transInfo
* @throws SQLException
*/
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();
}
/**
* 更
* @param transInfo
* @throws SQLException
*/
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 Listquery(TransInfo transInfo) throws SQLException{
Listlist = 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) {
// Listresult = new ArrayList<>();
// AccountDao dao = new AccountDao();
// try {
// result = dao.query(2);
// for(int i =0;iresult = 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();
Listresult = 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 {
/**
* 无事务的交易
* @param from
* @param to
* @param amount
* @return
* @throws SQLException
*/
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";
}
/**
* 有事务处理的交易
* 添加回滚机制
* @param from
* @param to
* @param amount
* @return
* @throws SQLException
*/
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);
conn.commit();//手动提交
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事务管理项目下载:
http://download.csdn.net/detail/peng_hong_fu/9646873