方法
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', '田阿斯顿发送到馥甄' );