controller
@Autowired
private IExecSqlService execSqlService;
@Log(title = "执行sql", businessType = BusinessType.OTHER)
@PostMapping("/exec")
public Response<?> exec(@RequestBody ExecSqlVo execSqlVo){
if (StringUtils.isBlank(execSqlVo.getSqlCommand())){
throw new CustomException("待执行sql不能为空");
}
return okResponse(execSqlService.exec(execSqlVo.getSqlCommand()));
}
service
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.xxx.mapper.ExecSqlMapper;
import com.xxx.service.IExecSqlService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Service
public class ExecSqlServiceImpl implements IExecSqlService {
@Autowired
private ExecSqlMapper execSqlMapper;
@Override
public Object exec(String sql) {
if (sql.startsWith("select") || sql.startsWith("SELECT")){
return (JSONArray)JSON.toJSON(execSqlMapper.execQuery(sql));
} else {
List result = new ArrayList();
Map<String, Object> map = new HashMap<>();
map.put("update rows", execSqlMapper.execUpdate(sql));
result.add(map);
return result;
}
}
}
mapper
import org.apache.ibatis.annotations.Mapper;
import java.util.LinkedHashMap;
import java.util.List;
@Mapper
public interface ExecSqlMapper {
List<LinkedHashMap<String, Object>> execQuery(String sql);
int execUpdate(String sql);
}
mapper.xml
<mapper namespace="com.xxx.mapper.ExecSqlMapper">
<select id="execQuery" parameterType="java.lang.String" resultType="java.util.LinkedHashMap">
${sql}
</select>
<update id="execUpdate" parameterType="java.lang.String">
${sql}
</update>
</mapper>
vue
<template>
<div class="app-container">
<el-form class="searchBar" :model="queryParams" ref="queryForm" :inline="true">
<el-input
type="textarea"
v-model="queryParams.sqlCommand"
placeholder="请输入待执行sql"
rows="13"
clearable
style="margin-top: 20px;margin-bottom: 20px;width:80%;"/>
<el-button type="danger" @click="handleQuery" style="margin-left: 40px;margin-bottom: 20px;">执行sql</el-button>
</el-form>
<el-row>
<el-col :span="4">执行结果:</el-col>
</el-row><br/>
<el-table border style="width: 100%" :data="resultTable" id="table">
<!-- 动态列表渲染 -->
<el-table-column
width="150"
:label="item.label"
:prop="item.prop"
v-for="(item, key) in result"
:key="key"
>
</el-table-column>
</el-table>
</div>
</template>
<script>
import { execSql} from '@/api/system/exec-sql'
export default {
name: 'ExeSqlMng',
props: {
// 是否有已修改
reload: { type: Boolean, default: false },
orgTypeOptions: { type: Array, default: () => [] }
},
data() {
return {
resultTable: [], //查看数据处理后的数据
result: [], //查看数据用于循环的数据
// 查询参数
queryParams: {
sqlCommand: null
},
// 菜单ID
menuId: this.$route.meta.menuId,
}
},
methods: {
/**点击当前行 */
rowClick(row) {
this.selectRow = row
},
/** 查询列表 */
getList() {
const loading = this.$loading(this.GLOBAL.Loading);
execSql(this.queryParams)
.then(response => {
loading.close();
this.result = this.getCol(response.data);
this.resultTable = this.getTable(response.data);
})
.catch((e) => {
loading.close()
})
},
/** 搜索按钮操作 */
handleQuery() {
if(!this.queryParams.sqlCommand){
this.$message({
type: 'warn',
message: `待执行sql为空`
});
return
}
this.getList()
},
getCol(src) {
let col = [];
for (let j in src[0]) {
col.push({
prop: j,
label: j,
});
}
return col;
},
getTable(src) {
let table = [];
for (let i = 0; i < src.length; i++) {
let temp = {};
for (let j in src[i]) {
temp[j] = src[i][j];
}
table.push(temp);
}
return table;
},
}
}
</script>
<style scoped>
.el-form--inline .el-form-item {
display: inline-block;
margin-right: 10px;
vertical-align: top;
width: 1000px;
}
</style>