MySQL資料庫事務、JDBC事務示例、連接池

1 事務的概念

事務（TRANSACTION）是作為單個邏輯工作單元執行的一系列操作。
這些操作作為一個整體一起向系統提交，要麼都執行、要麼都不執行。

2 事務的特點

1、原子性（Atomicity）

事務是一個完整的操作。

2、一致性（Consistency）

當事務完成時，數據必須處於一致狀態。

這裡寫圖片描述

3、隔離性（Isolation）

對數據修改的所有並發事務是彼此隔離的。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 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;
    }
    /**
     * 查 根據用戶名匹配查詢
     * @param account
     * @return
     * @throws SQLException
     */
    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 {
    /**
     * 增
     * @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 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 {
    /**
     * 無事務的交易
     * @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事務管理項目下載：
https://download.csdn.net/detail/peng_hong_fu/9646873

Related posts:

  1. MySql的事務操作與示例
  2. MySQL 查詢緩存的實際應用代碼示例
  3. 關於mysql事務行鎖forupdate實現寫鎖的功能
  4. MySql事務無法回滾的原因
  5. mysql中的觸發器和事務的操作
  6. mysql在線將基於日志的復制和基於事務的復制互換
  7. MySQL資料庫第七天—-Connection連接池、包裝模式(裝飾模式)與動態代理模式
  8. MySQL資料庫05約束、事務和分頁查詢

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *