Springboot后端管理项目+AI对话式分析

最新推荐文章于 2025-04-23 17:05:22 发布

user_admin_god

最新推荐文章于 2025-04-23 17:05:22 发布

阅读量1.2k

点赞数 33

文章标签： spring boot 人工智能数据库

本文链接：https://blog.youkuaiyun.com/user_admin_god/article/details/146704007

版权

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
@SaIgnore
public 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<String, Object> 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<String, Object> 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<Map<String, Object>> messages = new ArrayList<>();
        Map<String, Object> 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<Map<String, Object>> buildSqlParam(String tableInfos, String question) {
        List<Map<String, Object>> messages = new ArrayList<>();
        Map<String, Object> 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<Map<String, Object>> 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<Map<String, Object>> resultList = executeSql(sql);
        return JSONUtil.toJsonStr(resultList);
    }

    /**
     * 从AI返回的JSON中解析表信息
     *
     * @param response AI返回的JSON字符串
     * @return 解析后的表信息字符串
     */
    @SuppressWarnings("unchecked")
    private String parseTableFromResponse(String response) {
        try {
            Map<String, Object> responseMap = JSONUtil.toBean(response, Map.class);
            List<Map<String, Object>> choices = (List<Map<String, Object>>) responseMap.get("choices");
            if (choices != null && !choices.isEmpty()) {
                Map<String, Object> choice = choices.get(0);
                Map<String, Object> message = (Map<String, Object>) choice.get("message");
                if (message != null) {
                    String content = (String) message.get("content");
                    if (content.contains("[")) {
                        if (content.startsWith("\n")) {
                            content = content.replace("\n", "");
                        }
                        List<JSONObject> 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<String, Object> responseMap = JSONUtil.toBean(response, Map.class);
            List<Map<String, Object>> choices = (List<Map<String, Object>>) responseMap.get("choices");
            if (choices != null && !choices.isEmpty()) {
                Map<String, Object> choice = choices.get(0);
                Map<String, Object> message = (Map<String, Object>) choice.get("message");
                if (message != null) {
                    String content = (String) message.get("content");
                    if (content != null) {
                        if (content.startsWith("\n{")) {
                            Map<String, Object> 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<String> whiteList = Arrays.asList("sys_role_dept", "sys_role_menu", "sys_oper_log", "file_export_template");
        String[] split = tableNameStr.split(",");
        StringBuilder stringBuilder = new StringBuilder();
        List<String> sqlList = new ArrayList<>();
        for (String tableName : split) {
            // 屏蔽掉干扰表
            if (whiteList.contains(tableName)) {
                continue;
            }
            String sql = "SHOW CREATE TABLE `" + databaseName + "`.`" + tableName + "`";
            sqlList.add(sql);
        }
        List<Map<String, Object>> resultList = executeSqlList(sqlList);
        if (CollUtil.isNotEmpty(resultList)) {
            for (Map<String, Object> mapRow : resultList) {
                String createTableStatement = (String) mapRow.get("Create Table");
                stringBuilder.append(createTableStatement);
            }
            return stringBuilder.toString();
        }
        return null;
    }

    /**
     * 执行SQL语句并返回结果
     *
     * @param sqlList SQL语句
     * @return 结果列表
     */
    private List<Map<String, Object>> executeSqlList(List<String> sqlList) {
        List<Map<String, Object>> 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<String, Object> row = new HashMap<>();
                    for (int i = 1; i <= columnCount; i++) {
                        String columnName = resultSet.getMetaData().getColumnName(i);
                        Object columnValue = resultSet.getObject(i);
                        row.put(columnName, columnValue);
                    }
                    resultList.add(row);
                }
            }
        } catch (Exception e) {
            log.error("执行SQL失败: {}", e.getMessage(), e);
        }
        return resultList;
    }


    /**
     * 通用执行Ai 生成的SQL
     *
     * @param sql sql
     * @return List<Map < String, Object>>
     */
    private List<Map<String, Object>> executeSql(String sql) {
        List<Map<String, Object>> 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<String, Object> row = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = resultSet.getMetaData().getColumnName(i);
                    Object columnValue = resultSet.getObject(i);
                    row.put(columnName, columnValue);
                }
                resultList.add(row);
            }
        } catch (Exception e) {
            log.error("执行SQL失败: {}", e.getMessage(), e);
        }
        return resultList;
    }


    /**
     * sse调用
     */
    @RequestMapping("/zhi_pu_say")
    public SseEmitter testSseInvoke() {

        return new SseEmitter();
    }
}

效果>只有数据库里面有问题相关的表：就自动去找表，自动生成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
	}
}

在这里插入图片描述

这里再来秀一波：用户昵称叫王多鱼的这个人的角色名称?

在这里插入图片描述

看看返回的

{
	"choices": [{
		"finish_reason": "stop",
		"index": 0,
		"message": {
			"content": "\n{\n  \"answer\": \"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 = '王多鱼'\"\n}\n",
			"role": "assistant"
		}
	}],
	"created": 1743245014,
	"id": "202503291843319d1c4fe00dd94645",
	"model": "glm-4-flash",
	"request_id": "guo_tong_1743245117583",
	"usage": {
		"completion_tokens": 58,
		"prompt_tokens": 943,
		"total_tokens": 1001
	}
}