用于SQL生成的Spring Boot MCP客户端

一个Spring Boot应用程序，作为客户端与模型上下文协议（MCP）服务器交互，使用大型语言模型（LLM）从自然语言提示生成SQL查询。

📋 目录

概述
建筑
运作原理
先决条件
安装
配置
运行应用程序
API 文档
请求和响应示例
邮差收藏
MCP服务器通信
故障排除

🎯 概述

此应用程序提供了一个REST API，用于：

接受用户的自然语言提示
从MCP服务器获取数据库架构上下文
使用LLM（GPT-4）根据提示和上下文生成SQL查询
返回生成的带有相关上下文的SQL查询

🏗️ 建筑

User/Client
    ↓
Spring Boot MCP Client (This Application - Port 8081)
    ↓
    ├─→ MCP Server (Port 8080) - Provides database schema context
    │
    └─→ OpenAI GPT-4 - Generates SQL from prompt + context

组件

SqlGenerator控制器：REST端点处理HTTP请求
SQL生成器服务：编排SQL生成的业务逻辑
Mcp客户端：用于与MCP服务器通信的客户端（JSON-RPC 2.0）
聊天客户端：用于调用OpenAI的LLM API的客户端
模型：使用Swagger文档请求/响应DTO

🔄 运作原理

逐步过程

用户发送提示 通过REST API：

   POST /api/sql/generate
   Body: { "prompt": "Get all users who registered in the last 30 days" }

服务层接收请求 和电话 SqlGeneratorService.generateSqlQuery()

MCP客户端获取上下文 从MCP服务器：

- 发送 resources/list 向MCP服务器发出请求 - 接收可用数据库架构资源列表 - 对于每种资源，发送 resources/read 请求 - 收集所有架构信息作为上下文

聊天客户端呼叫LLM:

- 将用户提示与获取的上下文相结合 - 向OpenAI GPT-4发送特定指令 - 接收生成的SQL查询

返回响应 对于用户：

- 生成的SQL查询 - 使用的数据库架构上下文

✅ 先决条件

Java 17或更高版本
Maven 3.6+或Gradle 7.0+
MCP服务器在端口8080（或配置的端口）上运行
OpenAI API密钥（用于GPT-4访问）

📦 安装

克隆存储库

git clone 
cd spring-sql-generator-mcp-client

构建项目

使用Maven：

mvn clean install

使用Gradle：

./gradlew build

⚙️ 配置

编辑 src/main/resources/application.properties:

# Application Name
spring.application.name=mcp-client

# MCP Server Configuration
mcp.server.url=http://localhost:8080/mcp

# OpenAI Configuration
openai.api-key=${OPENAI_API_KEY:your-api-key-here}
openai.model=gpt-4

# Server Port
server.port=8081

# Logging
logging.level.com.example.mcpclient=DEBUG

# Swagger UI
springdoc.swagger-ui.path=/swagger-ui.html

环境变量

设置您的OpenAI API密钥：

Windows（CMD）：

set OPENAI_API_KEY=sk-your-actual-api-key

Windows（PowerShell）：

$env:OPENAI_API_KEY="sk-your-actual-api-key"

Linux/Mac：

export OPENAI_API_KEY=sk-your-actual-api-key

🚀 运行应用程序

使用Maven：

mvn spring-boot:run

使用Gradle：

./gradlew bootRun

使用JAR：

java -jar target/spring-sql-generator-mcp-client-0.0.1-SNAPSHOT.jar

应用程序将于启动 http://localhost:8081

📚 API 文档

Swagger 用户界面

访问交互式API文档：

http://localhost:8081/swagger-ui.html

API终点

1.生成SQL查询

端点： POST /api/sql/generate

说明： 从自然语言提示符生成SQL查询

请求正文：

{
  "prompt": "string"
}

答复：

{
  "sqlQuery": "string",
  "context": "string"
}

2.健康检查

端点： GET /api/sql/health

说明： 检查服务是否正在运行

答复： 200 OK 带有文本“MCP客户端正在运行”

📝 请求和响应示例

示例1：简单用户查询

请求：

POST http://localhost:8081/api/sql/generate
Content-Type: application/json

{
  "prompt": "Get all users who registered in the last 30 days"
}

答复：

{
  "sqlQuery": "SELECT * FROM users WHERE registration_date >= DATE_SUB(NOW(), INTERVAL 30 DAY);",
  "context": "\n=== Database Schema - Users Table ===\nTable: users\nColumns:\n- id (INTEGER, PRIMARY KEY)\n- username (VARCHAR(255), NOT NULL)\n- email (VARCHAR(255), UNIQUE, NOT NULL)\n- registration_date (TIMESTAMP, DEFAULT CURRENT_TIMESTAMP)\n- is_active (BOOLEAN, DEFAULT TRUE)\n"
}

示例2：联接查询

请求：

POST http://localhost:8081/api/sql/generate
Content-Type: application/json

{
  "prompt": "Show all orders with customer names and total amounts for orders over $1000"
}

答复：

{
  "sqlQuery": "SELECT c.name, o.order_id, o.total_amount FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id WHERE o.total_amount > 1000;",
  "context": "\n=== Database Schema - Customers Table ===\nTable: customers\nColumns:\n- customer_id (INTEGER, PRIMARY KEY)\n- name (VARCHAR(255), NOT NULL)\n- email (VARCHAR(255))\n\n=== Database Schema - Orders Table ===\nTable: orders\nColumns:\n- order_id (INTEGER, PRIMARY KEY)\n- customer_id (INTEGER, FOREIGN KEY REFERENCES customers)\n- total_amount (DECIMAL(10,2))\n- order_date (DATE)\n"
}

示例3：聚合查询

请求：

POST http://localhost:8081/api/sql/generate
Content-Type: application/json

{
  "prompt": "Count how many products are in each category"
}

答复：

{
  "sqlQuery": "SELECT category, COUNT(*) as product_count FROM products GROUP BY category;",
  "context": "\n=== Database Schema - Products Table ===\nTable: products\nColumns:\n- product_id (INTEGER, PRIMARY KEY)\n- name (VARCHAR(255), NOT NULL)\n- category (VARCHAR(100))\n- price (DECIMAL(10,2))\n- stock_quantity (INTEGER)\n"
}

🔧 MCP服务器通信

MCP协议详细信息

应用程序通过HTTP使用JSON-RPC 2.0协议与MCP服务器通信。

1.列出可用资源

MCP对服务器的请求：

{
  "jsonrpc": "2.0",
  "method": "resources/list",
  "params": {},
  "id": "550e8400-e29b-41d4-a716-446655440000"
}

来自服务器的MCP响应：

{
  "jsonrpc": "2.0",
  "id": "550e8400-e29b-41d4-a716-446655440000",
  "result": {
    "resources": [
      {
        "uri": "schema://users",
        "name": "Users Table Schema",
        "description": "Schema definition for users table",
        "mimeType": "text/plain"
      },
      {
        "uri": "schema://orders",
        "name": "Orders Table Schema",
        "description": "Schema definition for orders table",
        "mimeType": "text/plain"
      }
    ]
  }
}

2.阅读资源内容

MCP对服务器的请求：

{
  "jsonrpc": "2.0",
  "method": "resources/read",
  "params": {
    "uri": "schema://users"
  },
  "id": "550e8400-e29b-41d4-a716-446655440001"
}

来自服务器的MCP响应：

{
  "jsonrpc": "2.0",
  "id": "550e8400-e29b-41d4-a716-446655440001",
  "result": {
    "contents": [
      {
        "uri": "schema://users",
        "mimeType": "text/plain",
        "text": "Table: users\nColumns:\n- id (INTEGER, PRIMARY KEY)\n- username (VARCHAR(255), NOT NULL)\n- email (VARCHAR(255), UNIQUE)\n- registration_date (TIMESTAMP)"
      }
    ]
  }
}

3.错误响应示例

MCP错误响应：

{
  "jsonrpc": "2.0",
  "id": "550e8400-e29b-41d4-a716-446655440000",
  "error": {
    "code": -32601,
    "message": "Method not found",
    "data": {
      "method": "invalid/method"
    }
  }
}

📮 邮差收藏

导入此收藏

创建新的Postman收藏并添加以下请求：

1.生成SQL-简单查询

{
  "name": "Generate SQL - Simple User Query",
  "request": {
    "method": "POST",
    "header": [
      {
        "key": "Content-Type",
        "value": "application/json"
      }
    ],
    "body": {
      "mode": "raw",
      "raw": "{\n  \"prompt\": \"Get all users who registered in the last 30 days\"\n}"
    },
    "url": {
      "raw": "http://localhost:8081/api/sql/generate",
      "protocol": "http",
      "host": ["localhost"],
      "port": "8081",
      "path": ["api", "sql", "generate"]
    }
  },
  "response": [
    {
      "name": "Success Response",
      "status": "OK",
      "code": 200,
      "body": "{\n  \"sqlQuery\": \"SELECT * FROM users WHERE registration_date >= DATE_SUB(NOW(), INTERVAL 30 DAY);\",\n  \"context\": \"\\n=== Database Schema - Users Table ===\\nTable: users\\nColumns:\\n- id (INTEGER, PRIMARY KEY)\\n- username (VARCHAR(255), NOT NULL)\\n- email (VARCHAR(255), UNIQUE, NOT NULL)\\n- registration_date (TIMESTAMP, DEFAULT CURRENT_TIMESTAMP)\\n- is_active (BOOLEAN, DEFAULT TRUE)\\n\"\n}"
    }
  ]
}

2.生成SQL-连接查询

{
  "name": "Generate SQL - Orders with Customer Names",
  "request": {
    "method": "POST",
    "header": [
      {
        "key": "Content-Type",
        "value": "application/json"
      }
    ],
    "body": {
      "mode": "raw",
      "raw": "{\n  \"prompt\": \"Show all orders with customer names and total amounts for orders over $1000\"\n}"
    },
    "url": {
      "raw": "http://localhost:8081/api/sql/generate",
      "protocol": "http",
      "host": ["localhost"],
      "port": "8081",
      "path": ["api", "sql", "generate"]
    }
  },
  "response": [
    {
      "name": "Success Response",
      "status": "OK",
      "code": 200,
      "body": "{\n  \"sqlQuery\": \"SELECT c.name, o.order_id, o.total_amount FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id WHERE o.total_amount > 1000;\",\n  \"context\": \"\\n=== Database Schema - Customers Table ===\\nTable: customers\\nColumns:\\n- customer_id (INTEGER, PRIMARY KEY)\\n- name (VARCHAR(255), NOT NULL)\\n- email (VARCHAR(255))\\n\\n=== Database Schema - Orders Table ===\\nTable: orders\\nColumns:\\n- order_id (INTEGER, PRIMARY KEY)\\n- customer_id (INTEGER, FOREIGN KEY REFERENCES customers)\\n- total_amount (DECIMAL(10,2))\\n- order_date (DATE)\\n\"\n}"
    }
  ]
}

3.生成SQL聚合查询

{
  "name": "Generate SQL - Product Count by Category",
  "request": {
    "method": "POST",
    "header": [
      {
        "key": "Content-Type",
        "value": "application/json"
      }
    ],
    "body": {
      "mode": "raw",
      "raw": "{\n  \"prompt\": \"Count how many products are in each category\"\n}"
    },
    "url": {
      "raw": "http://localhost:8081/api/sql/generate",
      "protocol": "http",
      "host": ["localhost"],
      "port": "8081",
      "path": ["api", "sql", "generate"]
    }
  },
  "response": [
    {
      "name": "Success Response",
      "status": "OK",
      "code": 200,
      "body": "{\n  \"sqlQuery\": \"SELECT category, COUNT(*) as product_count FROM products GROUP BY category;\",\n  \"context\": \"\\n=== Database Schema - Products Table ===\\nTable: products\\nColumns:\\n- product_id (INTEGER, PRIMARY KEY)\\n- name (VARCHAR(255), NOT NULL)\\n- category (VARCHAR(100))\\n- price (DECIMAL(10,2))\\n- stock_quantity (INTEGER)\\n\"\n}"
    }
  ]
}

4.生成SQL-复杂筛选器

{
  "name": "Generate SQL - Active Users with Recent Orders",
  "request": {
    "method": "POST",
    "header": [
      {
        "key": "Content-Type",
        "value": "application/json"
      }
    ],
    "body": {
      "mode": "raw",
      "raw": "{\n  \"prompt\": \"Find all active users who have placed at least 3 orders in the last 6 months\"\n}"
    },
    "url": {
      "raw": "http://localhost:8081/api/sql/generate",
      "protocol": "http",
      "host": ["localhost"],
      "port": "8081",
      "path": ["api", "sql", "generate"]
    }
  },
  "response": [
    {
      "name": "Success Response",
      "status": "OK",
      "code": 200,
      "body": "{\n  \"sqlQuery\": \"SELECT u.user_id, u.username, u.email, COUNT(o.order_id) as order_count FROM users u INNER JOIN orders o ON u.user_id = o.user_id WHERE u.is_active = TRUE AND o.order_date >= DATE_SUB(NOW(), INTERVAL 6 MONTH) GROUP BY u.user_id, u.username, u.email HAVING COUNT(o.order_id) >= 3;\",\n  \"context\": \"\\n=== Database Schema ===\\nMultiple tables with relationships...\\n\"\n}"
    }
  ]
}

5.健康检查

{
  "name": "Health Check",
  "request": {
    "method": "GET",
    "header": [],
    "url": {
      "raw": "http://localhost:8081/api/sql/health",
      "protocol": "http",
      "host": ["localhost"],
      "port": "8081",
      "path": ["api", "sql", "health"]
    }
  },
  "response": [
    {
      "name": "Success Response",
      "status": "OK",
      "code": 200,
      "body": "MCP Client is running"
    }
  ]
}

cURL示例

# Example 1: Simple Query
curl -X POST http://localhost:8081/api/sql/generate \
  -H "Content-Type: application/json" \
  -d '{"prompt": "Get all users who registered in the last 30 days"}'

# Example 2: Join Query
curl -X POST http://localhost:8081/api/sql/generate \
  -H "Content-Type: application/json" \
  -d '{"prompt": "Show all orders with customer names and total amounts for orders over $1000"}'

# Example 3: Aggregate Query
curl -X POST http://localhost:8081/api/sql/generate \
  -H "Content-Type: application/json" \
  -d '{"prompt": "Count how many products are in each category"}'

# Example 4: Health Check
curl http://localhost:8081/api/sql/health

🐛 故障排除

常见问题

1.MCP服务器连接失败

错误： Error fetching context from MCP server: Connection refused

解决方案：

确保MCP服务器在配置的端口上运行（默认值：8080）
检查 mcp.server.url 在 application.properties
验证网络连接

2.OpenAI API关键问题

错误： 401 Unauthorized 来自OpenAI

解决方案：

验证您的 OPENAI_API_KEY 环境变量设置正确
请检查您的API密钥是否有效并且有足够的信用
确保钥匙以开头 sk-

3.空上下文响应

错误： No context available from resources

解决方案：

验证MCP服务器是否正确配置了数据库架构资源
检查MCP服务器日志是否有错误
使用JSON-RPC请求直接测试MCP服务器

4.端口已在使用中

错误： Port 8081 is already in use

解决方案：

更改端口 application.properties: server.port=8082
或者使用端口8081停止进程

调试模式

启用详细日志记录：

logging.level.com.example.mcpclient=DEBUG
logging.level.org.springframework.web.reactive.function.client=DEBUG

📄 许可证

\[在此处添加您的许可证信息\]

🤝 贡献

\[在此处添加贡献指南\]

📧 联系

\[在此处添加联系信息\]

______________________________________________________________________

内置： Spring Boot 3.2.1、Java 17、WebFlux、OpenAI GPT-4、MCP协议

Spring SQL Generator MCP Client

快速接入

详细介绍

用于SQL生成的Spring Boot MCP客户端

📋 目录

🎯 概述

🏗️ 建筑

组件

🔄 运作原理

逐步过程

✅ 先决条件

📦 安装

克隆存储库

构建项目

使用Maven：

使用Gradle：

⚙️ 配置

环境变量

🚀 运行应用程序

使用Maven：

使用Gradle：

使用JAR：

📚 API 文档

Swagger 用户界面

API终点

1.生成SQL查询

2.健康检查

📝 请求和响应示例

示例1：简单用户查询

示例2：联接查询

示例3：聚合查询

🔧 MCP服务器通信

MCP协议详细信息

1.列出可用资源

2.阅读资源内容

3.错误响应示例

📮 邮差收藏

导入此收藏

1.生成SQL-简单查询

2.生成SQL-连接查询

3.生成SQL聚合查询

4.生成SQL-复杂筛选器

5.健康检查

cURL示例

🐛 故障排除

常见问题

1.MCP服务器连接失败

2.OpenAI API关键问题

3.空上下文响应

4.端口已在使用中

调试模式

📄 许可证

🤝 贡献

📧 联系

目录标签

接入字段

权限和风险

来源信息

继续浏览同类 MCP