3. LangChain4j-RAG，实现简单的text-sql功能

技术分享 9个月前 (06-12) 0 999+

关注

1. 简介

前两章我们讲了如何使用LangChain4J进行AI交互, 其中包括

使用ChatLanguageModel、ChatMessage、ChatMemory等底层组件进行灵活/自由的与AI交互传送门
使用AI Services高级对象, 只关注业务逻辑, 使用简单的Api即可进行AI交互传送门

RAG（Retrieval-Augmented Generation，检索增强生成）: 是一种利用向量数据库召回专业知识并融入Prompt的大模型应用方案.

RAG其实分为两个阶段: 构建索引 和 检索

1.1 构建索引

构建索引的过程其实是构建知识库的过程如上图上半部分, 执行步骤如下:

数据准备 (数据形式不限如文件, 字符串, 数据表内容甚至是多媒体类型都可以)
读取数据内容并分割成文本块 (数据规模很可能非常庞大，整体存储具有难度，并且在查询的时候可能仅仅和其中的一个或几个段落有关系，所以需要分块处理将解析后的文档内容切分为适当的片段)
将分割的文本块向量化 (转换成机器能识别的内容)
将向量化的内容存储进向量数据库

1.2 检索

检索过程其实就是当我们进行提问的时候, 先使用之前准备好的知识库把提问内容相关的知识检索出来,然后一并将问题和检索结果交给大模型,辅助大模型生成回答如上图下半部分执行步骤如下:

用户提问
将用户提问的内容向量化
使用问题向量化的结果到向量数据库进行检索,并返回相关的知识
将问题和相关的知识组织成提示词交给大模型
大模型根据提示词思考并生成回答
响应用户

1.3 解决了什么问题

RAG核心是为了弥补传统通用大模型的两大短板：

知识时效性不足：传统大模型训练数据固定，难以及时获取最新信息（如近期的新闻、政策），而 RAG 通过实时检索外部知识库，让 AI 能调用最新数据生成回答。
专业领域知识受限：通用模型对垂直领域（如医疗、法律）的深度知识掌握有限，RAG 可连接行业专属数据库，提升回答的专业性和准确性。
简言之，RAG 让 AI “边查边答”，解决 “知识旧” 和 “不够专” 的问题，让回答更实时、更精准。

2. 实践案例

本章我们使用LangChain4J来实现一个简单的RAG需求: 整理一份学生成绩系统的数据库表ddl信息, 通过RAG实现让大模型帮我们生成sql语句, 比如向大模型提问: 张铁牛上学期语文考试成绩是多少? 希望AI给我们响应查询sql语句.

2.1 环境信息

使用SDK版本信息如下：

Java: 21 SpringBoot: 3.4.5 LangChain4j: 1.0.1 LLM: (使用在线的百炼(阿里)平台) 	embedding模型: text-embedding-v3   	chat模型: qwen-plus PGVector(postgresql版本的向量数据库): 0.8.0-pg17

2.1.1 部署PGVector

推荐使用docker-compose部署, yml文件如下:

version: '3' services:   pgvector:       container_name: pgvector     restart: always     image: pgvector/pgvector:0.8.0-pg17     privileged: true     ports:       - 5431:5432     environment:       POSTGRES_USER: root       POSTGRES_PASSWORD: 123456       PGDATA: /var/lib/postgresql/data/     volumes:       - ./data:/var/lib/postgresql/data/

2.2 Maven

<?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.5</version>         <relativePath/> <!-- lookup parent from repository -->     </parent>     <groupId>com.genuai</groupId>     <artifactId>langchain-test</artifactId>     <version>0.0.1-SNAPSHOT</version>     <name>langchain-test</name>     <description>langchain-test</description>      <properties>         <java.version>21</java.version>         <guava.version>33.0.0-jre</guava.version>     </properties>     <dependencyManagement>         <dependencies>             <dependency>                 <groupId>dev.langchain4j</groupId>                 <artifactId>langchain4j-bom</artifactId>                 <version>1.0.1</version>                 <type>pom</type>                 <scope>import</scope>             </dependency>         </dependencies>     </dependencyManagement>      <dependencies>         <dependency>             <groupId>org.springframework.boot</groupId>             <artifactId>spring-boot-starter-web</artifactId>         </dependency>          <dependency>             <groupId>dev.langchain4j</groupId>             <artifactId>langchain4j-open-ai-spring-boot-starter</artifactId>         </dependency>          <dependency>             <groupId>dev.langchain4j</groupId>             <artifactId>langchain4j-spring-boot-starter</artifactId>         </dependency>          <dependency>             <groupId>dev.langchain4j</groupId>             <artifactId>langchain4j-open-ai</artifactId>         </dependency>          <dependency>             <groupId>com.google.guava</groupId>             <artifactId>guava</artifactId>             <version>${guava.version}</version>         </dependency>          <dependency>             <groupId>dev.langchain4j</groupId>             <artifactId>langchain4j-reactor</artifactId>         </dependency>          <dependency>             <groupId>dev.langchain4j</groupId>             <artifactId>langchain4j-pgvector</artifactId>         </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>     </dependencies>      <build>         <plugins>             <plugin>                 <groupId>org.apache.maven.plugins</groupId>                 <artifactId>maven-compiler-plugin</artifactId>             </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>

2.3 构建模型和存储对象

private static ChatModel chatModel;  private static EmbeddingModel embeddingModel;  private static EmbeddingStore<TextSegment> embeddingStore;  private static JdkHttpClientBuilder jdkHttpClientBuilder;  @BeforeAll public static void init_embed_model() {     jdkHttpClientBuilder = JdkHttpClient             .builder()             .httpClientBuilder(HttpClient.newBuilder());     chatModel = OpenAiChatModel             .builder()             .httpClientBuilder(jdkHttpClientBuilder)             .apiKey(System.getenv("LLM_API_KEY"))             .modelName("qwen-plus")             .baseUrl("https://dashscope.aliyuncs.com/compatible-mode/v1")             // 打印请求日志             //.logRequests(true)             // 打印响应日志             //.logResponses(true)             .build();     embeddingModel = OpenAiEmbeddingModel             .builder()             .httpClientBuilder(jdkHttpClientBuilder)             .apiKey(System.getenv("LLM_API_KEY"))             .modelName("text-embedding-v3")             .baseUrl("https://dashscope.aliyuncs.com/compatible-mode/v1")             .build();     embeddingStore = PgVectorEmbeddingStore             .builder()             .host("localhost")                           // 必需：PostgresSQL 实例的主机             .port(5431)                                  // 必需：PostgresSQL 实例的端口             .database("postgres")                        // 必需：数据库名称             .user("root")                                // 必需：数据库用户             .password("123456")                          // 必需：数据库密码             .table("my_sql_embeddings")                  // 必需：存储嵌入的表名             .dimension(embeddingModel.dimension())       // 必需：嵌入的维度             .metadataStorageConfig(DefaultMetadataStorageConfig.defaultConfig()) // 将元数据存储配置             .build(); }

2.4 测试向量化

测试使用向量模型将sql信息转为向量数据

@Test public void should_print_embedding_content_when_embed_sql_resource() {     // 资源信息在文章末尾     final Document document = ClassPathDocumentLoader.loadDocument("student_ddl.sql");     // 创建 SQL 内容的分割器     DocumentSplitter splitter = new DocumentByRegexSplitter(             ";",     // 分割方式(正则)             ";",     // 连接方式(分割完之后每段内容末尾的分隔符)             2000,    // 每段内容最大片段长度             100      // 相邻片段之间的最大重叠字符数 (为了保证内容的完整性 分割时片段之间可能会重叠)     );     final List<TextSegment> textSegments = splitter.split(document);     final Response<List<Embedding>> embedResult = embeddingModel.embedAll(textSegments);     final List<Embedding> content = embedResult.content();     log.info("embedding content: {}", content); }

测试结果如下:

[main] INFO com.genuai.langchaintest.rag.AiRagTest -- embedding content: [Embedding { vector = [-0.05850703, -0.0043222117, -0.054852795, 0.0036689683, -0.07005912, -0.015255443, -0.0048452974, 0.060707428,  ... -0.0153250545, -0.06653458, -0.0023748502, -0.011913025, 0.052188545, 0.02347709, -0.0043522767, -0.017399414, 0.028711455, 0.0027141145, 0.008481608, 0.002006506, -0.00725541, 0.011253882] }]

2.5 测试构建索引

@Test public void should_store_vector_when_embed_sql_resource() {   final Document document = ClassPathDocumentLoader.loadDocument("student_ddl.sql");   DocumentSplitter splitter = new DocumentByRegexSplitter(";",";", 2000, 100);   final List<TextSegment> textSegments = splitter.split(document);   final Response<List<Embedding>> embedResult = embeddingModel.embedAll(textSegments);   final List<Embedding> content = embedResult.content();   // 将向量化结果和元数据一并存储到向量库   embeddingStore.addAll(content, textSegments); }

测试结果如下:

2.6 测试检索

@Test public void should_return_sql_when_chat_embed_sql_resource() {     ContentRetriever contentRetriever = EmbeddingStoreContentRetriever             .builder()             .embeddingStore(embeddingStore)             .embeddingModel(embeddingModel)             // 最大返回多少条相关的结果             .maxResults(10)             // 最小的得分(检索后每条数据都会有跟当前问题匹配度的得分, 得分越高越相近)             .minScore(0.65)             .build(); 	// 使用ai services 进行ai call     AiAssistantServiceWithMemoryTest assistant = AiServices             .builder(AiAssistantServiceWithMemoryTest.class)             .chatModel(chatModel)             // 注入内容检索器             .contentRetriever(contentRetriever)             .chatMemoryProvider(memoryId -> MessageWindowChatMemory                     .builder()                     .id(memoryId)                     .maxMessages(10)                     .build())             .build();     String id = "zhangtieniu-01";     final String q = "张铁牛上学期语文考试成绩是多少";     final String a = assistant.chatSql(id, q);     log.info("call ai q:{}na: {}", q, a); }

AI Services

import dev.langchain4j.service.MemoryId; import dev.langchain4j.service.SystemMessage; import dev.langchain4j.service.UserMessage; import dev.langchain4j.service.spring.AiService;  /**  * ai svc  *  * @author ludangxin  * @date 2025/6/5  */ @AiService public interface AiAssistantServiceWithMemoryTest {     String chat(@MemoryId String memoryId, @UserMessage String message);      @SystemMessage("你是一名sql分析专家 我会将sql相关的ddl给你, 需要你根据ddl生成合理且可执行的sql语句并返回")     String chatSql(@MemoryId String memoryId, @UserMessage String message); }

测试结果如下(开启了请求相应日志) :

[main] INFO dev.langchain4j.http.client.log.LoggingHttpClient -- HTTP request: - method: POST - url: https://dashscope.aliyuncs.com/compatible-mode/v1/chat/completions - headers: [Authorization: Beare...ff], [User-Agent: langchain4j-openai], [Content-Type: application/json] - body: {   "model" : "qwen-plus",   "messages" : [ {     "role" : "system",     "content" : "你是一名sql分析专家 我会将sql相关的ddl给你, 需要你根据ddl生成合理且可执行的sql语句并返回"   }, {     "role" : "user",     "content" : "张铁牛上学期语文考试成绩是多少nnAnswer using the following information:nALTER TABLE courses ADD CONSTRAINT fk_courses_department_id FOREIGN KEY (department_id) REFERENCES departments(department_id);nALTER TABLE courses ADD CONSTRAINT fk_courses_teacher_id FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id);nALTER TABLE teachers ADD CONSTRAINT fk_teachers_department_id FOREIGN KEY (department_id) REFERENCES departments(department_id);nALTER TABLE exam_arrangements ADD CONSTRAINT fk_exam_arrangements_course_id FOREIGN KEY (course_id) REFERENCES courses(course_id);nALTER TABLE scores ADD CONSTRAINT fk_scores_student_id FOREIGN KEY (student_id) REFERENCES students(student_id);nALTER TABLE scores ADD CONSTRAINT fk_scores_exam_id FOREIGN KEY (exam_id) REFERENCES exam_arrangements(exam_id)nn-- 创建成绩表nCREATE TABLE scores (n                        score_id INT PRIMARY KEY AUTO_INCREMENT,n                        student_id INT NOT NULL COMMENT '学生ID',n                        exam_id INT NOT NULL COMMENT '考试ID',n                        score DECIMAL(5,2) COMMENT '成绩',n                        score_type ENUM('原始分', '平时分', '卷面分', '最终分') NOT NULL DEFAULT '最终分' COMMENT '成绩类型',n                        remark VARCHAR(200) COMMENT '备注',n                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',n                        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',n                        UNIQUE KEY unique_student_exam (student_id, exam_id)n);nn-- 创建索引nCREATE INDEX idx_students_student_no ON students(student_no);nCREATE INDEX idx_students_major_id ON students(major_id);nCREATE INDEX idx_students_class_id ON students(class_id);nCREATE INDEX idx_majors_department_id ON majors(department_id);nCREATE INDEX idx_classes_major_id ON classes(major_id);nCREATE INDEX idx_courses_department_id ON courses(department_id);nCREATE INDEX idx_courses_teacher_id ON courses(teacher_id);nCREATE INDEX idx_teachers_department_id ON teachers(department_id);nCREATE INDEX idx_exam_arrangements_course_id ON exam_arrangements(course_id);nCREATE INDEX idx_scores_student_id ON scores(student_id);nCREATE INDEX idx_scores_exam_id ON scores(exam_id);nn-- 添加外键约束nALTER TABLE students ADD CONSTRAINT fk_students_major_id FOREIGN KEY (major_id) REFERENCES majors(major_id);nALTER TABLE students ADD CONSTRAINT fk_students_class_id FOREIGN KEY (class_id) REFERENCES classes(class_id);nALTER TABLE majors ADD CONSTRAINT fk_majors_department_id FOREIGN KEY (department_id) REFERENCES departments(department_id);nALTER TABLE classes ADD CONSTRAINT fk_classes_major_id FOREIGN KEY (major_id) REFERENCES majors(major_id)nn-- 创建教师表nCREATE TABLE teachers (n                          teacher_id INT PRIMARY KEY AUTO_INCREMENT,n                          teacher_no VARCHAR(20) UNIQUE NOT NULL COMMENT '教师编号',n                          name VARCHAR(50) NOT NULL COMMENT '姓名',n                          gender ENUM('男', '女', '其他') COMMENT '性别',n                          title VARCHAR(50) COMMENT '职称',n                          department_id INT NOT NULL COMMENT '所属院系ID',n                          email VARCHAR(100) UNIQUE COMMENT '邮箱',n                          phone VARCHAR(20) COMMENT '电话',n                          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',n                          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'n);nn-- 创建考试安排表nCREATE TABLE exam_arrangements (n                                   exam_id INT PRIMARY KEY AUTO_INCREMENT,n                                   course_id INT NOT NULL COMMENT '课程ID',n                                   exam_date DATE NOT NULL COMMENT '考试日期',n                                   start_time TIME NOT NULL COMMENT '开始时间',n                                   end_time TIME NOT NULL COMMENT '结束时间',n                                   exam_room VARCHAR(50) NOT NULL COMMENT '考场',n                                   invigilator VARCHAR(100) COMMENT '监考老师',n                                   exam_type ENUM('期中', '期末', '补考', '重修') NOT NULL COMMENT '考试类型',n                                   academic_year VARCHAR(20) NOT NULL COMMENT '学年',n                                   semester TINYINT NOT NULL COMMENT '学期',n                                   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',n                                   updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'n)nn-- 创建学生信息表nCREATE TABLE students (n                          student_id INT PRIMARY KEY AUTO_INCREMENT,n                          student_no VARCHAR(20) UNIQUE NOT NULL COMMENT '学号',n                          name VARCHAR(50) NOT NULL COMMENT '姓名',n                          gender ENUM('男', '女', '其他') COMMENT '性别',n                          birth_date DATE COMMENT '出生日期',n                          email VARCHAR(100) UNIQUE COMMENT '邮箱',n                          phone VARCHAR(20) COMMENT '电话',n                          address VARCHAR(200) COMMENT '地址',n                          enrollment_date DATE NOT NULL COMMENT '入学日期',n                          major_id INT NOT NULL COMMENT '专业ID',n                          class_id INT NOT NULL COMMENT '班级ID',n                          status ENUM('在读', '休学', '毕业', '退学') NOT NULL DEFAULT '在读' COMMENT '状态',n                          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',n                          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'n);nn-- 创建专业表nCREATE TABLE majors (n                        major_id INT PRIMARY KEY AUTO_INCREMENT,n                        major_name VARCHAR(50) UNIQUE NOT NULL COMMENT '专业名称',n                        department_id INT NOT NULL COMMENT '院系ID',n                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',n                        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'n)nn-- 创建院系表nCREATE TABLE departments (n                             department_id INT PRIMARY KEY AUTO_INCREMENT,n                             department_name VARCHAR(50) UNIQUE NOT NULL COMMENT '院系名称',n                             dean VARCHAR(50) COMMENT '院长',n                             phone VARCHAR(20) COMMENT '联系电话',n                             email VARCHAR(100) COMMENT '邮箱',n                             created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',n                             updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'n);nn-- 创建班级表nCREATE TABLE classes (n                         class_id INT PRIMARY KEY AUTO_INCREMENT,n                         class_name VARCHAR(50) NOT NULL COMMENT '班级名称',n                         major_id INT NOT NULL COMMENT '专业ID',n                         grade INT NOT NULL COMMENT '年级',n                         head_teacher VARCHAR(50) COMMENT '班主任',n                         created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',n                         updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'n);nn-- 创建课程表nCREATE TABLE courses (n                         course_id INT PRIMARY KEY AUTO_INCREMENT,n                         course_code VARCHAR(20) UNIQUE NOT NULL COMMENT '课程代码',n                         course_name VARCHAR(100) NOT NULL COMMENT '课程名称',n                         credit TINYINT NOT NULL COMMENT '学分',n                         course_type ENUM('必修课', '选修课', '公共课') NOT NULL COMMENT '课程类型',n                         department_id INT NOT NULL COMMENT '开课院系ID',n                         teacher_id INT NOT NULL COMMENT '授课教师ID',n                         created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',n                         updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'n)"   } ],   "stream" : false }  [main] INFO dev.langchain4j.http.client.log.LoggingHttpClient -- HTTP response: - status code: 200 - headers: [:status: 200], [content-length: 2644], [content-type: application/json], [date: Wed, 11 Jun 2025 02:20:11 GMT], [req-arrive-time: 1749608380626], [req-cost-time: 31767], [resp-start-time: 1749608412394], [server: istio-envoy], [set-cookie: acw_tc=c98b1f3b-b6e6-9271-a000-d232726b3d2c47d53afe44ffd30275d4f0a5315ca361;path=/;HttpOnly;Max-Age=1800], [vary: Origin,Access-Control-Request-Method,Access-Control-Request-Headers, Accept-Encoding], [x-dashscope-call-gateway: true], [x-envoy-upstream-service-time: 31762], [x-request-id: c98b1f3b-b6e6-9271-a000-d232726b3d2c] - body: {"choices":[{"message":{"role":"assistant","content":"根据提供的DDL和问题“张铁牛上学期语文考试成绩是多少”，我们需要从多个表中提取相关信息。以下是解决问题的步骤：nn1. **确定学生ID**：通过`students`表找到“张铁牛”的`student_id`。n2. **确定课程ID**：通过`courses`表找到“语文”课程的`course_id`。n3. **确定考试ID**：通过`exam_arrangements`表找到与“语文”课程相关的考试ID，并确保是“上学期”的考试（可以通过`semester`字段判断）。n4. **查询成绩**：通过`scores`表找到该学生在该考试中的成绩。nn以下是完整的SQL语句：nn```sqln-- Step 1: 获取张铁牛的学生IDnSELECT student_id nFROM students nWHERE name = '张铁牛';nn-- Step 2: 获取语文课程的课程IDnSELECT course_id nFROM courses nWHERE course_name = '语文';nn-- Step 3: 获取上学期语文考试的考试IDnSELECT exam_id nFROM exam_arrangements nWHERE course_id = (SELECT course_id FROM courses WHERE course_name = '语文') n  AND semester = (SELECT MAX(semester) FROM exam_arrangements WHERE academic_year = (SELECT academic_year FROM exam_arrangements ORDER BY exam_date DESC LIMIT 1));nn-- Step 4: 查询张铁牛上学期语文考试的成绩nSELECT s.score nFROM scores snJOIN students st ON s.student_id = st.student_idnJOIN exam_arrangements ea ON s.exam_id = ea.exam_idnJOIN courses c ON ea.course_id = c.course_idnWHERE st.name = '张铁牛'n  AND c.course_name = '语文'n  AND ea.semester = (SELECT MAX(semester) FROM exam_arrangements WHERE academic_year = (SELECT academic_year FROM exam_arrangements ORDER BY exam_date DESC LIMIT 1));n```nn### 解释：n1. **Step 1**：通过`students`表查找“张铁牛”的`student_id`。n2. **Step 2**：通过`courses`表查找“语文”课程的`course_id`。n3. **Step 3**：通过`exam_arrangements`表查找与“语文”课程相关且属于“上学期”的考试ID。n   - 使用`MAX(semester)`确保获取的是最近学年的上学期考试。n4. **Step 4**：将以上结果联合查询，从`scores`表中提取张铁牛在上学期语文考试中的成绩。nn请确保数据库中已存在相关数据以供查询。如果需要进一步调整或补充信息，请告知！"},"finish_reason":"stop","index":0,"logprobs":null}],"object":"chat.completion","usage":{"prompt_tokens":1498,"completion_tokens":558,"total_tokens":2056,"prompt_tokens_details":{"cached_tokens":0}},"created":1749608412,"system_fingerprint":null,"model":"qwen-plus","id":"chatcmpl-c98b1f3b-b6e6-9271-a000-d232726b3d2c"}  [main] INFO com.genuai.langchaintest.rag.AiRagTest -- call ai q:张铁牛上学期语文考试成绩是多少 a: 根据提供的DDL和问题“张铁牛上学期语文考试成绩是多少”，我们需要从多个表中提取相关信息。以下是解决问题的步骤：  1. **确定学生ID**：通过`students`表找到“张铁牛”的`student_id`。 2. **确定课程ID**：通过`courses`表找到“语文”课程的`course_id`。 3. **确定考试ID**：通过`exam_arrangements`表找到与“语文”课程相关的考试ID，并确保是“上学期”的考试（可以通过`semester`字段判断）。 4. **查询成绩**：通过`scores`表找到该学生在该考试中的成绩。  以下是完整的SQL语句：  ```sql -- Step 1: 获取张铁牛的学生ID SELECT student_id  FROM students  WHERE name = '张铁牛';  -- Step 2: 获取语文课程的课程ID SELECT course_id  FROM courses  WHERE course_name = '语文';  -- Step 3: 获取上学期语文考试的考试ID SELECT exam_id  FROM exam_arrangements  WHERE course_id = (SELECT course_id FROM courses WHERE course_name = '语文')    AND semester = (SELECT MAX(semester) FROM exam_arrangements WHERE academic_year = (SELECT academic_year FROM exam_arrangements ORDER BY exam_date DESC LIMIT 1));  -- Step 4: 查询张铁牛上学期语文考试的成绩 SELECT s.score  FROM scores s JOIN students st ON s.student_id = st.student_id JOIN exam_arrangements ea ON s.exam_id = ea.exam_id JOIN courses c ON ea.course_id = c.course_id WHERE st.name = '张铁牛'   AND c.course_name = '语文'   AND ea.semester = (SELECT MAX(semester) FROM exam_arrangements WHERE academic_year = (SELECT academic_year FROM exam_arrangements ORDER BY exam_date DESC LIMIT 1)); ```  ### 解释： 1. **Step 1**：通过`students`表查找“张铁牛”的`student_id`。 2. **Step 2**：通过`courses`表查找“语文”课程的`course_id`。 3. **Step 3**：通过`exam_arrangements`表查找与“语文”课程相关且属于“上学期”的考试ID。    - 使用`MAX(semester)`确保获取的是最近学年的上学期考试。 4. **Step 4**：将以上结果联合查询，从`scores`表中提取张铁牛在上学期语文考试中的成绩。  请确保数据库中已存在相关数据以供查询。如果需要进一步调整或补充信息，请告知！

小结

本章通过使用RAG特性实现一个简单的text-sql功能实现, 直观的感受了一下RAG的强大之处, 下一章我们将使用SpringBoot实现快速集成LangChain4J, 通过简单的配置即可实现AI的调用。

student_ddl.sql

-- 创建学生信息表 CREATE TABLE students (       student_id INT PRIMARY KEY AUTO_INCREMENT,       student_no VARCHAR(20) UNIQUE NOT NULL COMMENT '学号',       name VARCHAR(50) NOT NULL COMMENT '姓名',       gender ENUM('男', '女', '其他') COMMENT '性别',       birth_date DATE COMMENT '出生日期',       email VARCHAR(100) UNIQUE COMMENT '邮箱',       phone VARCHAR(20) COMMENT '电话',       address VARCHAR(200) COMMENT '地址',       enrollment_date DATE NOT NULL COMMENT '入学日期',       major_id INT NOT NULL COMMENT '专业ID',       class_id INT NOT NULL COMMENT '班级ID',       status ENUM('在读', '休学', '毕业', '退学') NOT NULL DEFAULT '在读' COMMENT '状态',       created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',       updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间' );  -- 创建专业表 CREATE TABLE majors (       major_id INT PRIMARY KEY AUTO_INCREMENT,       major_name VARCHAR(50) UNIQUE NOT NULL COMMENT '专业名称',       department_id INT NOT NULL COMMENT '院系ID',       created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',       updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间' );  -- 创建院系表 CREATE TABLE departments (       department_id INT PRIMARY KEY AUTO_INCREMENT,       department_name VARCHAR(50) UNIQUE NOT NULL COMMENT '院系名称',       dean VARCHAR(50) COMMENT '院长',       phone VARCHAR(20) COMMENT '联系电话',       email VARCHAR(100) COMMENT '邮箱',       created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',       updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间' );  -- 创建班级表 CREATE TABLE classes (       class_id INT PRIMARY KEY AUTO_INCREMENT,       class_name VARCHAR(50) NOT NULL COMMENT '班级名称',       major_id INT NOT NULL COMMENT '专业ID',       grade INT NOT NULL COMMENT '年级',       head_teacher VARCHAR(50) COMMENT '班主任',       created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',       updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间' );  -- 创建课程表 CREATE TABLE courses (       course_id INT PRIMARY KEY AUTO_INCREMENT,       course_code VARCHAR(20) UNIQUE NOT NULL COMMENT '课程代码',       course_name VARCHAR(100) NOT NULL COMMENT '课程名称',       credit TINYINT NOT NULL COMMENT '学分',       course_type ENUM('必修课', '选修课', '公共课') NOT NULL COMMENT '课程类型',       department_id INT NOT NULL COMMENT '开课院系ID',       teacher_id INT NOT NULL COMMENT '授课教师ID',       created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',       updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间' );  -- 创建教师表 CREATE TABLE teachers (       teacher_id INT PRIMARY KEY AUTO_INCREMENT,       teacher_no VARCHAR(20) UNIQUE NOT NULL COMMENT '教师编号',       name VARCHAR(50) NOT NULL COMMENT '姓名',       gender ENUM('男', '女', '其他') COMMENT '性别',       title VARCHAR(50) COMMENT '职称',       department_id INT NOT NULL COMMENT '所属院系ID',       email VARCHAR(100) UNIQUE COMMENT '邮箱',       phone VARCHAR(20) COMMENT '电话',       created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',       updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间' );  -- 创建考试安排表 CREATE TABLE exam_arrangements (       exam_id INT PRIMARY KEY AUTO_INCREMENT,       course_id INT NOT NULL COMMENT '课程ID',       exam_date DATE NOT NULL COMMENT '考试日期',       start_time TIME NOT NULL COMMENT '开始时间',       end_time TIME NOT NULL COMMENT '结束时间',       exam_room VARCHAR(50) NOT NULL COMMENT '考场',       invigilator VARCHAR(100) COMMENT '监考老师',       exam_type ENUM('期中', '期末', '补考', '重修') NOT NULL COMMENT '考试类型',       academic_year VARCHAR(20) NOT NULL COMMENT '学年',       semester TINYINT NOT NULL COMMENT '学期',       created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',       updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间' );  -- 创建成绩表 CREATE TABLE scores (       score_id INT PRIMARY KEY AUTO_INCREMENT,       student_id INT NOT NULL COMMENT '学生ID',       exam_id INT NOT NULL COMMENT '考试ID',       score DECIMAL(5,2) COMMENT '成绩',       score_type ENUM('原始分', '平时分', '卷面分', '最终分') NOT NULL DEFAULT '最终分' COMMENT '成绩类型',       remark VARCHAR(200) COMMENT '备注',       created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',       updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',       UNIQUE KEY unique_student_exam (student_id, exam_id) );  -- 创建索引 CREATE INDEX idx_students_student_no ON students(student_no); CREATE INDEX idx_students_major_id ON students(major_id); CREATE INDEX idx_students_class_id ON students(class_id); CREATE INDEX idx_majors_department_id ON majors(department_id); CREATE INDEX idx_classes_major_id ON classes(major_id); CREATE INDEX idx_courses_department_id ON courses(department_id); CREATE INDEX idx_courses_teacher_id ON courses(teacher_id); CREATE INDEX idx_teachers_department_id ON teachers(department_id); CREATE INDEX idx_exam_arrangements_course_id ON exam_arrangements(course_id); CREATE INDEX idx_scores_student_id ON scores(student_id); CREATE INDEX idx_scores_exam_id ON scores(exam_id);  -- 添加外键约束 ALTER TABLE students ADD CONSTRAINT fk_students_major_id FOREIGN KEY (major_id) REFERENCES majors(major_id); ALTER TABLE students ADD CONSTRAINT fk_students_class_id FOREIGN KEY (class_id) REFERENCES classes(class_id); ALTER TABLE majors ADD CONSTRAINT fk_majors_department_id FOREIGN KEY (department_id) REFERENCES departments(department_id); ALTER TABLE classes ADD CONSTRAINT fk_classes_major_id FOREIGN KEY (major_id) REFERENCES majors(major_id); ALTER TABLE courses ADD CONSTRAINT fk_courses_department_id FOREIGN KEY (department_id) REFERENCES departments(department_id); ALTER TABLE courses ADD CONSTRAINT fk_courses_teacher_id FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id); ALTER TABLE teachers ADD CONSTRAINT fk_teachers_department_id FOREIGN KEY (department_id) REFERENCES departments(department_id); ALTER TABLE exam_arrangements ADD CONSTRAINT fk_exam_arrangements_course_id FOREIGN KEY (course_id) REFERENCES courses(course_id); ALTER TABLE scores ADD CONSTRAINT fk_scores_student_id FOREIGN KEY (student_id) REFERENCES students(student_id); ALTER TABLE scores ADD CONSTRAINT fk_scores_exam_id FOREIGN KEY (exam_id) REFERENCES exam_arrangements(exam_id);

发表评论