package com.dgd.test;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.util.Properties;

public class JDBCToolsV2 {

    private  static DataSource ds;

    private  static ThreadLocal<Connection> th;

    //静态代码块，创建数据库连接池

    static {

        try {

            Properties p=new Properties();

            p.load(JDBCToolsV1.class.getClassLoader().getResourceAsStream("druid.properties"));

            ds= DruidDataSourceFactory.createDataSource(p);

            th=new ThreadLocal<>();

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

    public  static Connection getConnection(){

        //方式1: DriverManger.getConnection();

        //方式2: 数据库连接池, ds.getConnection();

        try {

            Connection conn=th.get();   //获取当前线程的共享连接对象

            if(conn==null)              //当前线程没有拿过连接，第一个获取连接

            {

                conn=ds.getConnection();//从线程池中哪一个新的

                th.set(conn);           //放到当前线程共享变量中

            }

            return  conn;

        } catch (SQLException e) {

            e.printStackTrace();

            return  null;

        }

    }

    //关闭连接方法

    public  static  void free( Connection conn){

        try {

            Connection conn=th.get();   //获取当前线程的共享连接对象
            if(conn！=null)              //当前线程获取连接
            {
                conn.close();　　　　　　//关闭当前数据库连接
            }
　　　　　　  th.set(null);           //将ThreadLocal设置为空，防止上次关闭，下次获取一个关闭的数据库连接


        } catch (SQLException e) {

            e.printStackTrace();

        }

    }

    public  static  int update3( String sql, Object... args) throws SQLException {

        Connection conn=getConnection();

        PreparedStatement ps=conn.prepareStatement(sql);

        if(args!=null && args.length>0)

        {

            for (int i = 0; i <args.length ; i++) {

                ps.setObject(i+1,args[i]);

            }

        }

        int len=ps.executeUpdate();

        ps.close();

        return  len;

    }

}

package com.dgd.test;

import org.junit.Test;

import java.sql.Connection;

import java.sql.SQLException;

public class TestJDBCToolV1 {

    @Test

    public  void  test2() throws SQLException {

        String sql1="INSERT INTO COURSE VALUES(NULL,?)";

        String sql2="INSERT INTO COURSE VALUES(NULL,?)";

        Connection conn= JDBCToolsV2.getConnection();

        conn.setAutoCommit(false);

        try {

            int len1=JDBCToolsV2.update3(sql1,"美术");

            int len2=JDBCToolsV2.update3(sql2,"体育");

            if(len1>0 && len2>0)

            {

                conn.commit();

            }

            else

            {

                conn.rollback();

            }

        } catch (SQLException e) {

            conn.rollback();

        }

        conn.setAutoCommit(true);

        JDBCToolsV2.free(conn);

    }

}