09 事务和连接池

文章目录

properties文件

driverClass=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
username=root
password=root
initialSize=10
maxActive=20
minIdle=5
maxWait=5000

连接池

package com.aistart.tech.utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;


public class JdbcPoolUtil {

   private static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();

    private static final Properties info = new Properties();

    private static DataSource dataSource = null;
//    private static Connection connection = null;

    static {
        try {
            info.load(JdbcPoolUtil.class.getClassLoader().getResourceAsStream("com/aistart/tech/config/druid.properties"));
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        //数据库连接池应该也同时被创建
        try {
           dataSource = DruidDataSourceFactory.createDataSource(info);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public static Connection getConnection() throws SQLException {

        Connection connection = threadLocal.get();

        if (connection==null){

            connection = dataSource.getConnection();

            threadLocal.set(connection);

        }


        return connection;
    }





    public static void freeConnection(){

        Connection connection = threadLocal.get();

        if (connection!=null){

            try {

                    //把线程变量清除
                    threadLocal.remove();

                    //恢复原来的状态
                    connection.setAutoCommit(true);

                    connection.close();

            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

    }


}

service层实现类

package com.aistart.tech.servcice.impl;

import com.aistart.tech.dao.IAccuntDao;
import com.aistart.tech.dao.impl.AccuntDaoImpl;
import com.aistart.tech.entity.Accunt;
import com.aistart.tech.servcice.AccountService;
import com.aistart.tech.utils.JdbcPoolUtil;

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


public class AccountServiceImpl implements AccountService {


    IAccuntDao accuntDao = new AccuntDaoImpl();
    @Override
    public int getBalance(int id) {
        return 0;
    }

    @Override
    public boolean transferMoney(Accunt accunt1, Accunt accunt2,int money) {

        Connection connection = null;
        try {

            //从数据库连接池拿出来一个连接
            connection = JdbcPoolUtil.getConnection();

            connection.setAutoCommit(false);

            accuntDao.subMoney(accunt1.getId(),money);

            accuntDao.addMoney(accunt2.getId(),money);

            connection.commit();


            System.out.println("service的conn"+ connection.getAutoCommit());


        } catch (Exception e) {

            try {
                connection.rollback();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
            throw new RuntimeException(e);
        }
        finally {
            JdbcPoolUtil.freeConnection();
        }



        return false;
    }
}

dao层实现类

package com.aistart.tech.dao.impl;

import com.aistart.tech.dao.IAccuntDao;
import com.aistart.tech.utils.JdbcPoolUtil;

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


public class AccuntDaoImpl implements IAccuntDao {
    @Override
    public int subMoney(int id, int money) {
        Connection connection = null;

        if (connection == null) {
            try {
                connection = JdbcPoolUtil.getConnection();

                System.out.println("这是dao层的connection"+connection.getAutoCommit());
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

        return 0;
    }

    @Override
    public int addMoney(int id, int money) {
        return 0;
    }
}

dao层实现类

package com.aistart.tech.dao.impl;

import com.aistart.tech.dao.IAccuntDao;
import com.aistart.tech.utils.JdbcPoolUtil;

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


public class AccuntDaoImpl implements IAccuntDao {
    @Override
    public int subMoney(int id, int money) {
        Connection connection = null;

        if (connection == null) {
            try {
                connection = JdbcPoolUtil.getConnection();

                System.out.println("这是dao层的connection"+connection.getAutoCommit());
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

        return 0;
    }

    @Override
    public int addMoney(int id, int money) {
        return 0;
    }
}