前提

由于业务需要，前端传递已经配置好的sql，所以我需要实现一个通用的动态查询方案

实现

controller

java
//contorller
   @ApiOperation(value = "执行指定sql的脚本")
    @PostMapping("getSqlScriptExcuteResult")
    public Result<Object> getSqlScriptExcuteResult(@RequestBody @Validated RecSearchSolutionSqlScriptExcuteResultRequest request) {
        List<Map<String, Object>> map = iRecSearchSolutionService.getSqlScriptExcuteResult(request);
        return Result.success(map);
    }

service层

java
//Service
    List<Map<String, Object>> getSqlScriptExcuteResult(RecSearchSolutionSqlScriptExcuteResultRequest request);

impl层

java
//impl

@Override
    public List<Map<String, Object>> getSqlScriptExcuteResult(RecSearchSolutionSqlScriptExcuteResultRequest request) {
        if (request.getPage() != 0 && request.getRows() != 0) {
            Integer begin= (request.getPage()-1)*request.getRows();
            Integer end=request.getPage()*request.getRows();
            String newSql = MessageFormat.format("SELECT *\n" +
                    "  FROM (SELECT TMP.*, ROWNUM ROW_ID\n" +
                    "          FROM (" +
                    "{0}" +
                    ") TMP\n" +
                    "         WHERE ROWNUM <= {1})\n" +
                    " WHERE ROW_ID > {2}\n", request.getSql(),end,begin);
            request.setSql(newSql);
        }
        logger.info(request.getSql());
        List<Map<String, Object>> map = commonService.queryForMapList(request.getSql());
        for (Map<String,Object> m: map) {
            for (Map.Entry<String, Object> set:m.entrySet()) {
                if (set.getValue() instanceof Date){
                    set.setValue(DateUtil.format((Date)set.getValue(),"yyyy-MM-dd HH:mm:ss"));
                }
            }
        }
        return map;
    }

commonService

java
package com.lhw.service;

import com.lhw.mapper.SqlHelperMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

@Service
public class CommonService {

    @Resource
    private JdbcTemplate jdbcTemplate;
    @Resource
    private SqlHelperMapper sqlHelperMapper;

    public String queryForString(String sql) {
        List<String> list = jdbcTemplate.queryForList(sql, String.class);
        if (list.size() == 0) {
            return "";
        } else {
            return list.get(0);
        }
    }

    public List<String> queryForStringArray(String sql) {
        List<String> list = jdbcTemplate.queryForList(sql, String.class);
        return list;
    }
    public List<Date> queryForDateArray(String sql) {
        List<Date> list = jdbcTemplate.queryForList(sql, Date.class);
        return list;
    }

    public Integer queryForInt(String sql) {
        List<Integer> list = jdbcTemplate.queryForList(sql, Integer.class);
        if (list.size() == 0) {
            return 0;
        } else {
            return list.get(0);
        }
    }

    public Date queryForDate(String sql) {
        List<Date> list = jdbcTemplate.queryForList(sql, Date.class);
        if (list.size() == 0) {
            return new Date();
        } else {
            return list.get(0);
        }
    }
    public <T>  List<T> queryForList(String sql,Class<T> elementType ){

          List<T> list= jdbcTemplate.queryForList(sql, elementType);
          return list;
    }
    public <T> List<T> query(String sql,RowMapper<T> rowMapper){
        return jdbcTemplate.query(sql,rowMapper);
    }
    public List<LinkedHashMap<String, Object>> superSelect(String sql) {
        return sqlHelperMapper.superSelect(sql);
    }

    public Integer superSelectForInt(String sql) {
        return sqlHelperMapper.superSelectForInt(sql);
    }

    public List<Map<String, Object>> queryForMapList(String sql) {
        return jdbcTemplate.queryForList(sql);
    }

}

可能出现的错误

使用queryForList,会提示Incorrect column count: expected 1, actual 2，因为只支持返回一个list 所以我们就需要在commonService里调整，直接使用query

java
    public List<SolutionConditionDictResponse> queryForDictList(String sql) {
        return jdbcTemplate.query(sql,BeanPropertyRowMapper.newInstance(SolutionConditionDictResponse.class));
    }