JDBC项目-MySQL 事务、JDBC事务示例、连接池dbcp和c3p0

最新推荐文章于 2022-01-05 09:31:45 发布

伍Wu哈Ha

最新推荐文章于 2022-01-05 09:31:45 发布

阅读量2k

点赞数

分类专栏： MySQL Java 文章标签： mysql 事务 c3p0 dbcp

本文链接：https://blog.csdn.net/Peng_Hong_fu/article/details/52742366

版权

Java 同时被 2 个专栏收录

36 篇文章 5 订阅

订阅专栏

MySQL

8 篇文章 0 订阅

订阅专栏

画上母亲安详的姿势
还有橡皮能擦去的争执
画上四季都不愁的粮食
悠闲的人从没心事
我没有擦去争吵的橡皮
只有一支画着孤独的笔
那夜空的月也不再亮
只有个忧郁的孩子在唱

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 表，记录交易的数据，交易金额，交易双方，交易时间

/*
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<Account> query(Integer id) throws SQLException {
        Connection conn = DBUtil.getConn();
        List<Account> list = new ArrayList<Account>();
        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<Account> query(Account account) throws SQLException{
        List<Account> 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<TransInfo> query(TransInfo transInfo) throws SQLException{
        List<TransInfo> 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<Account> result = new ArrayList<>();
    // AccountDao dao = new AccountDao();
    // try {
    // result = dao.query(2);
    // 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) {
    // System.out.println("查询失败");
    // e.printStackTrace();
    // }
    // }

//  public static void main(String[] args) {
//
//      AccountDao dao = new AccountDao();
//      Account account = new Account();
//      List<Account> 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<TransInfo> 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); //有事务处理的交易
    }
}

5 连接池

连接池是创建和管理一个连接的缓冲池的技术，这些连接准备好被任何需要它们的线程使用。

在实际应用开发中，特别是在WEB应用系统中，如果JSP、Servlet或EJB使用JDBC直接访问数据库中的数据，每一次数据访问请求都必须经历建立数据库连接、打开数据库、存取数据和关闭数据库连接等步骤，而连接并打开数据库是一件既消耗资源又费时的工作，如果频繁发生这种数据库操作，系统的性能必然会急剧下降，甚至会导致系统崩溃。数据库连接池技术是解决这个问题最常用的方法

这里写图片描述

1 dbcp 连接池

这里写图片描述

jar包下载地址：http://download.csdn.net/detail/peng_hong_fu/9647235

dbcp.properties文件

#数据库驱动
driverClassName=com.mysql.jdbc.Driver
#数据库连接地址
url=jdbc:mysql://127.0.0.1:3306/数据库名?characterEncoding=utf8&useSSL=false
#用户名
username=root
#密码
password=root

#连接池的最大数据库连接数。设为0表示无限制
maxActive=30
#最大空闲数，数据库连接的最大空闲时间。超过空闲时间，数据库连
#接将被标记为不可用，然后被释放。设为0表示无限制
maxIdle=10
#最大建立连接等待时间。如果超过此时间将接到异常。设为-1表示无限制
maxWait=1000 
#超过removeAbandonedTimeout时间后，是否进 行没用连接（废弃）的回收（默认为false，调整为true) 
removeAbandoned=true
#超过时间限制，回收没有用(废弃)的连接（默认为 300秒，调整为180）
removeAbandonedTimeout=180

利用连接池连接数据库所属包：package com.peng.db

DBCPUtil.java

//package com.peng.db;

import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;

public class DBCPUtil {
    /** 数据源，static **/
    private static DataSource DS;

    private static final String configFile = "resources/dbcp.properties";

    private static final String connectURL = "jdbc:mysql://127.0.0.1:3306/peng?characterEncoding=utf8&useSSL=false";
    private static final String username = "root";
    private static final String pswd = "root";
    private static final String driverClass = "com.mysql.jdbc.Driver";
    private static final int initialSize = 5;
    private static final int maxtotal = 200;
    private static final int maxActive = 30;
    private static final int maxIdle = 10;
    private static final int maxWaitMillis = 1000;
    private static final int minIdle = 1;

    /**
     * 从一个数据源获得一个连接
     * 
     * @return
     */
    public Connection getConn() {
        Connection conn = null;
        if (DS != null) {
            try {
                conn = DS.getConnection();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                conn.setAutoCommit(false);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return conn;
        }
        return conn;
    }

    /**
     * 默认的构造函数
     */
    public DBCPUtil() {
        /**
         * 在dbcp.properties文件中读取配置信息
         */
        initDbcp();
        /**
         * 自己设置BasicDataSource的参数
         */
        /*
         * initDS(connectURL, username, pswd, driverClass, initialSize,maxtotal,
         * maxActive, maxIdle, maxWaitMillis, minIdle);
         */
    }

    private static void initDbcp() {
        Properties pops = new Properties();
        try {
            File file = new File(configFile);
            FileInputStream in = new FileInputStream(file);
            // 读取配置文件
            pops.load(in);
            // 把文件里的参数存进DS中
            DS = BasicDataSourceFactory.createDataSource(pops);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /** 构造函数，初始化了 DS ，指定 数据库 **/
    public DBCPUtil(String connectURL) {
        initDS(connectURL);
    }

    /** 构造函数，初始化了 DS，指定所有参数 **/
    public DBCPUtil(String connectURL, String username, String pswd, String driverClass, int initialSize, int maxtotal,
            int maxActive, int maxIdle, int maxWaitMillis, int minIdle) {
        initDS(connectURL, username, pswd, driverClass, initialSize, maxtotal, maxActive, maxIdle, maxWaitMillis,
                minIdle);
    }

    /**
     * 
     * @param connectURL
     * @param username
     * @param pswd
     * @param driverClass
     * @param initialSize
     * @param maxtotal
     * @param maxActive
     * @param maxIdle
     * @param maxWaitMillis
     *            获取连接的最大等待毫秒数
     * @param minIdle
     *            最小连接数
     */
    public static void initDS(String connectURL, String username, String pswd, String driverClass, int initialSize,
            int maxtotal, int maxActive, int maxIdle, int maxWaitMillis, int minIdle) {
        BasicDataSource ds = new BasicDataSource();
        ds.setDriverClassName(driverClass);
        ds.setUsername(username);
        ds.setPassword(pswd);
        ds.setUrl(connectURL);
        ds.setInitialSize(initialSize); // 初始化连接数
        ds.setMaxTotal(maxtotal);
        ds.setMaxIdle(maxIdle);
        ds.setMaxWaitMillis(maxWaitMillis);
        ds.setMinIdle(minIdle);
        DS = ds;
    }

    private void initDS(String connectURL) {
        BasicDataSource ds = new BasicDataSource();
        ds.setUrl(connectURL);
        DS = ds;
    }
}

测试直连和利用连接池连接数据库的时间
所属包：package com.peng.test

TestDbcp.java

//package com.peng.test;

import java.sql.SQLException;
import java.util.Date;

//import com.peng.dao.AccountDao;
//import com.peng.model.Account;

public class TestDbcp {
    public static void main(String[] args) throws SQLException {
        // 1.通过不同的方式操纵数据库
        Date a = new Date();
        getMessage();
        Date b = new Date();
        System.out.println(b.getTime() - a.getTime());

        // 1.通过不同的方式操纵数据库
        Date c = new Date();
        getMessageByDbcp();
        Date d = new Date();
        System.out.println(d.getTime() - c.getTime());
    }

    public static void getMessage() throws SQLException {
        AccountDao dao = new AccountDao();
        Account a = new Account();
        a = dao.get(1);
        System.out.println(a.toString());
    }

    public static void getMessageByDbcp() throws SQLException {
        AccountDao dao = new AccountDao();
        Account b = new Account();
        b = dao.getByDbcp(2);
        System.out.println(b.toString());
    }

}

AccountDao 方法添加通过dbcp连接数据库的查询方法
AccountDao.java

    .
    .
    /**
     * 查 根据id值查询信息
     * 利用连接池连接数据库
     * @param id
     * @return
     * @throws SQLException
     */
    public Account getByDbcp(Integer id) throws SQLException {
        Account a = null;
        DBCPUtil dbcp = new DBCPUtil();
        Connection conn = dbcp.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;
    }
    .
    .

测试结果，连接池速度更快

Account [id=1, account=b, amount=250.0, createAt=2016-10-06 20:17:37.0]
368
Account [id=2, account=market, amount=0.0, createAt=2016-10-06 10:23:05.0]
106

Account [id=1, account=b, amount=250.0, createAt=2016-10-06 20:17:37.0]
373
Account [id=2, account=market, amount=0.0, createAt=2016-10-06 10:23:05.0]
106

Account [id=1, account=b, amount=250.0, createAt=2016-10-06 20:17:37.0]
363
Account [id=2, account=market, amount=0.0, createAt=2016-10-06 10:23:05.0]
108

2 c3p0 连接池

C3P0是一个开源的JDBC连接池，它实现了数据源和JNDI绑定，支持JDBC3规范和JDBC2的标准扩展。目前使用它的开源项目有Hibernate，Spring等。

代码结构部分目录：
这里写图片描述

jar包下载地址：http://download.csdn.net/detail/peng_hong_fu/9647565

配置连接池c3p0-config.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/imooc_db
        </property>

        <property name="initialPoolSize">3</property>
        <property name="minPoolSize">3</property>
        <property name="acquireIncrement">3</property>
        <property name="maxPoolSize">15</property>
        <property name="maxIdleTime">100</property>
        <property name="acquireRetryAttempts">30</property>
        <property name="acquireRetryDelay">1000</property>
    </default-config>
</c3p0-config>

c3p0配置连接工具类
C3P0Util.java

//package com.peng.db;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.SQLException;

import com.mchange.v2.c3p0.ComboPooledDataSource;
/**
 * 
 * Title: C3P0Util
 * Description: c3p0数据源配置类
 * Company: jxust
 * @author Peng
 * @date 上午11:01:58
 */
public class C3P0Util {
    private static ComboPooledDataSource ds = new ComboPooledDataSource();

    public C3P0Util() throws PropertyVetoException{
       ds = new ComboPooledDataSource();       
    }
    public static Connection getConnection() {

        try {
            return ds.getConnection();
        } catch (SQLException e) {  
            System.out.println("无法获取连接");
            throw new RuntimeException(e);
        }
    }
}

在AccountDao.java添加通过c3p0连接数据库的查询方法
AccountDao.java

    .
    .
    /**
     * 查 利用c3p0连接池连接数据库
     * @param id
     * @return
     * @throws SQLException
     * @throws PropertyVetoException
     */
public Account getByc3p0(Integer id) throws SQLException, PropertyVetoException {
        Account a = null;
        C3P0Util c3p0 = new C3P0Util();
        Connection conn = c3p0.getConnection();
        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;
    }
    .
    .

测试类观察连接数据库的事件，c3p0要时间开启连接，这项没有比较性
TestC3p0.java

//package com.peng.test;

import java.beans.PropertyVetoException;
import java.sql.SQLException;
import java.util.Date;

import com.peng.dao.AccountDao;
import com.peng.model.Account;

public class TestC3p0 {
    public static void main(String[] args) throws SQLException {

        Date a = new Date();
        getmessage();
        Date b = new Date();
        System.out.println(b.getTime() - a.getTime());

        Date c = new Date();
        getmessageByc3p0();
        Date d = new Date();
        System.out.println(d.getTime() - c.getTime());
    }

    public static void getmessage() throws SQLException {
        AccountDao dao = new AccountDao();
        Account account = new Account();

        account = dao.get(1);
        System.out.println(account.toString());

    }

    public static void getmessageByc3p0() throws SQLException {
        AccountDao dao = new AccountDao();
        Account account = new Account();
        try {
            account = dao.getByc3p0(1);
            System.out.println(account.toString());

        } catch (PropertyVetoException e) {
            e.printStackTrace();
        }
    }
}

结果：

Account [id=1, account=b, amount=250.0, createAt=2016-10-06 20:17:37.0]
380

<开启c3p0连接的输出日志>
十月 08, 2016 4:07:42 下午 com.mchange.v2.log.MLog 
信息: MLog clients using java 1.4+ standard logging.
十月 08, 2016 4:07:42 下午 com.mchange.v2.c3p0.cfg.C3P0Config 
警告: Unknown c3p0-config property: jdbcurl
十月 08, 2016 4:07:42 下午 com.mchange.v2.c3p0.C3P0Registry 
信息: Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]
十月 08, 2016 4:07:42 下午 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource 
信息: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 1hgf37m9j19bhp961jl56cq|1134affc, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hgf37m9j19bhp961jl56cq|1134affc, idleConnectionTestPeriod -> 0, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://127.0.0.1:3306/imooc_db, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 100, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 15, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
Sat Oct 08 16:07:42 CST 2016 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Sat Oct 08 16:07:42 CST 2016 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Sat Oct 08 16:07:42 CST 2016 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

Account [id=1, account=b, amount=250.0, createAt=2016-10-06 20:17:37.0]
706

3 dbcp和c3p0连接池的差异

这里写图片描述

源码

JDBC事务管理项目下载：
http://download.csdn.net/detail/peng_hong_fu/9646873
JDBC事务管理项目(添加dbcp和c3p0测试)：
http://download.csdn.net/detail/peng_hong_fu/9647631

伍Wu哈Ha

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
JDBC项目-MySQL 事务、JDBC事务示例、连接池dbcp和c3p0

我没有擦去争吵的橡皮只有一支画着孤独的笔 MySQL 事务、JDBC事务示例、连接池dbcp 、c3p0事务（TRANSACTION）是作为**单个逻辑工作单元**执行的一系列操作。这些操作作为一个**整体**一起向系统提交，要么都**执行**、要么都**不执行**。
复制链接

扫一扫