java攻城狮之路--复习JDBC(利用BeanUtils、JDBC元数据编写通用的查询方法;元数据;Blob;事务;批量处理)

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

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

driver=com.MySQL.jdbc.DriverjdbcUrl=jdbc:mysql://localhost:3306/testuser=rootpassword=123

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

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

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, "BBBB@163.COM");            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);        }    }

4、读取 blob 数据: 1. 使用 getBlob 方法读取到 Blob 对象2. 调用 Blob 的 getBinaryStream() 方法得到输入流。再使用 IO 操作即可.

@Test    public void readBlob(){        Connection connection = null;        PreparedStatement preparedStatement = null;        ResultSet resultSet = null;                try {            connection = JDBCTools.getConnection();            String sql = "SELECT id, name customerName, email, birth, picture "                     + "FROM customers WHERE id = 13";            preparedStatement = connection.prepareStatement(sql);            resultSet = preparedStatement.executeQuery();                        if(resultSet.next()){                int id = resultSet.getInt(1);                String name = resultSet.getString(2);                String email = resultSet.getString(3);                                System.out.println(id + ", " + name  + ", " + email);                Blob picture = resultSet.getBlob(5);                                InputStream in = picture.getBinaryStream();                System.out.println(in.available());                                 OutputStream out = new FileOutputStream("flower.jpg");                                byte [] buffer = new byte[1024];                int len = 0;                while((len = in.read(buffer)) != -1){                    out.write(buffer, 0, len);                }                                in.close();                out.close();            }                    } catch (Exception e) {            e.printStackTrace();        } finally{            JDBCTools.releaseDB(resultSet, preparedStatement, connection);        }    }

5、事务：

关于事务: 1. 如果多个操作, 每个操作使用的是自己的单独的连接, 则无法保证事务.2. 具体步骤: 1). 事务操作开始前, 开始事务:取消Connection 的默认提交行为. connection.setAutoCommit(false). 2). 如果事务的操作都成功,则提交事务: connection.commit(); 3). 回滚事务: 若出现异常, 则在 catch 块中回滚事务:connection.rollback();

    @Test    public void testTransaction() {        Connection connection = null;        try {            connection = JDBCTools.getConnection();            System.out.println(connection.getAutoCommit());            // 开始事务: 取消默认提交.            connection.setAutoCommit(false);            String sql = "UPDATE users SET balance = "                    + "balance - 500 WHERE id = 1";            update(connection, sql);            int i = 10 / 0;            System.out.println(i);            sql = "UPDATE users SET balance = " + "balance + 500 WHERE id = 2";            update(connection, sql);            // 提交事务            connection.commit();        } catch (Exception e) {            e.printStackTrace();            // 回滚事务            try {                connection.rollback();            } catch (SQLException e1) {                e1.printStackTrace();            }        } finally {            JDBCTools.releaseDB(null, null, connection);        }        /*         * try {         *          * //开始事务: 取消默认提交. connection.setAutoCommit(false);         *          * //...         *          * //提交事务 connection.commit(); } catch (Exception e) { //...         *          * //回滚事务 try { connection.rollback(); } catch (SQLException e1) {         * e1.printStackTrace(); } } finally{ JDBCTools.releaseDB(null, null,         * connection); }         */        // DAO dao = new DAO();        //        // String sql = "UPDATE users SET balance = " +        // "balance - 500 WHERE id = 1";        // dao.update(sql);        //        // int i = 10 / 0;        // System.out.println(i);        //        // sql = "UPDATE users SET balance = " +        // "balance + 500 WHERE id = 2";        // dao.update(sql);    }

    @Test    public void testTransactionIsolationRead() {        String sql = "SELECT balance FROM users WHERE id = 1";        Integer balance = getForValue(sql);        System.out.println(balance);     }    // 返回某条记录的某一个字段的值 或 一个统计的值(一共有多少条记录等.)    public <E> E getForValue(String sql, Object... args) {        // 1. 得到结果集: 该结果集应该只有一行, 且只有一列        Connection connection = null;        PreparedStatement preparedStatement = null;        ResultSet resultSet = null;        try {            // 1. 得到结果集            connection = JDBCTools.getConnection();            System.out.println(connection.getTransactionIsolation());             //            connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);            connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);                        preparedStatement = connection.prepareStatement(sql);            for (int i = 0; i < args.length; i++) {                preparedStatement.setObject(i + 1, args[i]);            }            resultSet = preparedStatement.executeQuery();            if (resultSet.next()) {                return (E) resultSet.getObject(1);            }        } catch (Exception ex) {            ex.printStackTrace();        } finally {            JDBCTools.releaseDB(resultSet, preparedStatement, connection);        }        // 2. 取得结果        return null;    }

6、批量处理JDBC语句提高处理速度:

    @Test    public void testBatch(){        Connection connection = null;        PreparedStatement preparedStatement = null;        String sql = null;                try {            connection = JDBCTools.getConnection();            JDBCTools.beginTx(connection);            sql = "INSERT INTO customers VALUES(?,?,?)";            preparedStatement = connection.prepareStatement(sql);            Date date = new Date(new java.util.Date().getTime());                        long begin = System.currentTimeMillis();            for(int i = 0; i < 100000; i++){                preparedStatement.setInt(1, i + 1);                preparedStatement.setString(2, "name_" + i);                preparedStatement.setDate(3, date);                                //"积攒" SQL                 preparedStatement.addBatch();                                //当 "积攒" 到一定程度, 就统一的执行一次. 并且清空先前 "积攒" 的 SQL                if((i + 1) % 300 == 0){                    preparedStatement.executeBatch();                    preparedStatement.clearBatch();                }            }                        //若总条数不是批量数值的整数倍, 则还需要再额外的执行一次.             if(100000 % 300 != 0){                preparedStatement.executeBatch();                preparedStatement.clearBatch();            }                        long end = System.currentTimeMillis();                        System.out.println("Time: " + (end - begin)); //569                        JDBCTools.commit(connection);        } catch (Exception e) {            e.printStackTrace();            JDBCTools.rollback(connection);        } finally{            JDBCTools.releaseDB(null, preparedStatement, connection);        }    }        @Test    public void testBatchWithPreparedStatement(){        Connection connection = null;        PreparedStatement preparedStatement = null;        String sql = null;                try {            connection = JDBCTools.getConnection();            JDBCTools.beginTx(connection);            sql = "INSERT INTO customers VALUES(?,?,?)";            preparedStatement = connection.prepareStatement(sql);            Date date = new Date(new java.util.Date().getTime());                        long begin = System.currentTimeMillis();            for(int i = 0; i < 100000; i++){                preparedStatement.setInt(1, i + 1);                preparedStatement.setString(2, "name_" + i);                preparedStatement.setDate(3, date);                                preparedStatement.executeUpdate();            }            long end = System.currentTimeMillis();                        System.out.println("Time: " + (end - begin)); //9819                        JDBCTools.commit(connection);        } catch (Exception e) {            e.printStackTrace();            JDBCTools.rollback(connection);        } finally{            JDBCTools.releaseDB(null, preparedStatement, connection);        }    }        /**     * 向  Oracle 的 customers 数据表中插入 10 万条记录     * 测试如何插入, 用时最短.      * 1. 使用 Statement.     */    @Test    public void testBatchWithStatement(){        Connection connection = null;        Statement statement = null;        String sql = null;                try {            connection = JDBCTools.getConnection();            JDBCTools.beginTx(connection);                        statement = connection.createStatement();                        long begin = System.currentTimeMillis();            for(int i = 0; i < 100000; i++){                sql = "INSERT INTO customers VALUES(" + (i + 1)                         + ", 'name_" + i + "', '29-6月 -13')";                statement.addBatch(sql);            }            long end = System.currentTimeMillis();                        System.out.println("Time: " + (end - begin)); //39567                        JDBCTools.commit(connection);        } catch (Exception e) {            e.printStackTrace();            JDBCTools.rol