最近来了一个奇葩的需求,记录一下,也记录一下我非常速度的搞出来
获取表下的部分字段
<select id="listFields" resultType="java.lang.String">
SELECT
column_name
FROM
information_schema.`COLUMNS`
WHERE
TABLE_SCHEMA = (SELECT DATABASE())
AND TABLE_NAME = #{tableName}
AND column_name NOT IN ('id', 'create_by', 'create_time', 'update_by', 'update_time')
</select>
根据json来匹配进行插入或者保存 JAVA代码
private static final String[] PARSE_PATTERNS = {"yyyy-MM-dd",
"yyyy-MM-dd HH:mm:ss", "yyyy-MM-dd HH:mm", "yyyy/MM/dd",
"yyyy/MM/dd HH:mm:ss", "yyyy/MM/dd HH:mm", "yyyyMMdd"};
@Autowired
private CommonMapper commonMapper;
/**
* 判断输入的字符串是不是日期格式
*/
private static boolean checkTimeFormat(String validateDate) {
try {
DateUtils.parseDate(validateDate, PARSE_PATTERNS);
return true;
} catch (ParseException e) {
return false;
}
}
/**
* 根据输入的表名和json对象进行插入或者更新
*/
public AjaxResult saveOrUpdateByJSON(String tableName, Map<String, Object> map) {
List<String> fields = commonMapper.listFields(tableName);
Map<String, Object> dataMap = new HashMap<>();
if (CollUtil.isEmpty(fields)) {
return AjaxResult.error("该表不存在!");
}
for (String field : fields) {
if (map.containsKey(field)) {
dataMap.put(field, map.get(field));
}
}
Object idObj = map.get("id");
String id = "";
checkFieldDataType(tableName, dataMap);
if (idObj == null || StringUtils.isEmpty(idObj.toString())) {
id = IdUtil.simpleUUID();
// 新增
dataMap.put("id", id);
dataMap.put("create_by", ShiroUtils.getLoginName());
dataMap.put("create_time", new Date());
dataMap.put("update_by", ShiroUtils.getLoginName());
dataMap.put("update_time", new Date());
return AjaxResult.success(commonMapper.saveObj(tableName, dataMap));
} else {
// 更新
id = idObj.toString();
dataMap.put("id", id);
dataMap.put("update_by", ShiroUtils.getLoginName());
dataMap.put("update_time", new Date());
return AjaxResult.success(commonMapper.updateObj(tableName, dataMap));
}
}
/**
* 检查表中的日期类型 与输入的日期类型
* 不匹配 赋值为null 进行保存或者更新
*/
private void checkFieldDataType(String tableName, Map<String, Object> dataMap) {
List<String> fields = commonMapper.selectDateTypeFields(tableName);
if (CollUtil.isNotEmpty(fields)) {
for (String field : fields) {
Object fieldObj = dataMap.get(field);
if (fieldObj != null) {
if (!(fieldObj instanceof Date) && !checkTimeFormat(fieldObj.toString())) {
dataMap.put(field, null);
}
}
}
}
}
MAPPER
<insert id="saveObj">
INSERT INTO ${tableName}
<foreach collection="dataMap.keys" item="key" open="(" close=")" separator=",">
${key}
</foreach>
values
<foreach collection="dataMap.values" item="value" open="(" close=")" separator=",">
#{value}
</foreach>
</insert>
<update id="updateObj">
UPDATE ${tableName} SET
<foreach collection="dataMap.keys" item="key" open="" close="" separator=",">
${key} = #{dataMap[${key}]}
</foreach>
WHERE id = #{dataMap[id]}
</update>