DBUtils框架详解

DBUtils框架 DBUtils框架的作用

DBUtils框架能够将将JDBC的方法封装，大大简化了Dao层的操作，且提供出了处理结果集的接口，可以依据项目做拓展。让整个JDBCUitls也变得更加简单。

1.框架处理结果集的接口：

ResultSetHandler（结果集处理的接口）、ColumnRrocessor（列接受器接口）

public interface ResultSetHandler {
    
    T handler(ResultSet resultSet) throws SQLException, IllegalAccessException, InstantiationException, InvocationTargetException;
}

public interface ColumnProcessor {
    
    T[] handler (ResultSet resultSet) throws SQLException;
}

2.框架的异常处理：

DBUtilException

public class DBUtilException extends RuntimeException {
    private int code;

    private String errorMessage;

    public DBUtilException(int code,String errorMessage){
        super(errorMessage);   //错误信息传递给父异常
        this.code=code;
        this.errorMessage=errorMessage;
    }
}

3. 存放错误码的枚举类：

ResponseCode

public enum ResponseCode {
    SELECt_MANY_NUMBER(555,"查询出多个结果，与预期要求不符"),
    SELECT_RESULT_NONE(444,"结果集为空，与预期不相符合"),
    SELECT_RESULT_ERROR(333,"处理集使用错误"),
    SELECT_PARAMETER_DIFFERENT(222,"参数个数不同，无法参与查询"),
    SELECT_PARAMETER_EMPTY(111,"含有空参数，无法参与查询");
    private int code;
    private String msg;

    private ResponseCode(int code ,String msg){
        this.code = code;
        this.msg = msg;
    }

    public int getCode() {
        return code;
    }

    public String getMsg() {
        return msg;
    }
}

4. 框架的核心执行类：

QueryRunner：通过处理集处理SQL语句，并返回相应的返回值

public class QueryRunner {

    //设置数据源
    private DataSource dataSource;

    //线程局部变量存储连接对象
    private static ThreadLocal local =new ThreadLocal<>();

    //有参构造方法，初始化数据源
    public QueryRunner(DataSource dataSource){
        this.dataSource=dataSource;
    }

    
    public Connection getConnection() throws SQLException {
        Connection connection=local.get();
        //查看当前local是否存有连接对象
        if(connection!=null){
            return connection;
        }else{
            //从数据源中获取一个新的connection
            connection = dataSource.getConnection();
            //装入local
            local.set(connection);
            return connection;
        }
    }

    
    public int update(String sql,Object... args) throws SQLException {
        //获取连接对象
        Connection connection = getConnection();
        //获取提交SQL语句的对象
        PreparedStatement statement = getPreparedStatement(sql, connection, args);
        //提交sql给数据库 获取返回值：受影响的行数
        int rows = statement.executeUpdate();
        close(statement,null);
        return rows;
    }

    
    public int update(Connection connection,String sql,Object... args) throws SQLException {
        //获取提交SQL语句的对象
        PreparedStatement statement = getPreparedStatement(sql, connection, args);
        //提交sql给数据库 获取返回值：受影响的行数
        int rows = statement.executeUpdate();
        close(statement,null);
        return rows;
    }

    
    public  T query(String sql, ResultSetHandler resultSetHandler, Object... args) throws SQLException, IllegalAccessException, InvocationTargetException, InstantiationException {

        //参数判断
        if (StringUtils.isEmpty(sql)||resultSetHandler==null){
            throw new DBUtilException(ResponseCode.SELECT_PARAMETER_EMPTY.getCode(),ResponseCode.SELECT_PARAMETER_EMPTY.getMsg());
        }
        PreparedStatement preparedStatement = getPreparedStatement(sql, getConnection(), args);
        //获取返回结果集
        ResultSet resultSet = preparedStatement.executeQuery();
        T t = resultSetHandler.handler(resultSet);
        close(preparedStatement,resultSet);
        return t;
    }


    
    private PreparedStatement getPreparedStatement(String sql, Connection connection, Object[] args) throws SQLException {
        PreparedStatement statement = connection.prepareStatement(sql);
        //获取sql参数的元数据
        ParametermetaData parametermetaData = statement.getParametermetaData();
        //获取参数个数（SQL语句中占位符的个数）
        int parameterCount = parametermetaData.getParameterCount();

        //判断占位符和去args参数个数是否相等
        if (parameterCount!= args.length){
            throw new DBUtilException(ResponseCode.SELECT_PARAMETER_DIFFERENT.getCode(), ResponseCode.SELECT_PARAMETER_DIFFERENT.getMsg());
        }
        //循环设置SQL中的占位符
        for (int i = 0; i < parameterCount; i++) {
            statement.setObject(i+1, args[i]);
        }
        return statement;
    }

    public void close(PreparedStatement statement,ResultSet resultSet) throws SQLException {
        Connection connection = local.get();
        if (resultSet!=null){
            resultSet.close();
        }
        if (statement!=null){
            statement.close();
        }
        if(null!=connection){
            connection.close();
            local.remove();
        } 
    }
}

5. 基础的结果集处理器和列处理器

BeanHandler：处理查询到的单行数据

public class BeanHandler implements ResultSetHandler {
    //设置封装对象
    private Class laCass;

    public BeanHandler(Class laCass){
        this.laCass=laCass;
    }
    @Override
    public T handler(ResultSet resultSet) throws SQLException, IllegalAccessException, InstantiationException, InvocationTargetException {
        //获取结果集的元数据
        ResultSetmetaData metaData = resultSet.getmetaData();
        //通过元数据获取列的数量
        int columnCount = metaData.getColumnCount();

        //开始封装对象
        //利用反射实例化对象
        T t = (T) laCass.newInstance();
        //判断结果集指针下是否有数据
        if (!resultSet.next()){
            return null;
        }
        //获取查询行的数据
        for (int i = 0; i < columnCount; i++) {
            //获取列名
            String columnName = metaData.getColumnName(i + 1);
            //获取值
            Object object = resultSet.getObject(i + 1);
            //使用BeanUtils工具类封装
            BeanUtils.copyProperty(t,columnName,object);
        }
        //再次判断是否有多行数据
        if (resultSet.next()){
            throw new DBUtilException(ResponseCode.SELECT_MANY_NUMBER.getCode(),ResponseCode.SELECT_MANY_NUMBER.getMsg());
        }
        return t;
    }
}

BeanListHandler：处理查询到的多行数据

public class BeanListHandler implements ResultSetHandler> {
    //设置封装对象
    private Class laCass;

    public BeanListHandler(Class laCass){
        this.laCass=laCass;
    }

    @Override
    public List handler(ResultSet resultSet) throws SQLException, IllegalAccessException, InstantiationException, InvocationTargetException {
        //获取结果集的元数据
        ResultSetmetaData metaData = resultSet.getmetaData();
        //获取列的数量
        int columnCount = metaData.getColumnCount();

        //创建集合
        List ts = new ArrayList();
        while (resultSet.next()){
            //创建封装对象
            T t = (T) laCass.newInstance();
            for (int i = 0; i < columnCount; i++) {
                //获取列名
                String columnName = metaData.getColumnName(i + 1);
                //获取值
                Object object = resultSet.getObject(i + 1);
                //使用BeanUtils工具类封装
                BeanUtils.copyProperty(t,columnName,object);
            }
            ts.add(t);
        }
        return ts;
    }
}

BeanArrayHandler：处理查询到的单列数据

注：这里使用到了处理单列的列处理器

public class BeanArrayHandler implements ResultSetHandler {
    private ColumnProcessor columnProcessor;

    public BeanArrayHandler(ColumnProcessor columnProcessor){
        this.columnProcessor = columnProcessor;
    }
    @Override
    public T[] handler(ResultSet resultSet) throws SQLException {

        if (!resultSet.next()){
            throw new DBUtilException(ResponseCode.SELECT_RESULT_NONE.getCode(), ResponseCode.SELECT_RESULT_NONE.getMsg());
        }
        return (T[]) columnProcessor.handler(resultSet);
    }
}

ColumnProcessor：单列处理器

public class BasicColumnProcessor implements ColumnProcessor {
    private Class aClass;
    public BasicColumnProcessor(Class aClass){
        this.aClass = aClass;
    }

    @Override
    public T[] handler(ResultSet resultSet) throws SQLException {
        //游标还原
        resultSet.beforeFirst();
        //获取结果集中的元数据
        ResultSetmetaData metaData = resultSet.getmetaData();
        //获取返回的列数
        int columnCount = metaData.getColumnCount();
        //判断当前列是否是1
        if (columnCount!=1){
            throw new DBUtilException(ResponseCode.SELECT_RESULT_ERROR.getCode(), ResponseCode.SELECT_RESULT_ERROR.getMsg());
        }
        //创建集合存放数据
        ArrayList ts = new ArrayList<>();
        while (resultSet.next()){
            //获取行的单个数据
            T t = (T) resultSet.getObject(1);
            ts.add(t);
        }
        return getArray(ts);
    }
    public T[] getArray(List tList){
        T[] t = (T[]) Array.newInstance(aClass,tList.size());
        for (int i = 0; i  
在JDBCUtils中使用DBUtils工具类 
public class JDBCUtils {
    //定义线程池
    private static DruidDataSource dataSource;

    //静态代码块初始化
    static {
        dataSource = new DruidDataSource();
        dataSource.setUrl("jdbc:mysql:///studentsystem?useUnicode=true&characterEncoding=UTF-8");
        dataSource.setUsername("root");
        dataSource.setPassword("1234");
    }

    
    public static QueryRunner queryRunner(){
        return new QueryRunner(dataSource);
    }
}
 
Dao层实现类使用示例： 
public class UserDAOImpl implements IUserDAO {

    
    @Override
    public void insert(User user) throws SQLException {
        JDBCUtils.queryRunner().update("insert into user(userName,password) values(?,?)",user.getUserName(),user.getPassword());
    }

    
    @Override
    public void update(User user) throws SQLException {
        JDBCUtils.queryRunner().update("update user set userName=?,password=? where id=?",user.getUserName(),user.getPassword(),user.getId());
    }

    
    @Override
    public User selectUserById(int id) throws InvocationTargetException, SQLException, InstantiationException, IllegalAccessException {
        return JDBCUtils.queryRunner().query("select * from user where id=?",new BeanHandler(User.class),id);
    }

    
    @Override
    public List select() throws InvocationTargetException, SQLException, InstantiationException, IllegalAccessException {
        return JDBCUtils.queryRunner().query("select * from user",new BeanListHandler(User.class));
    }

    
    @Override
    public void delete(int id) throws SQLException {
        JDBCUtils.queryRunner().update("delete from user where id=?",id);
    }
}

DBUtils框架详解

Java相关栏目本月热门文章