JDBC批量插入数据优化,使用addBatch和executeBatch

2024-01-24 07:29| 来源: 网络整理| 查看: 265

转载请注明出处：http://blog.csdn.net/linglongxin24/article/details/53769957 本文出自【DylanAndroid的博客】

在之前的玩转JDBC打造数据库操作万能工具类JDBCUtil，加入了高效的数据库连接池，利用了参数绑定有效防止SQL注入中其实忽略了一点，那就是SQL的批量插入的问题，如果来个for循环，执行上万次，肯定会很慢，那么，如何去优化呢？

一.用 preparedStatement.addBatch()配合preparedStatement.executeBatch()去批量插入 /** * 执行数据库插入操作 * * @param datas 插入数据表中key为列名和value为列对应的值的Map对象的List集合 * @param tableName 要插入的数据库的表名 * @return 影响的行数 * @throws SQLException SQL异常 */ public static int insertAll(String tableName, List datas) throws SQLException { /**影响的行数**/ int affectRowCount = -1; Connection connection = null; PreparedStatement preparedStatement = null; try { /**从数据库连接池中获取数据库连接**/ connection = DBConnectionPool.getInstance().getConnection(); Map valueMap = datas.get(0); /**获取数据库插入的Map的键值对的值**/ Set keySet = valueMap.keySet(); Iterator iterator = keySet.iterator(); /**要插入的字段sql，其实就是用key拼起来的**/ StringBuilder columnSql = new StringBuilder(); /**要插入的字段值，其实就是？**/ StringBuilder unknownMarkSql = new StringBuilder(); Object[] keys = new Object[valueMap.size()]; int i = 0; while (iterator.hasNext()) { String key = iterator.next(); keys[i] = key; columnSql.append(i == 0 ? "" : ","); columnSql.append(key); unknownMarkSql.append(i == 0 ? "" : ","); unknownMarkSql.append("?"); i++; } /**开始拼插入的sql语句**/ StringBuilder sql = new StringBuilder(); sql.append("INSERT INTO "); sql.append(tableName); sql.append(" ("); sql.append(columnSql); sql.append(" ) VALUES ("); sql.append(unknownMarkSql); sql.append(" )"); /**执行SQL预编译**/ preparedStatement = connection.prepareStatement(sql.toString()); /**设置不自动提交，以便于在出现异常的时候数据库回滚**/ connection.setAutoCommit(false); System.out.println(sql.toString()); for (int j = 0; j < datas.size(); j++) { for (int k = 0; k < keys.length; k++) { preparedStatement.setObject(k + 1, datas.get(j).get(keys[k])); } preparedStatement.addBatch(); } int[] arr = preparedStatement.executeBatch(); connection.commit(); affectRowCount = arr.length; System.out.println("成功了插入了" + affectRowCount + "行"); System.out.println(); } catch (Exception e) { if (connection != null) { connection.rollback(); } e.printStackTrace(); throw e; } finally { if (preparedStatement != null) { preparedStatement.close(); } if (connection != null) { connection.close(); } } return affectRowCount; } 二.实验论证 1.普通的插入方法一次性插入10000条数据所消耗的时间 private static void testAll1() { long start = System.currentTimeMillis(); try { for (int i = 0; i < 10000; i++) { Map map = new HashMap(); map.put("emp_id", 1013); map.put("name", "JDBCUtil测试"); map.put("job", "developer"); map.put("salary", 10000); map.put("hire_date", new java.sql.Date(System.currentTimeMillis())); DBUtil.insert("emp_test3", map); } System.out.println("共耗时" + (System.currentTimeMillis() - start)); } catch (SQLException e) { e.printStackTrace(); } } 打印结果

共耗时44110

2.优化后的方法一次性插入10000条数据所消耗的时间 private static void testAll2() { List datas = new ArrayList(); for (int i = 0; i < 10000; i++) { Map map = new HashMap(); map.put("emp_id", 1013); map.put("name", "JDBCUtil测试"); map.put("job", "developer"); map.put("salary", 10000); map.put("hire_date", new java.sql.Date(System.currentTimeMillis())); datas.add(map); } try { long start = System.currentTimeMillis(); DBUtil.insertAll("emp_test3", datas); System.out.println("共耗时" + (System.currentTimeMillis() - start)); } catch (SQLException e) { e.printStackTrace(); } } 打印结果

共耗时649

3.DBUtil的完整代码 package cn.bluemobi.dylan.util; import com.sun.istack.internal.Nullable; import java.sql.*; import java.util.*; import java.util.regex.Pattern; /** * 数据库JDBC连接工具类 * Created by yuandl on 2016-12-16. */ public class DBUtil { /** * 执行数据库插入操作 * * @param valueMap 插入数据表中key为列名和value为列对应的值的Map对象 * @param tableName 要插入的数据库的表名 * @return 影响的行数 * @throws SQLException SQL异常 */ public static int insert(String tableName, Map valueMap) throws SQLException { /**获取数据库插入的Map的键值对的值**/ Set keySet = valueMap.keySet(); Iterator iterator = keySet.iterator(); /**要插入的字段sql，其实就是用key拼起来的**/ StringBuilder columnSql = new StringBuilder(); /**要插入的字段值，其实就是？**/ StringBuilder unknownMarkSql = new StringBuilder(); Object[] bindArgs = new Object[valueMap.size()]; int i = 0; while (iterator.hasNext()) { String key = iterator.next(); columnSql.append(i == 0 ? "" : ","); columnSql.append(key); unknownMarkSql.append(i == 0 ? "" : ","); unknownMarkSql.append("?"); bindArgs[i] = valueMap.get(key); i++; } /**开始拼插入的sql语句**/ StringBuilder sql = new StringBuilder(); sql.append("INSERT INTO "); sql.append(tableName); sql.append(" ("); sql.append(columnSql); sql.append(" ) VALUES ("); sql.append(unknownMarkSql); sql.append(" )"); return executeUpdate(sql.toString(), bindArgs); } /** * 执行数据库插入操作 * * @param datas 插入数据表中key为列名和value为列对应的值的Map对象的List集合 * @param tableName 要插入的数据库的表名 * @return 影响的行数 * @throws SQLException SQL异常 */ public static int insertAll(String tableName, List datas) throws SQLException { /**影响的行数**/ int affectRowCount = -1; Connection connection = null; PreparedStatement preparedStatement = null; try { /**从数据库连接池中获取数据库连接**/ connection = DBConnectionPool.getInstance().getConnection(); Map valueMap = datas.get(0); /**获取数据库插入的Map的键值对的值**/ Set keySet = valueMap.keySet(); Iterator iterator = keySet.iterator(); /**要插入的字段sql，其实就是用key拼起来的**/ StringBuilder columnSql = new StringBuilder(); /**要插入的字段值，其实就是？**/ StringBuilder unknownMarkSql = new StringBuilder(); Object[] keys = new Object[valueMap.size()]; int i = 0; while (iterator.hasNext()) { String key = iterator.next(); keys[i] = key; columnSql.append(i == 0 ? "" : ","); columnSql.append(key); unknownMarkSql.append(i == 0 ? "" : ","); unknownMarkSql.append("?"); i++; } /**开始拼插入的sql语句**/ StringBuilder sql = new StringBuilder(); sql.append("INSERT INTO "); sql.append(tableName); sql.append(" ("); sql.append(columnSql); sql.append(" ) VALUES ("); sql.append(unknownMarkSql); sql.append(" )"); /**执行SQL预编译**/ preparedStatement = connection.prepareStatement(sql.toString()); /**设置不自动提交，以便于在出现异常的时候数据库回滚**/ connection.setAutoCommit(false); System.out.println(sql.toString()); for (int j = 0; j < datas.size(); j++) { for (int k = 0; k < keys.length; k++) { preparedStatement.setObject(k + 1, datas.get(j).get(keys[k])); } preparedStatement.addBatch(); } int[] arr = preparedStatement.executeBatch(); connection.commit(); affectRowCount = arr.length; System.out.println("成功了插入了" + affectRowCount + "行"); System.out.println(); } catch (Exception e) { if (connection != null) { connection.rollback(); } e.printStackTrace(); throw e; } finally { if (preparedStatement != null) { preparedStatement.close(); } if (connection != null) { connection.close(); } } return affectRowCount; } /** * 执行更新操作 * * @param tableName 表名 * @param valueMap 要更改的值 * @param whereMap 条件 * @return 影响的行数 * @throws SQLException SQL异常 */ public static int update(String tableName, Map valueMap, Map whereMap) throws SQLException { /**获取数据库插入的Map的键值对的值**/ Set keySet = valueMap.keySet(); Iterator iterator = keySet.iterator(); /**开始拼插入的sql语句**/ StringBuilder sql = new StringBuilder(); sql.append("UPDATE "); sql.append(tableName); sql.append(" SET "); /**要更改的的字段sql，其实就是用key拼起来的**/ StringBuilder columnSql = new StringBuilder(); int i = 0; List objects = new ArrayList(); while (iterator.hasNext()) { String key = iterator.next(); columnSql.append(i == 0 ? "" : ","); columnSql.append(key + " = ? "); objects.add(valueMap.get(key)); i++; } sql.append(columnSql); /**更新的条件:要更改的的字段sql，其实就是用key拼起来的**/ StringBuilder whereSql = new StringBuilder(); int j = 0; if (whereMap != null && whereMap.size() > 0) { whereSql.append(" WHERE "); iterator = whereMap.keySet().iterator(); while (iterator.hasNext()) { String key = iterator.next(); whereSql.append(j == 0 ? "" : " AND "); whereSql.append(key + " = ? "); objects.add(whereMap.get(key)); j++; } sql.append(whereSql); } return executeUpdate(sql.toString(), objects.toArray()); } /** * 执行删除操作 * * @param tableName 要删除的表名 * @param whereMap 删除的条件 * @return 影响的行数 * @throws SQLException SQL执行异常 */ public static int delete(String tableName, Map whereMap) throws SQLException { /**准备删除的sql语句**/ StringBuilder sql = new StringBuilder(); sql.append("DELETE FROM "); sql.append(tableName); /**更新的条件:要更改的的字段sql，其实就是用key拼起来的**/ StringBuilder whereSql = new StringBuilder(); Object[] bindArgs = null; if (whereMap != null && whereMap.size() > 0) { bindArgs = new Object[whereMap.size()]; whereSql.append(" WHERE "); /**获取数据库插入的Map的键值对的值**/ Set keySet = whereMap.keySet(); Iterator iterator = keySet.iterator(); int i = 0; while (iterator.hasNext()) { String key = iterator.next(); whereSql.append(i == 0 ? "" : " AND "); whereSql.append(key + " = ? "); bindArgs[i] = whereMap.get(key); i++; } sql.append(whereSql); } return executeUpdate(sql.toString(), bindArgs); } /** * 可以执行新增，修改，删除 * * @param sql sql语句 * @param bindArgs 绑定参数 * @return 影响的行数 * @throws SQLException SQL异常 */ public static int executeUpdate(String sql, Object[] bindArgs) throws SQLException { /**影响的行数**/ int affectRowCount = -1; Connection connection = null; PreparedStatement preparedStatement = null; try { /**从数据库连接池中获取数据库连接**/ connection = DBConnectionPool.getInstance().getConnection(); /**执行SQL预编译**/ preparedStatement = connection.prepareStatement(sql.toString()); /**设置不自动提交，以便于在出现异常的时候数据库回滚**/ connection.setAutoCommit(false); System.out.println(getExecSQL(sql, bindArgs)); if (bindArgs != null) { /**绑定参数设置sql占位符中的值**/ for (int i = 0; i < bindArgs.length; i++) { preparedStatement.setObject(i + 1, bindArgs[i]); } } /**执行sql**/ affectRowCount = preparedStatement.executeUpdate(); connection.commit(); String operate; if (sql.toUpperCase().indexOf("DELETE FROM") != -1) { operate = "删除"; } else if (sql.toUpperCase().indexOf("INSERT INTO") != -1) { operate = "新增"; } else { operate = "修改"; } System.out.println("成功" + operate + "了" + affectRowCount + "行"); System.out.println(); } catch (Exception e) { if (connection != null) { connection.rollback(); } e.printStackTrace(); throw e; } finally { if (preparedStatement != null) { preparedStatement.close(); } if (connection != null) { connection.close(); } } return affectRowCount; } /** * 通过sql查询数据, * 慎用，会有sql注入问题 * * @param sql * @return 查询的数据集合 * @throws SQLException */ public static List query(String sql) throws SQLException { return executeQuery(sql, null); } /** * 执行sql通过 Map限定查询条件查询 * * @param tableName 表名 * @param whereMap where条件 * @return List * @throws SQLException */ public static List query(String tableName, Map whereMap) throws Exception { String whereClause = ""; Object[] whereArgs = null; if (whereMap != null && whereMap.size() > 0) { Iterator iterator = whereMap.keySet().iterator(); whereArgs = new Object[whereMap.size()]; int i = 0; while (iterator.hasNext()) { String key = iterator.next(); whereClause += (i == 0 ? "" : " AND "); whereClause += (key + " = ? "); whereArgs[i] = whereMap.get(key); i++; } } return query(tableName, false, null, whereClause, whereArgs, null, null, null, null); } /** * 执行sql条件参数绑定形式的查询 * * @param tableName 表名 * @param whereClause where条件的sql * @param whereArgs where条件中占位符中的值 * @return List * @throws SQLException */ public static List query(String tableName, String whereClause, String[] whereArgs) throws SQLException { return query(tableName, false, null, whereClause, whereArgs, null, null, null, null); } /** * 执行全部结构的sql查询 * * @param tableName 表名 * @param distinct 去重 * @param columns 要查询的列名 * @param selection where条件 * @param selectionArgs where条件中占位符中的值 * @param groupBy 分组 * @param having 筛选 * @param orderBy 排序 * @param limit 分页 * @return List * @throws SQLException */ public static List query(String tableName, boolean distinct, String[] columns, String selection, Object[] selectionArgs, String groupBy, String having, String orderBy, String limit) throws SQLException { String sql = buildQueryString(distinct, tableName, columns, selection, groupBy, having, orderBy, limit); return executeQuery(sql, selectionArgs); } /** * 执行查询 * * @param sql 要执行的sql语句 * @param bindArgs 绑定的参数 * @return List结果集对象 * @throws SQLException SQL执行异常 */ public static List executeQuery(String sql, Object[] bindArgs) throws SQLException { List datas = new ArrayList(); Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { /**获取数据库连接池中的连接**/ connection = DBConnectionPool.getInstance().getConnection(); preparedStatement = connection.prepareStatement(sql); if (bindArgs != null) { /**设置sql占位符中的值**/ for (int i = 0; i < bindArgs.length; i++) { preparedStatement.setObject(i + 1, bindArgs[i]); } } System.out.println(getExecSQL(sql, bindArgs)); /**执行sql语句，获取结果集**/ resultSet = preparedStatement.executeQuery(); getDatas(resultSet); System.out.println(); } catch (Exception e) { e.printStackTrace(); throw e; } finally { if (resultSet != null) { resultSet.close(); } if (preparedStatement != null) { preparedStatement.close(); } if (connection != null) { connection.close(); } } return datas; } /** * 将结果集对象封装成List 对象 * * @param resultSet 结果多想 * @return 结果的封装 * @throws SQLException */ private static List getDatas(ResultSet resultSet) throws SQLException { List datas = new ArrayList(); /**获取结果集的数据结构对象**/ ResultSetMetaData metaData = resultSet.getMetaData(); while (resultSet.next()) { Map rowMap = new HashMap(); for (int i = 1; i

【本文地址】

公司简介

联系我们