时间：2021-07-01 10:21:17 帮助过：16人阅读
二.实验论证
1.普通的插入方法一次性插入10000条数据所消耗的时间
1 private static void testAll1() { 2 long start = System.currentTimeMillis(); 3 try { 4 for (int i = 0; i < 10000; i++) { 5 Map<String, Object> map = new HashMap<>(); 6 map.put("emp_id", 1013); 7 map.put("name", "JDBCUtil测试"); 8 map.put("job", "developer"); 9 map.put("salary", 10000); 10 map.put("hire_date", new java.sql.Date(System.currentTimeMillis())); 11 DBUtil.insert("emp_test3", map); 12 } 13 System.out.println("共耗时" + (System.currentTimeMillis() - start)); 14 } catch (SQLException e) { 15 e.printStackTrace(); 16 } 17 }
打印结果 共耗时44110
2.优化后的方法一次性插入10000条数据所消耗的时间
1 private static void testAll2() { 2 List<Map<String, Object>> datas = new ArrayList<>(); 3 for (int i = 0; i < 10000; i++) { 4 Map<String, Object> map = new HashMap<>(); 5 map.put("emp_id", 1013); 6 map.put("name", "JDBCUtil测试"); 7 map.put("job", "developer"); 8 map.put("salary", 10000); 9 map.put("hire_date", new java.sql.Date(System.currentTimeMillis())); 10 datas.add(map); 11 } 12 try { 13 long start = System.currentTimeMillis(); 14 DBUtil.insertAll("emp_test3", datas); 15 System.out.println("共耗时" + (System.currentTimeMillis() - start)); 16 } catch (SQLException e) { 17 e.printStackTrace(); 18 } 19 }
打印结果 共耗时649
3.DBUtil的完整代码
1 import com.sun.istack.internal.Nullable; 2 import java.sql.*; 3 import java.util.*; 4 import java.util.regex.Pattern; 5 6 /** 7 * 数据库JDBC连接工具类 8 */ 9 public class DBUtil { 10 11 /** 12 * 执行数据库插入操作 13 * 14 * @param valueMap 插入数据表中key为列名和value为列对应的值的Map对象 15 * @param tableName 要插入的数据库的表名 16 * @return 影响的行数 17 * @throws SQLException SQL异常 18 */ 19 public static int insert(String tableName, Map<String, Object> valueMap) throws SQLException { 20 21 /**获取数据库插入的Map的键值对的值**/ 22 Set<String> keySet = valueMap.keySet(); 23 Iterator<String> iterator = keySet.iterator(); 24 /**要插入的字段sql，其实就是用key拼起来的**/ 25 StringBuilder columnSql = new StringBuilder(); 26 /**要插入的字段值，其实就是？**/ 27 StringBuilder unknownMarkSql = new StringBuilder(); 28 Object[] bindArgs = new Object[valueMap.size()]; 29 int i = 0; 30 while (iterator.hasNext()) { 31 String key = iterator.next(); 32 columnSql.append(i == 0 ? "" : ","); 33 columnSql.append(key); 34 35 unknownMarkSql.append(i == 0 ? "" : ","); 36 unknownMarkSql.append("?"); 37 bindArgs[i] = valueMap.get(key); 38 i++; 39 } 40 /**开始拼插入的sql语句**/ 41 StringBuilder sql = new StringBuilder(); 42 sql.append("INSERT INTO "); 43 sql.append(tableName); 44 sql.append(" ("); 45 sql.append(columnSql); 46 sql.append(" ) VALUES ("); 47 sql.append(unknownMarkSql); 48 sql.append(" )"); 49 return executeUpdate(sql.toString(), bindArgs); 50 } 51 52 /** 53 * 执行数据库插入操作 54 * 55 * @param datas 插入数据表中key为列名和value为列对应的值的Map对象的List集合 56 * @param tableName 要插入的数据库的表名 57 * @return 影响的行数 58 * @throws SQLException SQL异常 59 */ 60 public static int insertAll(String tableName, List<Map<String, Object>> datas) throws SQLException { 61 /**影响的行数**/ 62 int affectRowCount = -1; 63 Connection connection = null; 64 PreparedStatement preparedStatement = null; 65 try { 66 /**从数据库连接池中获取数据库连接**/ 67 connection = DBConnectionPool.getInstance().getConnection(); 68 69 70 Map<String, Object> valueMap = datas.get(0); 71 /**获取数据库插入的Map的键值对的值**/ 72 Set<String> keySet = valueMap.keySet(); 73 Iterator<String> iterator = keySet.iterator(); 74 /**要插入的字段sql，其实就是用key拼起来的**/ 75 StringBuilder columnSql = new StringBuilder(); 76 /**要插入的字段值，其实就是？**/ 77 StringBuilder unknownMarkSql = new StringBuilder(); 78 Object[] keys = new Object[valueMap.size()]; 79 int i = 0; 80 while (iterator.hasNext()) { 81 String key = iterator.next(); 82 keys[i] = key; 83 columnSql.append(i == 0 ? "" : ","); 84 columnSql.append(key); 85 86 unknownMarkSql.append(i == 0 ? "" : ","); 87 unknownMarkSql.append("?"); 88 i++; 89 } 90 /**开始拼插入的sql语句**/ 91 StringBuilder sql = new StringBuilder(); 92 sql.append("INSERT INTO "); 93 sql.append(tableName); 94 sql.append(" ("); 95 sql.append(columnSql); 96 sql.append(" ) VALUES ("); 97 sql.append(unknownMarkSql); 98 sql.append(" )"); 99 100 /**执行SQL预编译**/ 101 preparedStatement = connection.prepareStatement(sql.toString()); 102 /**设置不自动提交，以便于在出现异常的时候数据库回滚**/ 103 connection.setAutoCommit(false); 104 System.out.println(sql.toString()); 105 for (int j = 0; j < datas.size(); j++) { 106 for (int k = 0; k < keys.length; k++) { 107 preparedStatement.setObject(k + 1, datas.get(j).get(keys[k])); 108 } 109 preparedStatement.addBatch(); 110 } 111 int[] arr = preparedStatement.executeBatch(); 112 connection.commit(); 113 affectRowCount = arr.length; 114 System.out.println("成功了插入了" + affectRowCount + "行"); 115 System.out.println(); 116 } catch (Exception e) { 117 if (connection != null) { 118 connection.rollback(); 119 } 120 e.printStackTrace(); 121 throw e; 122 } finally { 123 if (preparedStatement != null) { 124 preparedStatement.close(); 125 } 126 if (connection != null) { 127 connection.close(); 128 } 129 } 130 return affectRowCount; 131 } 132 133 /** 134 * 执行更新操作 135 * 136 * @param tableName 表名 137 * @param valueMap 要更改的值 138 * @param whereMap 条件 139 * @return 影响的行数 140 * @throws SQLException SQL异常 141 */ 142 public static int update(String tableName, Map<String, Object> valueMap, Map<String, Object> whereMap) throws SQLException { 143 /**获取数据库插入的Map的键值对的值**/ 144 Set<String> keySet = valueMap.keySet(); 145 Iterator<String> iterator = keySet.iterator(); 146 /**开始拼插入的sql语句**/ 147 StringBuilder sql = new StringBuilder(); 148 sql.append("UPDATE "); 149 sql.append(tableName); 150 sql.append(" SET "); 151 152 /**要更改的的字段sql，其实就是用key拼起来的**/ 153 StringBuilder columnSql = new StringBuilder(); 154 int i = 0; 155 List<Object> objects = new ArrayList<>(); 156 while (iterator.hasNext()) { 157 String key = iterator.next(); 158 columnSql.append(i == 0 ? "" : ","); 159 columnSql.append(key + " = ? "); 160 objects.add(valueMap.get(key)); 161 i++; 162 } 163 sql.append(columnSql); 164 165 /**更新的条件:要更改的的字段sql，其实就是用key拼起来的**/ 166 StringBuilder whereSql = new StringBuilder(); 167 int j = 0; 168 if (whereMap != null && whereMap.size() > 0) { 169 whereSql.append(" WHERE "); 170 iterator = whereMap.keySet().iterator(); 171 while (iterator.hasNext()) { 172 String key = iterator.next(); 173 whereSql.append(j == 0 ? "" : " AND "); 174 whereSql.append(key + " = ? "); 175 objects.add(whereMap.get(key)); 176 j++; 177 } 178 sql.append(whereSql); 179 } 180 return executeUpdate(sql.toString(), objects.toArray()); 181 } 182 183 /** 184 * 执行删除操作 185 * 186 * @param tableName 要删除的表名 187 * @param whereMap 删除的条件 188 * @return 影响的行数 189 * @throws SQLException SQL执行异常 190 */ 191 public static int delete(String tableName, Map<String, Object> whereMap) throws SQLException { 192 /**准备删除的sql语句**/ 193 StringBuilder sql = new StringBuilder(); 194 sql.append("DELETE FROM "); 195 sql.append(tableName); 196 197 /**更新的条件:要更改的的字段sql，其实就是用key拼起来的**/ 198 StringBuilder whereSql = new StringBuilder(); 199 Object[] bindArgs = null; 200 if (whereMap != null && whereMap.size() > 0) { 201 bindArgs = new Object[whereMap.size()]; 202 whereSql.append(" WHERE "); 203 /**获取数据库插入的Map的键值对的值**/ 204 Set<String> keySet = whereMap.keySet(); 205 Iterator<String> iterator = keySet.iterator(); 206 int i = 0; 207 while (iterator.hasNext()) { 208 String key = iterator.next(); 209 whereSql.append(i == 0 ? "" : " AND "); 210 whereSql.append(key + " = ? "); 211 bindArgs[i] = whereMap.get(key); 212 i++; 213 } 214 sql.append(whereSql); 215 } 216 return executeUpdate(sql.toString(), bindArgs); 217 } 218 219 /** 220 * 可以执行新增，修改，删除 221 * 222 * @param sql sql语句 223 * @param bindArgs 绑定参数 224 * @return 影响的行数 225 * @throws SQLException SQL异常 226 */ 227 public static int executeUpdate(String sql, Object[] bindArgs) throws SQLException { 228 /**影响的行数**/ 229 int affectRowCount = -1; 230 Connection connection = null; 231 PreparedStatement preparedStatement = null; 232 try { 233 /**从数据库连接池中获取数据库连接**/ 234 connection = DBConnectionPool.getInstance().getConnection(); 235 /**执行SQL预编译**/ 236 preparedStatement = connection.prepareStatement(sql.toString()); 237 /**设置不自动提交，以便于在出现异常的时候数据库回滚**/ 238 connection.setAutoCommit(false); 239 System.out.println(getExecSQL(sql, bindArgs)); 240 if (bindArgs != null) { 241 /**绑定参数设置sql占位符中的值**/ 242 for (int i = 0; i < bindArgs.length; i++) { 243 preparedStatement.setObject(i + 1, bindArgs[i]); 244 } 245 } 246 /**执行sql**/ 247 affectRowCount = preparedStatement.executeUpdate(); 248 connection.commit(); 249 String operate; 250 if (sql.toUpperCase().indexOf("DELETE FROM") != -1) { 251 operate = "删除"; 252 } else if (sql.toUpperCase().indexOf("INSERT INTO") != -1) { 253 operate = "新增"; 254 } else { 255 operate = "修改"; 256 } 257 System.out.println("成功" + operate + "了" + affectRowCount + "行"); 258 System.out.println(); 259 } catch (Exception e) { 260 if (connection != null) { 261 connection.rollback(); 262 } 263 e.printStackTrace(); 264 throw e; 265 } finally { 266 if (preparedStatement != null) { 267 preparedStatement.close(); 268 } 269 if (connection != null) { 270 connection.close(); 271 } 272 } 273 return affectRowCount; 274 } 275 276 /** 277 * 通过sql查询数据, 278 * 慎用，会有sql注入问题 279 * 280 * @param sql 281 * @return 查询的数据集合 282 * @throws SQLException 283 */ 284 public static List<Map<String, Object>> query(String sql) throws SQLException { 285 return executeQuery(sql, null); 286 } 287 288 /** 289 * 执行sql通过 Map<String, Object>限定查询条件查询 290 * 291 * @param tableName 表名 292 * @param whereMap where条件 293 * @return List<Map<String, Object>> 294 * @throws SQLException 295 */ 296 public static List<Map<String, Object>> query(String tableName, 297 Map<String, Object> whereMap) throws Exception { 298 String whereClause = ""; 299 Object[] whereArgs = null; 300 if (whereMap != null && whereMap.size() > 0) { 301 Iterator<String> iterator = whereMap.keySet().iterator(); 302 whereArgs = new Object[whereMap.size()]; 303 int i = 0; 304 while (iterator.hasNext()) { 305 String key = iterator.next(); 306 whereClause += (i == 0 ? "" : " AND "); 307 whereClause += (key + " = ? "); 308 whereArgs[i] = whereMap.get(key); 309 i++; 310 } 311 } 312 return query(tableName, false, null, whereClause, whereArgs, null, null, null, null); 313 } 314 315 /** 316 * 执行sql条件参数绑定形式的查询 317 * 318 * @param tableName 表名 319 * @param whereClause where条件的sql 320 * @param whereArgs where条件中占位符中的值 321 * @return List<Map<String, Object>> 322 * @throws SQLException 323 */ 324 public static List<Map<String, Object>> query(String tableName, 325 String whereClause, 326 String[] whereArgs) throws SQLException { 327 return query(tableName, false, null, whereClause, whereArgs, null, null, null, null); 328 } 329 330 /** 331 * 执行全部结构的sql查询 332 * 333 * @param tableName 表名 334 * @param distinct 去重 335 * @param columns 要查询的列名 336 * @param selection where条件 337 * @param selectionArgs where条件中占位符中的值 338 * @param groupBy 分组 339 * @param having 筛选 340 * @param orderBy 排序 341 * @param limit 分页 342 * @return List<Map<String, Object>> 343 * @throws SQLException 344 */ 345 public static List<Map<String, Object>> query(String tableName, 346