Java -- JDBC 学习--使用 DBUtils

Apache—DBUtils简介

commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库，它是对JDBC的简单封装，学习成本极低，并且使用dbutils能极大简化jdbc编码的工作量，同时也不会影响程序的性能。
API介绍：
org.apache.commons.dbutils.QueryRunner
org.apache.commons.dbutils.ResultSetHandler
工具类
org.apache.commons.dbutils.DbUtils

如下利用DbUtils进行常用操作的封装：

DAO接口：

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * 访问数据的 DAO 接口. 
 * 里边定义好访问数据表的各种方法
 * @param T: DAO 处理的实体类的类型. 
 */
public interface DAO<T> {

    /**
     * 批量处理的方法
     * @param connection
     * @param sql
     * @param args: 填充占位符的 Object [] 类型的可变参数.
     * @throws SQLException 
     */  
    void batch(Connection connection, 
            String sql, Object [] ... args) throws SQLException;
    
    /**
     * 返回具体的一个值, 例如总人数, 平均工资, 某一个人的 email 等.
     * @param connection
     * @param sql
     * @param args
     * @return
     * @throws SQLException 
     */
    <E> E getForValue(Connection connection,
            String sql, Object ... args) throws SQLException;
    
    /**
     * 返回 T 的一个集合
     * @param connection
     * @param sql
     * @param args
     * @return
     * @throws SQLException 
     */
    List<T> getForList(Connection connection,
            String sql, Object ... args) throws SQLException;
    
    /**
     * 返回一个 T 的对象
     * @param connection
     * @param sql
     * @param args
     * @return
     * @throws SQLException 
     */
    T get(Connection connection, String sql, 
            Object ... args) throws SQLException;
    
    /**
     * INSRET, UPDATE, DELETE
     * @param connection: 数据库连接
     * @param sql: SQL 语句
     * @param args: 填充占位符的可变参数.
     * @throws SQLException 
     */
    void update(Connection connection, String sql, 
            Object ... args) throws SQLException;

}

实现DAO类：

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

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.ScalarHandler;

/**
 * 使用 QueryRunner 提供其具体的实现
 * @param <T>: 子类需传入的泛型类型. 
 */
public class JdbcDaoImpl<T> implements DAO<T> {

    private QueryRunner queryRunner = null;
    private Class<T> type;
    
    public JdbcDaoImpl() {
        queryRunner = new QueryRunner();
        type = ReflectionUtils.getSuperGenericType(getClass());
    }
    
    @Override
    public void batch(Connection connection, String sql, Object[]... args) throws SQLException {
        queryRunner.batch(connection, sql, args);
    }

    @Override
    public <E> E getForValue(Connection connection, String sql, Object... args) throws SQLException {
        return (E) queryRunner.query(connection, sql, new ScalarHandler(), args);
    }

    @Override 
    public List<T> getForList(Connection connection, String sql, Object... args) 
            throws SQLException {
        return queryRunner.query(connection, sql, 
                new BeanListHandler<>(type), args);
    }

    @Override
    public T get(Connection connection, String sql, Object... args) throws SQLException { 
        return queryRunner.query(connection, sql, 
                new BeanHandler<>(type), args);
    }

    @Override
    public void update(Connection connection, String sql, Object... args) throws SQLException {
        queryRunner.update(connection, sql, args);
    }
    
}

 

public class CustomerDao 
    extends JdbcDaoImpl<Customer>{
    
}

Customer.java对象类：

import java.sql.Date;

public class Customer {

    private Integer id;
    private String name;
    private String email;
    private Date birth;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getCustomerName() {
        return name;
    }

    public void setCustomerName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Date getBirth() {
        return birth;
    }

    public void setBirth(Date birth) {
        this.birth = birth;
    }

    @Override
    public String toString() {
        return "Customer [id=" + id + ", name=" + name + ", email=" + email
                + ", birth=" + birth + "]";
    }

    public Customer(Integer id, String name, String email, Date birth) {
        super();
        this.id = id;
        this.name = name;
        this.email = email;
        this.birth = birth;
    }
    
    public Customer() {
        // TODO Auto-generated constructor stub
    }

}

调用示例：

@Test
    public void testGet() {
        Connection connection = null;
        
        try {
            connection = JDBCTools.getConnection();
            String sql = "SELECT id, name customerName, " +
                    "email, birth FROM customers " +
                    "WHERE id = ?";
            Customer customer = customerDao.get(connection, sql, 5);
            System.out.println(customer); 
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            JDBCTools.releaseDB(null, null, connection);
        }
    }

相关反射类：

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;

/**
 * 反射的 Utils 函数集合
 * 提供访问私有变量, 获取泛型类型 Class, 提取集合中元素属性等 Utils 函数
 * @author Administrator
 *
 */
public class ReflectionUtils {

    
    /**
     * 通过反射, 获得定义 Class 时声明的父类的泛型参数的类型
     * 如: public EmployeeDao extends BaseDao<Employee, String>
     * @param clazz
     * @param index
     * @return
     */
    @SuppressWarnings("unchecked")
    public static Class getSuperClassGenricType(Class clazz, int index){
        Type genType = clazz.getGenericSuperclass();
        
        if(!(genType instanceof ParameterizedType)){
            return Object.class;
        }
        
        Type [] params = ((ParameterizedType)genType).getActualTypeArguments();
        
        if(index >= params.length || index < 0){
            return Object.class;
        }
        
        if(!(params[index] instanceof Class)){
            return Object.class;
        }
        
        return (Class) params[index];
    }
    
    /**
     * 通过反射, 获得 Class 定义中声明的父类的泛型参数类型
     * 如: public EmployeeDao extends BaseDao<Employee, String>
     * @param <T>
     * @param clazz
     * @return
     */
    @SuppressWarnings("unchecked")
    public static<T> Class<T> getSuperGenericType(Class clazz){
        return getSuperClassGenricType(clazz, 0);
    }
    
    /**
     * 循环向上转型, 获取对象的 DeclaredMethod
     * @param object
     * @param methodName
     * @param parameterTypes
     * @return
     */
    public static Method getDeclaredMethod(Object object, String methodName, Class<?>[] parameterTypes){
        
        for(Class<?> superClass = object.getClass(); superClass != Object.class; superClass = superClass.getSuperclass()){
            try {
                //superClass.getMethod(methodName, parameterTypes);
                return superClass.getDeclaredMethod(methodName, parameterTypes);
            } catch (NoSuchMethodException e) {
                //Method 不在当前类定义, 继续向上转型
            }
            //..
        }
        
        return null;
    }
    
    /**
     * 使 filed 变为可访问
     * @param field
     */
    public static void makeAccessible(Field field){
        if(!Modifier.isPublic(field.getModifiers())){
            field.setAccessible(true);
        }
    }
    
    /**
     * 循环向上转型, 获取对象的 DeclaredField
     * @param object
     * @param filedName
     * @return
     */
    public static Field getDeclaredField(Object object, String filedName){
        
        for(Class<?> superClass = object.getClass(); superClass != Object.class; superClass = superClass.getSuperclass()){
            try {
                return superClass.getDeclaredField(filedName);
            } catch (NoSuchFieldException e) {
                //Field 不在当前类定义, 继续向上转型
            }
        }
        return null;
    }
    
    /**
     * 直接调用对象方法, 而忽略修饰符(private, protected)
     * @param object
     * @param methodName
     * @param parameterTypes
     * @param parameters
     * @return
     * @throws InvocationTargetException 
     * @throws IllegalArgumentException 
     */
    public static Object invokeMethod(Object object, String methodName, Class<?> [] parameterTypes,
            Object [] parameters) throws InvocationTargetException{
        
        Method method = getDeclaredMethod(object, methodName, parameterTypes);
        
        if(method == null){
            throw new IllegalArgumentException("Could not find method [" + methodName + "] on target [" + object + "]");
        }
        
        method.setAccessible(true);
        
        try {
            return method.invoke(object, parameters);
        } catch(IllegalAccessException e) {
            System.out.println("不可能抛出的异常");
        } 
        
        return null;
    }
    
    /**
     * 直接设置对象属性值, 忽略 private/protected 修饰符, 也不经过 setter
     * @param object
     * @param fieldName
     * @param value
     */
    public static void setFieldValue(Object object, String fieldName, Object value){
        Field field = getDeclaredField(object, fieldName);
        
        if (field == null)
            throw new IllegalArgumentException("Could not find field [" + fieldName + "] on target [" + object + "]");
        
        makeAccessible(field);
        
        try {
            field.set(object, value);
        } catch (IllegalAccessException e) {
            System.out.println("不可能抛出的异常");
        }
    }
    
    /**
     * 直接读取对象的属性值, 忽略 private/protected 修饰符, 也不经过 getter
     * @param object
     * @param fieldName
     * @return
     */
    public static Object getFieldValue(Object object, String fieldName){
        Field field = getDeclaredField(object, fieldName);
        
        if (field == null)
            throw new IllegalArgumentException("Could not find field [" + fieldName + "] on target [" + object + "]");
        
        makeAccessible(field);
        
        Object result = null;
        
        try {
            result = field.get(object);
        } catch (IllegalAccessException e) {
            System.out.println("不可能抛出的异常");
        }
        
        return result;
    }
}