接口测试平台：支持SQL语句执行(Mysql、Oracle)

public class SqlCase {

    /** 主键id */
    private int id;

    /** 创建人 */
    private String createrName;

    /** 创建人code */
    private String createrCode;

    /** 创建时间 */
    @JsonFormat(locale="zh", timezone="GMT+8", pattern="yyyy-MM-dd")
    private Date createDate;

    /** case名 */
    private String caseName;

    /** 数据库名 */
    private String databaseName;

    /** 数据库id */
    private Integer databaseId;

    /** sql语句 */
    private String sql;

    /** 描述 */
    private String description;

    /** 需要保存的变量 */
    private String variableListValue;
    private List<VariableSave> variableList;

    /** 响应结果 */
    private String result;
}

 public ResponseVo excuteSqlRequest(SqlCase sqlCase) {

        ResponseVo responseVo = new ResponseVo();

        // 获取数据库配置信息
        DataBase dataBase = apiTestConfigMapper.selectDataBaseById(sqlCase.getDatabaseId());
        dataBase.setSql(sqlCase.getSql());

        // Sql执行
        String result = ApiTestUtils.doSqlRequest(dataBase);
        sqlCase.setResult(result);

        // 保存变量
        ApiTestUtils.saveVariable(result, sqlCase.getVariableListValue(), ApiTestConfig.GLOBAL_COLLECTION_ID);

        // 更新数据库保存的信息
        sqlCaseMapper.updateSqlCase(sqlCase);

        // 将执行后的结果返回给前端
        responseVo.setIsSuccess(Boolean.TRUE);
        responseVo.setResult(sqlCase);

        return responseVo;
    }

public class DataBase {

    private Integer id;

    private String dbName;

    private String url;

    private String username;

    private String password;

    /** sql语句，只做执行使用 */
    private String sql;

    private String description;

    /**
     * 数据库类型
     * 1：mysql  2：oracle
     */
    private Integer databaseType;
}

public static String doSqlRequest(DataBase dataBase){

        String sql = dataBase.getSql();
        String url = "";
        Integer databaseType = dataBase.getDatabaseType();
        if (databaseType == 1){
            url = "jdbc:mysql://" + dataBase.getUrl() + "?characterEncoding=UTF-8";
        }else if (databaseType == 2){
            url = "jdbc:oracle:thin:@" + dataBase.getUrl();
        }
        String username = dataBase.getUsername();
        String password = dataBase.getPassword();

        //  储存结果集
        List<Map<String, Object>> list = new ArrayList<>();

        Connection con = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            //  加载驱动
            if (databaseType == 1){
                Class.forName("com.mysql.jdbc.Driver");
            }else if (databaseType == 2){
                Class.forName("oracle.jdbc.driver.OracleDriver");
            }
            //  获取数据库连接
            con = DriverManager.getConnection(url,username,password);
            st = con.prepareStatement(sql);

            //  通过正则进行增删改查的判断
            String selectReg = "^select|SELECT";
            Pattern selectP = Pattern.compile(selectReg);
            Matcher selectM = selectP.matcher(sql);

            JSONArray jsonArray = new JSONArray();

            //  查询
            if (selectM.find()){
                rs = st.executeQuery();
                //  获取查询结果元数据,如果rs为空则抛错，如果不为空则转为map赋值后转为String返回给前端
                ResultSetMetaData md = rs.getMetaData();
                int columnCount = md.getColumnCount();

                while (rs.next()){
                    Map<String,Object> rowData = new HashMap<>(16);
                    JSONObject jsonObject = new JSONObject();
                    for (int i = 1; i <= columnCount; i++) {
                        rowData.put(md.getColumnName(i), rs.getObject(i));
                        jsonObject.put(md.getColumnName(i), rs.getObject(i));
                    }

                    list.add(rowData);
                    jsonArray.add(jsonObject);
                }

                if (list.size()==0){
                    return "请检查sql语句";
                }
                return JSON.toJSONString(list);
            }
            //  改\查\删
            else {
                Integer i = st.executeUpdate();
                if(i > 0){
                    return "执行成功";
                }else {
                    return "执行失败，请检查sql语句";
                }
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return "数据库驱动加载失败";
        } catch (SQLException e) {
            e.printStackTrace();
            return e.toString();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    log.error("释放结果集失败");
                }
            }
            if (st != null) {
                try {
                    st.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (con != null) {
                try {
                    log.debug("数据库链接关闭成功！");
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    log.error("关闭链接失败");
                }
            }
        }
    }