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.Driver jdbcUrl=jdbc:mysql://localhost:3306/test user=root password=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、事务:
• 事务:指构成单个逻辑工作单元的操作集合
• 事务处理:保证所有事务都作为一个工作单元来执行,即使出现了故障,都不能改变这种执行方式。当在一个事务中执行多个操作时,要么所有的事务都被提交(commit),要么整个事务回滚(rollback)到最初状态
• 当一个连接对象被创建时,默认情况下是自动提交事务:每次执行一个 SQL 语句时,如果执行成功,就会向数据库自动提交,而不能回滚
• 为了让多个 SQL 语句作为一个事务执行:
––调用 Connection 对象的 setAutoCommit(false); 以取消自动提交事务
––在所有的 SQL 语句都成功执行后,调用 commit(); 方法提交事务
––在出现异常时,调用 rollback(); 方法回滚事务
––若此时 Connection 没有被关闭, 则需要恢复其自动提交状态。
数据库的隔离级别:
• 对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制,
就会导致各种并发问题:
–––脏读: 对于两个事物 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段. 之后, 若 T2 回滚,
T1读取的内容就是临时且无效的.
–––不可重复读: 对于两个事物 T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段.
之后,T1再次读取同一个字段, 值就不同了.
–––幻读: 对于两个事物 T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行.
之后, 如果 T1 再次读取同一个表, 就会多出几行.
• 数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响,
避免各种并发问题.
• 一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的
干扰程度,隔离级别越高, 数据一致性就越好, 但并发性越弱.
• Oracle 支持的 2 种事务隔离级别:READ COMMITED, SERIALIZABLE,Oracle 默认的事务隔离级别为:
READ COMMITED。
• Mysql 支持 4 中事务隔离级别. Mysql 默认的事务隔离级别为: REPEATABLE READ。
在 MySql 中设置隔离级别:
• 每启动一个 mysql 程序, 就会获得一个单独的数据库连接. 每个数据库连接都有一个全局变量
@@tx_isolation, 表示当前的事务隔离级别. MySQL 默认的隔离级别为 Repeatable Read
• 查看当前的隔离级别: SELECT @@tx_isolation;
• 设置当前 mySQL 连接的隔离级别:
–set transaction isolation level read committed;
• 设置数据库系统的全局的隔离级别:
–set global transaction isolation level read committed;
关于事务:
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语句提高处理速度:
• 当需要成批插入或者更新记录时。可以采用Java的批量更新机制,
这一机制允许多条语句一次性提交给数据库批量处理。通常情况下比单独提交处理更有效率
• JDBC的批量处理语句包括下面两个方法:
–addBatch(String):添加需要批量处理的SQL语句或是参数;
–executeBatch(): 执行批量处理语句;
• 通常我们会遇到两种批量执行SQL语句的情况:
– 多条SQL语句的批量处理;
– 一个SQL语句的批量传参;
@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.rollback(connection); } finally{ JDBCTools.releaseDB(null, statement, connection); } }