3. LangChain4j-RAG，實現簡單的text-sql功能

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.ldx</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.ldx.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" : "張鐵牛上學期語文考試成績是多少\n\nAnswer 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)\n\n-- 創建成績表\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);\n\n-- 創建索引\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);\n\n-- 添加外鍵約束\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)\n\n-- 創建教師表\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);\n\n-- 創建考試安排表\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)\n\n-- 創建學生信息表\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);\n\n-- 創建專業表\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)\n\n-- 創建院系表\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);\n\n-- 創建班級表\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);\n\n-- 創建課程表\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和問題“張鐵牛上學期語文考試成績是多少”，我們需要從多個表中提取相關信息。以下是解決問題的步驟：\n\n1. **確定學生ID**：通過`students`表找到“張鐵牛”的`student_id`。\n2. **確定課程ID**：通過`courses`表找到“語文”課程的`course_id`。\n3. **確定考試ID**：通過`exam_arrangements`表找到與“語文”課程相關的考試ID，并確保是“上學期”的考試（可以通過`semester`字段判斷）。\n4. **查詢成績**：通過`scores`表找到該學生在該考試中的成績。\n\n以下是完整的SQL語句：\n\n```sql\n-- Step 1: 獲取張鐵牛的學生ID\nSELECT student_id \nFROM students \nWHERE name = '張鐵牛';\n\n-- Step 2: 獲取語文課程的課程ID\nSELECT course_id \nFROM courses \nWHERE course_name = '語文';\n\n-- Step 3: 獲取上學期語文考試的考試ID\nSELECT 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));\n\n-- Step 4: 查詢張鐵牛上學期語文考試的成績\nSELECT s.score \nFROM scores s\nJOIN students st ON s.student_id = st.student_id\nJOIN exam_arrangements ea ON s.exam_id = ea.exam_id\nJOIN courses c ON ea.course_id = c.course_id\nWHERE 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```\n\n### 解釋：\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`表中提取張鐵牛在上學期語文考試中的成績。\n\n請確保數據庫中已存在相關數據以供查詢。如果需要進一步調整或補充信息，請告知！"},"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.ldx.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`表中提取張鐵牛在上學期語文考試中的成績。

請確保數據庫中已存在相關數據以供查詢。如果需要進一步調整或補充信息，請告知！

3.小結

本章通過使用RAG特性實現一個簡單的text-sql功能實現, 直觀的感受了一下RAG的強大之處, 下一章我們將使用SpringBoot實現快速集成LangChain4J, 通過簡單的配置即可實現AI的調用。

4. 源碼

測試過程中的代碼已全部上傳至github, 歡迎點贊收藏倉庫地址: https://github.com/ludangxin/langchain4j-test

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);

posted @ 2025-06-11 23:25 張鐵牛閱讀(769) 評論(0) 收藏舉報

刷新頁面返回頂部

張鐵牛