Build a SQL Generator with Spring AI

A detailed step-by-step guide to build a safe natural-language-to-SQL generator using Spring Boot, Spring AI ChatClient, structured output, schema context, validation, and H2.

A SQL generator converts a user's natural language question into a SQL query.

Example:

Show top 5 customers by total order amount.

Generated SQL:

SELECT c.name, SUM(o.total_amount) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.name
ORDER BY total_spent DESC
LIMIT 5;

This is useful for dashboards, analytics assistants, admin tools, internal reporting, and data exploration.

But SQL generation must be handled carefully. You should never blindly execute whatever an AI model generates.

In this guide, we will build a safe Spring AI SQL generator that:

Accepts a natural language question.
Sends only allowed schema context to the AI model.
Generates structured SQL output.
Validates that the query is read-only.
Blocks dangerous SQL such as DROP, DELETE, UPDATE, INSERT, and ALTER.
Optionally executes the query against an H2 demo database.
Returns SQL, explanation, warnings, columns, and rows.

Real-Time Use Case

Imagine an internal sales dashboard where a business user asks:

Which products generated the highest revenue last month?

The assistant should generate and run:

SELECT p.name AS product_name, SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.id
JOIN orders o ON oi.order_id = o.id
WHERE o.order_date >= DATE '2026-05-01'
  AND o.order_date < DATE '2026-06-01'
GROUP BY p.name
ORDER BY revenue DESC;

Then return rows like:

[
  {
    "PRODUCT_NAME": "Spring AI Course",
    "REVENUE": 1499.70
  }
]

Final APIs

API	Method	Purpose
`/api/sql/health`	`GET`	Check service health
`/api/sql/schema`	`GET`	View the schema context given to the model
`/api/sql/generate`	`POST`	Generate SQL but do not execute
`/api/sql/execute`	`POST`	Generate, validate, and execute read-only SQL

Architecture

flowchart TD
    User["User question"] --> Controller["SqlGeneratorController"]
    Controller --> Service["SqlGeneratorService"]
    Service --> Schema["Schema Context"]
    Schema --> ChatClient["Spring AI ChatClient"]
    ChatClient --> LLM["AI Model"]
    LLM --> Plan["Structured SqlPlan"]
    Plan --> Validator["SQL Safety Validator"]
    Validator -->|Safe SELECT| Database["H2 Demo Database"]
    Validator -->|Unsafe| Block["Reject Request"]
    Database --> Result["Rows + Columns"]
    Result --> User

Safety Rules

For this tutorial, we enforce these rules:

Rule	Why
Only `SELECT` queries allowed	Prevent data changes
Only known tables allowed	Prevent access to hidden tables
No semicolon chaining	Prevent multiple statements
No comments	Reduce injection tricks
No DDL/DML keywords	Block destructive actions
Add result limits	Avoid huge responses
Schema is controlled by server	User cannot invent schema

This is a learning implementation, but these ideas matter in real applications.

Tools and Frameworks

Tool	Recommended Version	Purpose
Java	21 or later	Runtime
Spring Boot	4.0.x	REST API
Spring AI	2.0.0	ChatClient and structured output
OpenAI	Current API	SQL generation model
H2 Database	Runtime dependency	Demo SQL database
Maven	3.9+	Build tool
curl or Postman	Any current version	API testing

Project Structure

spring-ai-sql-generator/
├── pom.xml
└── src/
    └── main/
        ├── java/
        │   └── com/
        │       └── codewithvenu/
        │           └── sqlgenerator/
        │               ├── SqlGeneratorApplication.java
        │               ├── controller/
        │               │   └── SqlGeneratorController.java
        │               ├── dto/
        │               │   ├── ExecuteSqlResponse.java
        │               │   ├── GenerateSqlRequest.java
        │               │   ├── GenerateSqlResponse.java
        │               │   └── SqlPlan.java
        │               ├── exception/
        │               │   └── GlobalExceptionHandler.java
        │               └── service/
        │                   ├── DatabaseQueryService.java
        │                   ├── SchemaContextService.java
        │                   ├── SqlGeneratorService.java
        │                   └── SqlSafetyValidator.java
        └── resources/
            ├── application.yml
            ├── data.sql
            └── schema.sql

Step 1: Create Maven Project

File: pom.xml

<?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>4.0.0</version>
        <relativePath/>
    </parent>

    <groupId>com.codewithvenu</groupId>
    <artifactId>spring-ai-sql-generator</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>spring-ai-sql-generator</name>

    <properties>
        <java.version>21</java.version>
        <spring-ai.version>2.0.0</spring-ai.version>
    </properties>

    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.ai</groupId>
                <artifactId>spring-ai-bom</artifactId>
                <version>${spring-ai.version}</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>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-validation</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.ai</groupId>
            <artifactId>spring-ai-starter-model-openai</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

Step 2: Configure Spring Boot and OpenAI

File: src/main/resources/application.yml

server:
  port: 8080

spring:
  application:
    name: spring-ai-sql-generator

  datasource:
    url: jdbc:h2:mem:salesdb;MODE=PostgreSQL;DATABASE_TO_UPPER=true
    username: sa
    password:
    driver-class-name: org.h2.Driver

  h2:
    console:
      enabled: true

  sql:
    init:
      mode: always

  ai:
    openai:
      api-key: ${OPENAI_API_KEY}
      chat:
        options:
          model: gpt-4.1-mini
          temperature: 0.1

Set your OpenAI API key:

export OPENAI_API_KEY="your-openai-api-key-here"

Windows PowerShell:

$env:OPENAI_API_KEY="your-openai-api-key-here"

Step 3: Create Demo Database Schema

File: src/main/resources/schema.sql

DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL,
    city VARCHAR(100) NOT NULL,
    created_at DATE NOT NULL
);

CREATE TABLE products (
    id BIGINT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    category VARCHAR(100) NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL
);

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    order_date DATE NOT NULL,
    status VARCHAR(50) NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE order_items (
    id BIGINT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

File: src/main/resources/data.sql

INSERT INTO customers (id, name, email, city, created_at) VALUES
(1, 'Venu Rao', '[email protected]', 'Dallas', DATE '2026-01-10'),
(2, 'Maya Patel', '[email protected]', 'Austin', DATE '2026-02-15'),
(3, 'John Smith', '[email protected]', 'Chicago', DATE '2026-03-20'),
(4, 'Sara Lee', '[email protected]', 'Dallas', DATE '2026-04-05');

INSERT INTO products (id, name, category, unit_price) VALUES
(1, 'Spring AI Course', 'Education', 49.99),
(2, 'Java Interview Guide', 'Books', 19.99),
(3, 'System Design Notes', 'Books', 29.99),
(4, 'Cloud Architecture Workshop', 'Education', 99.99);

INSERT INTO orders (id, customer_id, order_date, status, total_amount) VALUES
(101, 1, DATE '2026-05-05', 'COMPLETED', 149.97),
(102, 1, DATE '2026-05-20', 'COMPLETED', 99.99),
(103, 2, DATE '2026-05-21', 'COMPLETED', 49.99),
(104, 3, DATE '2026-06-01', 'PENDING', 29.99),
(105, 4, DATE '2026-06-10', 'COMPLETED', 119.98);

INSERT INTO order_items (id, order_id, product_id, quantity, unit_price) VALUES
(1001, 101, 1, 3, 49.99),
(1002, 102, 4, 1, 99.99),
(1003, 103, 1, 1, 49.99),
(1004, 104, 3, 1, 29.99),
(1005, 105, 2, 1, 19.99),
(1006, 105, 4, 1, 99.99);

Step 4: Main Application Class

File: SqlGeneratorApplication.java

package com.codewithvenu.sqlgenerator;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class SqlGeneratorApplication {

    public static void main(String[] args) {
        SpringApplication.run(SqlGeneratorApplication.class, args);
    }
}

Step 5: Create DTOs

GenerateSqlRequest

File: dto/GenerateSqlRequest.java

package com.codewithvenu.sqlgenerator.dto;

import jakarta.validation.constraints.NotBlank;
import jakarta.validation.constraints.Size;

public record GenerateSqlRequest(
    @NotBlank(message = "question is required")
    @Size(max = 1000, message = "question must be less than 1000 characters")
    String question,

    Integer maxRows
) {
    public int safeMaxRows() {
        if (maxRows == null) {
            return 50;
        }
        return Math.min(Math.max(maxRows, 1), 200);
    }
}

SqlPlan

File: dto/SqlPlan.java

package com.codewithvenu.sqlgenerator.dto;

import java.util.List;

public record SqlPlan(
    String sql,
    String explanation,
    List<String> tablesUsed,
    List<String> assumptions,
    boolean readOnly
) {
}

GenerateSqlResponse

File: dto/GenerateSqlResponse.java

package com.codewithvenu.sqlgenerator.dto;

import java.time.Instant;
import java.util.List;

public record GenerateSqlResponse(
    String question,
    String sql,
    String explanation,
    List<String> tablesUsed,
    List<String> assumptions,
    boolean safe,
    List<String> warnings,
    Instant createdAt
) {
}

ExecuteSqlResponse

File: dto/ExecuteSqlResponse.java

package com.codewithvenu.sqlgenerator.dto;

import java.time.Instant;
import java.util.List;
import java.util.Map;

public record ExecuteSqlResponse(
    String question,
    String sql,
    String explanation,
    List<String> columns,
    List<Map<String, Object>> rows,
    int rowCount,
    List<String> warnings,
    Instant createdAt
) {
}

Step 6: Create Schema Context Service

The model should not guess your schema. Give it a clear schema context.

File: service/SchemaContextService.java

package com.codewithvenu.sqlgenerator.service;

import org.springframework.stereotype.Service;

@Service
public class SchemaContextService {

    public String schemaContext() {
        return """
            Database dialect: H2 in PostgreSQL compatibility mode.

            Allowed tables and columns:

            customers:
            - id BIGINT primary key
            - name VARCHAR
            - email VARCHAR
            - city VARCHAR
            - created_at DATE

            products:
            - id BIGINT primary key
            - name VARCHAR
            - category VARCHAR
            - unit_price DECIMAL

            orders:
            - id BIGINT primary key
            - customer_id BIGINT foreign key to customers.id
            - order_date DATE
            - status VARCHAR
            - total_amount DECIMAL

            order_items:
            - id BIGINT primary key
            - order_id BIGINT foreign key to orders.id
            - product_id BIGINT foreign key to products.id
            - quantity INT
            - unit_price DECIMAL

            Relationships:
            - customers.id = orders.customer_id
            - orders.id = order_items.order_id
            - products.id = order_items.product_id

            Business rules:
            - Revenue means SUM(order_items.quantity * order_items.unit_price)
            - Completed sales should filter orders.status = 'COMPLETED'
            - Use DATE 'YYYY-MM-DD' for date literals
            - Use LIMIT for row limits
            """;
    }
}

Step 7: Create SQL Safety Validator

This validator blocks dangerous SQL before execution.

File: service/SqlSafetyValidator.java

package com.codewithvenu.sqlgenerator.service;

import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import java.util.Set;
import java.util.regex.Pattern;

@Service
public class SqlSafetyValidator {

    private static final Set<String> ALLOWED_TABLES = Set.of(
        "customers",
        "products",
        "orders",
        "order_items"
    );

    private static final List<String> BLOCKED_KEYWORDS = List.of(
        "INSERT",
        "UPDATE",
        "DELETE",
        "DROP",
        "ALTER",
        "TRUNCATE",
        "CREATE",
        "MERGE",
        "CALL",
        "EXEC",
        "GRANT",
        "REVOKE"
    );

    public ValidationResult validate(String sql) {
        List<String> warnings = new ArrayList<>();

        if (sql == null || sql.isBlank()) {
            return new ValidationResult(false, List.of("SQL is empty"));
        }

        String normalized = sql.trim();
        String upper = normalized.toUpperCase(Locale.ROOT);

        if (!upper.startsWith("SELECT")) {
            warnings.add("Only SELECT statements are allowed");
        }

        if (normalized.contains(";")) {
            warnings.add("Semicolons are not allowed because multiple statements are blocked");
        }

        if (normalized.contains("--") || normalized.contains("/*") || normalized.contains("*/")) {
            warnings.add("SQL comments are not allowed");
        }

        for (String keyword : BLOCKED_KEYWORDS) {
            if (Pattern.compile("\\b" + keyword + "\\b").matcher(upper).find()) {
                warnings.add("Blocked keyword found: " + keyword);
            }
        }

        for (String table : extractLikelyTableNames(normalized)) {
            if (!ALLOWED_TABLES.contains(table.toLowerCase(Locale.ROOT))) {
                warnings.add("Unknown or disallowed table referenced: " + table);
            }
        }

        return new ValidationResult(warnings.isEmpty(), warnings);
    }

    private List<String> extractLikelyTableNames(String sql) {
        List<String> tables = new ArrayList<>();
        String[] tokens = sql.replace(",", " ").replace("\n", " ").split("\\s+");

        for (int i = 0; i < tokens.length - 1; i++) {
            String token = tokens[i].toUpperCase(Locale.ROOT);
            if (token.equals("FROM") || token.equals("JOIN")) {
                String table = tokens[i + 1].replaceAll("[^A-Za-z0-9_]", "");
                if (!table.isBlank()) {
                    tables.add(table);
                }
            }
        }

        return tables;
    }

    public record ValidationResult(boolean safe, List<String> warnings) {
    }
}

This is intentionally simple for learning. Production SQL validation should use a SQL parser such as JSqlParser, Apache Calcite, or database-native prepared query analysis.

Step 8: Create Database Query Service

File: service/DatabaseQueryService.java

package com.codewithvenu.sqlgenerator.service;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;

@Service
public class DatabaseQueryService {

    private final JdbcTemplate jdbcTemplate;

    public DatabaseQueryService(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public List<Map<String, Object>> executeSelect(String sql) {
        return jdbcTemplate.queryForList(sql);
    }
}

Step 9: Create SQL Generator Service

File: service/SqlGeneratorService.java

package com.codewithvenu.sqlgenerator.service;

import com.codewithvenu.sqlgenerator.dto.ExecuteSqlResponse;
import com.codewithvenu.sqlgenerator.dto.GenerateSqlRequest;
import com.codewithvenu.sqlgenerator.dto.GenerateSqlResponse;
import com.codewithvenu.sqlgenerator.dto.SqlPlan;
import org.springframework.ai.chat.client.ChatClient;
import org.springframework.stereotype.Service;

import java.time.Instant;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Service
public class SqlGeneratorService {

    private final ChatClient chatClient;
    private final SchemaContextService schemaContextService;
    private final SqlSafetyValidator sqlSafetyValidator;
    private final DatabaseQueryService databaseQueryService;

    public SqlGeneratorService(
        ChatClient.Builder builder,
        SchemaContextService schemaContextService,
        SqlSafetyValidator sqlSafetyValidator,
        DatabaseQueryService databaseQueryService
    ) {
        this.chatClient = builder
            .defaultSystem("""
                You are a senior SQL generator.
                Generate safe, read-only SQL using only the provided schema.

                Rules:
                - Generate exactly one SELECT query.
                - Do not generate INSERT, UPDATE, DELETE, DROP, ALTER, TRUNCATE, CREATE, MERGE, CALL, EXEC, GRANT, or REVOKE.
                - Do not use tables or columns outside the provided schema.
                - Do not include comments.
                - Do not include semicolons.
                - Prefer explicit JOINs.
                - Add LIMIT when the user asks for top results or when maxRows is provided.
                - Return a structured SQL plan.
                """)
            .build();
        this.schemaContextService = schemaContextService;
        this.sqlSafetyValidator = sqlSafetyValidator;
        this.databaseQueryService = databaseQueryService;
    }

    public GenerateSqlResponse generate(GenerateSqlRequest request) {
        SqlPlan plan = generatePlan(request);
        SqlSafetyValidator.ValidationResult validation = sqlSafetyValidator.validate(plan.sql());

        return new GenerateSqlResponse(
            request.question(),
            plan.sql(),
            plan.explanation(),
            plan.tablesUsed(),
            plan.assumptions(),
            validation.safe(),
            validation.warnings(),
            Instant.now()
        );
    }

    public ExecuteSqlResponse execute(GenerateSqlRequest request) {
        SqlPlan plan = generatePlan(request);
        SqlSafetyValidator.ValidationResult validation = sqlSafetyValidator.validate(plan.sql());

        if (!validation.safe()) {
            throw new IllegalArgumentException("Generated SQL is unsafe: " + validation.warnings());
        }

        List<Map<String, Object>> rows = databaseQueryService.executeSelect(plan.sql());
        List<String> columns = rows.isEmpty()
            ? List.of()
            : new ArrayList<>(rows.get(0).keySet());

        return new ExecuteSqlResponse(
            request.question(),
            plan.sql(),
            plan.explanation(),
            columns,
            rows,
            rows.size(),
            validation.warnings(),
            Instant.now()
        );
    }

    private SqlPlan generatePlan(GenerateSqlRequest request) {
        return chatClient
            .prompt()
            .user(user -> user
                .text("""
                    Schema:
                    {schema}

                    User question:
                    {question}

                    Maximum rows:
                    {maxRows}

                    Generate a SQL plan.
                    The SQL must be one read-only SELECT statement.
                    If the user asks for all rows, still limit results to {maxRows}.
                    """)
                .param("schema", schemaContextService.schemaContext())
                .param("question", request.question())
                .param("maxRows", request.safeMaxRows()))
            .call()
            .entity(SqlPlan.class, spec -> spec.validateSchema());
    }

    public String schema() {
        return schemaContextService.schemaContext();
    }
}

Important:

.entity(SqlPlan.class, spec -> spec.validateSchema()) asks Spring AI to return structured output and validate it against the Java record schema.
Java still validates the generated SQL after the model response.
The /execute endpoint refuses unsafe SQL.

Step 10: Create Controller

File: controller/SqlGeneratorController.java

package com.codewithvenu.sqlgenerator.controller;

import com.codewithvenu.sqlgenerator.dto.ExecuteSqlResponse;
import com.codewithvenu.sqlgenerator.dto.GenerateSqlRequest;
import com.codewithvenu.sqlgenerator.dto.GenerateSqlResponse;
import com.codewithvenu.sqlgenerator.service.SqlGeneratorService;
import jakarta.validation.Valid;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.Map;

@RestController
@RequestMapping("/api/sql")
public class SqlGeneratorController {

    private final SqlGeneratorService sqlGeneratorService;

    public SqlGeneratorController(SqlGeneratorService sqlGeneratorService) {
        this.sqlGeneratorService = sqlGeneratorService;
    }

    @GetMapping("/health")
    public Map<String, String> health() {
        return Map.of("status", "UP", "service", "spring-ai-sql-generator");
    }

    @GetMapping("/schema")
    public Map<String, String> schema() {
        return Map.of("schema", sqlGeneratorService.schema());
    }

    @PostMapping("/generate")
    public GenerateSqlResponse generate(@Valid @RequestBody GenerateSqlRequest request) {
        return sqlGeneratorService.generate(request);
    }

    @PostMapping("/execute")
    public ExecuteSqlResponse execute(@Valid @RequestBody GenerateSqlRequest request) {
        return sqlGeneratorService.execute(request);
    }
}

Step 11: Add Error Handling

File: exception/GlobalExceptionHandler.java

package com.codewithvenu.sqlgenerator.exception;

import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.MethodArgumentNotValidException;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.RestControllerAdvice;

import java.time.Instant;
import java.util.HashMap;
import java.util.Map;

@RestControllerAdvice
public class GlobalExceptionHandler {

    @ExceptionHandler(MethodArgumentNotValidException.class)
    public ResponseEntity<Map<String, Object>> handleValidation(MethodArgumentNotValidException ex) {
        Map<String, String> fields = new HashMap<>();

        ex.getBindingResult().getFieldErrors().forEach(error ->
            fields.put(error.getField(), error.getDefaultMessage())
        );

        Map<String, Object> body = new HashMap<>();
        body.put("timestamp", Instant.now());
        body.put("status", HttpStatus.BAD_REQUEST.value());
        body.put("error", "Validation failed");
        body.put("fields", fields);

        return ResponseEntity.badRequest().body(body);
    }

    @ExceptionHandler(IllegalArgumentException.class)
    public ResponseEntity<Map<String, Object>> handleBadRequest(IllegalArgumentException ex) {
        Map<String, Object> body = new HashMap<>();
        body.put("timestamp", Instant.now());
        body.put("status", HttpStatus.BAD_REQUEST.value());
        body.put("error", "Bad request");
        body.put("message", ex.getMessage());

        return ResponseEntity.badRequest().body(body);
    }
}

Step 12: Run the Application

mvn spring-boot:run

Health check:

curl http://localhost:8080/api/sql/health

Expected output:

{
  "service": "spring-ai-sql-generator",
  "status": "UP"
}

View schema context:

curl http://localhost:8080/api/sql/schema

Step 13: Generate SQL Without Executing

Input:

curl -X POST http://localhost:8080/api/sql/generate \
  -H "Content-Type: application/json" \
  -d '{
    "question": "Show top 3 customers by total completed order amount",
    "maxRows": 3
  }'

Expected output shape:

{
  "question": "Show top 3 customers by total completed order amount",
  "sql": "SELECT c.name, SUM(o.total_amount) AS total_spent FROM customers c JOIN orders o ON c.id = o.customer_id WHERE o.status = 'COMPLETED' GROUP BY c.name ORDER BY total_spent DESC LIMIT 3",
  "explanation": "This query joins customers and orders, filters completed orders, groups by customer, and sorts by total order amount.",
  "tablesUsed": ["customers", "orders"],
  "assumptions": ["Completed order amount uses orders.total_amount"],
  "safe": true,
  "warnings": []
}

Step 14: Generate and Execute SQL

Input:

curl -X POST http://localhost:8080/api/sql/execute \
  -H "Content-Type: application/json" \
  -d '{
    "question": "Show revenue by product category for completed orders",
    "maxRows": 10
  }'

Expected output:

{
  "question": "Show revenue by product category for completed orders",
  "sql": "SELECT p.category, SUM(oi.quantity * oi.unit_price) AS revenue FROM order_items oi JOIN products p ON oi.product_id = p.id JOIN orders o ON oi.order_id = o.id WHERE o.status = 'COMPLETED' GROUP BY p.category ORDER BY revenue DESC LIMIT 10",
  "explanation": "This query calculates revenue by product category for completed orders.",
  "columns": ["CATEGORY", "REVENUE"],
  "rows": [
    {
      "CATEGORY": "Education",
      "REVENUE": 249.96
    },
    {
      "CATEGORY": "Books",
      "REVENUE": 19.99
    }
  ],
  "rowCount": 2,
  "warnings": []
}

Exact SQL may vary, but it should be a safe SELECT.

Step 15: More Real-Time Examples

Example 1: Pending Orders

Input:

{
  "question": "List pending orders with customer name and amount",
  "maxRows": 20
}

Possible SQL:

SELECT o.id AS order_id, c.name AS customer_name, o.total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'PENDING'
LIMIT 20

Example 2: Best Selling Products

Input:

{
  "question": "Which products sold the most units?",
  "maxRows": 5
}

Possible SQL:

SELECT p.name, SUM(oi.quantity) AS units_sold
FROM order_items oi
JOIN products p ON oi.product_id = p.id
GROUP BY p.name
ORDER BY units_sold DESC
LIMIT 5

Example 3: Customers From Dallas

Input:

{
  "question": "Show customers from Dallas and their total completed spend",
  "maxRows": 10
}

Possible SQL:

SELECT c.name, c.email, SUM(o.total_amount) AS total_spend
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE c.city = 'Dallas'
  AND o.status = 'COMPLETED'
GROUP BY c.name, c.email
ORDER BY total_spend DESC
LIMIT 10

Unsafe Request Example

Input:

curl -X POST http://localhost:8080/api/sql/execute \
  -H "Content-Type: application/json" \
  -d '{
    "question": "Delete all pending orders",
    "maxRows": 10
  }'

Expected behavior:

The model should refuse or generate a safe explanation.
If it generates unsafe SQL, Java validation blocks it.

Possible output:

{
  "timestamp": "2026-06-23T10:15:30Z",
  "status": 400,
  "error": "Bad request",
  "message": "Generated SQL is unsafe: [Only SELECT statements are allowed, Blocked keyword found: DELETE]"
}

Why Structured Output Matters

Instead of asking the model for plain SQL text, we ask for a structured SqlPlan.

public record SqlPlan(
    String sql,
    String explanation,
    List<String> tablesUsed,
    List<String> assumptions,
    boolean readOnly
) {
}

This is better because the application gets:

The query.
The explanation.
The tables used.
The assumptions.
Whether the model believes it is read-only.

But remember:

The model's readOnly value is only a hint. Java validation must still enforce safety.

Production Improvements

Before production, add:

Real SQL parser validation.
Database user with read-only permissions.
Query timeout.
Row limit enforced at database level.
Audit logging.
User authorization by table or column.
PII masking.
Query cost estimation.
Human review for sensitive datasets.
Semantic layer instead of raw schema exposure.

Common Mistakes

Mistake	Problem	Better Approach
Executing model SQL directly	Dangerous	Validate and use read-only DB user
Giving full production schema	Data exposure risk	Provide only allowed schema
Allowing all SQL	Can modify data	Only allow `SELECT`
No row limit	Large result sets	Enforce `LIMIT`
No audit logs	Hard to trace misuse	Log user, SQL, result count
Trusting model `readOnly`	Model can be wrong	Validate in Java
No PII handling	Privacy risk	Mask sensitive fields

Complete Test Script

curl http://localhost:8080/api/sql/health

curl http://localhost:8080/api/sql/schema

curl -X POST http://localhost:8080/api/sql/generate \
  -H "Content-Type: application/json" \
  -d '{"question":"Show top 3 customers by total completed order amount","maxRows":3}'

curl -X POST http://localhost:8080/api/sql/execute \
  -H "Content-Type: application/json" \
  -d '{"question":"Show revenue by product category for completed orders","maxRows":10}'

curl -X POST http://localhost:8080/api/sql/execute \
  -H "Content-Type: application/json" \
  -d '{"question":"List pending orders with customer name and amount","maxRows":20}'

curl -X POST http://localhost:8080/api/sql/execute \
  -H "Content-Type: application/json" \
  -d '{"question":"Delete all pending orders","maxRows":10}'

Summary

You built a safe SQL generator with Spring AI.

The core flow is:

User asks a business question.
Application adds controlled schema context.
Spring AI generates a structured SQL plan.
Java validates the SQL.
Safe SELECT queries can execute.
The API returns SQL, explanation, rows, and warnings.

The most important production rule:

Never trust model-generated SQL directly. Treat it as a draft, validate it, enforce read-only permissions, and audit every request.

Build a SQL Generator with Spring AI

Real-Time Use Case

Final APIs

Architecture

Safety Rules

Tools and Frameworks

Project Structure

Step 1: Create Maven Project

Step 2: Configure Spring Boot and OpenAI

Step 3: Create Demo Database Schema

Step 4: Main Application Class

Step 5: Create DTOs

GenerateSqlRequest

SqlPlan

GenerateSqlResponse

ExecuteSqlResponse

Step 6: Create Schema Context Service

Step 7: Create SQL Safety Validator

Step 8: Create Database Query Service

Step 9: Create SQL Generator Service

Step 10: Create Controller

Step 11: Add Error Handling

Step 12: Run the Application

Step 13: Generate SQL Without Executing

Step 14: Generate and Execute SQL

Step 15: More Real-Time Examples

Example 1: Pending Orders

Example 2: Best Selling Products

Example 3: Customers From Dallas

Unsafe Request Example

Why Structured Output Matters

Production Improvements

Common Mistakes

Complete Test Script

Summary

References