用于SQL生成的Spring Boot MCP客户端
一个Spring Boot应用程序，作为客户端与模型上下文协议（MCP）服务器交互，使用大型语言模型（LLM）从自然语言提示生成SQL查询。
📋 目录
🎯 概述
此应用程序提供了一个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-keyWindows（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.htmlAPI终点
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协议