前端页面输入SQL语句，后端用jdbc连接查询

1、Utils类

public class PageUtils {

    /**
     * 分页函数
     * @param currentPage   当前页数
     * @param pageSize  每一页的数据条数
     * @param list  要进行分页的数据列表
     * @return  当前页要展示的数据
     */
    public static Page getPages(int currentPage, int pageSize, List list){
        Page page = new Page();
        int size = list.size();

        if(pageSize > size) {
            pageSize = size;
        }

        // 求出最大页数，防止currentPage越界
        int maxPage = size % pageSize == 0 ? size / pageSize : size / pageSize + 1;

        if(currentPage > maxPage) {
            currentPage = maxPage;
        }

        // 当前页第一条数据的下标
        int curIdx = currentPage > 1 ? (currentPage - 1) * pageSize : 0;

        List pageList = new ArrayList();

        // 将当前页的数据放进pageList
        for(int i = 0; i < pageSize && curIdx + i < size; i++) {
            pageList.add(list.get(curIdx + i));
        }

        page.setCurrent(currentPage).setSize(pageSize).setTotal(list.size()).setRecords(pageList);
        return page;
    }

    /**
     * 分页函数
     * @param currentPage   当前页数
     * @param pageSize  每一页的数据条数
     * @param total 数据总数
     * @param list  查询到的数据
     * @return  当前页要展示的数据
     */
    public static Page getPages(int currentPage, int pageSize,int total, List list){
        Page page = new Page();
        int size = total;

        if(pageSize > size) {
            pageSize = size;
        }

        // 求出最大页数，防止currentPage越界
        int maxPage = size % pageSize == 0 ? size / pageSize : size / pageSize + 1;

        if(currentPage > maxPage) {
            currentPage = maxPage;
        }



        page.setCurrent(currentPage).setSize(pageSize).setTotal(total).setRecords(list);
        return page;
    }

}

2、业务层

/**
 * ${comments}
 * sql查询
 *
 * @author luoqianqian
 * @email *****@mail.com
 * @date 2021.3.9 9:27
 */
@Service
public class KsCorpSqlInfoExportServiceImpl implements KsCorpSqlInfoExprotService {

    @Autowired
    private KsCorpInfoExprotMapper infoExprotMapper;

    @Autowired
    private KsCorpSqlInfoExprotMapper sqlInfoExprotMapper;

    @Autowired
    private SysDictDetailMapper dictDetailMapper;

    @Override
    public Object findListByPage(KsCorpSqlInfoExportEntity sqlInfoExportEntity) {
        /*QueryWrapper<KsCorpInfoExprotEntity> wrapper2 = new QueryWrapper<>();
        wrapper2.apply(sqlInfoExportEntity.getSql());
        List<KsCorpInfoExprotEntity> aa = infoExprotMapper.selectList(wrapper2);*/

        //返回给前端的错误提示
        String toFront = "查询有误，请重新输入查询";

        //判断当前页和每页显示是否为null
        if(sqlInfoExportEntity.getPage() == null){
            sqlInfoExportEntity.setPage(1);
        }
        if(sqlInfoExportEntity.getLimit() == null){
            sqlInfoExportEntity.setLimit(10);
        }

        //防止 sql注入风险：
        //是否为查询语句
        boolean isSelect = sqlInfoExportEntity.getSql().indexOf("s") == 0;
        // “;” 是否存在，如果存在，是否在语句的末尾
        boolean isExistSemicolon = (sqlInfoExportEntity.getSql().lastIndexOf(";") == (sqlInfoExportEntity.getSql().length() - 1))
                || (!sqlInfoExportEntity.getSql().contains(";"));

        if (isSelect && isExistSemicolon){
            /*//查询 sys_dict_detail 表的 机构状态
            QueryWrapper<SysDictDetailEntity> wrapper = new QueryWrapper<>();
            List<String> valueList = new ArrayList<>();*/

            //以一个空格或多个空格分割SQL语句字符串
            String[] sqlKeywordArray = sqlInfoExportEntity.getSql().split("\\s+");
            String tableName = sqlKeywordArray[3];
            //获取需要实例化的对象的类名
            String entityName = tableName;
            for(int i =0 ; i< tableName.length() ; i++) {
                String temp = tableName.charAt(i) + "";
                if (temp.equals("_")) {
                    entityName = entityName.substring(0,1).toUpperCase() + entityName.substring(1,i+1) + entityName.substring(i+1,i+2).toUpperCase() + entityName.substring(i+2);
                }
            }
            entityName = entityName.replace("_","");
            System.out.println(entityName);

            //根据表名查询该表的所有字段的名称
            List<String> columns = sqlInfoExprotMapper.selectAllTableColumn(tableName);

            Connection conn = null;
            PreparedStatement ps = null;
            ResultSet rs = null;

            int count = 0;

            //判断是否有数据查询到
            Boolean isSelectData = false;
            //查询数据的总量 和失去了语句
            Integer total = null;
            String totalSql = "";

            //把实体类的添加到 List
            List entityList = new ArrayList<>();
            try{
                //注册驱动
                Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
                //设置链接地址
                /*String url ="jdbc:sqlserver://223.112.127.3:8001;databaseName=farenkuj";
                String user = "sa";
                String password = "yg@2019";*/

                String url ="jdbc:sqlserver://10.36.1.201:1433;databaseName=farenku";
                String user = "frk";
                String password = "yg@frk@2020";
                //连接数据库
                conn = DriverManager.getConnection(url,user,password);
                //获取语句执行平台，并创建数据库查询
                String sql = "";
                //判断是否有 where
                boolean isHaveWhere = false;
                for(int i = 0;i<sqlKeywordArray.length;i++){
                    sql += sqlKeywordArray[i] + " ";
                    if (sqlKeywordArray[i].toLowerCase().equals("select")){
                        sql+=" top " + sqlInfoExportEntity.getLimit() + " ";
                    }
                    if (sqlKeywordArray[i].toLowerCase().equals("where")){
                        sql+=" " + sqlKeywordArray[3] + ".data_id not in (select top " + sqlInfoExportEntity.getLimit()*(sqlInfoExportEntity.getPage()-1)
                                + " data_id " + " from " + sqlKeywordArray[3] + ") and ";
                        isHaveWhere = true;
                    }
                    //判断如果是最后一次循环并且没有 where 的话，拼接 where 上去
                    if (i == sqlKeywordArray.length -1){
                        if (!isHaveWhere){
                            sql += " where " + sqlKeywordArray[3] + ".data_id not in (select top " + sqlInfoExportEntity.getLimit()*(sqlInfoExportEntity.getPage()-1)
                                    + " data_id " + " from " + sqlKeywordArray[3] + ")";
                        }
                    }
                }
                //
                totalSql = "select count(*) total from (" + sqlInfoExportEntity.getSql() + ") a;";

                ps = conn.prepareStatement(sql);



                //执行查询语句
                rs = ps.executeQuery();
                while (rs.next()){

                    //total = rs.getInt("total");

                    if (count==0){
                        //进入这里则代表查询到了数据
                        isSelectData = true;
                        count++;
                    }


                    //动态创建实例
                    Object entity = Class.forName("com.company.project.entity." + entityName + "Entity").newInstance();
                    Class infoExportEntityClass = entity.getClass();
                    Field[] fields = infoExportEntityClass.getDeclaredFields();
                    //利用循环
                    for (int i = 0;i < fields.length;i++){
                        //设置私有属性的访问权限
                        fields[i].setAccessible(true);
                        //获取字段的名称
                        String filedName = fields[i].getName();
                        //在大写字母前添加 “_” 符号
                        String name = filedName.replaceAll("[A-Z]", "_$0");
                        name = name.toLowerCase();

                        //获取字段类型
                        String type = fields[i].getGenericType().toString();

                        //判断实体类属性与表的字段名是否一致
                        for (int j = 0;j < columns.size();j++){
                            if(name.equals(columns.get(j))){
                                //再判断字段的类型
                                switch (type){
                                    case "class java.lang.String":
                                        fields[i].set(entity,rs.getString(name));
                                        break;
                                    case "class java.lang.Integer":
                                        fields[i].set(entity,rs.getInt(name));
                                        break;
                                    case "class java.util.Date":
                                        fields[i].set(entity,rs.getTimestamp(name));
                                        break;
                                }

                            }
                        }


                    }


                    //添加到实体类集合
                    entityList.add(entity);

                }

            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
                toFront = "不好意思，您输入的SQL语句在" + e.getMessage() +"请重新输入！";
                return toFront;
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InstantiationException e) {
                e.printStackTrace();
            } finally {
                if (null != conn){
                    try{
                        conn.close();
                        conn = null;
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (null != ps){
                    try{
                        ps.close();
                        ps = null;
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (null != rs){
                    try{
                        rs.close();
                        rs = null;
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }

            total = sqlInfoExprotMapper.selectCount(totalSql);

            //如果没查询到数据，则返回给前端 无数据
            if (!isSelectData){
                toFront = "无数据";
                return toFront;
            }

            //数据分页设置
            Page page = PageUtils.getPages(sqlInfoExportEntity.getPage(),sqlInfoExportEntity.getLimit(),total,entityList);

            //批量查询 sys_dict_detail 1349172937746477058
            QueryWrapper<SysDictDetailEntity> wrapper = new QueryWrapper<>();
            wrapper.eq("dict_id","1349172937746477058");
            List<SysDictDetailEntity> dictDetailEntities = dictDetailMapper.selectList(wrapper);

            //设置机构状态
            for (Object o : page.getRecords()) {

                Class entityClass = o.getClass();
                Field[] fields = entityClass.getDeclaredFields();
                for(int i = 0;i < fields.length;i++){
                    //设置私有属性的访问权限
                    fields[i].setAccessible(true);

                    //获取字段的名称
                    String filedName = fields[i].getName();

                    if(filedName.equals("corpStatusName")){
                        //遍历 sys_dict_detail 表的数据
                        for (SysDictDetailEntity dictDetailEntity : dictDetailEntities) {
                            try {

                                if (fields[i-1].get(o) != null){
                                    if (fields[i-1].get(o).equals(dictDetailEntity.getValue())) {
                                        fields[i].set(o, dictDetailEntity.getLabel());
                                    }
                                }

                            } catch (IllegalAccessException e) {
                                e.printStackTrace();
                            }
                        }
                    }


                }
            }

            return page;


        }




        return toFront;
    }
}