Java实现在线SQL编程最新完整版

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

前言：由于前段时间，项目组长分配的任务是要完成一个在线编写SQL并要实现查询功能的需求，最终需要将查询到的数据以JSON格式显示到响应数据的区域，以供操作者进行查看，一开始拿到需求时想着直接使用Mybatis进行操作不就可以了，完全没必要大费周章，因为在MyBatis中有个拼接SQL的语法，可以使用${sql}来进行执行输入的SQL语句，但是实际操作起来并不是想象中的那么简单，因为使用MyBatis会将数据源固定在本项目所使用的数据库，而不可以进行数据源之间的切换，无法进行其他数据源中表的查询操作 ❌，所以在实现过程也是相当艰难曲折...

? 难度分析

在线执行SQL语句的查询主要的难点分为以下几点：其一则是对JDBC的部分的API理解的不够透彻，导致在实现某些工鞥是并不是想象中的顺利；其二在于对于查询部分属性的SQL语句该如何使用Java进行实现，是将输入的字符串进行分割再拼接还是使用直接整条语句的查询操作；其三则是用户可以动态的切换数据源，并且对相应数据源下的表进行查询操作，如果使用Mybatis进行SQL的查询操作则无法进行数据源的切换，只能查询所在微服务项目所连接的数据库进行查询，否则无法进行相应的操作，即会出现该数据库下并不存在所查询的某张数据库表的错误信息 ❌

? 项目回顾（在线编写SQL查询）

? 最终效果演示

? 技术选型：

? SpringBoot
? MyBatis-Plus
? JDBC
? Vue
? 代码编辑器monaco-editor

? 需求分析

使用者在页面可以选择切换需要操作的数据源，并在编辑区域内输入SQL语句进行查询，查询语句可以是全表查询表中所有字段或者根据所需查询指定某几列字段对应的值，点击查询之后即可在响应数据之后以JSON的格式将查询到的每一条数据封装一个对象最后显示到响应数据区域以供操作者进行观看以达到可视化工具的效果。

? 项目搭建

① 引入项目所需要的相关`Maven`依赖

<!--SpringBoot相关依赖-->
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
  <groupId>commons-lang</groupId>
  <artifactId>commons-lang</artifactId>
  <version>2.6</version>
</dependency>
<!--servlet-api相关依赖-->
<dependency>
  <groupId>javax.servlet</groupId>
  <artifactId>servlet-api</artifactId>
  <version>2.5</version>
  <scope>provided</scope>
</dependency>
<!--MySQL驱动相关依赖-->
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.28</version>
</dependency>

② 编写配置文件

server:
  port: 8080
spring:
  datasource:
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/data_source?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai

③ 创建Controller前端控制器

	@PostMapping("/dynamic-query")
	@ApiOperation(value = "动态执行SQL")
	public Result<?> dynamicQuery(@RequestBody DynamicQuery dynamicQuery){
		List<HashMap<Object,Object>> res = null;
		BladeVisualDb visualDb = bladeVisualDbService.getOne(new QueryWrapper<BladeVisualDb>().eq("id", dynamicQuery.getId()));
		List<HashMap<String,Object>> result = ConnectBySql.connAndExecSql(visualDb.getUrl(), visualDb.getDriverClass(), visualDb.getUsername(), visualDb.getPassword(),dynamicQuery.getSql());
		return Result.ok(result);
	}

④ 探究 JDBC中ResultSet中的方法

next()

解释： 将光标从其当前位置向前移动一行。 ResultSet 游标最初位于第一行之前；第一次调用 next 方法使第一行成为当前行；第二次调用使第二行成为当前行，依此类推。实则对查询到的数据进行遍历操作

getString(String columnLabel)

解释： 根据表中对应的列名称来查询到该列对应到的数据

getMetaData()

解释： 获取某张表中列的数量、类型和列名称

findColumn(String columnLabel)

解释： 返回某个列的索引值即下标

⑤ 获取所有的数据源

因为使用者是可以动态的选择数据源从而来对表中的数据进行相关操作，所以首先要查询出可供连接的数据源

	 
	@ApiOperation(value = "下拉列表")
	@GetMapping("/db-list")
	public Result<?> list(){
		List<BladeVisualDb> bladeVisualDbs = bladeVisualDbService.list();
		return Result.ok(bladeVisualDbs);
	}

⑥ 实现执行查询逻辑

首先执行查询有两种方式：所有字段的全查询即select * from xx 或者查询指定字段即select xx from xx

所有字段的全查询即select * from xx

在进行所有字段的查询时，由于无法得知需要查询的表中有什么字段，所以首先需要对输入的SQL字符串进行判断是否是全字段查询，然后即可获取查询表中的所有字段，然后再一一的进行查询出字段对应的值即可，此操作也是需要对输入SQL字符串进行分割的，拿出所有插叙的表名即可。

ResultSet resultSet = stmt.executeQuery(sql);
//如果输入的SQL属于select * 操作
if (sql.contains("*")){
  List<String> list = new ArrayList<>();
  //获取SQL中需要查询的表的结构
  ResultSetMetaData metaData = resultSet.getMetaData();
  int columnCount = metaData.getColumnCount();
  for (int i = 1; i <= columnCount; i++) {
    log.info("属性列   ======>{}",metaData.getColumnName(i));
    //获取表中的所有列
    list.add(metaData.getColumnName(i));
  }
  while (resultSet.next()){
    HashMap<String,Object> hashMap = new HashMap<>();
    for (String index : list) {
      //根据列名称查询出该列对应的数据
      String rs = resultSet.getString(index);
      //将其放入列和值存放HashMap中
      hashMap.put(index,rs);
      //将多个HashMap合并成一个Map
      hashMap.putAll(hashMap);
    }
    res.add(hashMap);
  }
}

查询指定字段即select xx from xx

在使用JDBC进行指定字段查询时需要对输入的SQL字符串进行分割后将所需要查询到的字段再使用JDBC中的getString(String columnName)进行查询字段对应的值

    
    @NotNull
    private static List<String> getString(String sql) {
        List<String> list = new ArrayList<>();
        String str = sql.substring(0, sql.indexOf("from"));
        String realSql = str.replace("select", "").trim();
        if (realSql.contains(",")){
            String[] split = realSql.split(",");
            for (String s : split) {
                list.add(s);
            }
        }else {
            list.add(realSql);
        }
        return list;
    }

随后将分割完成的SQL存放到List集合中再进行查询操作

ResultSet resultSet = stmt.executeQuery(sql);
while (resultSet.next()){
  HashMap<String,Object> hashMap = new HashMap<>();
  List<String> str = getString(sql);
  for (String index : str) {
    String rs = resultSet.getString(index);
    hashMap.put(index,rs);
    hashMap.putAll(hashMap);
  }
  res.add(hashMap);
}

经过对操作者输入的SQL即可完成对表的查询操作，由于所要实现的是可动态切换数据源从而进行相关的查询操作，所以在此操作逻辑中首先需要连接数据库，后再对输入的SQL进行分割查询等操作。合并后的完整代码：

    
    public static List<HashMap<String,Object>> connAndExecSql(String url, String driverClass, String username, String password, String sql) {
        Boolean result = false;
        Connection conn = null;
        Statement stmt = null;
        List<HashMap<String,Object>> res = new ArrayList<>();

        try {
            Class.forName(driverClass);
            System.out.println("------------连接数据库-----------");
            conn = DriverManager.getConnection(url,username,password);
            stmt = conn.createStatement();

            ResultSet resultSet = stmt.executeQuery(sql);
            //如果输入的SQL属于select * 操作
            if (sql.contains("*")){
                List<String> list = new ArrayList<>();
                //获取SQL中需要查询的表的结构
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();
                for (int i = 1; i <= columnCount; i++) {
                    log.info("属性列   ======>{}",metaData.getColumnName(i));
                    //获取表中的所有列
                    list.add(metaData.getColumnName(i));
                }
                while (resultSet.next()){
                    HashMap<String,Object> hashMap = new HashMap<>();
                    for (String index : list) {
                        //根据列名称查询出该列对应的数据
                        String rs = resultSet.getString(index);
                        //将其放入列和值存放HashMap中
                        hashMap.put(index,rs);
                        //将多个HashMap合并成一个Map
                        hashMap.putAll(hashMap);
                    }
                    res.add(hashMap);
                }
            }else {
                while (resultSet.next()){
                    HashMap<String,Object> hashMap = new HashMap<>();
                    List<String> str = getString(sql);
                    for (String index : str) {
                        String rs = resultSet.getString(index);
                        hashMap.put(index,rs);
                        hashMap.putAll(hashMap);
                    }
                    res.add(hashMap);
                }
            }
            //完成连接数据库
            stmt.close();
            conn.close();
            System.out.println("查询连接结束");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }catch (SQLException e){
            e.printStackTrace();
        }
        return res;
    }

    
    @NotNull
    private static List<String> getString(String sql) {
        List<String> list = new ArrayList<>();
        String str = sql.substring(0, sql.indexOf("from"));
        String realSql = str.replace("select", "").trim();
        if (realSql.contains(",")){
            String[] split = realSql.split(",");
            for (String s : split) {
                list.add(s);
            }
        }else {
            list.add(realSql);
        }
        return list;
    }