1.设计基于ThreadLocal实现的JDBC工具类。

在上小节的基础上，我们继续完善基于ThreadLocal实现的JDBC工具类，更名为DruidDBUtil，把常见的数据库的操作(CRUD)包括事务封装到这个工具类中。

package com.simoniu.util;


import com.alibaba.druid.pool.DruidDataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.List;
import java.util.Properties;

public class DruidDBUtil {

    //声明druid连接池对象
    private static DruidDataSource dataSource = null;

    //访问数据库的小帮手
    private static String url = null;
    private static String username = null;
    private static String password = null;
    /**
     * 初始连接数
     **/
    private static int initialSize;
    /**
     * 最大活动连接数
     **/
    private static int maxActive;
    /**
     * 最小闲置连接数
     **/
    private static int minIdle;
    /**
     * 连接耗尽时最大等待获取连接时间
     **/
    private static long maxWait;

    //保证线程安全的数据库访问，一个线程只绑定一个链接对象，多次访问时同一个连接对象
    private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();

    private DruidDBUtil() {

    }

    static {
        initPool();
    }

    //初始化数据库连接池
    private static void initPool() {
        try {
            Properties pro = new Properties();
            InputStream in = DruidDBUtil.class.getClassLoader().getResourceAsStream("druid.properties");
            pro.load(in); //读取属性文档配置信息
            String driverClassName = pro.getProperty("driverClassName");
            Class.forName(driverClassName);
            url = pro.getProperty("url");
            username = pro.getProperty("username");
            password = pro.getProperty("password");
            initialSize = Integer.parseInt(pro.getProperty("initialSize"));
            maxActive = Integer.parseInt(pro.getProperty("maxActive"));
            maxWait = Integer.parseInt(pro.getProperty("maxWait"));
            minIdle = Integer.parseInt(pro.getProperty("minIdle"));

            //创建druid数据源
            dataSource = new DruidDataSource();
            dataSource.setUrl(url);
            dataSource.setUsername(username);
            dataSource.setPassword(password);

            //设置连接池中初始连接数
            dataSource.setInitialSize(initialSize);
            //设置最大连接数
            dataSource.setMaxActive(maxActive);
            //设置最小的闲置链接数
            dataSource.setMinIdle(minIdle);
            //设置最大的等待时间(等待获取链接的时间)
            dataSource.setMaxWait(maxWait);


        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    //由于使用了数据库连接池，我们不采用单例模式
    public static Connection getConnection() throws Exception {
        // 先从当前线程上获得链接
        Connection conn = tl.get();
        try {
            if (conn == null || conn.isClosed()) {
                //从连接池中获取连接对象
                conn = dataSource.getConnection();
                // 把连接绑定到当前线程上
                tl.set(conn);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    //关闭所有数据库访问对象
    public static void closeAll() {

        try {

            if (DruidDBUtil.getConnection() != null && !DruidDBUtil.getConnection().isClosed())
                DruidDBUtil.getConnection().close();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    // 所有的增删改的方法
    public static int executeUpdate(String sql, List list) throws Exception {

        //添加第二个参数的意义是为了获得新增记录的主键。
        PreparedStatement pstm = getConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

        if (list != null) {
            for (int i = 0; i < list.size(); i++) {
                pstm.setObject(i + 1, list.get(i));
            }
        }
        return pstm.executeUpdate();
    }

    //执行批处理
    public static int[] executeBatch(String sql,List<List> list) throws Exception{
        //添加第二个参数的意义是为了获得新增记录的主键。
        PreparedStatement pstm = getConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        if (list != null) {
            for (int i = 0; i < list.size(); i++) {
                for(int j=0; j< list.get(i).size();j++){
                    pstm.setObject(j + 1, list.get(i).get(j));
                }
                pstm.addBatch();
            }
        }
        return pstm.executeBatch();
    }


    //执行批处理返回主键的集合
    public static long[] executeBatchWithReturnKeys(String sql,List<List> list) throws Exception{
        //添加第二个参数的意义是为了获得新增记录的主键。
        PreparedStatement pstm = getConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        long[] keys = null;
        if (list != null) {
            keys = new long[list.size()];
            for (int i = 0; i < list.size(); i++) {
                keys[i] = executeUpdateWithReturnKey(sql,list.get(i));
            }
        }
        return keys;
    }


    //执行更新语句并且返回新增记录的主键值,主键值为long类型
    public static long executeUpdateWithReturnKey(String sql, List list) throws Exception {

        //添加第二个参数的意义是为了获得新增记录的主键。
        PreparedStatement pstm = getConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        if (list != null) {
            for (int i = 0; i < list.size(); i++) {
                pstm.setObject(i + 1, list.get(i));
            }
        }
        pstm.executeUpdate();
        ResultSet rs = pstm.getGeneratedKeys();
        long key = -1;
        //注意：这里必须把游标指向第一条记录。
        if(rs.next()){
            key = rs.getLong(1);
        }
        return key;
    }

    //所有的查询的方法
    public static ResultSet executeQuery(String sql, List list) throws Exception{

        PreparedStatement pstm = getConnection().prepareStatement(sql);
        if (list != null) {
            for (int i = 0; i < list.size(); i++) {
                pstm.setObject(i + 1, list.get(i));
            }
        }
        return pstm.executeQuery();

    }

    //开启事务
    public static void startTransaction() {
        Connection conn = null;
        try {
            conn = getConnection();
            conn.setAutoCommit(false);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //提交事务
    public static void commit() {
        Connection conn = null;
        try {
            conn = getConnection();
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //回滚
    public static void rollback() {
        Connection conn = null;
        try {
            conn = getConnection();
            conn.rollback();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //释放资源
    public static void release() {
        Connection conn = null;
        try {
            conn = getConnection();
            conn.close();
            tl.remove();// 与线程池有关，解除关系
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //关闭事务
    public static void endTransaction() {
        Connection conn = null;
        try {
            conn = getConnection();
            conn.setAutoCommit(true);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

2.测试基于ThreadLocal JDBC工具类 测试基于ThreadLocal JDBC工具类实现单表的CRUD。

Students实体类

package com.simoniu.entity;


import java.io.Serializable;

public class Students implements Serializable,Cloneable {

    private Integer sid; //学号
    private String sname; //姓名
    private String gender; //密码


    public Students() {
    }

    public Students(Integer sid, String sname, String gender) {
        this.sid = sid;
        this.sname = sname;
        this.gender = gender;
    }

    public Students( String sname, String gender) {

        this.sname = sname;
        this.gender = gender;
    }

    public Integer getSid() {
        return sid;
    }

    public void setSid(Integer sid) {
        this.sid = sid;
    }

    public String getSname() {
        return sname;
    }

    public void setSname(String sname) {
        this.sname = sname;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    @Override
    public String toString() {
        return "Students{" +
                "sid=" + sid +
                ", sname='" + sname + '\'' +
                ", gender='" + gender + '\'' +
                '}';
    }
}

编写测试类

package com.simoniu.test;

/*
* 使用DruidDBUtil工具类实现针对学生表的增删改查
*
* */

import com.simoniu.entity.Students;
import com.simoniu.util.DruidDBUtil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class DruidDBUtilCrudTest {

    //添加单个学生返回主键
    public static long addStudentsWithReturnKey(Students s){
        String sql = "insert into students (sname,gender) values (?,?)";
        List argList = new ArrayList();
        try{
            argList.add(s.getSname());
            argList.add(s.getGender());
            //开启事务
            DruidDBUtil.startTransaction();
            long key = DruidDBUtil.executeUpdateWithReturnKey(sql,argList);
            return key;

        }catch(Exception ex){
            ex.printStackTrace();
            DruidDBUtil.rollback();
            return -1;
        }finally{
            DruidDBUtil.commit();
            DruidDBUtil.endTransaction();
            DruidDBUtil.release();
        }
    }

    //添加单个学生不返回主键
    public static boolean addStudents(Students s){
        String sql = "insert into students (sname,gender) values (?,?)";
        List argList = new ArrayList();
        try{
            argList.add(s.getSname());
            argList.add(s.getGender());
            //开启事务
            DruidDBUtil.startTransaction();
            DruidDBUtil.executeUpdate(sql,argList);
            return true;

        }catch(Exception ex){
            ex.printStackTrace();
            DruidDBUtil.rollback();
            return false;
        }finally{
            DruidDBUtil.commit();
            DruidDBUtil.endTransaction();
            DruidDBUtil.release();
        }
    }

    //添加学生集合返回主键
    public static long[] addStudentsListWithKeys(List<Students> studentsList){
        long[] keys = new long[studentsList.size()];
        String sql = "insert into students (sname,gender) values (?,?)";
        List<List> argsList = new ArrayList<List>();
        try{
            DruidDBUtil.startTransaction();
            for(Students s: studentsList){
                List argList = new ArrayList();
                argList.add(s.getSname());
                argList.add(s.getGender());
                argsList.add(argList);
            }
            keys = DruidDBUtil.executeBatchWithReturnKeys(sql,argsList);
            return keys;
        }catch(Exception ex){
            ex.printStackTrace();
            DruidDBUtil.rollback();
            return keys;
        }finally{
            DruidDBUtil.commit();
            DruidDBUtil.endTransaction();
            DruidDBUtil.release();
        }
    }

    //添加学生集合不返回主键
    public static boolean addStudentsList(List<Students> studentsList){

        String sql = "insert into students (sname,gender) values (?,?)";
        List<List> argsList = new ArrayList<List>();
        try{
            DruidDBUtil.startTransaction();
            for(Students s: studentsList){
                List argList = new ArrayList();
                argList.add(s.getSname());
                argList.add(s.getGender());
                argsList.add(argList);
            }
            DruidDBUtil.executeBatch(sql,argsList);
            return true;
        }catch(Exception ex){
            ex.printStackTrace();
            DruidDBUtil.rollback();
            return false;
        }finally{
            DruidDBUtil.commit();
            DruidDBUtil.endTransaction();
            DruidDBUtil.release();
        }
    }


    //根据学号查询指定学生
    public static Students queryStudentsBySid(int sid){
        String sql = "select * from Students where sid = ?";
        List argList = new ArrayList();
        Students s = null;
        ResultSet rs = null;
        try{
            argList.add(sid);
            rs = DruidDBUtil.executeQuery(sql,argList);
            if(rs.next()){
                s = new Students();
                s.setSid(rs.getInt("sid"));
                s.setSname(rs.getString("sname"));
                s.setGender(rs.getString("gender"));
            }
            return s;
        }catch(Exception ex){
            ex.printStackTrace();
            return s;
        }finally{

            DruidDBUtil.release();
        }
    }

    //更新学生
    public static boolean updateStudents(int sid, Students s){
        String sql = "update students set sname=? , gender=? where sid = ?";
        List argList = new ArrayList();
        try{
            DruidDBUtil.startTransaction();
            argList.add(s.getSname());
            argList.add(s.getGender());
            argList.add(sid);
            DruidDBUtil.executeUpdate(sql,argList);
            //long key = DruidDBUtil.executeUpdateWithReturnKey(sql,argList);
            //System.out.println("key=" + key);
            return true;
        }catch(Exception ex){
            ex.printStackTrace();
            DruidDBUtil.rollback();
            return false;
        }finally{
            DruidDBUtil.commit();
            DruidDBUtil.endTransaction();
            DruidDBUtil.release();
        }
    }

    //删除指定学号的学生
    public static boolean deleteStudentsBySid(int sid){
        String sql ="delete from students where sid = ?";
        List argList = new ArrayList();
        try{
            DruidDBUtil.startTransaction();
            argList.add(sid);
            DruidDBUtil.executeUpdate(sql,argList);
            return true;
        }catch(Exception ex){
            ex.printStackTrace();
            DruidDBUtil.rollback();
            return false;
        }finally{
            DruidDBUtil.commit();
            DruidDBUtil.endTransaction();
            DruidDBUtil.release();
        }
    }


    public static void main(String[] args) {
        //1.测试insert不返回主键
        Students s = new Students("张三丰","男");
        if(addStudents(s)){
            System.out.println("添加成功！");
        }else{
            System.out.println("添加失败！");
        }

        //测试insert返回主键
        Students s2 = new Students("张无忌","男");

        long key = addStudentsWithReturnKey(s2);
        if(key!=-1) {
            s2.setSid((int) key);
            System.out.println("添加成功!");
            System.out.println(s2);
        }else{
            System.out.println("添加失败！");
        }


        //2.测试查询指定ID的学生
        s = queryStudentsBySid(3);
        System.out.println(s);

        //3.测试修改
        s = new Students("李凯","男");
        if(updateStudents(3,s)){
            System.out.println("修改成功！");
        }else{
            System.out.println("修改失败！");
        }

        //4.测试删除
        if(deleteStudentsBySid(70)){
            System.out.println("删除成功！");
        }else{
            System.out.println("删除失败！");
        }

        //5.测试批量新增返回主键集合
        List<Students> studentsList = new ArrayList<Students>();
        studentsList.add(new Students("曹操","男"));
        studentsList.add(new Students("曹仁","男"));
        studentsList.add(new Students("曹植","男"));

        long[] keys =  addStudentsListWithKeys(studentsList);
        for(int i=0;i<studentsList.size();i++){
            studentsList.get(i).setSid((int)keys[i]);
        }

        System.out.println(studentsList);

        //6.测试批量新增不返回主键
        if(addStudentsList(studentsList)){
            System.out.println("批量新增成功！");
        }else{
            System.out.println("批量新增失败！");
        }
    }
}

运行结果：

添加成功！
添加成功!
Students{sid=98, sname='张无忌', gender='男'}
Students{sid=3, sname='李凯', gender='男'}
修改成功！
删除成功！
[Students{sid=99, sname='曹操', gender='男'}, Students{sid=100, sname='曹仁', gender='男'}, Students{sid=101, sname='曹植', gender='男'}]
批量新增成功！