杂记

68 阅读1分钟

最近来了一个奇葩的需求,记录一下,也记录一下我非常速度的搞出来

获取表下的部分字段

<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>