事务mysql红黑联盟_MySQL事务、JDBC事务示例、连接池

对数据修改的所有并发事务是彼此隔离的。喎�"/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)来禁止自动提交

31bae62cb7a6a5369b8767c7b11f2071.png

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

项目目录结构:

301773de91a3b9fd6ffc6d1d63875450.png

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直接访问数据库中的数据,每一次数据访问请求都必须经历建立数据库连接、打开数据库、存取数据和关闭数据库连接等步骤,而连接并打开数据库是一件既消耗资源又费时的工作,如果频繁发生这种数据库操作,系统的性能必然会急剧下降,甚至会导致系统崩溃。数据库连接池技术是解决这个问题最常用的方法

56250287ee71c0ddb3942d1aa1eaf8db.png

dbcp

c3p0

《TODO》2016 .10.7

源码">源码

JDB C事务管理项目下载:

http://download.csdn.net/detail/peng_hong_fu/9646873

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值