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