一个后台基于数据库配置sql的通用查询接口

kd_bright

已于 2023-04-21 20:34:59 修改

阅读量813

点赞数

文章标签： sql java 数据库

于 2023-04-21 20:31:10 首次发布

本文链接：https://blog.csdn.net/kd_bright/article/details/130296375

版权

1、controller

@Slf4j
@Api(tags = "通用查询")
@RestController
@RequestMapping("/dic")
public class CommonDicController {

   
    @Resource
    SqlService sqlService;
 

    @RequestMapping(value="/commonSelect",method= {RequestMethod.GET, RequestMethod.POST})
    @ApiOperation(value = "通用查询接口")
    public ApiResult<List<Map<String, Object>>> jsonData(@RequestBody String params){
        Map hashMap = JsonUtils.JsonToMap(params);
        SysUser user = ContextHolder.obtainUserIdNameDefault();
        hashMap.put("userId",user.getUserId());
        log.info("查询参数{}", JSONObject.toJSONString(hashMap));
        return  new ApiResult<>(sqlService.executeSqlByUser(hashMap));
        //return  new ApiResult<>(sqlService.executeSql(params));
    }
}

service

@Service
public class SqlService {

    private static final Logger logger = LoggerFactory.getLogger(SqlService.class);

    @Resource
    private SysSqlDao smcSqlDao;





    public List<Map<String, Object>> executeSql(Map map) {
        List<Map<String, Object>> result = new ArrayList<>();
        try {
            //sql注入校验
            if (CheckSqlParamUtils.mapCheck(map)){
                logger.error("参数未通过sql注入校验",map);
                throw new CheckedException("参数未通过sql注入校验");
            }
            String id = (String) map.get("id");
            if (StringUtils.isEmpty(id)) {
                logger.error("id为空，找不到对应sql");

                return result;
            }
            SysSql sysSql = smcSqlDao.getSqlById(id);

            if (sysSql == null) {
                logger.error("根据id匹配sql失败");
                return result;
            }
            String sql = sysSql.getSql();
            String params = sysSql.getParams();
            String name = sysSql.getNameCn();

            //向sql中替换参数并执行
            String realSql = DbUtil.replaceParam(sql, map);

            //如果表里的params字段有值，则先将入参的值替换params字段，再用字段替换sql中的占位符
            //将params字段用入参替换为 String["value1","value2",...] 形式的字符数组
            String[] realparams = DbUtil.replaceVar(map, params);
            logger.info("replaceVar执行完毕，准备调用replaceSqlConstant获取sql");

            //用字符数组里的值去替换sql语句中包含的‘&’。
            // 返回值：替换了‘&’的sql（这个sql只剩‘？’没换）和没用来替换‘&’而剩下的参数（用来替换‘’？）
            Map<String, Object> m = DbUtil.replaceSqlConstant(realSql, realparams);
            logger.info("replaceSqlConstant执行完毕，准备执行sql");
            //logger.info("数据源为{}",DynamicDataSourceContextHolder.getDataSource());

            Object params1[] = (String[]) m.get("params");
            if (params1 == null) {
                params1 = new Object[]{};
            }
            realSql = (String) m.get("sql");
            logger.info("最终sql：{}" , realSql);
            result = smcSqlDao.executeASql(realSql);

        } catch (Exception e) {
            logger.error("查询异常" + e);
        }finally {
            //DynamicContextHolder.setDataSource(Constant.DEFAULT_DATA_SOURCE_NAME);
        }
        return DbUtil.getStringListMap(result);
    }


    public List<Map<String, Object>> executeSql(String str) {

        Map hashMap = JsonUtils.JsonToMap(str);
        return executeSql(hashMap);
    }


    public List<Map<String, Object>> executeSqlByUser(Map hashMap) {


        return executeSql(hashMap);
    }


}

工具类

/**
 * dbUtils
 */

public class DbUtil {

    private static final Logger log = LoggerFactory.getLogger(DbUtil.class);



    public static List<Map<String, Object>> getStringListMap(List<Map<String, Object>> obj) {
        List<Map<String, Object>> result;
        if (obj == null)
            return new ArrayList<>();
        else {
            result = new ArrayList<>(obj.size());
            for (Map<String, Object> map : obj) {
                Map<String, Object> newMap = null;
                newMap = getStringStringMap(map, newMap);
                result.add(newMap);
            }
        }
        return result;
    }

    private static Map<String, Object> getStringStringMap(Map<String, Object> obj, Map<String, Object> result) {
        if (obj != null) {
            result = new LinkedHashMap<>();
            Object value;
            Iterator iter = obj.keySet().iterator();
            String key;
            while (iter.hasNext()) {
                key = iter.next().toString();
                //key = key.toLowerCase();
                value = obj.get(key);
                if (value == null) {
                    result.put(key, "");
                } else if (value instanceof Clob) {
                    Clob clob = (Clob) value;
                    try {
                        result.put(key, clob.getSubString(1, (int) clob.length()));
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                } else if (value instanceof Timestamp) {
                    Date date = new Date(((Timestamp) value).getTime());
                    String str = DateUtil.formatDateTime(date);
                    result.put(key, str);
                } else if (value instanceof Integer) {
                    result.put(key, value.toString());
                } else {
                    result.put(key, value);
                }
            }
        }
        return result;
    }


    /**
     * 替换sql语句中的常量，即占位符&
     *
     * @param sql
     * @param params
     */

    public static Map<String, Object> replaceSqlConstant(String sql, String[] params) throws Exception {
        try {
            Map<String, Object> result = new HashMap<String, Object>();
            String[] strs = {"&", "?"};
            List<String> paramsList = new ArrayList<String>();
            int fromIndex, index = 0;
            String sqlTemp = sql;
            while ((fromIndex = StringUtils.indexOfAny(sqlTemp, strs)) > -1) {
                if (sqlTemp.indexOf("&") == fromIndex) {
                    sql = sql.replaceFirst("&", params[index]);
                } else {
                    paramsList.add(params[index]);
                }

                sqlTemp = sqlTemp.substring(fromIndex + 1);
                index++;
            }

            result.put("sql", sql);

            String[] p = paramsList.toArray(new String[paramsList.size()]);
            result.put("params", p);
            return result;
        } catch (Exception e) {
//            log.error(sql.replaceAll("[\\t\\n\\r]", " ").substring(0, 50));
            if (params != null)
                for (String param : params) {
                    log.error("预备替换变量：" + param);
                }
            log.error("替换变量出错：", e);
            throw new Exception("替换变量时出错" + e, e);
        }
    }

    static String VAR_SPLIT_STR = "$";

    /**
     * 将params中的变量名替换成变量值
     * params=$prov_code$,$interval$
     *
     * @param varMap prov_code=100,interval=05MI
     * @param param
     * @return params=100,05MI
     */

    public static String[] replaceVar(Map<String, String> varMap, String param) {
        String[] params = param == null ? null : param.split(",");

        if (params == null) {
            return null;
        }

        for (String key : varMap.keySet()) {
            for (int i = 0; i < params.length; i++) {
                params[i] = StringUtils.replace(params[i], VAR_SPLIT_STR + key + VAR_SPLIT_STR, varMap.get(key));
            }
        }
        return params;
    }

    public static String replaceParam(String sql, Map<String, String> paramMap) {
        String regex = "\\{@(\\w+)\\}";
        Matcher m = Pattern.compile(regex).matcher(sql);
        while (m.find()) {  // {@name} 或 {@nameflg}
            String sqlStr = m.group();
            String field = m.group(1);  // name

            if (!field.endsWith("Flag")) { //{@name}的情况
                String name = paramMap.get(field);
                String flag = "{@" + field + "Flag}";
                String flagvalue;
                if (name != null && !"".equals(name)) {//前台传入了参数
                    flagvalue = "1"; //条件
                    // 不自动添加单引号
                    // name = "'"+name+"'";
                } else {//前台没有传入参数

                    // 涉及到登录人信息的从cas获取
                    if (field.equals("login_name")) {
                        flagvalue = "0";
                        //name = CasUtil.getUserIdFromCas();
                    } else {
                        flagvalue = "0";
                        // 不自动添加单引号
                        //name = "'##'";
                        name = "##";
                    }

                }
                sql = sql.replace(sqlStr, name);
                sql = sql.replace(flag, flagvalue);

            }
        }

        return sql;
    }

    //将对象转成map
    public static Map<String, Object> objToMap(Object obj) {
        Map<String, Object> map = new HashMap<String, Object>();
        // 获取f对象对应类中的所有属性域
        Field[] fields = getAllFields(obj);
        for (int i = 0, len = fields.length; i < len; i++) {
            String varName = fields[i].getName();
            varName = varName.toLowerCase();//将key置为小写，默认为对象的属性
            try {
                // 获取原来的访问控制权限
                boolean accessFlag = fields[i].isAccessible();
                // 修改访问控制权限
                fields[i].setAccessible(true);
                // 获取在对象f中属性fields[i]对应的对象中的变量
                Object o = fields[i].get(obj);
                if (o != null)
                    map.put(varName, o.toString());
                // System.out.println("传入的对象中包含一个如下的变量：" + varName + " = " + o);
                // 恢复访问控制权限
                fields[i].setAccessible(accessFlag);
            } catch (IllegalArgumentException ex) {
                ex.printStackTrace();
            } catch (IllegalAccessException ex) {
                ex.printStackTrace();
            }
        }
        return map;
    }

    /**
     * 返回对象的Field（包括私有的和父类的）
     */
    public static Field[] getAllFields(Object object) {
        Class clazz = object.getClass();
        List<Field> fieldList = new ArrayList<>();
        while (clazz != null) {
            fieldList.addAll(new ArrayList<>(Arrays.asList(clazz.getDeclaredFields())));
            clazz = clazz.getSuperclass();
        }
        Field[] fields = new Field[fieldList.size()];
        fieldList.toArray(fields);
        return fields;
    }

}

DAO

public interface SysSqlDao extends BaseMapper<SysSql> {

    @Select("select * from sys_sql where name = #{name} and status = 1")
    SysSql getSqlById(String id);

    @Select("${realSql}")
    List<Map<String, Object>> executeASql(String realSql);
}

实体类

@Data
public class SysSql {



    private String id;

    private String name;

    private String nameCn;

    private String sql;

    private String params;


    private String status;

    private String createBy;

    private LocalDateTime createTime;


    private LocalDateTime updateTime;
}

表结构

CREATE TABLE `sys_sql` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL COMMENT 'sql名称',
  `name_cn` varchar(255) DEFAULT NULL COMMENT '描述',
  `sql` text COMMENT '具体sql',
  `params` varchar(255) DEFAULT NULL COMMENT '参数',
  `status` tinyint(4) DEFAULT NULL COMMENT '0失效1 有效',
  `create_by` varchar(255) DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `name_index` (`name`(191)) USING BTREE COMMENT 'sql名称索引'
) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

sql示例

SELECT city_name as cityName FROM sys_city
where status = 1
and city_code = {@code}

工具类

public class JsonUtils {

    private static ObjectMapper mapper = new ObjectMapper();
    private static final Logger log = LoggerFactory.getLogger(JsonUtils.class);


    /**
     * json 转换为 map
     */
    public static HashMap<String, Object> JsonToMap(String json) {
        HashMap<String, Object> map1 = new HashMap<>();
        if (StringUtils.isEmpty(json)) {
            return map1;
        }
        try {
            map1 = mapper.readValue(json, HashMap.class);
            return map1;
        } catch (Exception e) {
            e.printStackTrace();
            log.error("json to map error : " + e.getMessage());
        }
        return map1;
    }


    public static String toString(Object obj) {
        return toJson(obj);
    }

    /**
     * 将java对象转换为json字符串
     */
    public static String toJson(Object obj) {
        try {
            String string = mapper.writeValueAsString(obj);
            return string;
        } catch (Exception e) {
            throw new RuntimeException("序列化对象【" + obj + "】时出错", e);
        }
    }
}