数据库开发四：JDBC数据库开发进阶三(JDBC工具类-JdbcUtils)

JdbcUtils
问题情境事务的处理
Dao层：不应涉及业务,只对数据库进行访问
Service层：处理逻辑业务（不应该出现数据库相关东西比如Connection）

Dao层处理事务

public void xxx(){
        Connection con = null;
    try{
        con = JdbcUtils.getConnection();
        con.setAutoCommitted(false);
        QueryRunner qr = new QueryRunner();
        String sql = "";
        Object[]params=...;
        qr.update(con,sql,params);
        String sql = "";
        Object[]params=...;
        qr.update(con,sql,params);
        con.commit();
    }catch(Exception e){
        try{
            con.close();
        }catch(Exception e){
            con.rollback();
        }
    }
}

Service层处理事务

public class xxxService(){
   private xxxDao dao = new xxxDao();
     public void serviceMethod(){
        Connection con =  null;
           try{
               con = JdbcUtils.getConnection();
               con.setAutoCommit("false");
               dao.daoMethod1(...);
               dao.daoMethod2(...);
               con.commit();
           }catch(Exception e){
              try{
                     con.close();
                 }catch(Exception e){
                     con.rollback();
                 }
           }
       }
}

理想代码结构如下

public class  xxxService(){
    private xxxDao dao = new xxxDao();

    public void serviceMethod(){
        try{
            JdbcUtils.beginTransaction();
            dao.daoMethod1(...);
            dao.daoMethod2(...);
            JdbcUtils.commitTransaction();
        }catch(Exception e){
            JdbcUtils.rollbackTransaction();
        }
    }
}

接下来直接贴代码

package jdbc2;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

/**
 * Created by kevin on 2020/3/29.
 */
public class JdbcUtils {
    //加载配置文件的默认配置,需要配置c3p0-config.xml(放在src/main/Resource目录下即可)
    private static ComboPooledDataSource dataSource = new ComboPooledDataSource();

    //事务专用连接
    private static Connection connection = null;

    public static Connection getConnection() throws SQLException{
        if(connection!=null){
            return connection;
        }else{
            return dataSource.getConnection();
        }
    }

    public static DataSource getDataSource(){
        return dataSource;
    }

    /**
     * 开启事务
     * 1.获取一个Connection,设置它的setAutoCommit(false)
     * 2.还要保证dao中使用的连接是我们刚刚创建的
     */
    public static void beginTransaction(){
        try {
            if(connection!=null){
                throw new SQLException("已经开启事务,请勿重复操作");
            }
            connection = getConnection();
            connection.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 提交事务
     * 1.获取beginTransaction提供的Connection,然后调用commit方法
     */
    public static void commitTransaction(){

        try {
            if(connection==null){
                throw new SQLException("还没有开启事务!不能提交");
            }
            connection.commit();
            connection.close();
            connection= null;//必须清空值否则下次会得到一个已经关闭的连接
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 回滚事务
     * 1.获取beginTransaction提供的Connection,然后调用了rollback方法
     */
    public static void rollbackTransaction(){
        try {
            if(connection==null){
                throw new SQLException("还没有开启事务!不能回滚");
            }
            connection.rollback();
            connection.close();
            connection= null;//必须清空值否则下次会得到一个已经关闭的连接
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

package jdbc2;
import org.apache.commons.dbutils.QueryRunner;
import java.sql.Connection;
import java.sql.SQLException;

/**
 * Created by kevin on 2020/3/29.
 */
public class AccountDao {
    public void update(String name,double money){
        try {
            QueryRunner queryRunner = new QueryRunner();
            String sql = "update account set balance = balance+? where name = ?";
            Object [] params = {money,name};
            //需要保证多次调用的是同一个连接
            Connection connection = JdbcUtils.getConnection();
            queryRunner.update(connection,sql,params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

package jdbc2;

import org.junit.Test;

/**
 * Created by kevin on 2020/3/30.
 */
public class Demo1 {
    private AccountDao dao = new AccountDao();
    @Test
    public void ServiceMethod(){
        try {
            JdbcUtils.beginTransaction();
            dao.update("zs",10000);
            dao.update("ls",-10000);
            JdbcUtils.commitTransaction();
        } catch (Exception e) {
            try {
                JdbcUtils.rollbackTransaction();
            } catch (Exception e1) {
                e1.printStackTrace();
            }
        }
    }
}

c3p0-config.xml

<?xml version="1.0" encoding="utf-8" ?>
<c3p0-config>
    <!--默认使用default-config配置-->
    <default-config>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/test?serverTimezone=UTC&amp;useUnicode=true&amp;characterEncoding=utf-8</property>
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="user">root</property>
        <property name="password">mysql</property>

        <property name="initialPoolSize">10</property>
        <property name="maxIdleTime">30</property>
        <property name="maxPoolSize">50</property>
        <property name="minPoolSize">10</property>
    </default-config>
    <!--命名配置named-config-->
    <!--<named-config name="myConfig">-->
        <!--<property name="jdbcUrl">jdbc:mysql://localhost:3306/test?serverTimezone=UTC&amp;useUnicode=true&amp;characterEncoding=utf-8</property>-->
        <!--<property name="driverClass">com.mysql.cj.jdbc.Driver</property>-->
        <!--<property name="user">root</property>-->
        <!--<property name="password">mysql</property>-->
        <!--<property name="initialPoolSize">10</property>-->
        <!--<property name="maxIdleTime">30</property>-->
        <!--<property name="maxPoolSize">50</property>-->
        <!--<property name="minPoolSize">10</property>-->
    <!--</named-config>-->
</c3p0-config>

下载源码

此处提供相关源码:点击此处
sql漏了补上

/*
Navicat MySQL Data Transfer

Source Server         : test
Source Server Version : 50550
Source Host           : localhost:3306
Source Database       : test

Target Server Type    : MYSQL
Target Server Version : 50550
File Encoding         : 65001

Date: 2020-04-02 12:17:35
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for account
-- ----------------------------
DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `balance` decimal(12,0) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of account
-- ----------------------------
INSERT INTO `account` VALUES ('1', 'zs', '110000');
INSERT INTO `account` VALUES ('2', 'ls', '90000');
INSERT INTO `account` VALUES ('3', 'ww', '100000');