测试之家

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

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

需求来源：

目前许多公司已经或者有计划的在开展 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 完成，年底不至于数据太难看😂

2 个赞
共收到 1 条回复 时间 点赞
向上 #1 · 2025年05月07日 1 个赞

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

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