Still Writing SQL by Hand? How Spring AI Generates More Accurate Queries

1. Introduction

Manual SQL is inefficient and error‑prone; Spring AI can generate accurate MySQL queries directly from natural‑language questions by leveraging the database schema.

2. Environment

Spring Boot 3.5.0 with Maven dependencies:

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
  <groupId>com.mysql</groupId>
  <artifactId>mysql-connector-j</artifactId>
  <scope>runtime</scope>
</dependency>
<dependency>
  <groupId>com.alibaba.cloud.ai</groupId>
  <artifactId>spring-ai-alibaba-starter-dashscope</artifactId>
  <version>${spring-ai-alibaba-dashscope.version}</version>
</dependency>

3. Schema Extraction

SchemaService reads table names and column metadata from information_schema, builds a Markdown document that lists each table, its comment, and a markdown table of columns (name, type, key, comment). It normalises null comments, replaces line‑breaks, and maps key codes to readable labels (PK, FK/Idx, Unique).

@Service
public class SchemaService {
    private final JdbcTemplate jdbcTemplate;
    public SchemaService(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; }
    public List<String> getTableNames() {
        String sql = """
            SELECT TABLE_NAME FROM information_schema.TABLES
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_TYPE = 'BASE TABLE'
            """;
        return jdbcTemplate.queryForList(sql, String.class);
    }
    public String getSchemaDescription() {
        StringBuilder schema = new StringBuilder();
        schema.append("## 数据库 Schema 信息

");
        List<String> tables = getTableNames();
        String columnSql = """
            SELECT COLUMN_NAME, DATA_TYPE, COLUMN_KEY, COLUMN_COMMENT
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?
            ORDER BY ORDINAL_POSITION
            """;
        String tableCommentSql = """
            SELECT TABLE_COMMENT FROM information_schema.TABLES
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?
            """;
        for (String table : tables) {
            schema.append("### 表名：").append(table).append("
");
            try {
                String tableComment = jdbcTemplate.queryForObject(tableCommentSql, String.class, table);
                schema.append(tableComment != null && !tableComment.isEmpty() ? tableComment : "(无表注释)").append("
");
            } catch (Exception e) { schema.append("
"); }
            schema.append("| 字段名 | 数据类型 | 约束/键 | 业务注释/说明 |
");
            schema.append("| :--- | :--- | :--- | :--- |
");
            List<Map<String, Object>> columns = jdbcTemplate.queryForList(columnSql, table);
            for (Map<String, Object> col : columns) {
                String colName = col.get("COLUMN_NAME").toString();
                String dataType = col.get("DATA_TYPE").toString();
                String key = col.get("COLUMN_KEY") != null ? col.get("COLUMN_KEY").toString() : "";
                String comment = col.get("COLUMN_COMMENT") != null ? col.get("COLUMN_COMMENT").toString() : "";
                comment = comment.replace("
", " ").replace("\r", " ");
                String keyDisplay;
                if ("PRI".equals(key)) keyDisplay = "PK (主键)";
                else if ("MUL".equals(key)) keyDisplay = "FK/Idx (外键/索引)";
                else if ("UNI".equals(key)) keyDisplay = "Unique (唯一)";
                else keyDisplay = "-";
                if (comment == null || comment.trim().isEmpty()) comment = "(无注释)";
                schema.append("| ").append(colName).append(" | ").append(dataType).append(" | ")
                      .append(keyDisplay).append(" | ").append(comment).append(" |
");
            }
            schema.append("
");
        }
        return schema.toString();
    }
}

4. AI Prompt Service

DashScopeService creates a ChatClient with a system prompt that forces the model to act as a MySQL expert and return **only** the SQL statement. generateSql formats a message containing the Markdown schema and the user question, calls the model, and passes the raw response to extractSql, which removes optional markdown fences and trailing semicolons.

@Service
public class DashScopeService {
    private final ChatClient chatClient;
    public DashScopeService(ChatClient.Builder builder) {
        String system = """
            你是一位MySQL SQL专家。你唯一的工作是将自然语言问题转换为有效的MySQL SQL查询。
            规则:
            - 仅返回SQL查询，其他什么都不返回。
            - 不要包含markdown代码块、倒引号或任何解释。
            - 使用模式中出现的精确表名和列名 - 不要改变（字母）大小写。
            - 始终使用正确的MySQL语法。
            - 如果无法用给定的模式回答问题，则精确返回: SELECT 'Unable to answer with given schema';
            """;
        this.chatClient = builder.defaultAdvisors(new SimpleLoggerAdvisor())
                               .defaultSystem(system)
                               .build();
    }
    public String generateSql(String question, String schemaDescription) {
        String message = """
            数据库Schema:
            %s
            问题: %s
            """.formatted(schemaDescription, question);
        String content = this.chatClient.prompt().user(message).call().content();
        return extractSql(content);
    }
    private String extractSql(String raw) {
        String cleaned = raw.trim();
        Pattern codeBlock = Pattern.compile("```(?:sql)?\\s*(.*?)\\s*```", Pattern.DOTALL | Pattern.CASE_INSENSITIVE);
        Matcher matcher = codeBlock.matcher(cleaned);
        if (matcher.find()) cleaned = matcher.group(1).trim();
        if (cleaned.contains(";")) cleaned = cleaned.substring(0, cleaned.indexOf(";") + 1).trim();
        return cleaned;
    }
}

5. Orchestration Service

Text2SqlService obtains the schema markdown, asks DashScopeService for a SQL statement, executes it via SqlExecutionService, and returns a QueryResponse that holds the original question, generated SQL, and query results.

@Service
public class Text2SqlService {
    private final DashScopeService dashScopeService;
    private final SchemaService schemaService;
    private final SqlExecutionService executionService;
    public Text2SqlService(DashScopeService dashScopeService, SchemaService schemaService,
                           SqlExecutionService executionService) {
        this.dashScopeService = dashScopeService;
        this.schemaService = schemaService;
        this.executionService = executionService;
    }
    public QueryResponse process(String question) {
        String schema = this.schemaService.getSchemaDescription();
        String sql = this.dashScopeService.generateSql(question, schema);
        List<Map<String, Object>> result = this.executionService.execute(sql);
        return QueryResponse.success(question, sql, result);
    }
}

6. Controller

A REST controller exposes /api/query. It validates the question parameter, delegates to Text2SqlService, and returns the QueryResponse as JSON.

@RestController
@RequestMapping("/api")
public class Text2SqlController {
    private final Text2SqlService text2SqlService;
    public Text2SqlController(Text2SqlService text2SqlService) { this.text2SqlService = text2SqlService; }
    @GetMapping("/query")
    public ResponseEntity<QueryResponse> query(String question) {
        if (!StringUtils.hasLength(question)) {
            return ResponseEntity.ok(QueryResponse.error("", null, "请输入你的问题"));
        }
        return ResponseEntity.ok(text2SqlService.process(question));
    }
}

7. Testing

Running the endpoint with various natural‑language questions repeatedly yields correct MySQL statements, confirming that the end‑to‑end flow—from schema extraction to AI‑driven generation—works reliably.

8. Conclusion

Integrating Spring AI with Spring Boot transforms repetitive SQL authoring into a natural‑language‑driven workflow, dramatically improving developer efficiency and reducing syntax errors.