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 id="executeSql" parameterType="String" resultType="java.util.LinkedHashMap">  
        ${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" :border=true :stripe=true :fit=true style="width: 100%" v-loading.body="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: {

        }


    })

转载于:https://www.cnblogs.com/gloxing/p/9025003.html