JDBCTemplate及事务性工具类调用

JDBCTemplate及事务性工具类调用

JDBC工具类 JdbcUtils.java

package com.yqs.utils;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.transaction.support.TransactionSynchronizationManager;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

public class JdbcUtils {

    private static DataSource dataSource = null;

    static {
        try (
                InputStream input = JdbcUtils.class.getResourceAsStream("/JDBCUtils.properties")) {
            Properties properties = new Properties();
            properties.load(input);
            dataSource = new DriverManagerDataSource(properties.getProperty("url"), properties.getProperty("username"), properties.getProperty("password"));
        } catch (Exception ex) {
           throw new RuntimeException(ex);
        }
    }


    public static JdbcTemplate getJDBCTemplate () {
        JdbcTemplate template = new JdbcTemplate(dataSource);
        return template;
    }

    /**
     * 获取唯一的连接池对象
     * @return
     */
    public static DataSource getDataSource(){
        return dataSource;
    }

    /**
     * 开启线程绑定+事务控制+获取数据库连接
     * @return  获取到的数据库连接
     */
    public static Connection StartTransaction(){
        //开启线程绑定-未开启时开启
        if (!TransactionSynchronizationManager.isSynchronizationActive()) {
            TransactionSynchronizationManager.initSynchronization();
        }
        //获取Connection
        Connection conn = DataSourceUtils.getConnection(JdbcUtils.getDataSource());
        return conn;
    }

    /**
     * 提交事务
     * @param conn 处理事物的连接对象
     */
    public static void commit(Connection conn){
        try {
            conn.commit();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            clear(conn);
        }
    }

    /**
     * 回滚事务
     * @param conn 处理事物的连接对象
     */
    public static void rollback(Connection conn){
        try {
            conn.rollback();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            clear(conn);
        }
    }

    /**
     * 内部二次提炼：存储着清除资源和归还连接的操作
     * @param conn 被归还的连接
     */
    private static void clear(Connection conn){
        //清除资源
        TransactionSynchronizationManager.clear();
        //解除绑定
        TransactionSynchronizationManager.unbindResourceIfPossible(dataSource);
        //在连接不为空时归还连接至连接池
        if (conn != null) {
            DataSourceUtils.releaseConnection(conn, dataSource);
        }
    }


}

JDBCUtils.properties

url=jdbc:mysql://localhost:3306/d1?useUnicode=true&characterEncoding=utf-8
username=root
password=123456

调用JDBCUtils的逻辑代码

package com.yqs.service.Impl;

import com.yqs.dao.Impl.UserDaoImpl;
import com.yqs.dao.UserDao;
import com.yqs.entity.User;
import com.yqs.service.UserService;
import com.yqs.utils.JdbcUtils;
import org.springframework.jdbc.datasource.DataSourceUtils;

import java.sql.Connection;

public class UserServiceImpl implements UserService {

    UserDao userDao = new UserDaoImpl();

    Connection conn = null;

    /**
     * 注册功能
     *
     * @param username 输入用户账号
     * @param password 输入用户密码
     * @return 返回boolean
     */
    @Override
    public boolean register(String username, String password) {
        boolean boo = false;

        try {
            //开启事务
            conn = JdbcUtils.StartTransaction();
            System.out.println(">>正在开启手动控制事务");
            conn.setAutoCommit(false);
            //执行功能代码

            if (userDao.selectByUsername(username) != null) {
                throw new RuntimeException("已经存在重复的账户号码");
            }
            int n = userDao.insert(username, password);

            if (n == 1) {
                boo = true;
            } else {
                boo = false;
            }

            //提交确认事务
            System.out.println(">>正在提交事务...");
            JdbcUtils.commit(conn);
        } catch (Exception e) {

            try {
                //撤销回滚
                System.out.println(">>正在回滚事务...");
                JdbcUtils.rollback(conn);
            } catch (Exception e1) {
                throw new RuntimeException(e1);
            }
            throw new RuntimeException(e);

        }


        return boo;
    }

    /**
     * 登录功能
     *
     * @param username 输入用户账号
     * @param password 输入用户密码
     * @return 返回boolean
     */
    @Override
    public boolean login(String username, String password) {

        boolean boo = false;

        try {
            //开启事务
            conn = JdbcUtils.StartTransaction();
            System.out.println(">>正在开启手动控制事务");
            conn.setAutoCommit(false);
            //执行功能代码

            User user = userDao.selectCheck(username, password);
            if (user.getUserName().equals(null)) {
                throw new RuntimeException("用户名不存在");
            }
            if (user.equals(null)) {
                boo = false;
            } else {
                boo = true;
            }

            //提交确认事务
            System.out.println(">>正在提交事务...");
            JdbcUtils.commit(conn);
        } catch (Exception e) {

            try {
                //撤销回滚
                System.out.println(">>正在回滚事务...");
                JdbcUtils.rollback(conn);
            } catch (Exception e1) {
                throw new RuntimeException(e1);
            }
            throw new RuntimeException(e);

        }

        return boo;
    }
}

事务控制分析和实现
事务问题分析和处理