insert语句拼接,需要注意数据类型,所以使用instanceof类型判断
/**
*
* @Author ljy
* @Description 拼接insert语句
* @Date 16:17 2024/3/20
* @param tableName
* @param data
* @return java.lang.String
**/
public String generateCreateSql(String tableName, String data){
JSONObject jsonObject = JSON.parseObject(data);
List<String> keys = new ArrayList<>();
List<String> values = new ArrayList<>();
for (String key : jsonObject.keySet()) {
Object value = jsonObject.get(key);
if(null==value){
continue;
}
keys.add(key);
// 根据值的类型进行处理,这里假设值都为字符串类型,实际情况可能需要根据值的类型进行适当转换
if (value instanceof String) {
values.add("'" + value + "'");
} else {
values.add(value.toString());
}
}
String columns = String.join(",", keys);
String columnValues = String.join(",", values);
return String.format("INSERT INTO %s (%s) VALUES (%s)", tableName, columns, columnValues);
}
update语句拼接,需要注意的是大部分表的主键都是id,但也有例外,有些关联表的主键可能是组合id,有些表主键名称可能不叫id,那么拼接update语句的条件最靠谱的就是找到主键
LinkedHashMap<String, String> primaryKeyMap = tablesConfig.getMap();
这是统计出表主键不为id,包含组合主键的表名称:主键字段(主键字段1,主键字段2)的一个map
/**
*
* @Author ljy
* @Description 拼接update语句
* @Date 16:18 2024/3/20
* @param tableName
* @param data
* @return java.lang.String
**/
public String generateUpdateSql(String tableName, String data){
LinkedHashMap<String, String> primaryKeyMap = tablesConfig.getMap();
JSONObject jsonObject = JSON.parseObject(data);
StringBuilder updateBuilder = new StringBuilder();
int size = jsonObject.size();
int count = 0;
for (String key : jsonObject.keySet()) {
Object value = jsonObject.get(key);
if (value instanceof String) {
updateBuilder.append(key).append(" = '").append(value).append("'");
} else {
updateBuilder.append(key).append(" = ").append(value);
}
count++;
if (count < size) {
updateBuilder.append(", ");
}
}
String condition;
if (primaryKeyMap.containsKey(tableName)) {
String primaryKey = primaryKeyMap.get(tableName);
if (primaryKey.contains(",")) {
// 处理多个主键字段
String[] primaryKeys = primaryKey.split(",");
StringBuilder conditionBuilder = new StringBuilder();
for (String pk : primaryKeys) {
conditionBuilder.append(pk).append(" = ").append(jsonObject.get(pk)).append(" AND ");
}
// 去除最后的 " AND "
condition = conditionBuilder.substring(0, conditionBuilder.length() - 5);
} else {
// 只有一个主键字段
condition = primaryKey + " = " + jsonObject.get(primaryKey);
}
} else {
// 默认条件
condition = "id = " + jsonObject.get("id");
}
String updateStatement = "UPDATE " + tableName + " SET " + updateBuilder.toString() + " WHERE " + condition + ";";
return updateStatement;
}