方法
constructUpdateSQL
private static String constructUpdateSQL(String tableName, List<Map<String, Object>> maps, String primaryKeyName) { /*update testMultiUpdateSQL set remark = case id when 1 then '是小明介绍来的' when 2 then '比较有钱' end, realname = case id when 1 then '田馥甄' when 2 then '陈嘉桦' end where id in ( 1, 2 )*/ // 主键值合集 List<String> primaryKeyValues = new ArrayList<String>(); for (Map<String, Object> map : maps) { primaryKeyValues.add(String.valueOf(map.get(primaryKeyName))); } // 列名集合 List<String> keys = new ArrayList<String>(); Map<String, Object> sigleMap = maps.get(0); for (Map.Entry<String, Object> sigleEntry : sigleMap.entrySet()) { String key = sigleEntry.getKey(); if (key.equals(primaryKeyName)) { continue; } else { keys.add(key); } } StringBuilder updateSQL = new StringBuilder(); updateSQL.append("UPDATE " + tableName + " SET "); for (String key : keys) { updateSQL.append(key + " = case " + primaryKeyName + " "); for (String pkv : primaryKeyValues) { for (Map<String, Object> map : maps) { if (map.get(primaryKeyName).equals(pkv)) { String val = String.valueOf(map.get(key)); updateSQL.append("when '" + pkv + "' then '" + val + "'"); break; } } } updateSQL.append("end,"); } if (updateSQL.toString().endsWith(",")) { updateSQL.deleteCharAt(updateSQL.length() - 1); } updateSQL.append(" WHERE " + primaryKeyName + " in "); updateSQL.append("("); for (String primaryKeyValue : primaryKeyValues) { updateSQL.append("'" + primaryKeyValue + "',"); } if (updateSQL.toString().endsWith(",")) { updateSQL.deleteCharAt(updateSQL.length() - 1); } updateSQL.append(")"); return updateSQL.toString(); }
constructInsertSQL
private static String constructInsertSQL(String tableName, List<Map<String, Object>> maps) { if (maps == null || maps.size() == 0) { return ""; } StringBuilder sb1 = new StringBuilder(); sb1.append("INSERT INTO "); sb1.append(tableName); sb1.append("("); Map<String, Object> allField = maps.get(0); // 获取第一个集合,用户获取字段名 for (Map.Entry<String, Object> soe : allField.entrySet()) { String fieldName = soe.getKey(); sb1.append(fieldName + ","); } if (sb1.lastIndexOf(",") == sb1.length() - 1) { sb1.deleteCharAt(sb1.length() - 1); } sb1.append(")"); sb1.append(" VALUES "); StringBuilder sb2 = new StringBuilder(); for (Map<String, Object> map : maps) { StringBuilder t = new StringBuilder(); t.append("("); for (Map.Entry<String, Object> filed : map.entrySet()) { Object fieldValue = filed.getValue(); if (!PubString.isNullOrSpace(fieldValue)) { t.append("'" + fieldValue + "',"); } else { t.append("'',"); } } if (t.lastIndexOf(",") == t.length() - 1) { t.deleteCharAt(t.length() - 1); } t.append("),"); sb2.append(t); } if (sb2.lastIndexOf(",") == sb2.length() - 1) { sb2.deleteCharAt(sb2.length() - 1); } sb1.append(sb2).append(";"); return sb1.toString(); }
测试
Map m1 = new HashMap(); m1.put("id", "7f758e9e4e1b47cf8fad39f022e0a425"); m1.put("remark", "按时"); m1.put("realname", "阿斯顿发多少"); Map m2 = new HashMap(); m2.put("id", "d629146efa804612a64860f1e85d1249"); m2.put("remark", "陈嘉阿斯顿发斯蒂芬桦"); m2.put("realname", "田阿斯顿发送到馥甄"); List<Map<String, Object>> maps = new ArrayList<Map<String, Object>>(); maps.add(m1); maps.add(m2); String updateSQL = constructUpdateSQL("testMultiUpdateSQL", maps, "id"); System.out.println(updateSQL); String insertSQL = constructInsertSQL("testMultiUpdateSQL", maps); System.out.println(insertSQL);
update testMultiUpdateSQL set remark = case id when '7f758e9e4e1b47cf8fad39f022e0a425' then '按时' when 'd629146efa804612a64860f1e85d1249' then '陈嘉阿斯顿发斯蒂芬桦' end, realname = case id when '7f758e9e4e1b47cf8fad39f022e0a425' then '阿斯顿发多少' when 'd629146efa804612a64860f1e85d1249' then '田阿斯顿发送到馥甄' end where id in ( '7f758e9e4e1b47cf8fad39f022e0a425', 'd629146efa804612a64860f1e85d1249' ) ================================================================================================== insert into testMultiUpdateSQL( remark, id, realname ) values ( '按时', '7f758e9e4e1b47cf8fad39f022e0a425', '阿斯顿发多少' ), ( '陈嘉阿斯顿发斯蒂芬桦', 'd629146efa804612a64860f1e85d1249', '田阿斯顿发送到馥甄' );