一、使用Spring AI 实现 Text2Sql 自然语言查询数据（准备工作）
一、使用Spring AI 实现 Text2Sql 自然语言查询数据（准备工作）
==================================================================================
==================================================================================
参考资料：
==================================================================================
在LinuxmacOS和Windows上配置APIKey为环境变量-大模型服务平台百炼-阿里云 (aliyun.com)
springai如何支持dashscope,不使用ai-alibaba_spring.ai.dashscope.chat.options.model-CSDN博客
==================================================================================
1、创建SpringBoot工程
1.1、application.yml
server:
port: 8897
spring:
application:
name: springai_text2sql
# 数据库配置
datasource:
url: jdbc:mysql://localhost:3306/text2sql_db?connectTimeout=60000&socketTimeout=60000&allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
driver-class-name: com.mysql.cj.jdbc.Driver
username: root
password: [自己的密码]
# JPA 配置
jpa:
hibernate:
ddl-auto: validate
show-sql: true
properties:
hibernate:
format_sql: true
dialect: org.hibernate.dialect.MySQLDialect
ai:
openai:
# 注意去掉 /v1，Spring AI 会自动补
base-url: https://dashscope.aliyuncs.com/compatible-mode
api-key: ${BAILIAN_API_KEY}
chat:
options:
model: qwen-max
# dashscope:
# # 阿里百炼 api_key
# api-key: ${BAILIAN_API_KEY} # 从环境变量读取密钥
# 日志配置
logging:
level:
org.springframework.ai: ERROR
com.example: DEBUG
org.hibernate.SQL: DEBUG
org.hibernate.type.descriptor.sql.BasicBinder: TRACE
1.2、pom.xml
<?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.5.8</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>springai_text2sql</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>springai_text2sql</name>
<description>springai_text2sql</description>
<url/>
<licenses>
<license/>
</licenses>
<developers>
<developer/>
</developers>
<scm>
<connection/>
<developerConnection/>
<tag/>
<url/>
</scm>
<properties>
<java.version>17</java.version>
<spring-ai.version>1.1.0</spring-ai.version>
</properties>
<dependencies>
<!-- Spring Boot Starters -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-validation</artifactId>
</dependency>
<!-- Spring AI Dependencies -->
<dependency>
<groupId>org.springframework.ai</groupId>
<artifactId>spring-ai-starter-model-openai</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.ai</groupId>
<artifactId>spring-ai-starter-mcp-client</artifactId>
</dependency>
<!-- Database -->
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<scope>runtime</scope>
</dependency>
<!-- JSON Processing -->
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
</dependency>
<!-- Development Tools -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
<optional>true</optional>
</dependency>
<!-- Lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<!-- Testing -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.ai</groupId>
<artifactId>spring-ai-bom</artifactId>
<version>${spring-ai.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
</project>
2、新建测试数据库
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for departments
-- ----------------------------
DROP TABLE IF EXISTS `departments`;
CREATE TABLE `departments` (
`id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '部门ID',
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '部门名称',
`manager_id` bigint(0) NULL DEFAULT NULL COMMENT '部门经理ID',
`budget` decimal(15, 2) NOT NULL COMMENT '部门预算',
`location` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '办公地点',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `name`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '部门信息表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of departments
-- ----------------------------
INSERT INTO `departments` VALUES (1, '技术部', 7, 2000000.00, '北京');
INSERT INTO `departments` VALUES (2, '销售部', 3, 1500000.00, '上海');
INSERT INTO `departments` VALUES (3, '人事部', 5, 800000.00, '北京');
INSERT INTO `departments` VALUES (4, '财务部', 6, 600000.00, '北京');
INSERT INTO `departments` VALUES (5, '市场部', 8, 1000000.00, '上海');
-- ----------------------------
-- Table structure for employees
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
`id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '员工ID',
`name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '员工姓名',
`department` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '所属部门',
`position` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '职位',
`salary` decimal(10, 2) NOT NULL COMMENT '工资',
`hire_date` date NOT NULL COMMENT '入职日期',
`email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '邮箱地址',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `email`(`email`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '员工信息表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of employees
-- ----------------------------
INSERT INTO `employees` VALUES (1, '张三', '技术部', '高级工程师', 15000.00, '2022-01-15', 'zhangsan@company.com');
INSERT INTO `employees` VALUES (2, '李四', '技术部', '工程师', 12000.00, '2022-03-20', 'lisi@company.com');
INSERT INTO `employees` VALUES (3, '王五', '销售部', '销售经理', 18000.00, '2021-11-10', 'wangwu@company.com');
INSERT INTO `employees` VALUES (4, '赵六', '销售部', '销售代表', 10000.00, '2023-02-01', 'zhaoliu@company.com');
INSERT INTO `employees` VALUES (5, '钱七', '人事部', '人事经理', 16000.00, '2021-08-05', 'qianqi@company.com');
INSERT INTO `employees` VALUES (6, '孙八', '财务部', '会计师', 14000.00, '2022-06-15', 'sunba@company.com');
INSERT INTO `employees` VALUES (7, '周九', '技术部', '架构师', 25000.00, '2020-12-01', 'zhoujiu@company.com');
INSERT INTO `employees` VALUES (8, '吴十', '市场部', '市场专员', 11000.00, '2023-01-10', 'wushi@company.com');
-- ----------------------------
-- Table structure for project_members
-- ----------------------------
DROP TABLE IF EXISTS `project_members`;
CREATE TABLE `project_members` (
`id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '关系ID',
`project_id` bigint(0) NOT NULL COMMENT '项目ID',
`employee_id` bigint(0) NOT NULL COMMENT '员工ID',
`role` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '在项目中的角色',
`join_date` date NOT NULL COMMENT '加入项目日期',
PRIMARY KEY (`id`) USING BTREE,
INDEX `project_id`(`project_id`) USING BTREE,
INDEX `employee_id`(`employee_id`) USING BTREE,
CONSTRAINT `project_members_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `project_members_ibfk_2` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '项目成员关系表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of project_members
-- ----------------------------
INSERT INTO `project_members` VALUES (1, 1, 1, '开发负责人', '2023-01-01');
INSERT INTO `project_members` VALUES (2, 1, 2, '开发工程师', '2023-01-01');
INSERT INTO `project_members` VALUES (3, 1, 7, '技术架构师', '2023-01-01');
INSERT INTO `project_members` VALUES (4, 2, 1, '项目经理', '2023-03-01');
INSERT INTO `project_members` VALUES (5, 2, 2, '开发工程师', '2023-03-01');
INSERT INTO `project_members` VALUES (6, 3, 7, '技术负责人', '2022-10-01');
INSERT INTO `project_members` VALUES (7, 3, 6, '数据分析师', '2022-10-01');
INSERT INTO `project_members` VALUES (8, 4, 3, '项目经理', '2023-02-15');
INSERT INTO `project_members` VALUES (9, 4, 4, '业务分析师', '2023-02-15');
INSERT INTO `project_members` VALUES (10, 5, 6, '项目经理', '2022-12-01');
-- ----------------------------
-- Table structure for projects
-- ----------------------------
DROP TABLE IF EXISTS `projects`;
CREATE TABLE `projects` (
`id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '项目ID',
`name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '项目名称',
`description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '项目描述',
`start_date` date NOT NULL COMMENT '开始日期',
`end_date` date NOT NULL COMMENT '结束日期',
`status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '项目状态',
`budget` decimal(15, 2) NOT NULL COMMENT '项目预算',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '项目信息表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of projects
-- ----------------------------
INSERT INTO `projects` VALUES (1, '电商平台重构', '重构现有电商平台，提升性能和用户体验', '2023-01-01', '2023-06-30', '进行中', 500000.00);
INSERT INTO `projects` VALUES (2, '移动端应用开发', '开发公司移动端应用', '2023-03-01', '2023-08-31', '进行中', 300000.00);
INSERT INTO `projects` VALUES (3, '数据分析系统', '构建企业数据分析平台', '2022-10-01', '2023-02-28', '已完成', 200000.00);
INSERT INTO `projects` VALUES (4, '客户管理系统', '升级客户关系管理系统', '2023-02-15', '2023-07-15', '进行中', 150000.00);
INSERT INTO `projects` VALUES (5, '财务系统优化', '优化财务系统性能', '2022-12-01', '2023-01-31', '已完成', 80000.00);
SET FOREIGN_KEY_CHECKS = 1;
本文来自博客园，作者：老羅，转载请注明原文链接：https://www.cnblogs.com/laoluo2025/p/19264021
浙公网安备 33010602011771号