Generate SQL with Spring AI: LLM‑Powered Queries in Spring Boot 3

1. Introduction

Large language models (LLMs) can generate source code when given sufficient context and clear instructions. By providing DDL statements and a prompt that specifies the desired SQL output format, LLMs can also construct SQL queries.

The following example shows how to interact with an LLM via Spring AI to generate a SELECT query and execute it against a database.

2. Practical Example

2.1 Dependency Management

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
<dependency>
  <groupId>com.alibaba.cloud.ai</groupId>
  <artifactId>spring-ai-alibaba-starter</artifactId>
  <version>1.0.0-M6.1</version>
</dependency>

The Alibaba model platform is used; the JDBC starter allows execution of the generated SQL.

2.2 Configuration Files

Application properties for Spring AI:

spring:
  ai:
    dashscope:
      api-key: sk-xxxooo
      base-url: https://dashscope.aliyuncs.com/compatible-mode/v1
      chat:
        options:
          model: qwen-turbo

DDL and data scripts (schema.sql and data.sql) are placed in classpath:script/ and automatically executed on startup:

spring:
  sql:
    init:
      schema-locations: classpath:script/schema.sql
      data-locations: classpath:script/data.sql
      platform: mysql
      mode: always

2.3 Prompt Template

Based on the provided DDL, write a SQL query to answer the QUESTION.
Only generate a SELECT query. Do not add any surrounding text or markers.
If the question is not a query (e.g., INSERT, UPDATE, DELETE), reply that the operation is not supported.
If the question cannot be answered, state that the DDL does not support it.

QUESTION
{question}

DDL
{ddl}

2.4 Controller Implementation

@RestController
public class SqlController {
  @Value("classpath:script/schema.sql")
  private Resource ddlResource;
  @Value("classpath:/sql-prompt-template.tml")
  private Resource sqlPromptTemplateResource;

  private final ChatClient aiClient;
  private final JdbcTemplate jdbcTemplate;

  public SqlController(ChatClient.Builder aiClientBuilder, JdbcTemplate jdbcTemplate) {
    this.aiClient = aiClientBuilder.build();
    this.jdbcTemplate = jdbcTemplate;
  }

  @PostMapping(path = "/sql")
  public AiResponse sql(@RequestBody AiRequest request) throws IOException {
    String schema = ddlResource.getContentAsString(Charset.defaultCharset());
    String query = aiClient.prompt()
        .advisors(new SimpleLoggerAdvisor())
        .user(userSpec -> userSpec
            .text(sqlPromptTemplateResource)
            .param("question", request.text())
            .param("ddl", schema))
        .call().content();
    if (query.toLowerCase().startsWith("select")) {
      return new AiResponse(query, jdbcTemplate.queryForList(query));
    }
    throw new AiException(query);
  }

  public static record AiRequest(String text) {}
  public static record AiResponse(String sqlQuery, List<Map<String, Object>> results) {}
}

The controller uses ChatClient to send the prompt to the LLM and JdbcTemplate to execute the returned SELECT query.

2.5 Logging Configuration

logging:
  level:
    '[org.springframework.ai.chat.client.advisor]': debug

2.6 Testing

A two‑table join query is sent to the API; the LLM generates a correct SELECT statement, which is executed and the results are returned. Screenshots of the console output confirm successful execution. When a non‑SELECT operation (e.g., INSERT) is submitted, the service responds with a message indicating the operation is not supported, as defined by the prompt template.

All steps together provide a complete workflow for LLM‑driven SQL generation and execution within a Spring Boot 3 application.