简单的通用查询设计

1.数据设计

写的不是很好，如果能对现在的你有一些启发或帮助，可以点个关注啊！

{
    // 表信息
    "tableInfo":{
        // 库名
        "dbName": "szxc_dc",
        // 表名称
        "tableName":"dig_customized_data",
        // 列名
        "column":"id, content, component_code, county_code",
        // 逻辑删除字段
        "logicDelete":"is_deleted",
         // 逻辑非删除值
        "logicValue":"N", 
         // json列
        "jsonColumn":"content",
         // 是否包含json列
        "hasJsonColumn":true
    },
    // 操作类型
    "optionalType":"SELECT",
    // 查询字段详情
    "columnDetails":[
        {
            // 是否必选参数
            "isMust":false, 
            // 参数名
            "paramName":"projectName", 
            // 参数类型
            "paramType":"string",
            // 比较类型
            "compareType":"like",
            // 是否为sql字段
            "isSqlConcat":true,
             // 数据库映射字段
            "mappingColumn":"projectName",
            // 是否为json列
            "isJsonColumn":true
        },
        {
            "isMust":false,
            "pattern":"-?[1-9]\d*",
            "paramName":"minTotal",
            "paramType":"number",
            "compareType":"ge",
            "isSqlConcat":true,
            "mappingColumn":"investment",
            "isJsonColumn":true
        },
        {
            "isMust":false,
            "pattern":"-?[1-9]\d*",
            "paramName":"maxTotal",
            "paramType":"number",
            "compareType":"le",
            "isSqlConcat":true,
            "mappingColumn":"investment",
            "isJsonColumn":true
        },
        {
            "isMust":false,
            "minLength":1,
            "paramName":"statusCode",
            "paramType":"string",
            "compareType":"eq",
            "isSqlConcat":true,
            "mappingColumn":"statusCode",
            "isJsonColumn":true
        },
        {
            "isMust":false,
            "minLength":1,
            "paramName":"projectType",
            "paramType":"string",
            "compareType":"locate",
            "isSqlConcat":true,
            "mappingColumn":"projectType",
            "isJsonColumn":true
        },
        {
            "isMust":true,
            "pattern":"-?[1-9]\d*",
            "minNumber":0,
            "paramName":"pageSize",
            "paramType":"number",
            "isSqlConcat":false
        },
        {
            "isMust":true,
            "pattern":"-?[1-9]\d*",
            "minNumber":0,
            "paramName":"pageNum",
            "paramType":"number",
            "isSqlConcat":false
        },
        {
            "isMust":true,
            "minNumber":0,
            "paramName":"componentCode",
            "paramType":"string",
            "compareType":"eq",
            "isSqlConcat":true,
            "mappingColumn":"component_code",
            "isJsonColumn":false
        },
        {
            "isMust":true,
            "minNumber":0,
            "paramName":"countyCode",
            "paramType":"number",
            "compareType":"eq",
            "isSqlConcat":true,
            "mappingColumn":"county_code",
            "isJsonColumn":false
        }
    ]
}

2.实体

@Builder
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString(callSuper = true)
public class QueryWrapper {

    private TableInfo tableInfo;

    private String optionalType;

    private List<QueryValidatorData> columnDetails;
}

@Builder
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString(callSuper = true)
public class TableInfo {
    
    private String dbName;

    private String tableName;

    private String column;

    private String logicDelete;

    private String logicValue;

    private String jsonColumn;

    private Boolean hasJsonColumn;
}

@Builder
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString(callSuper = true)
public class QueryValidatorData {

    private String paramName;

    private String paramType;

    private Boolean isMust;

    private String pattern;

    private Boolean isSqlConcat;

    private String compareType;

    private Integer minLength;

    private Integer minNumber;

    private String mappingColumn;

    private Boolean isJsonColumn;



    public static String compareTypeParse(String compareType) {
        switch (compareType) {
            case "ge" :
                return ">=";
            case "gt" :
                return ">";
            case "le" :
                return "<=";
            case "lt" :
                return "<";
            case "eq" :
                return "=";
            case "like" :
                return "like";
            default:
                throw new IllegalArgumentException("非法参数类型");
        }
    }
}

3.参数校验

package org.example.universal;

import org.springframework.util.Assert;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.regex.Pattern;
import java.util.regex.PatternSyntaxException;

/**
 * 描述:
 *
 * @author : sunyubin
 * @date : 2020/8/10 09:29
 **/
public class UniversalSchemaValidator {

    /** 参数校验 */
    public static void validation(List<QueryValidatorData> queryParams, Map<String, Object> param) {
        if (CollectionUtils.isEmpty(queryParams)) {
            return;
        }
        // 遍历校验参数， 拼接sql查询条件
        for (QueryValidatorData validation : queryParams) {
            String paramName = validation.getParamName();
            Boolean isMust = validation.getIsMust();
            Object paramValue = param.get(paramName);
            String paramType = validation.getParamType();
            Assert.hasLength(paramType, "参数有误，请检查！");

            // 因查询接口使用到类型基本可以划分为 string 和 date 和 number(Integer, Long)
            // string 和 date 使用较多，其中date规则行较强
            // 且查询接口中除固定参数外，其他参数都为非必填且参数后续需拼接sql，
            // 所以先校验其是否必选，非必选且参数值为null或""时直接跳过
            if (isMust) {
                if (Objects.equals(paramType, UniversalDataConst.STRING_TYPE)
                        || Objects.equals(paramType, UniversalDataConst.DATE_TYPE)) {
                    if (StringUtils.isEmpty(paramValue)) {
                        throw new IllegalArgumentException("参数有误， 请检查！");
                    }
                }
                else if (Objects.equals(paramType, UniversalDataConst.NUMBER_TYPE)) {
                    if (Objects.isNull(paramValue)) {
                        throw new IllegalArgumentException("参数有误， 请检查！");
                    }
                }
            }
            // 预防空字符串
            if (StringUtils.isEmpty(Objects.isNull(paramValue) ? null : paramValue.toString())) {
                continue;
            }
            Boolean isSqlConcat = validation.getIsSqlConcat();
            Assert.notNull(isSqlConcat, "校验参数类型缺失");
            if (!isSqlConcat) {
                continue;
            }

            String compareType = validation.getCompareType();
            Assert.notNull(compareType, "校验参数类型缺失");

            String pattern = validation.getPattern();
            if (null != pattern && pattern.length() > 0) {
                if (Objects.equals(paramType, UniversalDataConst.NUMBER_TYPE)) {
                    // Integer Long 等number格式校验
                    boolean matches = Pattern.matches(pattern, paramValue.toString());
                    // 指定最小值比较
                    Integer minNumber = validation.getMinNumber();
                    boolean isGe = true;
                    if (Objects.nonNull(minNumber)) {
                       isGe = (Integer)paramValue >= minNumber;
                    }

                    if (!matches || !isGe) {
                        throw new PatternSyntaxException("数字格式有误", pattern, 0);
                    }
                }
                if (Objects.equals(paramType, UniversalDataConst.DATE_TYPE)) {
                    // 日期格式校验
                    boolean matches = Pattern.matches(pattern, paramValue.toString());
                    if (!matches) {
                        throw new PatternSyntaxException("日期格式有误", pattern, 0);
                    }
                }
                if (Objects.equals(paramType, UniversalDataConst.STRING_TYPE)) {
                    // string 校验
                    int length = paramValue.toString().length();
                    Integer minLength = validation.getMinLength();
                    if (length < minLength) {
                        throw new IllegalArgumentException("参数格式有误");
                    }
                }
            }

        }
    }


}

4.sql解析

package org.example.universal;

import org.springframework.util.Assert;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import java.util.List;
import java.util.Map;
import java.util.Objects;

/**
 * 描述:
 *
 * @author : sunyubin
 * @date : 2020/8/18 09:19
 **/
public class SqlParse {

    /** 解析参数为sql字符串 */
    public static String parse(QueryWrapper qw, Map<String, Object> param) {
        StringBuilder sb = new StringBuilder();
        String jsonColumn = null;
        TableInfo tableInfo = qw.getTableInfo();
        sb.append("SELECT ").append(tableInfo.getColumn()).append(" FROM ")
                .append(tableInfo.getTableName()).append(" ");
        List<QueryValidatorData> queryParams = qw.getColumnDetails();
        if (CollectionUtils.isEmpty(queryParams)) {
            return "";
        }

        if (tableInfo.getHasJsonColumn()) {
            jsonColumn = tableInfo.getJsonColumn();
        }

        int firstFlag = 0;
        // 根据映射数据获取参数中数据， 拼接字符串
        for (QueryValidatorData validation : queryParams) {
            String paramName = validation.getParamName();
            Object paramValue = param.get(paramName);
            String paramType = validation.getParamType();
            // 预防空字符串
            if (StringUtils.isEmpty(Objects.isNull(paramValue) ? null : paramValue.toString())) {
                continue;
            }
            Boolean isSqlConcat = validation.getIsSqlConcat();
            Assert.notNull(isSqlConcat, "校验参数类型缺失");
            if (!isSqlConcat) {
                continue;
            }

            String compareType = validation.getCompareType();
            Assert.notNull(compareType, "校验参数类型缺失");
            String compare = QueryValidatorData.compareTypeParse(compareType);
            String mappingColumn = validation.getMappingColumn();
            boolean isJsonColumn = validation.getIsJsonColumn();

            if (StringUtils.hasLength(tableInfo.getLogicDelete()) && 0 == firstFlag) {
               sb.append("WHERE ").append(tableInfo.getLogicDelete())
                      .append(" = '").append(tableInfo.getLogicValue()).append("' ");
                firstFlag++;
            }
            if (0 == firstFlag) {
                sb.append("WHERE ");
                if (isJsonColumn) {
                    if (Objects.equals(compare, UniversalDataConst.LOCATE)) {
                        sb.append(" LOCATE('").append(paramValue).append("', ").append(jsonColumn)
                                .append(" ->> '$.").append(mappingColumn).append("') ");
                    }
                    else {
                        sb.append(jsonColumn).append(" ->> '$.").append(mappingColumn).append("'");
                    }
                }
                else {
                    if (Objects.equals(compare, UniversalDataConst.LOCATE)) {
                        sb.append(" LOCATE('").append(paramValue).append("', ").append(mappingColumn).append("') ");
                    }
                    else {
                        sb.append(mappingColumn).append(" ");
                    }
                }
            }
            else {
                sb.append("AND ");
                if (isJsonColumn) {
                    if (Objects.equals(compare, UniversalDataConst.LOCATE)) {
                        sb.append(" LOCATE('").append(paramValue).append("', ").append(jsonColumn)
                                .append(" ->> '$.").append(mappingColumn).append("') ");
                    }
                    else {
                        sb.append(jsonColumn).append(" ->> '$.").append(mappingColumn).append("' ");
                    }
                }
                else {
                    if (Objects.equals(compare, UniversalDataConst.LOCATE)) {
                        sb.append(" LOCATE('").append(paramValue).append("', ").append(mappingColumn).append(") ");
                    }
                    else {
                        sb.append(mappingColumn).append(" ");
                    }
                }
            }
            firstFlag++;
            if (Objects.equals(compare, UniversalDataConst.RIGHT_LIKE)) {
                sb.append("LIKE ").append("'").append(paramValue).append("%' ");
            }
            else if (Objects.equals(compare, UniversalDataConst.LOCATE)) {
               continue;
            }
            else {
                sb.append(compare).append(" ");
                if (Objects.equals(paramType, "string") || Objects.equals(paramType, "date")) {
                    sb.append("'").append(paramValue).append("' ");
                }
                else if (Objects.equals(paramType, "number")) {
                    sb.append(" ").append(paramValue).append(" ");
                }
                else {
                    throw new IllegalArgumentException("非法参数");
                }
            }
        }
        return sb.toString();
    }


}

5.执行

1.参数

Map<String, Object> param = Maps.newHashMap();
        param.put("componentCode", "PROJECT");
        param.put("countyCode", 330108);
        param.put("pageNum", 1);
        param.put("pageSize", 10);
        param.put("projectName", "projectName测试1");
        param.put("minTotal", 30);
        param.put("maxTotal", 50);
        param.put("statusCode", "statusCode测试2");
        param.put("projectType", "projectType测试3");

2.解析后sql

SELECT id, content, component_code, county_code 
FROM dig_customized_data 
WHERE is_deleted = 'N' 
AND content ->> '$.projectName' LIKE  'projectName测试1%'
AND content ->> '$.investment' >= 30 
AND content ->> '$.investment' <= 50 
AND content ->>' $.statusCode' = 'statusCode测试2' 
AND content ->> '$.projectType' = 'projectType测试3'
AND componentCode = 'PROJECT'
AND countyCode = 330108