最近客户提一个需求，需要在系统功能中增加一个页面，可以直接执行sql语句。
实现这个功能有三个难点：
1.查询语句中查询的table不确定，前端展示的时候表头也就不是固定的
2.通过jdbc执行sql语句，在执行查询语句的时候需要返回两个数据，一个是查询结果，一个是查询结果里的所有字段的集合（前端展示时的表头）
3.查询结果需要分页展示，考虑了一下，决定使用在sql里实现分页的功能
具体代码如下：
<template> <div class="app-container" style="text-align: -webkit-center;"> <el-card style="width: 98%; height: 593px"> <el-row :gutter="20"> <el-form :inline="true" style="text-align:-webkit-left;"> <el-form-item label="SQL: "> <el-input v-model="sqls" placeholder="请输入sql" clearable size="small"></el-input> </el-form-item> <el-form-item> <el-button type="primary" @click="getSql1()" size="mini" >执行</el-button> </el-form-item> <el-form-item label="返回信息:"> <el-input v-model="msgs" size="small"></el-input> </el-form-item> </el-form> </el-row> <el-table :data="tableData1" border loading height="320" highlight-current-row> <el-table-column v-for="item in tableData" :label="item" :property="item" :key = "item" width="160"> </el-table-column> </el-table> <el-pagination style="float: right" @size-change="sizeChangeHandle" @current-change="currentChangeHandle" :current-page="pageIndex" :page-sizes="[10, 20, 50, 100]" :page-size="pageSize" :total="totalPage" layout="total, sizes, prev, pager, next, jumper"> </el-pagination> </el-card> </div> </template> <script> import { getSqlRes} from '@/api/platform/crds/companycreditinfo/webSql' import {matchRating} from "../../../api/platform/crds/debt/runRating"; import {remote} from '@/api/admin/dict.js' export default { name: "index", data(){ return{ pageIndex: 1, pageSize: 10, totalPage: 0, sqls:undefined, tableData:[], tableData1:[], msgs:"", loading: true, } }, components :{ }, created() { }, methods:{ sizeChangeHandle(val) { this.pageSize = val this.pageIndex = 1 this.getSql() }, currentChangeHandle(val) { this.pageIndex = val this.getSql() }, getSql() { getSqlRes({sql:this.sqls,current: this.pageIndex, size: this.pageSize}).then(response => { if (response && response.data.code === 0) { this.msgs = response.data.data.msg this.tableData = response.data.data.table this.tableData1 = response.data.data.data this.totalPage = response.data.data.totalPage } else { this.msgs = response.data.data.msg this.tableData=[] this.tableData1 = [] this.totalPage = 0 } }) }, getSql1() { getSqlRes({sql:this.sqls,current: 1, size: 10}).then(response => { if (response && response.data.code === 0) { this.msgs = response.data.data.msg this.tableData = response.data.data.table this.tableData1 = response.data.data.data this.totalPage = response.data.data.totalPage this.pageIndex = 1 this.pageSize = 10 } else { this.msgs = response.data.data.msg this.tableData=[] this.tableData1 = [] this.pageSize = 0 } }) }, } } </script>
后台代码：
public class SqlUtil { public static R run(Map<String,String> map) { String msg = "error"; String sql = map.get("sql").trim(); String current = map.get("current"); int cur = Integer.parseInt(current); String size = map.get("size"); int si= Integer.parseInt(size); int start = si * (cur - 1) + 1; int end = si * cur; String sHead = ""; HashMap<String, Object> mapData = new HashMap<String, Object>(); if(sql != null && sql.length() >=6 ) { sHead = sql.substring(0, 6); } else { mapData.put("msg","请输入正确的sql语句"); mapData.put("totalPage",0); mapData.put("table",""); mapData.put("data",""); return new R<>(mapData); } Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; try { Class.forName("oracle.jdbc.driver.OracleDriver"); String url = map.get("url"); String user = map.get("username"); String password = map.get("password"); conn = DriverManager.getConnection(url, user, password); if ("select".equalsIgnoreCase(sHead)) { String sqlCount = "select count(*) count from(" + sql +")"; pstmt = conn.prepareStatement(sqlCount); rs = pstmt.executeQuery(); int totalPage = 0; if(rs.next()) { totalPage=rs.getInt("count"); } sql = "select * from ( select s.*,rownum rn from (" + sql + ")s where rownum <= " + end + ") where rn >= "+start; pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery(); ResultSetMetaData md = rs.getMetaData(); int columnCount = md.getColumnCount(); List<Object> lists = new ArrayList<Object>(); List<Object> list1 = new ArrayList<Object>(); for(int j = 1; j <= columnCount; j++) { list1.add(md.getColumnName(j)); } while(rs.next()) { HashMap<String, Object> rowData = new HashMap<String, Object>(); for (int i = 1; i <= columnCount; i++) { rowData.put(md.getColumnName(i), rs.getObject(i)); } lists.add(rowData); } mapData.put("totalPage",totalPage); mapData.put("table",list1); mapData.put("data",lists); mapData.put("msg",sHead+"执行成功"); return new R<>(mapData); }else { pstmt = conn.prepareStatement(sql); pstmt.executeUpdate(); mapData.put("msg",sHead+"执行成功"); mapData.put("table",""); mapData.put("data",""); return new R<>(mapData); } }catch (ClassNotFoundException e) { msg = e.getMessage(); //System.out.println(msg); } catch(Exception e) { msg = e.getMessage(); } finally{ try{ if(rs != null){ rs.close(); } if(pstmt != null){ pstmt.close(); } if(conn != null){ conn.close(); } } catch (SQLException e) { msg = e.getMessage(); } } mapData.put("table",""); mapData.put("data",""); mapData.put("msg",msg); return new R<>(mapData); } }