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

kd_bright

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

阅读量1k

点赞数

文章标签： 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());