Springboot后端管理项目+AI对话式分析
Springboot后端管理项目+AI对话式分析什么叫对话式分析
对话式分析（Conversational Analytics）是一种通过自然语言交互实现数据探索的技术。用户无需掌握SQL或编程技能，像与真人对话一样用日常语言提问（如："上季度华东区哪些产品的退货率超过5%？"），系统自动解析语义、生成查询、返回可视化结果。
与传统分析方式的本质区别
[*]对比维度 传统分析 对话式分析
[*]交互方式 编写SQL/使用固定报表模板 直接输入自然语言问题
[*]响应速度 需求排期→开发→测试（3天+） 实时响应（秒级）
[*]技术门槛 需懂数据库结构和技术术语 普通业务人员零门槛操作
[*]灵活度 受限于预设指标和维度 支持任意组合的即时分析
Springboot + 智普AI 实现
智普AI-官网：https://bigmodel.cn/dev/activities/free/glm-4-flash
对话式-数据库自动分析：核心就是数据库表和字段注释一定要清楚
示例数据库(一定要中文描述足够详细)
CREATE TABLE `sys_dept` (
`dept_id` bigint(20) NOT NULL COMMENT '部门id',
`tenant_id` varchar(20) DEFAULT '000000' COMMENT '租户编号',
`parent_id` bigint(20) DEFAULT '0' COMMENT '父部门id',
`ancestors` varchar(500) DEFAULT '' COMMENT '祖级列表',
`dept_name` varchar(30) DEFAULT '' COMMENT '部门名称',
`dept_category` varchar(100) DEFAULT NULL COMMENT '部门类别编码',
`order_num` int(11) DEFAULT '0' COMMENT '显示顺序',
`leader` bigint(20) DEFAULT NULL COMMENT '负责人',
`phone` varchar(11) DEFAULT NULL COMMENT '联系电话',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
`status` char(1) DEFAULT '0' COMMENT '部门状态（0正常 1停用）',
`del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
`create_dept` bigint(20) DEFAULT NULL COMMENT '创建部门',
`create_by` bigint(20) DEFAULT NULL COMMENT '创建者',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_by` bigint(20) DEFAULT NULL COMMENT '更新者',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`dept_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='部门表';
CREATE TABLE `sys_user` (
`user_id` bigint(20) NOT NULL COMMENT '用户ID',
`tenant_id` varchar(20) DEFAULT '000000' COMMENT '租户编号',
`dept_id` bigint(20) DEFAULT NULL COMMENT '部门ID',
`user_name` varchar(30) NOT NULL COMMENT '用户账号',
`nick_name` varchar(30) NOT NULL COMMENT '用户昵称',
`user_type` varchar(10) DEFAULT 'sys_user' COMMENT '用户类型（sys_user系统用户）',
`email` varchar(50) DEFAULT '' COMMENT '用户邮箱',
`phone_number` varchar(11) DEFAULT '' COMMENT '手机号码',
`sex` char(1) DEFAULT '0' COMMENT '用户性别（0男 1女 2未知）',
`avatar` bigint(20) DEFAULT NULL COMMENT '头像地址',
`password` varchar(100) DEFAULT '' COMMENT '密码',
`status` char(1) DEFAULT '0' COMMENT '帐号状态（1正常 0停用）',
`del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
`login_ip` varchar(128) DEFAULT '' COMMENT '最后登录IP',
`login_date` datetime DEFAULT NULL COMMENT '最后登录时间',
`create_dept` bigint(20) DEFAULT NULL COMMENT '创建部门',
`create_by` bigint(20) DEFAULT NULL COMMENT '创建者',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_by` bigint(20) DEFAULT NULL COMMENT '更新者',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='用户信息表';
CREATE TABLE `sys_role` (
`role_id` bigint(20) NOT NULL COMMENT '角色ID',
`tenant_id` varchar(20) DEFAULT '000000' COMMENT '租户编号',
`role_name` varchar(30) NOT NULL COMMENT '角色名称',
`role_key` varchar(100) NOT NULL COMMENT '角色权限字符串',
`role_sort` int(11) NOT NULL COMMENT '显示顺序',
`data_scope` char(1) DEFAULT '1' COMMENT '数据范围（1：全部数据权限 2：自定数据权限 3：本部门数据权限 4：本部门及以下数据权限）',
`menu_check_strictly` tinyint(1) DEFAULT '1' COMMENT '菜单树选择项是否关联显示',
`dept_check_strictly` tinyint(1) DEFAULT '1' COMMENT '部门树选择项是否关联显示',
`status` char(1) NOT NULL COMMENT '角色状态（0正常 1停用）',
`del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
`create_dept` bigint(20) DEFAULT NULL COMMENT '创建部门',
`create_by` bigint(20) DEFAULT NULL COMMENT '创建者',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_by` bigint(20) DEFAULT NULL COMMENT '更新者',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`role_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='角色信息表';实现流程
[*]用户发起请求
[*]用户通过 /ai/zhi_pu_qa 接口发起请求，传入 question 和 dbName 参数。
[*]获取数据库表名和注释
[*]调用 getTablesAndComments 方法，使用 SHOW CREATE TABLE 语句获取指定数据库的所有表名和注释。
[*]SQL 语句示例：
SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test_admin_123';
[*]检查表名和注释是否为空
[*]如果 tablesAndComments 为空，返回“知识库未找到相关信息”。
[*]询问AI获取相关表信息
[*]调用 handlerAiQuestion 方法，将表名和注释信息传递给 AI，询问与问题相关的表信息。
[*]AI 返回的格式示例：
{
"choices": [
{
"finish_reason": "stop",
"index": 0,
"message": {
"content": "[{\"tableName\":\"sys_user\"}]",
"role": "assistant"
}
}
],
"created": 1743232278,
"id": "202503291511179a664d1e8d0245d7",
"model": "glm-4-flash",
"request_id": "guo_tong_1743232383435",
"usage": {
"completion_tokens": 32,
"prompt_tokens": 498,
"total_tokens": 530
}
}
[*]检查AI返回是否为空
[*]如果 tableInfosJson 为空，返回“知识库未找到相关信息”。
[*]解析AI返回 的表信息
[*]调用 parseTableFromResponse 方法，从 AI 返回的 JSON 中解析出表名列表
[*]解析示例：
[{"tableName":"sys_user"}]
[*]解析后得到表名字符串：
sys_user
[*]检查表信息是否为空
[*]如果 tableNames 为空，返回“知识库未找到相关信息”。
[*]获取表的DDL
[*]调用 getTableDdl 方法，根据解析出的表名列表获取每个表的建表语句（DDL）。
[*]SQL 语句示例：
SHOW CREATE TABLE `test_admin_123`.`sys_user`;
[*]检查DDL是否为空
[*]如果 columnsAndComments 为空，返回“知识库未找到相关信息”。
[*]询问AI生成SQL
[*]调用 handlerAiQuestion 方法，将表的 DDL 信息传递给 AI，询问生成查询 SQL。
[*]AI 返回的格式示例：
{
"choices": [
{
"finish_reason": "stop",
"index": 0,
"message": {
"content": "SELECT * FROM `sys_user` WHERE YEAR(`create_time`) = 2024",
"role": "assistant"
}
}
],
"created": 1743232278,
"id": "202503291511179a664d1e8d0245d7",
"model": "glm-4-flash",
"request_id": "guo_tong_1743232383435",
"usage": {
"completion_tokens": 32,
"prompt_tokens": 498,
"total_tokens": 530
}
}
[*]检查AI返回是否为空
[*]如果 result 为空，返回“接口调用失败，请检查日志!”。
[*]解析AI返回的SQL
[*]调用 parseSqlFromResponse 方法，从 AI 返回的 JSON 中解析出 SQL 语句。
[*]解析示例：
{
"choices": [
{
"finish_reason": "stop",
"index": 0,
"message": {
"content": "SELECT * FROM `sys_user` WHERE YEAR(`create_time`) = 2024",
"role": "assistant"
}
}
]
}
[*]解析后得到 SQL 语句：
SELECT * FROM `sys_user` WHERE YEAR(`create_time`) = 2024
[*]检查SQL是否为空
[*]如果 sql 为空，返回“解析SQL失败，请检查AI返回的内容!”。
[*]执行SQL
[*]调用 executeSql 方法，使用 JDBC 执行解析出的 SQL 语句，获取查询结果。
[*]返回查询结果
[*]将查询结果转换为 JSON 字符串，返回给客户端。
[*]
[{
"tenant_id": "000000",
"del_flag": "0",
"create_time": 1731411199000,
"create_dept": 103,
"user_name": "test",
"sex": "0",
"login_date": 1731411199000,
"remark": "QQ",
"avatar": 10085,
"login_ip": "127.0.0.1",
"create_by": 1,
"password": "f379eaf3c831b04de153469d1bec345e",
"update_time": 1731411199000,
"user_type": "sys_user",
"user_id": 3,
"nick_name": "本部门及以下 密码666666",
"phone_number": "15888888888",
"dept_id": 108,
"update_by": 3,
"email": "crazyLionLi@163.com",
"status": "1"
}, {
"tenant_id": "000000",
"del_flag": "0",
"create_time": 1731411199000,
"create_dept": 103,
"user_name": "test1",
"sex": "1",
"login_date": 1731411199000,
"remark": "CMD",
"avatar": 10086,
"login_ip": "127.0.0.1",
"create_by": 1,
"password": "f379eaf3c831b04de153469d1bec345e",
"update_time": 1731411199000,
"user_type": "sys_user",
"user_id": 4,
"nick_name": "仅本人 密码666666",
"phone_number": "15888888888",
"dept_id": 102,
"update_by": 4,
"email": "crazyLionLi@163.com",
"status": "1"
}, {
"tenant_id": "000000",
"del_flag": "0",
"create_time": 1735371444000,
"create_dept": 103,
"user_name": "zhoujielun",
"sex": "1",
"login_date": 1731411199000,
"remark": "我是周杰伦，夜曲一响上台领奖",
"avatar": 10086,
"login_ip": "0:0:0:0:0:0:0:1",
"create_by": 1,
"password": "e10adc3949ba59abbe56e057f20f883e",
"update_time": 1735371444000,
"user_type": "sys_user",
"user_id": 1872909700790542337,
"nick_name": "周杰伦",
"phone_number": "14837983573",
"dept_id": 103,
"update_by": 1,
"email": "zhoujielun@163.com",
"status": "1"
}, {
"tenant_id": "000000",
"del_flag": "0",
"create_time": 1735372046000,
"create_dept": 103,
"user_name": "wanglihong",
"sex": "1",
"login_date": 1731411199000,
"remark": "我是王力宏，爱错一响，立即登场",
"avatar": 10086,
"login_ip": "0:0:0:0:0:0:0:1",
"create_by": 1,
"password": "e10adc3949ba59abbe56e057f20f883e",
"update_time": 1735372046000,
"user_type": "sys_user",
"user_id": 1872912222804516866,
"nick_name": "王力宏",
"phone_number": "15837357332",
"dept_id": 103,
"update_by": 1,
"email": "wanglihong@163.com",
"status": "1"
}, {
"tenant_id": "000000",
"del_flag": "0",
"create_time": 1735372147000,
"create_dept": 103,
"user_name": "huachenyu",
"sex": "1",
"login_date": 1731411199000,
"remark": "我是华晨宇，华语乐坛永远的神",
"avatar": 10086,
"login_ip": "0:0:0:0:0:0:0:1",
"create_by": 1,
"password": "e10adc3949ba59abbe56e057f20f883e",
"update_time": 1735711391000,
"user_type": "sys_user",
"user_id": 1872912647666540546,
"nick_name": "华晨宇",
"phone_number": "15837557332",
"dept_id": 103,
"update_by": 1,
"email": "huachenyu@163.com",
"status": "1"
}, {
"tenant_id": "000000",
"del_flag": "0",
"create_time": 1735372174000,
"create_dept": 103,
"user_name": "dengziqi",
"sex": "1",
"login_date": 1731411199000,
"remark": "我是邓紫棋，泡沫一响，立即登场",
"avatar": 10086,
"login_ip": "0:0:0:0:0:0:0:1",
"create_by": 1,
"password": "e10adc3949ba59abbe56e057f20f883e",
"update_time": 1735372174000,
"user_type": "sys_user",
"user_id": 1872912759570571265,
"nick_name": "邓紫棋",
"phone_number": "15837557332",
"dept_id": 103,
"update_by": 1,
"email": "dengziqi@163.com",
"status": "1"
}, {
"tenant_id": "000000",
"del_flag": "0",
"create_time": 1735372214000,
"create_dept": 103,
"user_name": "chenyixun",
"sex": "1",
"login_date": 1731411199000,
"remark": "我是陈奕迅，孤独患者一响，立即登场",
"avatar": 10086,
"login_ip": "0:0:0:0:0:0:0:1",
"create_by": 1,
"password": "e10adc3949ba59abbe56e057f20f883e",
"update_time": 1735372214000,
"user_type": "sys_user",
"user_id": 1872912929188225026,
"nick_name": "陈奕迅",
"phone_number": "15837557232",
"dept_id": 103,
"update_by": 1,
"email": "chenyixun@163.com",
"status": "1"
}, {
"tenant_id": "000000",
"del_flag": "0",
"create_time": 1735372278000,
"create_dept": 103,
"user_name": "linjunjie",
"sex": "1",
"login_date": 1731411199000,
"remark": "我是林俊杰，江南一响，青春回响",
"avatar": 10086,
"login_ip": "0:0:0:0:0:0:0:1",
"create_by": 1,
"password": "e10adc3949ba59abbe56e057f20f883e",
"update_time": 1735372278000,
"user_type": "sys_user",
"user_id": 1872913195568472066,
"nick_name": "林俊杰",
"phone_number": "15837117232",
"dept_id": 103,
"update_by": 1,
"email": "linjunjie@163.com",
"status": "1"
}, {
"tenant_id": "000000",
"del_flag": "0",
"create_time": 1735372341000,
"create_dept": 103,
"user_name": "layVueSuper",
"sex": "1",
"login_date": 1731411199000,
"remark": "我是layVueSuper,暮色回响",
"avatar": 10086,
"login_ip": "0:0:0:0:0:0:0:1",
"create_by": 1,
"password": "e10adc3949ba59abbe56e057f20f883e",
"update_time": 1735372341000,
"user_type": "sys_user",
"user_id": 1872913463194427394,
"nick_name": "layVueSuper",
"phone_number": "15837127232",
"dept_id": 103,
"update_by": 1,
"email": "layVueSuper@163.com",
"status": "1"
}, {
"tenant_id": "000000",
"del_flag": "0",
"create_time": 1735372409000,
"create_dept": 103,
"user_name": "zhangyunjing",
"sex": "1",
"login_date": 1731411199000,
"remark": "我是张芸京,偏爱永不落席",
"avatar": 10086,
"login_ip": "0:0:0:0:0:0:0:1",
"create_by": 1,
"password": "e10adc3949ba59abbe56e057f20f883e",
"update_time": 1735372409000,
"user_type": "sys_user",
"user_id": 1872913747660513281,
"nick_name": "张芸京",
"phone_number": "15837137232",
"dept_id": 103,
"update_by": 1,
"email": "zhangyunjing@163.com",
"status": "1"
}, {
"tenant_id": "000000",
"del_flag": "0",
"create_time": 1735372459000,
"create_dept": 103,
"user_name": "weilian",
"sex": "1",
"login_date": 1731411199000,
"remark": "我是韦礼安,如果可以-新星崛起",
"avatar": 10086,
"login_ip": "0:0:0:0:0:0:0:1",
"create_by": 1,
"password": "e10adc3949ba59abbe56e057f20f883e",
"update_time": 1735372459000,
"user_type": "sys_user",
"user_id": 1872913957228912641,
"nick_name": "韦礼安",
"phone_number": "15837137233",
"dept_id": 103,
"update_by": 1,
"email": "weilian@163.com",
"status": "1"
}, {
"tenant_id": "000000",
"del_flag": "0",
"create_time": 1735372508000,
"create_dept": 103,
"user_name": "wangfei",
"sex": "1",
"login_date": 1731411199000,
"remark": "我是王菲,如果可以-最后的天后",
"avatar": 10086,
"login_ip": "0:0:0:0:0:0:0:1",
"create_by": 1,
"password": "e10adc3949ba59abbe56e057f20f883e",
"update_time": 1735372508000,
"user_type": "sys_user",
"user_id": 1872914162380709890,
"nick_name": "王菲",
"phone_number": "15837137234",
"dept_id": 103,
"update_by": 1,
"email": "wangfei@163.com",
"status": "1"
}, {
"tenant_id": "000000",
"del_flag": "0",
"create_time": 1735372569000,
"create_dept": 103,
"user_name": "huge",
"sex": "1",
"login_date": 1731411199000,
"remark": "我是胡歌,仙剑-最后的古装",
"avatar": 10086,
"login_ip": "0:0:0:0:0:0:0:1",
"create_by": 1,
"password": "e10adc3949ba59abbe56e057f20f883e",
"update_time": 1735372569000,
"user_type": "sys_user",
"user_id": 1872914420015833090,
"nick_name": "胡歌",
"phone_number": "15837137236",
"dept_id": 103,
"update_by": 1,
"email": "huge@163.com",
"status": "1"
}, {
"tenant_id": "000000",
"del_flag": "0",
"create_time": 1735372652000,
"create_dept": 103,
"user_name": "zhongli",
"sex": "1",
"login_date": 1731411199000,
"remark": "我是钟离,璃月-岩王帝君",
"avatar": 10086,
"login_ip": "0:0:0:0:0:0:0:1",
"create_by": 1,
"password": "e10adc3949ba59abbe56e057f20f883e",
"update_time": 1735372652000,
"user_type": "sys_user",
"user_id": 1872914765664231425,
"nick_name": "钟离",
"phone_number": "15837137536",
"dept_id": 103,
"update_by": 1,
"email": "zhongli@163.com",
"status": "1"
}]代码示例：
/**
* AI对话式分析同步调用
*/
@RequestMapping("/zhi_pu_qa")
public String testInvoke(@RequestParam(value = "question", defaultValue = "2024年创建的用户信息有哪些?", required = false) String question,
@RequestParam(value = "dbName", defaultValue = "test_admin_123", required = false) String dbName) {
// 获取指定数据库的所有表名和表注释
String tablesAndComments = getTablesAndComments(dbName);
String notFindMsg = "知识库未找到相关信息";
if (CharSequenceUtil.isBlank(tablesAndComments)) {
return notFindMsg;
}
// 询问Ai获取指定问题和表的相似的相关的表信息
String tableInfosJson = handlerAiQuestion(tablesAndComments, question, false);
if (CharSequenceUtil.isBlank(tableInfosJson)) {
return notFindMsg;
}
// 处理AI回答的数据获取实际用的表
String tableNames = parseTableFromResponse(tableInfosJson);
if (CharSequenceUtil.isBlank(tableNames)) {
return notFindMsg;
}
// 根据表名称--获取对应的表结构的列名和注释
String columnsAndComments = getTableDdl(dbName, tableNames);
// 将问题询问AI关联到那几张表--定位
String result = handlerAiQuestion(columnsAndComments, question, true);
if (result == null) {
return "接口调用失败，请检查日志!";
}
// 解析获取到的SQL
String sql = parseSqlFromResponse(result);
if (sql == null) {
return "解析SQL失败，请检查AI返回的内容!";
}
// 调用JDBC连接执行该SQL拿到结果
List<Map<String, Object>> resultList = executeSql(sql);
// 将结果转换为JSON字符串返回
return JSONUtil.toJsonStr(resultList);
}你还想要完整示例吧
package com.gt.quality.controller;import cn.dev33.satoken.annotation.SaIgnore;import cn.hutool.core.collection.CollUtil;import cn.hutool.core.text.CharSequenceUtil;import cn.hutool.http.HttpResponse;import cn.hutool.http.HttpUtil;import cn.hutool.http.Method;import cn.hutool.json.JSONObject;import cn.hutool.json.JSONUtil;import com.gt.quality.config.ZhiPuAIConstant;import lombok.extern.slf4j.Slf4j;import org.springframework.beans.factory.annotation.Value;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RequestParam;import org.springframework.web.bind.annotation.RestController;import org.springframework.web.servlet.mvc.method.annotation.SseEmitter;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.Statement;import java.util.ArrayList;import java.util.Arrays;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.Objects;import java.util.stream.Collectors;/** * 万里悲秋常作客,百年多病独登台 * * @author : makeJava */@RestController@RequestMapping("/ai")@Slf4j@SaIgnorepublic class ZhiPuAiController { // 请自定义自己的业务id private static final String REQUEST_ID_TEMPLATE = "guo_tong_%d"; private static final String COMPLETIONS_URL = "https://open.bigmodel.cn/api/paas/v4/chat/completions"; @Value("${spring.datasource.url}") private String dbUrl; @Value("${spring.datasource.username}") private String dbUsername; @Value("${spring.datasource.password}") private String dbPassword; /**
* AI对话式分析同步调用
*/
@RequestMapping("/zhi_pu_qa")
public String testInvoke(@RequestParam(value = "question", defaultValue = "2024年创建的用户信息有哪些?", required = false) String question,
@RequestParam(value = "dbName", defaultValue = "test_admin_123", required = false) String dbName) {
// 获取指定数据库的所有表名和表注释
String tablesAndComments = getTablesAndComments(dbName);
String notFindMsg = "知识库未找到相关信息";
if (CharSequenceUtil.isBlank(tablesAndComments)) {
return notFindMsg;
}
// 询问Ai获取指定问题和表的相似的相关的表信息
String tableInfosJson = handlerAiQuestion(tablesAndComments, question, false);
if (CharSequenceUtil.isBlank(tableInfosJson)) {
return notFindMsg;
}
// 处理AI回答的数据获取实际用的表
String tableNames = parseTableFromResponse(tableInfosJson);
if (CharSequenceUtil.isBlank(tableNames)) {
return notFindMsg;
}
// 根据表名称--获取对应的表结构的列名和注释
String columnsAndComments = getTableDdl(dbName, tableNames);
// 将问题询问AI关联到那几张表--定位
String result = handlerAiQuestion(columnsAndComments, question, true);
if (result == null) {
return "接口调用失败，请检查日志!";
}
// 解析获取到的SQL
String sql = parseSqlFromResponse(result);
if (sql == null) {
return "解析SQL失败，请检查AI返回的内容!";
}
// 调用JDBC连接执行该SQL拿到结果
List<Map<String, Object>> resultList = executeSql(sql);
// 将结果转换为JSON字符串返回
return JSONUtil.toJsonStr(resultList);
} /** * Description: * * @author: makeJava * @date: 2025-03-29 16:55:35 * @return: */ private static String handlerAiQuestion(String params, String question, boolean createSql) { Map body = new HashMap(); body.put("model", "glm-4-flash"); Object messages; if (createSql) { messages = buildSqlParam(params, question); } else { messages = getSelectTableNames(params, question); } body.put("messages", messages); body.put("request_id", String.format(REQUEST_ID_TEMPLATE, System.currentTimeMillis())); body.put("do_sample", true); body.put("stream", false); body.put("temperature", 0.95); body.put("max_tokens", 4095); Map responseFormat = new HashMap(); responseFormat.put("type", "json_object"); body.put("response_format", responseFormat); // function、retrieval、web_search。 body.put("type", "web_search"); String result = "null"; try (HttpResponse response = HttpUtil.createRequest(Method.POST, COMPLETIONS_URL) .body(JSONUtil.toJsonStr(body)) .header("Authorization", "Bearer " + ZhiPuAIConstant.ZHI_PU_AI_API_KEY) .execute()) { // 使用 try-with-resources 确保资源自动关闭 result = response.body(); } catch (Exception e) { log.error("调用接口失败: {}", e.getMessage(), e); return null; } log.info("调用接口返回: {}", result); return result; } /** * 根据问题获取关联到要查询的表名 */ private static Object getSelectTableNames(String tableNames, String question) { List messages = new ArrayList(); Map msgItem = new HashMap(); msgItem.put("role", "user"); msgItem.put("content", "请你作为数据分析师，" + "现在数据库里面存在表这些：" + tableNames + ";帮助查询出" + question + ";具体输入格式返回为JSON,示例->[{'tableName':'table01'},{'tableName':'table02'}]]。"); messages.add(msgItem); return messages; } private static List buildSqlParam(String tableInfos, String question) { List messages = new ArrayList(); Map msgItem = new HashMap(); msgItem.put("role", "user"); msgItem.put("content", "请你作为数据分析师，" + "现在数据库的表结构是这样：" + tableInfos + ";帮助查询出" + question + "，具体输入的内容为标准的SQL即可。"); messages.add(msgItem); return messages; } /** * 获取数据库的表名和注释 * * @param databaseName 数据库名称 * @return 表名和注释的JSON字符串 */ public String getTablesAndComments(String databaseName) { String sql = "SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '" + databaseName + "'"; List resultList = executeSql(sql); return JSONUtil.toJsonStr(resultList); } /** * 获取指定表的列名和注释 * * @param tableName 表名 * @return 列名和注释的JSON字符串 */ public String getColumnsAndComments(String databaseName, String tableName) { String sql = "SELECT COLUMN_NAME, COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '" + databaseName + "' AND TABLE_NAME = '" + tableName + "'"; List resultList = executeSql(sql); return JSONUtil.toJsonStr(resultList); } /** * 从AI返回的JSON中解析表信息 * * @param response AI返回的JSON字符串 * @return 解析后的表信息字符串 */ @SuppressWarnings("unchecked") private String parseTableFromResponse(String response) { try { Map responseMap = JSONUtil.toBean(response, Map.class); List choices = (List) responseMap.get("choices"); if (choices != null && !choices.isEmpty()) { Map choice = choices.get(0); Map message = (Map) choice.get("message"); if (message != null) { String content = (String) message.get("content"); if (content.contains("[")) { if (content.startsWith("\n")) { content = content.replace("\n", ""); } List list = JSONUtil.toList(content, JSONObject.class); return list.stream() .map(jsonObject -> jsonObject.getStr("tableName")) .collect(Collectors.joining(",")); } log.info("解析JSON---->: {}", content); } } } catch (Exception e) { log.error("解析JSON失败: {}", e.getMessage(), e); } return null; } /** * 从AI返回的JSON中解析SQL * * @param response AI返回的JSON字符串 * @return 解析后的SQL字符串 */ @SuppressWarnings("unchecked") private String parseSqlFromResponse(String response) { try { Map responseMap = JSONUtil.toBean(response, Map.class); List choices = (List) responseMap.get("choices"); if (choices != null && !choices.isEmpty()) { Map choice = choices.get(0); Map message = (Map) choice.get("message"); if (message != null) { String content = (String) message.get("content"); if (content != null) { if (content.startsWith("\n{")) { Map contentMap = JSONUtil.toBean((String) message.get("content"), Map.class); Object answer = contentMap.get("answer"); if (answer != null) { return answer.toString(); } Object sqlQuery = contentMap.get("sql_query"); if (sqlQuery != null){ return sqlQuery.toString(); } Object query = contentMap.get("query"); if (query != null){ return query.toString(); } return contentMap.toString(); } content = content.replace("sql\n", ""); return content; } } } } catch (Exception e) { log.error("解析JSON失败: {}", e.getMessage(), e); } return null; } /** * 获取指定表的建表语句DDL * * @param databaseName 数据库名称 * @param tableNameStr 表名 * @return 建表语句的JSON字符串 */ public String getTableDdl(String databaseName, String tableNameStr) { // 干扰表白名单--屏蔽掉 List whiteList = Arrays.asList("sys_role_dept", "sys_role_menu", "sys_oper_log", "file_export_template"); String[] split = tableNameStr.split(","); StringBuilder stringBuilder = new StringBuilder(); List sqlList = new ArrayList(); for (String tableName : split) { // 屏蔽掉干扰表 if (whiteList.contains(tableName)) { continue; } String sql = "SHOW CREATE TABLE `" + databaseName + "`.`" + tableName + "`"; sqlList.add(sql); } List resultList = executeSqlList(sqlList); if (CollUtil.isNotEmpty(resultList)) { for (Map mapRow : resultList) { String createTableStatement = (String) mapRow.get("Create Table"); stringBuilder.append(createTableStatement); } return stringBuilder.toString(); } return null; } /** * 执行SQL语句并返回结果 * * @param sqlList SQL语句 * @return 结果列表 */ private List executeSqlList(List sqlList) { List resultList = new ArrayList(); try (Connection connection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword); Statement statement = connection.createStatement(); ) { for (String sql : sqlList) { ResultSet resultSet = statement.executeQuery(sql); int columnCount = resultSet.getMetaData().getColumnCount(); while (resultSet.next()) { Map row = new HashMap(); for (int i = 1; i */ private List executeSql(String sql) { List resultList = new ArrayList(); try (Connection connection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(sql)) { int columnCount = resultSet.getMetaData().getColumnCount(); while (resultSet.next()) { Map row = new HashMap(); for (int i = 1; i 只有数据库里面有问题相关的表：就自动去找表，自动生成SQL：可以多张表单张表都可以哟
这里使用了多表联查的问题：
实际SQL :
SELECT d.dept_name FROM sys_user u JOIN sys_dept d ON u.dept_id = d.dept_id WHERE u.nick_name = '王力宏'
智普AI的返回的结果
{ "choices": [{ "finish_reason": "stop", "index": 0, "message": { "content": "\n{\n\"answer\": \"SELECT d.dept_name FROM sys_user u JOIN sys_dept d ON u.dept_id = d.dept_id WHERE u.nick_name = '王力宏'\"\n}\n", "role": "assistant" } }], "created": 1743241826, "id": "20250329175024947ec2101b9e4442", "model": "glm-4-flash", "request_id": "guo_tong_1743241930598", "usage": { "completion_tokens": 46, "prompt_tokens": 909, "total_tokens": 955 }}
这里再来秀一波 ：用户昵称叫王多鱼的这个人的角色名称?
SELECT r.role_name FROM sys_user u INNER JOIN sys_user_role ur ON u.user_id = ur.user_id INNER JOIN sys_role r ON ur.role_id = r.role_id WHERE u.nick_name = '王多鱼'
看看返回的
{
"choices": [{
"finish_reason": "stop",
"index": 0,
"message": {
"content": "\n{\n\"answer\": \"SELECT d.dept_name FROM sys_user u JOIN sys_dept d ON u.dept_id = d.dept_id WHERE u.nick_name = '王力宏'\"\n}\n",
"role": "assistant"
}
}],
"created": 1743241826,
"id": "20250329175024947ec2101b9e4442",
"model": "glm-4-flash",
"request_id": "guo_tong_1743241930598",
"usage": {
"completion_tokens": 46,
"prompt_tokens": 909,
"total_tokens": 955
}
}出处：http://www.cnblogs.com/gtnotgod】/个性签名：独学而无友，则孤陋而寡闻。做一个灵魂有趣的人！
如果觉得这篇文章对你有小小的帮助的话，记得在右下角点个“推荐”哦，博主在此感谢！
Java入门到入坟
万水千山总是情，打赏一分行不行，所以如果你心情还比较高兴，也是可以扫码打赏博主，哈哈哈(っ•̀ω•́)っ✎⁾⁾！
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！ 收藏一下 不知道什么时候能用到 感谢发布原创作品，程序园因你更精彩 谢谢楼主提供！
页:
[1]