通过在java中添加自定义的sql查询的jdbctemplate，可以实现前端传入任意sql然后在后端查询的效果
package com.ruoyi.web.controller.tool;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.ruoyi.common.annotation.Log;
import com.ruoyi.common.core.controller.BaseController;
import com.ruoyi.common.core.domain.AjaxResult;
import com.ruoyi.common.enums.BusinessType;
/**
* SQL控制台
*/
@RestController
@RequestMapping("/tool/sqlConsole")
public class SqlConsoleController extends BaseController
{
@Autowired
private DataSource dataSource;
/**
* 获取SQL查询的列名
*/
@PreAuthorize("@ss.hasRole('admin')")
@PostMapping("/columns")
public AjaxResult columns(@RequestBody Map<String, String> params)
{
String sql = params.get("sql");
if (sql == null || sql.trim().isEmpty())
{
return AjaxResult.error("SQL语句不能为空");
}
sql = sql.trim();
try
{
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
// 直接在原查询末尾追加 LIMIT 0，只获取列元数据不返回数据
String columnSql = sql + " LIMIT 0";
List<String> columns = jdbcTemplate.query(columnSql, rs -> {
List<String> cols = new ArrayList<>();
int count = rs.getMetaData().getColumnCount();
for (int i = 1; i <= count; i++)
{
cols.add(rs.getMetaData().getColumnLabel(i));
}
return cols;
});
return AjaxResult.success(columns);
}
catch (Exception e)
{
return AjaxResult.error("获取列名失败: " + e.getMessage());
}
}
/**
* 执行SQL语句
*/
@PreAuthorize("@ss.hasRole('admin')")
@Log(title = "SQL控制台", businessType = BusinessType.OTHER)
@PostMapping("/execute")
public AjaxResult execute(@RequestBody Map<String, String> params)
{
String sql = params.get("sql");
if (sql == null || sql.trim().isEmpty())
{
return AjaxResult.error("SQL语句不能为空");
}
sql = sql.trim();
try
{
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
String upperSql = sql.toUpperCase().replaceAll("^\s+", "");
if (upperSql.startsWith("SELECT") || upperSql.startsWith("SHOW") || upperSql.startsWith("DESCRIBE") || upperSql.startsWith("DESC") || upperSql.startsWith("EXPLAIN"))
{
List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql);
List<String> columns = new ArrayList<>();
if (!rows.isEmpty())
{
columns.addAll(rows.get(0).keySet());
}
Map<String, Object> data = new HashMap<>();
data.put("type", "select");
data.put("columns", columns);
data.put("rows", rows);
return AjaxResult.success(data);
}
else if (upperSql.startsWith("INSERT") || upperSql.startsWith("UPDATE") || upperSql.startsWith("DELETE"))
{
int affectedRows = jdbcTemplate.update(sql);
Map<String, Object> data = new HashMap<>();
data.put("type", "dml");
data.put("affectedRows", affectedRows);
return AjaxResult.success(data);
}
else
{
jdbcTemplate.execute(sql);
Map<String, Object> data = new HashMap<>();
data.put("type", "ddl");
data.put("message", "执行成功");
return AjaxResult.success(data);
}
}
catch (Exception e)
{
return AjaxResult.error("执行失败: " + e.getMessage());
}
}
}
以下为前端代码，可以查询出对应的字段以及对应的sql结果
<template>
<div class="app-container">
<el-alert
title="仅管理员可使用此功能，请谨慎操作，SQL语句将直接在数据库中执行。"
type="warning"
show-icon
:closable="false"
style="margin-bottom: 15px;"
/>
<el-input
v-model="sql"
type="textarea"
:rows="8"
placeholder="请输入SQL语句，例如：select * from sys_user limit 10"
/>
<div style="margin: 10px 0;">
<el-button type="primary" :loading="loading" @click="handleExecute">执 行</el-button>
<el-button :loading="loadingColumns" @click="handleShowColumns">查看字段</el-button>
<el-button @click="handleClear">清 空</el-button>
</div>
<el-alert
v-if="errorMsg"
:title="errorMsg"
type="error"
show-icon
style="margin-bottom: 15px;"
/>
<el-alert
v-if="successMsg"
:title="successMsg"
type="success"
show-icon
style="margin-bottom: 15px;"
/>
<div v-if="columnList.length > 0" style="margin-bottom: 15px;">
<el-tag v-for="col in columnList" :key="col" style="margin-right: 8px; margin-bottom: 5px;">{{ col }}</el-tag>
</div>
<el-table
v-if="columns.length > 0"
:data="rows"
border
style="width: 100%;"
max-height="500"
>
<el-table-column
v-for="col in columns"
:key="col"
:prop="col"
:label="col"
:show-overflow-tooltip="true"
/>
</el-table>
</div>
</template>
<script>
import { executeSql, getSqlColumns } from '@/api/tool/sqlConsole'
export default {
name: 'SqlConsole',
data() {
return {
sql: '',
loading: false,
loadingColumns: false,
columns: [],
rows: [],
columnList: [],
errorMsg: '',
successMsg: ''
}
},
methods: {
handleExecute() {
if (!this.sql.trim()) {
this.errorMsg = '请输入SQL语句'
return
}
this.loading = true
this.errorMsg = ''
this.successMsg = ''
this.columns = []
this.rows = []
executeSql(this.sql).then(res => {
const data = res.data
if (data.type === 'select') {
this.columns = data.columns
this.rows = data.rows
if (data.rows.length === 0) {
this.successMsg = '查询成功，无数据'
} else {
this.successMsg = '查询成功，共 ' + data.rows.length + ' 条数据'
}
} else if (data.type === 'dml') {
this.successMsg = '执行成功，影响 ' + data.affectedRows + ' 行'
} else {
this.successMsg = data.message || '执行成功'
}
}).catch(err => {
this.errorMsg = err.msg || err.message || '执行失败'
}).finally(() => {
this.loading = false
})
},
handleClear() {
this.sql = ''
this.columns = []
this.rows = []
this.columnList = []
this.errorMsg = ''
this.successMsg = ''
},
handleShowColumns() {
if (!this.sql.trim()) {
this.errorMsg = '请输入SQL语句'
return
}
this.loadingColumns = true
this.errorMsg = ''
this.columnList = []
getSqlColumns(this.sql).then(res => {
this.columnList = res.data
if (res.data.length === 0) {
this.successMsg = '未获取到字段信息'
} else {
this.successMsg = '共 ' + res.data.length + ' 个字段'
}
}).catch(err => {
this.errorMsg = err.msg || err.message || '获取字段失败'
}).finally(() => {
this.loadingColumns = false
})
}
}
}
</script>
