java攻城狮之路--复习JDBC(利用BeanUtils、以及JDBC元数据编写通用的查询方法)

浏览数：30 / 时间：2015年06月12日

1、利用BeanUtils的前提得要加入以下两个jar包：

commons-beanutils-1.8.0.jar

commons-logging-1.1.1.jar

package com.shellway.jdbcDAO;

import java.util.List;
import org.junit.Test;

public class TestDAO {
    DAO dao = new DAO();

    @Test
    public void testUpdate() throws Exception {
        String sql = "update examstudent set grade=? where flow_id=12345";
        dao.update(sql, 88);
    }

    @Test
    public void test() throws Exception {

        String sql = "select flow_id flowID,type,id_card idCard, "
                + "exam_card examCard,student_name studentName,location,grade "
                + "from examstudent where flow_id = ? ";
        Student stu = dao.get(Student.class, sql, 12345);
        System.out.println(stu);
    }

    @Test
    public void testGetSome() throws Exception {
        String sql = "select flow_id flowID,type,id_card idCard, "
                + "exam_card examCard,student_name studentName,location,grade "
                + "from examstudent";
        List<Student> students = dao.getForList(Student.class, sql);
        System.out.println(students);
    }

    @Test
    public void testGetForValue() throws Exception {
        String sql = "select grade from examstudent where flow_id = ? ";
        Object obj = dao.getforvalue(sql, 123456);
        System.out.println(obj);
    }
}

测试类

package com.shellway.jdbcDAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.BeanUtils;

public class DAO {

    public void update(String sql, Object... args) throws Exception {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCTools.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCTools.release(null, ps, conn);
        }
    }

    // 获取一个对象通用的方法
    public <T> T get(Class<T> clazz, String sql, Object... args)
            throws Exception {
        T entity = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = JDBCTools.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            Map<String, Object> map = null;

            while (rs.next()) {
                map = new HashMap<String, Object>();
                for (int i = 0; i < rsmd.getColumnCount(); i++) {
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    Object columnValue = rs.getObject(i + 1);
                    map.put(columnLabel, columnValue);
                }
                if (map.size() > 0) {
                    entity = clazz.newInstance();
                    for (Map.Entry<String, Object> entry : map.entrySet()) {
                        String label = entry.getKey();
                        Object value = entry.getValue();
                        BeanUtils.setProperty(entity, label, value);
                    }
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCTools.release(rs, ps, conn);
        }
        return entity;
    }

    // 获取一组对象通用的方法
    public <T> List<T> getForList(Class<T> clazz, String sql, Object... args)
            throws Exception {
        T entity = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<T> result = new ArrayList<T>();

        try {
            conn = JDBCTools.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();

            ResultSetMetaData rsmd = rs.getMetaData();
            List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
            Map<String, Object> map = new HashMap<String, Object>();

            while (rs.next()) {
                for (int i = 0; i < rsmd.getColumnCount(); i++) {
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    Object columnValue = rs.getObject(i + 1);
                    map.put(columnLabel, columnValue);
                }
                list.add(map);
                if (list.size() > 0) {
                    entity = clazz.newInstance();
                    for (Map<String, Object> ll : list) {
                        for (Map.Entry<String, Object> entry : ll.entrySet()) {
                            String label = entry.getKey();
                            Object value = entry.getValue();
                            BeanUtils.setProperty(entity, label, value);
                        }
                    }
                    result.add(entity);
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCTools.release(rs, ps, conn);
        }
        return result;
    }

    // 获取一个对象中一列的值通用的方法
    public <E> E getforvalue(String sql, Object... args) throws Exception {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCTools.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            if (rs.next()) {
                return (E) rs.getObject(1);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCTools.release(rs, ps, conn);
        }
        return null;
    }
}

通用的DAO.java

package com.shellway.jdbcDAO;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import java.sql.PreparedStatement;

public class JDBCTools {

    public static <T> T get(Class<T> clazz, String sql, Object... args)
            throws Exception {
        T entity = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = JDBCTools.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();

            ResultSetMetaData rsmd = rs.getMetaData();
            Map<String, Object> map = new HashMap<String, Object>();
            while (rs.next()) {
                for (int i = 0; i < rsmd.getColumnCount(); i++) {
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    Object columnValue = rs.getObject(i + 1);
                    // Object columnValue = rs.getObject(columnLabel);
                    map.put(columnLabel, columnValue);
                }
            }
            if (map.size() > 0) {
                entity = clazz.newInstance();
                for (Map.Entry<String, Object> values : map.entrySet()) {
                    String key = values.getKey();
                    Object value = values.getValue();
                    ReflectionUtils.setFieldValue(entity, key, value);
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCTools.release(rs, ps, conn);
        }
        return entity;
    }

    public static void update(String sql, Object... args) throws Exception {
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = JDBCTools.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            System.out.println(sql);
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCTools.release(null, ps, conn);
        }
    }

    public static Connection getConnection() throws Exception {

        Properties proper = new Properties();
        InputStream in = JDBCTools.class.getClassLoader().getResourceAsStream(
                "jdbc.properties");
        proper.load(in);
        String driverClass = proper.getProperty("driver");
        String jdbcUrl = proper.getProperty("jdbcUrl");
        String user = proper.getProperty("user");
        String password = proper.getProperty("password");

        Class.forName(driverClass);
        Connection connection = DriverManager.getConnection(jdbcUrl, user,
                password);
        return connection;
    }

    public static void release(ResultSet rs, Statement state, Connection conn)
            throws Exception {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (state != null) {
            try {
                state.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

}

JDBCTools工具类

driver=com.mysql.jdbc.Driver
jdbcUrl=jdbc:mysql://localhost:3306/test
user=root
password=123

配置文件jdbc.properties

package com.shellway.jdbc;

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;
    }
}

反射工具类：ReflectionUtils.java 也可通过它代替BeanUtils的setProperty()给对象赋值然后返回该对象

2、获取数据库中的一些元数据：

    @Test
    public void testDatebaseMetaData() throws Exception {
        Connection conn = null;
        ResultSet rs = null;
        try {
            conn = JDBCTools.getConnection();
            DatabaseMetaData dbmd = conn.getMetaData();
            // 得到数据库的一些基本信息
            int version = dbmd.getDatabaseMajorVersion();
            System.out.println(version);
            String user = dbmd.getUserName();
            System.out.println(user);
            rs = dbmd.getCatalogs();
            while (rs.next()) {
                String str = rs.getString(1);
                System.out.println(str);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCTools.release(rs, null, conn);
        }
    }

通过DatabaseMetaData dbmd = conn.getMetaData();获取

3、获取插入记录的主键值，在加入购物车时用到

    @Test
    public void testPrimeryKey() throws Exception {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "insert into customer(name,email,birth) values(?,?,?)  ";

        try {
            conn = JDBCTools.getConnection();
            ps = conn.prepareStatement(sql, ps.RETURN_GENERATED_KEYS);
            ps.setString(1, "BBBB");
            ps.setString(2, "[email protected]");
            ps.setDate(3, new Date(new java.util.Date().getTime()));
            ps.executeUpdate();
            rs = ps.getGeneratedKeys();
            if (rs.next()) {
                Object obj = rs.getObject(1);
                System.out.println(obj);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCTools.release(null, ps, conn);
        }
    }