手写数据库客户端_手写数据库系统

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()));
    }
1
2
3
4
5
6
7
8
9
10
11

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;
        }
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

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);
}
1
2
3
4
5
6
7
8
9
10
11
12

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>
1
2
3
4
5
6
7
8
9
10

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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112