Apache Commons DBUtil组件的数据库操作类

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 调用Apache Commons DBUtil组件的数据库操作类
 * new DBUtils();//读取默认数据源,禁用事务
 * new DBUtils("dataSourceName");//读取指定数据源,禁用事务
 * new DBUtils(true/false);//读取默认数据源,启用/禁用事务
 * new DBUtils("dataSourceName",true/false);//读取指定数据源,启用/禁用事务
 * Created by 张勇波 on 2016/8/18.
 */
public class DBUtils {

    private final DataSource dataSource;
    private final QueryRunner queryRunner;
    private static ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>();
    private static final Log LOG = LogFactory.getLog(DBUtils.class);
    /**
     * 是否启用事务,默认禁用
     */
    private boolean transactional = false;

    /**
     * 获取默认数据源
     */
    public DBUtils() {
        dataSource = (DataSource) SpringTool.getBean("dataSource");
        queryRunner = new QueryRunner(dataSource);
        this.transactional = false;
    }

    /**
     * 获取数据源
     *
     * @param dataSourceName DataSource配置的ID
     */
    public DBUtils(String dataSourceName) {
        dataSource = (DataSource) SpringTool.getBean(dataSourceName);
        queryRunner = new QueryRunner(dataSource);
        this.transactional = false;
    }

    /**
     * 获取默认数据源
     *
     * @param transactional 是否启用事务
     */
    public DBUtils(boolean transactional) {
        dataSource = (DataSource) SpringTool.getBean("dataSource");
        queryRunner = new QueryRunner(dataSource);
        this.transactional = transactional;
    }

    /**
     * 获取数据源
     *
     * @param dataSourceName DataSource配置的ID
     * @param transactional  是否启用事务
     */
    public DBUtils(String dataSourceName, boolean transactional) {
        dataSource = (DataSource) SpringTool.getBean(dataSourceName);
        queryRunner = new QueryRunner(dataSource);
        this.transactional = transactional;
    }

    /**
     * 从dataSource获取Connection连接并绑定到当前线程
     *
     * @return Connection
     */
    public Connection getConnection() {
        //从当前线程中获取Connection
        Connection conn = threadLocal.get();
        if (conn == null) {
            //从数据源中获取数据库连接
            try {
                conn = dataSource.getConnection();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            //将conn绑定到当前线程
            threadLocal.set(conn);
        }
        return conn;
    }

    /**
     * 开启当前线程的Connection事物
     * 当前线程内没有Connection连接时，创建Connection连接绑定到当前线程并开启事物
     */
    public void startTransaction() {
        try {
            Connection conn = threadLocal.get();
            if (conn == null) {
                conn = getConnection();
            }
            //开启事务
            conn.setAutoCommit(false);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 当前线程的Connection进行事物回滚
     */
    public void rollback() {
        try {
            //从当前线程中获取Connection
            Connection conn = threadLocal.get();
            if (conn != null) {
                //回滚事务
                conn.rollback();
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 当前线程的Connection进行提交commit
     */
    public void commit() {
        try {
            //从当前线程中获取Connection
            Connection conn = threadLocal.get();
            if (conn != null) {
                //提交事务
                conn.commit();
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 关闭当前线程绑定Connection并解绑
     */
    public void close() {
        try {
            //从当前线程中获取Connection
            Connection conn = threadLocal.get();
            if (conn != null) {
                conn.close();
                //解除当前线程上绑定conn
                threadLocal.remove();
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 执行sql语句
     * <code>
     * executeUpdate("update user set username = 'kitty' where username = ?", "hello kitty");
     * </code>
     *
     * @param sql   sql语句
     * @param param 参数
     * @return 受影响的行数
     */
    public int update(String sql, Object param) {
        return update(sql, new Object[]{param});
    }

    /**
     * 执行sql语句
     *
     * @param sql    sql语句
     * @param params 参数数组
     * @return 受影响的行数
     */
    public int update(String sql, Object... params) {
        int affectedRows = 0;
        if (transactional) {
            Connection conn = getConnection();
            startTransaction();
            try {
                if (params == null) {
                    affectedRows = queryRunner.update(conn, sql);
                } else {
                    affectedRows = queryRunner.update(conn, sql, params);
                }
                commit();
            } catch (SQLException e) {
                rollback();
                LOG.error("DBUtils更新数据时错误，更新已回滚。", e);
            } finally {
                close();
            }
        } else {
            try {
                if (params == null) {
                    affectedRows = queryRunner.update(sql);
                } else {
                    affectedRows = queryRunner.update(sql, params);
                }
            } catch (SQLException e) {
                LOG.error("DBUtils更新数据时错误。", e);
            }
        }
        return affectedRows;
    }

    /**
     * 执行批量sql语句
     *
     * @param sql    sql语句
     * @param params 二维参数数组
     * @return 受影响的行数的数组
     */
    public int[] batchUpdate(String sql, Object[]... params) {
        int[] affectedRows = new int[0];
        if (transactional) {
            Connection conn = getConnection();
            startTransaction();
            try {
                affectedRows = queryRunner.batch(conn, sql, params);
                commit();
            } catch (SQLException e) {
                rollback();
                LOG.error("DBUtils批量更新数据时错误，更新已回滚。", e);
            } finally {
                close();
            }
        } else {
            try {
                affectedRows = queryRunner.batch(sql, params);
            } catch (SQLException e) {
                LOG.error("DBUtils批量更新数据时错误。", e);
            }
        }
        return affectedRows;
    }

    /**
     * 执行查询，将每行的结果保存到一个Map对象中，然后将所有Map对象保存到List中
     *
     * @param sql   sql语句
     * @param param 参数
     * @return 查询结果
     */
    public DBSet find(String sql, Object param) {
        return find(sql, new Object[]{param});
    }

    /**
     * 执行查询，将每行的结果保存到一个Map对象中，然后将所有Map对象保存到List中
     *
     * @param sql    sql语句
     * @param params 参数数组
     * @return 查询结果
     */
    public DBSet find(String sql, Object... params) {
        LOG.info(sql);
        DBSet dbset = new DBSet();
        if (transactional) {
            Connection conn = getConnection();
            startTransaction();
            List<Map<String, Object>> list = null;
            try {
                if (params == null) {
                    list = queryRunner.query(conn, sql, new MapListHandler());
                } else {
                    list = queryRunner.query(conn, sql, new MapListHandler(), params);
                }
                commit();
            } catch (SQLException e) {
                rollback();
                LOG.error("DBUtils查询数据时错误。（事务启用模式）", e);
            } finally {
                close();
            }
            if (list != null) {
                dbset.addAll(list);
            }
        } else {
            List<Map<String, Object>> list = null;
            try {
                if (params == null) {
                    list = queryRunner.query(sql, new MapListHandler());
                } else {
                    list = queryRunner.query(sql, new MapListHandler(), params);
                }
            } catch (SQLException e) {
                LOG.error("DBUtils查询数据时错误。", e);
            }
            if (list != null) {
                dbset.addAll(list);
            }
        }
        return dbset;
    }

    /**
     * 执行查询，将每行的结果保存到Bean中，然后将所有Bean保存到List中
     *
     * @param entityClass 类名
     * @param sql         sql语句
     * @param param       参数
     * @return 查询结果
     */
    public <T> List<T> find(Class<T> entityClass, String sql, Object param) {
        return find(entityClass, sql, new Object[]{param});
    }

    /**
     * 执行查询，将每行的结果保存到Bean中，然后将所有Bean保存到List中
     *
     * @param entityClass 类名
     * @param sql         sql语句
     * @param params      参数数组
     * @return 查询结果
     */
    public <T> List<T> find(Class<T> entityClass, String sql, Object... params) {
        List<T> list = new ArrayList<T>();
        if (transactional) {
            Connection conn = getConnection();
            startTransaction();
            try {
                if (params == null) {
                    list = (List<T>) queryRunner.query(conn, sql, new BeanListHandler(entityClass));
                } else {
                    list = (List<T>) queryRunner.query(conn, sql, new BeanListHandler(entityClass), params);
                }
                commit();
            } catch (SQLException e) {
                rollback();
                LOG.error("DBUtils查询数据时错误。（事务启用模式）", e);
            } finally {
                close();
            }
        } else {
            try {
                if (params == null) {
                    list = (List<T>) queryRunner.query(sql, new BeanListHandler(entityClass));
                } else {
                    list = (List<T>) queryRunner.query(sql, new BeanListHandler(entityClass), params);
                }
            } catch (SQLException e) {
                LOG.error("DBUtils查询数据时错误。", e);
            }
        }
        return list;
    }

    /**
     * 查询出结果集中的第一条记录，并封装成对象
     *
     * @param entityClass 类名
     * @param sql         sql语句
     * @param param       参数
     * @return 对象
     */
    public <T> T findFirst(Class<T> entityClass, String sql, Object param) {
        return findFirst(entityClass, sql, new Object[]{param});
    }

    /**
     * 查询出结果集中的第一条记录，并封装成对象
     *
     * @param entityClass 类名
     * @param sql         sql语句
     * @param params      参数数组
     * @return 对象
     */
    public <T> T findFirst(Class<T> entityClass, String sql, Object... params) {
        Object object = null;
        if (transactional) {
            Connection conn = getConnection();
            startTransaction();
            try {
                if (params == null) {
                    object = queryRunner.query(conn, sql, new BeanHandler(entityClass));
                } else {
                    object = queryRunner.query(conn, sql, new BeanHandler(entityClass), params);
                }
                commit();
            } catch (SQLException e) {
                rollback();
                LOG.error("DBUtils查询数据时错误。（事务启用模式）", e);
            } finally {
                close();
            }
        } else {

            try {
                if (params == null) {
                    object = queryRunner.query(sql, new BeanHandler(entityClass));
                } else {
                    object = queryRunner.query(sql, new BeanHandler(entityClass), params);
                }
            } catch (SQLException e) {
                LOG.error("DBUtils查询数据时错误。", e);
            }
        }
        return (T) object;
    }
}