DBUtils开源框架

Commons DbUtils是Apache组织提供的一个对JDBC进行简单封装的开源工具类库，使用它能够简化JDBC应用程序的开发，同时也不会影响程序的性能。

QueryRunner类：
该类简化了SQL查询，它与ResultHandler接口一起使用可以完成大部分的数据库操作。能够大大减少编码量。
该类提供了两个构造方法：
1、不带参的构造方法
2、需要一个javax.sql.DataSource来作为参数的构造函数，使用这个构造函数实例化QueryRunner对象后，它会从连接池中获取Connection

ResultHandler接口：
当把从数据库中查询到的结果封装到ResultSet对象中后，具体应该对这个结果集作何种处理是框架作者不可得知的，所以ResultHandler接口的思想就是：由用户决定对这个结果集作何种处理。
ResultHandler接口只有一个方法：handle()方法
用户可以实现这个接口，自行决定如何处理结果集

public T handle(ResultSet arg0) throws SQLException

自己写了一个类简单模拟了这个功能：
在此博文中主要关注JDBCUtils的query()方法和接口实现类即可

package pers.msidolphin.newcase.utils;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import org.junit.Test;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class JDBCUtils {

    private static ComboPooledDataSource c3p0 = null;

    static {
        try {
            c3p0 = new ComboPooledDataSource("mysql");
        }catch(Exception ex) {
            throw new ExceptionInInitializerError(ex);
        }
    }

    public static Connection getConnection() throws SQLException {
        return JDBCUtils.c3p0.getConnection();
    }

    public static ComboPooledDataSource getDataSource() {
        return JDBCUtils.c3p0;
    }


    public static int update(String sql, Object[] params) throws SQLException {
        // TODO Auto-generated method stub
        if(sql == null || "".equals(sql)) {
            return -1;
        }
        if(params == null) {
            params = new Object[0];
        }
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = JDBCUtils.getConnection();
            statement = connection.prepareStatement(sql);
            for(int index = 0 ; index < params.length ; ++index) {
                statement.setObject(index + 1, params[index]);
            }
            return statement.executeUpdate();
        }finally {
            JDBCUtils.release(statement);
            JDBCUtils.release(connection);
        }
    }

    public static Object query(String sql, Object[] params, ResultHandler handle) throws Exception {
        if(sql == null || "".equals(sql)) {
            return null;
        }
        if(params == null) {
            params = new Object[0];
        }
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtils.getConnection();
            statement = connection.prepareStatement(sql);
            //给占位符赋值
            for(int index = 0 ; index < params.length ; ++index) {
                statement.setObject(index + 1, params[index]);
            }
            resultSet = statement.executeQuery();
            //获得结果集后，由于不知道如何处理这个结果集，于是就交给用户自己处理
            Object res = handle.handler(resultSet);
            return res;
        }finally {
            JDBCUtils.release(statement);
            JDBCUtils.release(statement);
            JDBCUtils.release(connection);
        }
    }


    public static void release(ResultSet resultSet) {
        if(resultSet != null) {
            try {
                resultSet.close();
            }catch(Exception ex) {
                ex.printStackTrace();
            }
        }
        resultSet = null;
    }

    public static void release(Statement statement) {
        if(statement != null) {
            try {
                statement.close();
            }catch(Exception ex) {
                ex.printStackTrace();
            }
        }
        statement = null;
    }

    public static void release(Connection connection) {
        if(connection != null) {
            try {
                connection.close();
            }catch(Exception ex) {
                ex.printStackTrace();
            }
        }
        connection = null;
    }   
}

模拟ResultHandler接口

package pers.msidolphin.newcase.utils;

import java.sql.ResultSet;

public interface ResultHandler {

    Object handler(ResultSet resultSet) throws Exception;
}

模拟BeanListHandler实现类

package pers.msidolphin.newcase.utils;

import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BeanListHandler implements ResultHandler {

    private Class clazz;

    public BeanListHandler(Class clazz) {
        // TODO Auto-generated constructor stub
        this.clazz = clazz;
    }

    @Override
    public Object handler(ResultSet resultSet) throws SQLException, InstantiationException, IllegalAccessException, IntrospectionException, IllegalArgumentException, InvocationTargetException {
        // TODO Auto-generated method stub
        if(resultSet == null) {
            return null;
        }
        Object obj = null;
        ResultSetMetaData metaDate = resultSet.getMetaData();
        int cols = metaDate.getColumnCount();
        PropertyDescriptor propertyDescriptor = null;
        Method method = null;
        List list = new ArrayList();
        while(resultSet.next()) {
            obj = clazz.newInstance();
            for(int index = 1; index <= cols ; ++index) {
                String columnName = metaDate.getColumnName(index);
                Object value = resultSet.getObject(index);
                //使用内省操作javabean
                propertyDescriptor = new PropertyDescriptor(columnName, clazz);
                method = propertyDescriptor.getWriteMethod();
                method.invoke(obj, value);
            }
            list.add(obj);
        }
        return (list.size() > 0 ? list : null);
    }

}

当然，框架作者也提供了几个ResultHandler接口的实现类：
ArrayHandler：把结果集中的第一行数据转成一个对象数组返回
ArrayListHandler：把结果集中的每一行数据转成一个对象数组，再把这个对象数组放到List集合中
BeanHandler：将结果集中的第一行数据封装到一个对应的（由用户指定）的JavaBean实例对象中
BeanListHandler：将结果集中的每一行数据封装到对应的（由用户指定）的JavaBean实例对象中，再把这些JavaBean实例对象放到一个List集合中
ColumnListHandler：将结果集中某一列的数据放到List集合中
MapHandler：将结果集中的第一行数据封装到一个Map集合中，key是表对应的字段名，value则是字段对应的值
MapListHandler：将结果集中的每一行数据封装到Map集合中，再把这些Map集合放到一个List集合中
KeyedHandler：将结果集中的每一行数据封装到Map集合中，再把这些Map集合放到一个Map集合中，外面的这个Map集合的key为用户指定的字段名
ScalarHandler： 用于获取结果集中第一行某列的数据并封装到对象中返回

KeyedHandler的解释可能有点绕，画个图直观一点：

下面则是关于如何使用这些实现类的示例：使用了c3p0数据库连接池，Junit进行测试

ArrayHandler:

@Test
    public void testArrayHandler() throws SQLException {
        QueryRunner runner = new QueryRunner(C3P0Utiles.getC3p0());
        String sql = "select * from customer where id = ?";
        Object param = "058f8a58-a097-4b13-a6b2-969b6f49ff26";
        Object[] result = runner.query(sql, new ArrayHandler(), param);
        System.out.println(Arrays.asList(result));
    }

ArrayListHandler:

@Test
    public void testArrayListHandler() throws SQLException {
        QueryRunner runner = new QueryRunner(C3P0Utiles.getC3p0());
        String sql = "select * from customer";
        List<Object[]> result = runner.query(sql, new ArrayListHandler());
        for (Object[] object : result) {
            System.out.println(Arrays.asList(object));
        }
    }

BeanHandler:

@Test
    public void testBeanHandler() throws SQLException {
        QueryRunner runner = new QueryRunner(C3P0Utiles.getC3p0());
        String sql = "select * from customer where id = ?";
        Object[] params = {"058f8a58-a097-4b13-a6b2-969b6f49ff26"};
        Customer customer = runner.query(sql, new org.apache.commons.dbutils.handlers.BeanHandler<>(Customer.class), params);
        System.out.println(customer);
    }

BeanListHandler:

@Test
    public void testBeanListHandler() throws SQLException {
        QueryRunner runner = new QueryRunner(C3P0Utiles.getC3p0());
        String sql = "select * from customer";
        List<Customer> list = runner.query(sql, new BeanListHandler<>(Customer.class));
        for (Customer customer : list) {
            System.out.println(customer);
        }
    }

ColumnListHandler:

@Test
    public void testColumListHandler() throws SQLException {
        QueryRunner runner = new QueryRunner(C3P0Utiles.getC3p0());
        String sql = "select * from customer";
        List<Object> list = runner.query(sql, new ColumnListHandler<>("id"));
        for (Object object : list) {
            String id = (String) object;
            System.out.println(id);
        }
    }

MapHandler:

@Test
    public void testMapHandler() throws SQLException {
        QueryRunner runner = new QueryRunner(C3P0Utiles.getC3p0());
        String sql = "select * from customer where id = ?";
        Object[] params = {"058f8a58-a097-4b13-a6b2-969b6f49ff26"};
        Map<String, Object> map = runner.query(sql, new MapHandler(), params);
        for(Map.Entry<String, Object> entry : map.entrySet()) {
            System.out.println(entry.getKey() + "=" + entry.getValue());
        }
    }

MapListHandler:

@Test
    public void testMapListHandler() throws SQLException {
        QueryRunner runner = new QueryRunner(C3P0Utiles.getC3p0());
        String sql = "select * from customer";
        List<Map<String, Object>> list = runner.query(sql, new MapListHandler());
        for (Map<String, Object> map : list) {
            System.out.println("====================================");
            for(Map.Entry<String, Object> entry : map.entrySet()) {
                System.out.println(entry.getKey() + "=" + entry.getValue());
            }
            System.out.println();
        }
    }

KeyedHandler:

@Test
    public void testKeyedHandler() throws SQLException {
        QueryRunner runner = new QueryRunner(C3P0Utiles.getC3p0());
        String sql = "select * from customer";
        Map<Object, Map<String, Object>>  maps = runner.query(sql, new KeyedHandler<>("id"));
        for(Map.Entry<Object, Map<String, Object>> entrys : maps.entrySet()) {
            System.out.println("====================================");
            for(Map.Entry<String, Object> entry : entrys.getValue().entrySet()) {
                System.out.println(entry.getKey() + "=" + entry.getValue());
            }
            System.out.println();
        }
    }

ScalarHandler:

@Test
    public void testScalarHandler() throws SQLException {
        QueryRunner runner = new QueryRunner(C3P0Utiles.getC3p0());
        String sql = "select count(*) from customer";
        Long count = runner.query(sql, new ScalarHandler<>(1));
        System.out.println(count);
    }

完…