Build a Text‑to‑SQL Chatbot with Spring AI and DeepSeek LLM

1. Project Overview

Modern applications increasingly use natural‑language interfaces, allowing non‑technical users to query data by speaking or typing. A text‑to‑SQL chatbot bridges human language and databases by converting natural questions into executable SQL statements using a large language model (LLM).

2. Project Setup

2.1 Add Dependencies

<dependency>
  <groupId>org.springframework.ai</groupId>
  <artifactId>spring-ai-starter-model-deepseek</artifactId>
</dependency>
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <scope>runtime</scope>
</dependency>
<dependency>
  <groupId>org.flywaydb</groupId>
  <artifactId>flyway-core</artifactId>
</dependency>
<dependency>
  <groupId>org.flywaydb</groupId>
  <artifactId>flyway-mysql</artifactId>
</dependency>

Configure

application.yaml

with DeepSeek API key, model, and MySQL datasource using

${}

placeholders to read environment variables.

2.2 Design Database with Flyway

Create migration scripts under

src/main/resources/db/migration

:

CREATE TABLE classes (
  id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID())),
  name VARCHAR(50) NOT NULL UNIQUE,
  grade VARCHAR(20) NOT NULL,
  teacher VARCHAR(50) NOT NULL,
  room_number VARCHAR(20) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE courses (
  id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID())),
  name VARCHAR(50) NOT NULL UNIQUE,
  code VARCHAR(20) NOT NULL UNIQUE,
  credits INT NOT NULL DEFAULT 1,
  description TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE students (
  id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID())),
  name VARCHAR(50) NOT NULL,
  student_number VARCHAR(20) NOT NULL UNIQUE,
  gender ENUM('Male','Female') NOT NULL,
  age INT NOT NULL,
  phone VARCHAR(15),
  email VARCHAR(100),
  class_id BINARY(16) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT student_fkey_class FOREIGN KEY (class_id) REFERENCES classes(id)
);

CREATE TABLE student_courses (
  id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID())),
  student_id BINARY(16) NOT NULL,
  course_id BINARY(16) NOT NULL,
  enrollment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  score DECIMAL(5,2),
  status ENUM('Enrolled','Completed','Dropped') DEFAULT 'Enrolled',
  CONSTRAINT sc_fkey_student FOREIGN KEY (student_id) REFERENCES students(id),
  CONSTRAINT sc_fkey_course FOREIGN KEY (course_id) REFERENCES courses(id),
  UNIQUE KEY unique_student_course (student_id, course_id)
);

Insert sample data for classes, courses, and students using additional migration scripts (e.g.,

V02__adding_classes_data.sql

,

V03__adding_courses_data.sql

,

V04__adding_students_data.sql

).

3. Configure AI Prompt

Create

system-prompt.st

that instructs the LLM to generate only SELECT statements, reject non‑SELECT operations, detect SQL injection, and include table prefixes for JOINs. The placeholder

{ddl}

will be replaced with the actual DDL.

4. Build the Text‑to‑SQL Chatbot

4.1 Define Chatbot Beans

@Bean
PromptTemplate systemPrompt(Resource systemPrompt, Resource ddlSchema) throws IOException {
    PromptTemplate template = new PromptTemplate(systemPrompt);
    template.add("ddl", ddlSchema.getContentAsString(Charset.defaultCharset()));
    return template;
}

@Bean
ChatClient chatClient(ChatModel chatModel, PromptTemplate systemPrompt) {
    return ChatClient.builder(chatModel)
        .defaultSystem(systemPrompt.render())
        .build();
}

4.2 Implement Service Classes

@Service
class SqlGenerator {
    private final ChatClient chatClient;
    String generate(String question) {
        String response = chatClient.prompt(question).call().content();
        if (!response.startsWith("SELECT")) {
            throw new InvalidQueryException(response);
        }
        return response;
    }
}

@Service
class SqlExecutor {
    private final JdbcClient jdbcClient;
    List<Map<String, Object>> execute(String query) {
        List<Map<String, Object>> result = jdbcClient.sql(query).query().listOfRows();
        if (result.isEmpty()) {
            throw new EmptyResultException("No results found.");
        }
        return result;
    }
}

4.3 Expose REST API

@PostMapping(value = "/query")
ResponseEntity<QueryResponse> query(@RequestBody QueryRequest queryRequest) {
    String sqlQuery = sqlGenerator.generate(queryRequest.question());
    List<Map<String, Object>> result = sqlExecutor.execute(sqlQuery);
    return ResponseEntity.ok(new QueryResponse(result));
}

record QueryRequest(String question) {}
record QueryResponse(List<Map<String, Object>> result) {}

Enable SQL logging in

application.yaml

to see generated queries.

5. Interact with the Chatbot

Use curl to send natural‑language questions:

curl -X POST http://localhost:8080/query \
  -H "Content-Type: application/json" \
  -d '{"question": "查询高三年级所有学生的姓名和班级信息"}'

The response returns student names with class details. Another example counts students per class:

curl -X POST http://localhost:8080/query \
  -H "Content-Type: application/json" \
  -d '{"question": "统计每个班级的学生人数，按人数降序排列"}'

Check the logs to view the LLM‑generated SQL, e.g.:

SELECT students.name, classes.name as class_name
FROM students
JOIN classes ON students.class_id = classes.id
WHERE classes.grade = '高三'
ORDER BY classes.name;

6. Summary

The tutorial demonstrates a complete end‑to‑end solution for a text‑to‑SQL chatbot using Spring AI, Flyway, and the DeepSeek LLM. It covers database schema design, prompt engineering, bean configuration, service implementation, REST exposure, and practical interaction examples, laying a solid foundation for further enhancements such as permission control, result visualization, and LLM output optimization.