自动化工具 尝试落地 AI 赋能测试的 okr 指标之一:AI 生成 sql 工具(java 版)

dun · 2025年05月07日 · 最后由 向上 回复于 2025年05月07日 · 1262 次阅读

需求来源:

目前许多公司已经或者有计划的在开展 AI 赋能测试的 OKR,我们也跑不掉。从去年的各种不屑到今年不得不执行,并且作为了年终 OKR 考核指标,写了一些 AI 辅助工具,大概也不会真正的落地,本文分享工具之一:AI 一键生成 sql 工具,附上效果图:

主要思路:

1、连接测试数据库,通过前端页面自动拉取数据库所有的表以及表结构;
2、在 AI 生成 SQL 输入框输入 sql 需求,生成 SQL;
3、SQL 编辑器支持在线编辑 SQL,并可以直接执行 SQL;
4、返回执行 SQL 的结果;
应该是一个很简单的 SQL 编辑器工具,技术上用 springBoot+html 实现,实际用的 cursor 辅助编程(是真好用),没办法,测试这边的 AI 赋能真的难搞,没有成熟落地并大面积推广的例子,只能摸着石头过 OKR,欢迎大家提供更多 OKR 指标建议。

先附上前端页面

<!DOCTYPE html>
<html lang="zh-CN" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>SQL生成与执行工具</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/css/bootstrap.min.css">
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/codemirror@5.65.6/lib/codemirror.css">
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/codemirror@5.65.6/theme/dracula.css">
    <link rel="stylesheet" href="/css/styles.css">
</head>
<body>
    <div class="container-fluid">
        <header class="bg-primary text-white p-3 mb-4">
            <h1 class="text-center">MySQL查询工具</h1>
        </header>

        <div class="row">
            <!-- 左侧表格列表 -->
            <div class="col-md-3">
                <div class="card mb-3">
                    <div class="card-header bg-secondary text-white">
                        <h5 class="mb-0">数据库表</h5>
                    </div>
                    <div class="card-body">
                        <div class="list-group">
                            <div th:each="table : ${tables}" class="table-item">
                                <div class="form-check">
                                    <input class="form-check-input table-checkbox" type="checkbox" th:value="${table}" th:id="${'table-' + table}">
                                    <label class="form-check-label" th:for="${'table-' + table}" th:text="${table}"></label>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>

                <div class="card mb-3" id="table-structure-card" style="display: none;">
                    <div class="card-header bg-secondary text-white">
                        <h5 class="mb-0">表结构</h5>
                    </div>
                    <div class="card-body">
                        <div id="table-structure-container"></div>
                    </div>
                </div>
            </div>

            <!-- 右侧SQL生成和执行区域 -->
            <div class="col-md-9">
                <div class="card mb-3">
                    <div class="card-header bg-secondary text-white">
                        <h5 class="mb-0">通过AI生成SQL</h5>
                    </div>
                    <div class="card-body">
                        <div class="form-group mb-3">
                            <label for="prompt">输入你的需求(中文描述)</label>
                            <textarea id="prompt" class="form-control" rows="3" placeholder="例如:查询用户表中年龄大于30岁的用户名和邮箱"></textarea>
                        </div>
                        <button id="generate-btn" class="btn btn-primary">生成SQL</button>
                    </div>
                </div>

                <div class="card mb-3">
                    <div class="card-header bg-secondary text-white">
                        <h5 class="mb-0">SQL编辑器</h5>
                    </div>
                    <div class="card-body">
                        <textarea id="sql-editor"></textarea>
                        <div class="mt-3">
                            <button id="execute-btn" class="btn btn-success">执行SQL</button>
                            <button id="clear-btn" class="btn btn-warning ms-2">清空</button>
                        </div>
                    </div>
                </div>

                <div class="card" id="result-container" style="display: none;">
                    <div class="card-header bg-secondary text-white">
                        <h5 class="mb-0">查询结果</h5>
                    </div>
                    <div class="card-body">
                        <div id="result-info" class="mb-3"></div>
                        <div class="table-responsive">
                            <table id="result-table" class="table table-striped table-bordered">
                                <thead id="result-header"></thead>
                                <tbody id="result-body"></tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/jquery@3.6.0/dist/jquery.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/js/bootstrap.bundle.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/codemirror@5.65.6/lib/codemirror.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/codemirror@5.65.6/mode/sql/sql.js"></script>
    <script src="/js/main.js"></script>
</body>
</html>

后端数据库 service

package com.community.sqlapp.service;

import lombok.RequiredArgsConstructor;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.*;

@Service
@RequiredArgsConstructor
public class DatabaseService {

    private final JdbcTemplate jdbcTemplate;

    /**
     * 获取所有数据库表名
     */
    public List<String> getAllTables() {
        try {
            // 尝试不同的SQL查询方式
            String sql = "SELECT table_name FROM information_schema.tables WHERE table_schema = DATABASE()";
            List<String> tables = jdbcTemplate.queryForList(sql, String.class);

            // 添加日志
            System.out.println("找到的表: " + tables);
            return tables;
        } catch (Exception e) {
            e.printStackTrace();
            // 出错时尝试回退到原始方法
            try {
                return jdbcTemplate.queryForList("SHOW TABLES", String.class);
            } catch (Exception ex) {
                ex.printStackTrace();
                return new ArrayList<>();
            }
        }
    }

    /**
     * 获取表结构信息
     */
    public List<Map<String, Object>> getTableStructure(String tableName) {
        String sql = "DESCRIBE " + tableName;
        return jdbcTemplate.queryForList(sql);
    }

    /**
     * 执行SQL查询并返回结果
     */
    public Map<String, Object> executeQuery(String sql) {
        Map<String, Object> result = new HashMap<>();
        try {
            if (sql.trim().toLowerCase().startsWith("select") || 
                sql.trim().toLowerCase().startsWith("show") || 
                sql.trim().toLowerCase().startsWith("describe")) {
                List<Map<String, Object>> data = jdbcTemplate.queryForList(sql);

                // 获取列名
                Set<String> columnNames = new LinkedHashSet<>();
                if (!data.isEmpty()) {
                    columnNames.addAll(data.get(0).keySet());
                }

                result.put("success", true);
                result.put("data", data);
                result.put("columns", columnNames);
                result.put("rowCount", data.size());
            } else {
                int rowsAffected = jdbcTemplate.update(sql);
                result.put("success", true);
                result.put("rowsAffected", rowsAffected);
                result.put("message", "执行成功,影响了 " + rowsAffected + " 行数据");
            }
        } catch (Exception e) {
            result.put("success", false);
            result.put("error", e.getMessage());
        }
        return result;
    }
} 

后端调用大模型的 service

package com.community.sqlapp.service;

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ObjectNode;
import lombok.RequiredArgsConstructor;
import okhttp3.*;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.util.List;
import java.util.Map;
import java.util.concurrent.TimeUnit;

@Service
@RequiredArgsConstructor
public class QianwenService {

    @Value("${qianwen.api.url}")
    private String apiUrl;

    @Value("${qianwen.api.key}")
    private String apiKey;

    private final ObjectMapper objectMapper = new ObjectMapper();
/*    private final OkHttpClient client = new OkHttpClient.Builder()
            .connectTimeout(30, TimeUnit.SECONDS)
            .readTimeout(30, TimeUnit.SECONDS)
            .build();*/
    private final OkHttpClient client = new OkHttpClient.Builder()
        .connectTimeout(30, TimeUnit.SECONDS)
        .readTimeout(120, TimeUnit.SECONDS)
        .writeTimeout(30, TimeUnit.SECONDS)
        .build();
    /**
     * 通过千问API生成SQL
     * @param prompt 用户的SQL需求描述
     * @param tables 数据库表信息
     * @return 生成的SQL语句
     */
    public String generateSql(String prompt, Map<String, List<Map<String, Object>>> tables) {
        try {
            // 构建提示信息
            StringBuilder fullPrompt = new StringBuilder();
            fullPrompt.append("我需要生成一个SQL查询,数据库表结构如下:\n\n");

            // 添加表结构信息
            for (Map.Entry<String, List<Map<String, Object>>> entry : tables.entrySet()) {
                String tableName = entry.getKey();
                List<Map<String, Object>> columns = entry.getValue();

                fullPrompt.append("表名: ").append(tableName).append("\n");
                fullPrompt.append("列信息:\n");

                for (Map<String, Object> column : columns) {
                    fullPrompt.append("- ").append(column.get("Field"))
                            .append(", 类型: ").append(column.get("Type"))
                            .append(", 可空: ").append(column.get("Null"))
                            .append(", 键: ").append(column.get("Key"))
                            .append("\n");
                }
                fullPrompt.append("\n");
            }

            // 添加用户需求
            fullPrompt.append("基于以上表结构,请生成以下SQL查询:\n");
            fullPrompt.append(prompt).append("\n");
            fullPrompt.append("请只返回SQL语句,不要有任何解释。");

            // 构建流式请求
            ObjectNode requestBody = objectMapper.createObjectNode();
            //requestBody.put("model", "qwen-vl-max-latest");
            requestBody.put("model", "qwen-turbo");
            // 使用input.prompt而不是messages
            ObjectNode input = objectMapper.createObjectNode();
            input.put("prompt", fullPrompt.toString());
            requestBody.set("input", input);

            // 参数配置
            ObjectNode parameters = objectMapper.createObjectNode();
            parameters.put("result_format", "text");
            parameters.put("temperature", 0.1);
            parameters.put("top_p", 0.1);
            parameters.put("stream", true);
            requestBody.set("parameters", parameters);

/*            RequestBody body = RequestBody.create(
                    MediaType.parse("application/json"),
                    objectMapper.writeValueAsString(requestBody)
            );*/
            RequestBody body = RequestBody.create(
                    MediaType.parse("application/json"),
                    objectMapper.writeValueAsString(requestBody)
            );

            System.out.println("请求体: " + objectMapper.writeValueAsString(requestBody));
            Request request = new Request.Builder()
                    .url(apiUrl)
                    .addHeader("Authorization", "Bearer " + apiKey)
                    .addHeader("Content-Type", "application/json")
                    .post(body)
                    .build();
            //处理非流式响应
            StringBuilder responseBuilder = new StringBuilder();
            try (Response response = client.newCall(request).execute()) {
                if (!response.isSuccessful()) {
                    throw new IOException("API调用失败,状态码: " + response.code());
                }

                if (response.body() == null) {
                    throw new IOException("响应体为空");
                }

                String responseBody = response.body().string();
                System.out.println("响应体: " + responseBody); // 调试日志

                JsonNode node = objectMapper.readTree(responseBody);
                String text = node.path("output").path("text").asText();
                if (text != null && !text.isEmpty()) {
                    // 去除Markdown代码块标记
                    text = text.replace("```sql", "").replace("```", "").trim();
                    responseBuilder.append(text);
                } else {
                    throw new IOException("未收到有效响应内容");
                }
            }
            // 检查并返回结果
            String result = responseBuilder.toString().trim();
            if (result.isEmpty()) {
                throw new IOException("未收到有效响应");
            }

            // 去除可能的注释和前后空白
            //result = result.replaceAll("(?m)^--.*$", "").trim();
            // 确保返回的是有效的SQL语句
            if (!isValidSql(result)) {
                throw new IOException("API返回了无效的SQL语句: " + result);
            }
            return result;

        }
        catch (IOException e) {
            System.err.println("IO错误: " + e.getMessage());
            throw new RuntimeException("处理流式响应时发生IO错误", e);
        } catch (Exception e) {
            System.err.println("未知错误: " + e.getMessage());
            throw new RuntimeException("处理流式响应时发生未知错误", e);
        }
    }

    /**
     * @param sql 添加SQL有效性检查方法
     * @return
     */
    private boolean isValidSql(String sql) {
        if (sql == null || sql.trim().isEmpty()) {
            return false;
        }

        // 简单的SQL格式检查
        String lowerSql = sql.toLowerCase();
        return lowerSql.startsWith("select") ||
                lowerSql.startsWith("insert") ||
                lowerSql.startsWith("update") ||
                lowerSql.startsWith("delete") ||
                lowerSql.startsWith("create") ||
                lowerSql.startsWith("alter") ||
                lowerSql.startsWith("drop");
    }
} 

SQLController 类

package com.community.sqlapp.controller;

import com.community.sqlapp.service.DatabaseService;
import com.community.sqlapp.service.QianwenService;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.*;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Controller
@RequiredArgsConstructor
public class SqlController {

    private final DatabaseService databaseService;
    private final QianwenService qianwenService;

    @GetMapping("/")
    public String index(Model model) {
        try {
            List<String> tables = databaseService.getAllTables();
            System.out.println("控制器获取到的表: " + tables);
            model.addAttribute("tables", tables);
        } catch (Exception e) {
            e.printStackTrace();
            model.addAttribute("error", "获取表失败: " + e.getMessage());
        }
        return "index";
    }

    @GetMapping("/table/{tableName}")
    @ResponseBody
    public List<Map<String, Object>> getTableStructure(@PathVariable String tableName) {
        System.out.println(databaseService.getTableStructure(tableName).get(0));
        return databaseService.getTableStructure(tableName);

    }

    @PostMapping("/execute")
    @ResponseBody
    public Map<String, Object> executeQuery(@RequestBody Map<String, String> request) {
        String sql = request.get("sql");
        return databaseService.executeQuery(sql);
    }

    @PostMapping("/generate")
    @ResponseBody
    public Map<String, String> generateSql(@RequestBody Map<String, Object> request) {
        Map<String, String> response = new HashMap<>();
        try {
            String prompt = (String) request.get("prompt");
            List<String> selectedTables = (List<String>) request.get("tables");

            // 获取表结构信息
            Map<String, List<Map<String, Object>>> tablesInfo = new HashMap<>();
            for (String tableName : selectedTables) {
                List<Map<String, Object>> tableStructure = databaseService.getTableStructure(tableName);
                tablesInfo.put(tableName, tableStructure);
            }

            // 调用API生成SQL
            String generatedSql = qianwenService.generateSql(prompt, tablesInfo);
            response.put("sql", generatedSql);

        } catch (Exception e) {
            e.printStackTrace();
            // 返回更友好的错误信息
            response.put("sql", "-- 生成SQL时出错: " + e.getMessage() + "\n请尝试简化您的查询需求。");
        }
        return response;
    }
} 

yml 配置文件

spring:
  application:
    name: xlxz

  datasource:
    url: jdbc:mysql://localhost:3306/ai_tester?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
    username: root #数据库账号
    password: root #数据库密码
    driver-class-name: com.mysql.cj.jdbc.Driver

  # Thymeleaf配置
  thymeleaf:
    cache: false
    prefix: classpath:/templates/
    suffix: .html
    mode: HTML

# 日志配置
logging:
  level:
    com:
      community:
        sqlapp: DEBUG
    org:
      springframework:
        jdbc:
          core: DEBUG

# 千问API配置 (请替换为实际的API密钥)
qianwen:
  api:
    url: https://dashscope.aliyuncs.com/api/v1/services/aigc/text-generation/generation
    key: #替换为自己的大模型key
    model: qwen-turbo #使用的大模型

mybatis-plus:
  mapper-locations: classpath:mapper/*.xml

# 服务器配置
server:
  port: 8080

pom 依赖文件

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.4.4</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo</name>
    <description>demo</description>
    <url/>
    <licenses>
        <license/>
    </licenses>
    <developers>
        <developer/>
    </developers>
    <scm>
        <connection/>
        <developerConnection/>
        <tag/>
        <url/>
    </scm>
    <properties>
        <java.version>17</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <version>8.0.33</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>com.squareup.okhttp3</groupId>
            <artifactId>okhttp</artifactId>
            <version>4.9.3</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!-- XML处理 -->
        <dependency>
            <groupId>org.dom4j</groupId>
            <artifactId>dom4j</artifactId>
            <version>2.1.3</version>
        </dependency>
        <!-- ZIP压缩 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-compress</artifactId>
            <version>1.21</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.alibaba/dashscope-sdk-java -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>dashscope-sdk-java</artifactId>
            <version>2.18.2</version>
        </dependency>
        <!-- JSON解析 -->
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-databind</artifactId>
            <version>2.16.1</version>
        </dependency>
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-core</artifactId>
            <version>2.16.1</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <annotationProcessorPaths>
                        <path>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </path>
                    </annotationProcessorPaths>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

总结

SQL 查询工具其他人不知道,但是我偶尔会用一下,gitee 链接:AI 生成 SQL 工具
目前在开发中的小工具:根据产品原型图生成测试点,并输出为 xmind 导图,可以直接在导图上进行人工写用例,至于为什么只生成测试点不输出测试用例,懂得都懂,完全是吃力不讨好,修改已有用例效率不如在测试点后面现写用例。
大家还有哪些建议的点,集思广益,目标就是一点点把 OKR 完成,年底不至于数据太难看😂

共收到 1 条回复 时间 点赞

Swagger,HAR 自动转换,生成 pytest 脚本,生成 jmeter 脚本,生成 Locust 脚本,各种工具都丢给 cursor 曲写

需要 登录 后方可回复, 如果你还没有账号请点击这里 注册