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: {

		}


	})