Introduction

Enterprise systems store huge amounts of data in databases.

But a common problem is:

Business users cannot write SQL queries.

So we build:

SQL AI Agent (Text-to-SQL System)

---

What We Are Building

A SQL AI Agent that can:

• Convert natural language to SQL
• Execute queries safely
• Fetch results from database
• Explain query results
• Support analytics queries

---

Architecture Overview

flowchart TD

User

SpringBoot_API

SQLAgent

PlannerAgent

ExecutorAgent

SQLGenerator

DatabaseTool

LLM

MCP_Server

User --> SpringBoot_API
SpringBoot_API --> SQLAgent

SQLAgent --> PlannerAgent
SQLAgent --> ExecutorAgent

PlannerAgent --> MCP_Server
ExecutorAgent --> MCP_Server

MCP_Server --> SQLGenerator
MCP_Server --> DatabaseTool
MCP_Server --> LLM

---

Step 1: Create Spring Boot Project

Dependencies:

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

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

---

Step 2: SQL Request Model

public class SQLRequest {
    private String question;
}

---

Step 3: SQL Response Model

public class SQLResponse {
    private String sqlQuery;
    private Object result;
}

---

Step 4: SQL Controller

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

    private final SQLAgentService sqlAgentService;

    public SQLController(SQLAgentService sqlAgentService) {
        this.sqlAgentService = sqlAgentService;
    }

    @PostMapping("/ask")
    public SQLResponse ask(@RequestBody SQLRequest request) {
        return sqlAgentService.process(request);
    }
}

---

Step 5: SQL Agent Service

@Service
public class SQLAgentService {

    private final PlannerAgent plannerAgent;
    private final ExecutorAgent executorAgent;

    public SQLAgentService(PlannerAgent plannerAgent,
                           ExecutorAgent executorAgent) {
        this.plannerAgent = plannerAgent;
        this.executorAgent = executorAgent;
    }

    public SQLResponse process(SQLRequest request) {

        // 1. Plan query strategy
        String plan = plannerAgent.createPlan(request.getQuestion());

        // 2. Generate SQL via MCP
        String sql = executorAgent.generateSQL(plan, request.getQuestion());

        // 3. Execute SQL via MCP
        Object result = executorAgent.executeSQL(sql);

        // 4. Build response
        SQLResponse response = new SQLResponse();
        response.setSqlQuery(sql);
        response.setResult(result);

        return response;
    }
}

---

Step 6: Planner Agent

@Service
public class PlannerAgent {

    public String createPlan(String question) {

        if (question.toLowerCase().contains("sales")) {
            return "SALES_QUERY_PLAN";
        }

        if (question.toLowerCase().contains("customer")) {
            return "CUSTOMER_QUERY_PLAN";
        }

        return "GENERAL_SQL_PLAN";
    }
}

---

Step 7: Executor Agent (SQL Generator + Runner)

@Service
public class ExecutorAgent {

    private final JdbcTemplate jdbcTemplate;

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

    // Generate SQL (via MCP/LLM in real system)
    public String generateSQL(String plan, String question) {

        switch (plan) {

            case "SALES_QUERY_PLAN":
                return "SELECT * FROM sales ORDER BY amount DESC";

            case "CUSTOMER_QUERY_PLAN":
                return "SELECT * FROM customers";

            default:
                return "SELECT 1";
        }
    }

    // Execute SQL safely
    public Object executeSQL(String sql) {
        return jdbcTemplate.queryForList(sql);
    }
}

---

Step 8: MCP Integration (Advanced Layer)

Now we upgrade with MCP:

ExecutorAgent → MCP Server → SQL Generator Tool + DB Tool + LLM

---

MCP Enhanced Architecture

flowchart TD

SQLAgent

PlannerAgent

ExecutorAgent

MCP_Client

MCP_Server

SQLTool

DBTool

LLM

SQLAgent --> PlannerAgent
SQLAgent --> ExecutorAgent

ExecutorAgent --> MCP_Client
MCP_Client --> MCP_Server

MCP_Server --> SQLTool
MCP_Server --> DBTool
MCP_Server --> LLM

---

SQL AI Workflow

flowchart TD

UserQuestion

PlanCreation

SQLGeneration

QueryExecution

ResultFormatting

Response

UserQuestion --> PlanCreation
PlanCreation --> SQLGeneration
SQLGeneration --> QueryExecution
QueryExecution --> ResultFormatting
ResultFormatting --> Response

---

Example 1: Sales Query

Input:

Show top selling products

Flow:

1. Planner selects sales plan
2. SQL generated
3. Database queried
4. Result returned

---

Example 2: Customer Query

Input:

List all customers

Flow:

1. Planner selects customer plan
2. SQL generated
3. MCP DB tool executes query
4. Response returned

---

Example 3: Analytics Query

Input:

Total revenue by month

Flow:

1. Planner selects analytics plan
2. SQL aggregation generated
3. DB executes query
4. Result formatted

---

Enterprise Architecture

flowchart LR

Client

API_Gateway

SQLAgent

PlannerAgent

ExecutorAgent

MCP_Layer

DatabaseTool

SQLGenerator

Client --> API_Gateway
API_Gateway --> SQLAgent

SQLAgent --> PlannerAgent
PlannerAgent --> ExecutorAgent

ExecutorAgent --> MCP_Layer

MCP_Layer --> SQLGenerator
MCP_Layer --> DatabaseTool

---

Benefits of SQL AI Agent

1. Natural Language Access

• No SQL knowledge needed

---

2. Faster Analytics

• Instant query generation

---

3. Automation

• Reduces analyst workload

---

4. Enterprise Integration

• Works with existing databases

---

5. Scalable Architecture

• MCP-based modular design

---

Challenges

❌ SQL injection risks
❌ Incorrect query generation
❌ Complex schema mapping
❌ Performance issues
❌ Access control enforcement

---

Best Practices

✅ Always validate generated SQL
✅ Use read-only database roles
✅ Limit query complexity
✅ Add MCP tool validation layer
✅ Log all queries
✅ Use schema-aware prompts

---

Common Mistakes

❌ Direct execution of LLM SQL output
❌ No query validation
❌ No access control
❌ No pagination handling
❌ Ignoring performance tuning

---

When to Use SQL AI Agents

Use when:

• Business users need data access
• Analytics queries are frequent
• Reporting automation required
• Data exploration systems needed

---

When NOT to Use

Avoid when:

• Highly sensitive production DB writes
• Complex transactional systems
• Systems requiring strict manual SQL control

---

Summary

In this article, you learned:

• How to build a SQL AI Agent
• Planner + Executor architecture
• Text-to-SQL generation flow
• MCP integration for enterprise systems
• Database execution layer
• Enterprise architecture design
• Best practices and challenges

You now have a complete SQL AI Agent system, which can evolve into a full enterprise data intelligence platform using Java, Spring Boot, and MCP.

---