JDBC（二）封装连接工具类和封装，结合Properties配置文件连接工具类的封装，结合ThreadLocal开启事务，结合DBUtils执行数据库语句操作

本文链接：https://blog.csdn.net/weixin_45800201/article/details/123834604

@[TOC](目录)

一、开启事务

1.1 连接工具类的简单封装

1.1.1 dao层

package com.peanut.demo01_transaction.dao;

import com.peanut.demo01_transaction.utils.ConnectionUtilityClass;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * @Description 赚钱操作数据库
 * @Package com.peanut.dao
 * @ClassName ToMakeMoneyDao
 * @Data 2022-03-29 11:17
 * @Author peanut
 */
public class ToMakeMoneyDao {

    /**
     * 钱到账
     * 异常向上层抛出，给service层捕获，因为那些开启了异常回滚
     * @param username
     * @param money
     * @param conn
     * @return
     * @throws SQLException
     */
    public static int addMoney(String username, Double money, Connection conn) throws SQLException {
        PreparedStatement ps = conn.prepareStatement("update account set money=money+? where username= ?");
        ps.setDouble(1, money);
        ps.setString(2, username);
        int i = ps.executeUpdate();
        ConnectionUtilityClass.closeStatement(ps);
        if (i > 0) {
            return 1;
        }
        return 0;
    }

    /**
     * 钱转账
     * 异常向上层抛出，给service层捕获，因为那些开启了异常回滚
     * @param username
     * @param money
     * @param conn
     * @return
     * @throws SQLException
     */
    public static int loseMoney(String username, Double money, Connection conn) throws SQLException {
        PreparedStatement ps = conn.prepareStatement("update account set money=money-? where username= ?");
        ps.setDouble(1, money);
        ps.setString(2, username);
        int i = ps.executeUpdate();
        ConnectionUtilityClass.closeStatement(ps);
        if (i > 0) {
            return 1;
        }
        return 0;
    }
}

1.1.2 service层（开启事务）

package com.peanut.demo01_transaction.service;

import com.peanut.demo01_transaction.utils.ConnectionUtilityClass;
import com.peanut.demo01_transaction.dao.ToMakeMoneyDao;

import java.sql.Connection;
import java.sql.SQLException;

/**
 * @Description 业务逻辑层，赚钱具体实现
 * @Package com.peanut.service
 * @ClassName ToMakeMoneyService
 * @Data 2022-03-29 11:00
 * @Author peanut
 */
public class ToMakeMoneyService {

    /**
     * @Description 赚钱操作
     * @Return void
     * @Method_name toMakeMoney
     * @Param [from, to, money]
     */
    public void toMakeMoney(String from, String to, Double money) {
        Connection conn = ConnectionUtilityClass.getConnection();
        try {
        	//开启事务
            conn.setAutoCommit(false);
            ToMakeMoneyDao.loseMoney(from, money, conn);
            //设置异常点
            double a = 1 / 0;
            ToMakeMoneyDao.addMoney(to, money, conn);
            //提交事务
            conn.commit();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            try {
            //异常回滚
                conn.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } finally {
            ConnectionUtilityClass.closeConnection(conn);
        }
    }
}

1.1.3 utils连接工具类的封装

package com.peanut.demo01_transaction.utils;

import java.sql.*;

/**
 * 数据库连接工具类
 * 懒汉设计模式
 *
 * @author peanut
 */
public class ConnectionUtilityClass {

    private static ConnectionUtilityClass cuc;

    private static Connection conn;

    private ConnectionUtilityClass() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/qf?characterEncoding=utf-8&serverTimezone=UTC&useSSL=false";
            String username = "数据库用户名";
            String password = "数据库密码";
            conn = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取数据库连接
     *
     * @return
     */
    public static Connection getConnection() {
        synchronized (ConnectionUtilityClass.class) {
            if (cuc == null) {
                cuc = new ConnectionUtilityClass();
            }
            return conn;
        }
    }

    /**
     * 关闭rs连接
     *
     * @param rs
     * @return
     */
    public static Boolean closeResult(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                return false;
            }
        }
        return true;
    }

    /**
     * 关闭st
     *
     * @param st
     * @return
     */
    public static Boolean closeStatement(Statement st) {
        if (st != null) {
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                return false;
            }
        }
        return true;
    }

    /**
     * 关闭数据库连接
     *
     * @param conn
     * @return
     */
    public static Boolean closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
                //关闭连接后，把工具类对象设置为空，以便下次重新连接
                cuc = null;
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                return false;
            }
        }
        return true;
    }
}

1.1.4 测试类

package com.peanut.demo01_transaction;
import com.peanut.demo01_transaction.service.ToMakeMoneyService;

/**
 *@Description 测试类 java中开启事务
 *@Package com
 *@ClassName Test
 *@Data 2022-03-29 11:50
 *@Author peanut
 */
public class Test {
    public static void main(String[] args) {
        ToMakeMoneyService tmms = new ToMakeMoneyService();
        tmms.toMakeMoney("lisi","zhangsan",100.0);
    }
}