web执行sql----vue mybatis
java
@ResponseBody
@RequestMapping(value = "/sqlMap", method = RequestMethod.POST)
public String executeSql(HttpServletRequest req) {
JSONObject jsona = new JSONObject();
try {
Map<String, Object> params = HttpServiceUtils.getParamsFromReq(req);
String sqlStr = null;
if (!params.containsKey("sql")) {
throw new Exception("不存在sql");
}
sqlStr = params.get("sql").toString();
//不处理insert update delete drop alter create all_ user_避免动态注入
if(sqlStr.indexOf("insert") > -1
|| sqlStr.indexOf("update") >-1
|| sqlStr.indexOf("delete") > -1
|| sqlStr.indexOf("drop") > -1
|| sqlStr.indexOf("alter") >-1
|| sqlStr.indexOf("create") >-1
|| sqlStr.indexOf("all_") >-1
|| sqlStr.indexOf("user_") >-1){
throw new Exception("避免sql注入,不允许执行");
}
//处理特殊字符
sqlStr = sqlStr.replaceAll(";","");
List<LinkedHashMap<String, Object>> resultList = sqlMapper.executeSql(sqlStr);
//clob处理
Iterator<LinkedHashMap<String,Object>> itA = resultList.iterator();
while(itA.hasNext()){
LinkedHashMap jsonObj = itA.next();
Iterator<String> it = jsonObj.keySet().iterator();
while(it.hasNext()){
String key = it.next();
Object value = jsonObj.get(key);
if(value instanceof oracle.sql.CLOB){
value = ClobUtil.clobToString((CLOB)value);
jsonObj.put(key, value);
}else if(value instanceof java.sql.Timestamp){
value = value.toString();
jsonObj.put(key, value);
}
}
}
JSONArray resultJa = JSONArray.fromObject(resultList);
jsona.accumulate("data", resultJa);
jsona.accumulate("total", 0);
return ResponseUtils.success(jsona);
} catch (Exception e) {
log.error("执行脚本失败:{}", e);
return ResponseUtils.failure(e.getMessage());
}
}
mapper.xml
<select >
${sqlStr}
</select>
mapper.java
public List<LinkedHashMap<String, Object>> executeSql(@Param(value="sqlStr") String sqlStr);
html
<div class='sql-map'>
<el-input v-model='sql' type="textarea" autosize></el-input>
<el-button @click='executeSql' type="success">提交</el-button>
<el-input v-model='result' type="textarea"></el-input>
<el-table v-if='data.length>0' :data="data" :loading">
<el-table-column v-for='item in keys' :prop="item" :label="item" show-overflow-tooltip sortable>
</el-table-column>
</el-table>
</div>
js
Vue.component('sql-map',{
template: '#sqlMap',
mixins: [mixin_basic],
created: function () {
},
mounted: function () {
},
beforeDestroy: function () {
},
data: function(){return{
name:'sqlMap',
title: "sqlMap",
pageSize: GetPageSize(),
loading: false,
sql:'select * from component_inst a where rownum<5;',
result:'',
data:[],
keys:[]
}},
methods: {
executeSql: function (inParams, callBack) {
//条数限制
if(this.sql.indexOf('where') <= -1 || this.sql.length == 0){
this.$alert('请输入where子句', '服务异常');
return;
}
this.loading = true
var vueThis = this;
//重置
vueThis.data = [];
vueThis.keys =[];
if(this.sql.indexOf('and rownum<20;') <= -1 ){
if(this.sql.indexOf(';') > -1 ){
this.sql = this.sql.replace(';',' and rownum<20;');
}else{
this.sql = this.sql + ' and rownum<20;';
}
}
//参数
var params = {};
Object.assign(params, { 'sql': this.sql });
callServicePolyfill(this, 'executeSql', params, function (res) {
try {
vueThis.result = res;
res = JSON.parse(res);
if (!!res.statusCd && res.statusCd != '200') {
throw res.message;
} else {
vueThis.data = res.data;
for(var key in vueThis.data){
var item = vueThis.data[key];
for(var j in item){
if(vueThis.keys.indexOf(j)<=-1){
vueThis.keys.push(j);
}
if(vueThis.isJsonObj(item[j])){
item[j] = JSON.stringify(item[j]);
}else if(Array.isArray(item[j])){
item[j] = item[j] +'';
}
}
}
};
} catch (err) {
console.error(err)
console.error(res)
vueThis.info('执行sql失败,' + err);
} finally {
vueThis.loading = false;
}
})
},
isJsonObj:function(obj){
var isjson = typeof(obj) == "object" && Object.prototype.toString.call(obj).toLowerCase() == "[object object]" && !obj.length;
return isjson;
}
},
computed: {
}
})