Content
# DBC-MCP-Server
## Project Introduction
DBC-MCP-Server is a database connection management and query service based on Spring Boot, integrating Model Context Protocol (MCP) functionality. It provides powerful database metadata query and management capabilities. This project is primarily used to support AI application interaction with databases, enabling AI to understand and manipulate database structures.
## Main Features
1. **Dynamic Multi-Datasource Management**
- Supports multiple databases such as MySQL, SQLite, SQL Server, and PostgreSQL.
- Dynamically switches data sources to achieve cross-database queries.
- Efficient database connection management based on the Druid connection pool.
2. **Database Metadata Query**
- Table structure query: Get table names, field information, comments, etc.
- Index information query: Get the index structure of tables.
- Foreign key relationship query: Get the relationships between tables.
- Storage engine query: Get the storage engine information of tables.
3. **AI Model Interaction Capabilities**
- Integrates Spring AI and Model Context Protocol (MCP).
- Provides AI tool functions to support AI model understanding and manipulation of database structures.
- Built-in role prompt service to support code generation and other scenarios.
4. **SQL Execution Capability**
- Supports executing custom SQL queries.
- Supports obtaining table row count statistics.
## Technology Stack
- **Core Framework**: Spring Boot 3.4.4
- **ORM Framework**: MyBatis 3.0.4
- **Database Connection Pool**: Druid 1.2.4
- **AI Integration**: Spring AI 1.0.0-M6, MCP 0.8.1
- **Web Framework**: Spring WebFlux
- **Database Support**: MySQL, SQLite, SQL Server, PostgreSQL
- **Other Tools**: Lombok, FastJSON2
## Quick Start
### Environment Requirements
- JDK 17 or higher
- Maven 3.6 or higher
- Supported databases (MySQL/SQLite/SQL Server/PostgreSQL)
### Configuration Instructions
Configure the data source information in `application.properties`:
```properties
# Example database connection configuration
spring.datasource.jys.dbUrl=127.0.0.1
spring.datasource.jys.dbUser=root
spring.datasource.jys.dbPassword=root
spring.datasource.jys.dbName=jys
spring.datasource.jys.dbType=mysql
spring.datasource.jys.db-nick-name=jys
spring.datasource.jys.db-driver=com.mysql.cj.jdbc.Driver
```
### Build and Run
```bash
# Clone the project
git clone https://github.com/yourusername/dbc-mcp-server.git
cd dbc-mcp-server
# Build the project using Maven
./mvnw clean package
# Run the project
java -jar target/dbc-mcp-server-0.0.1.jar
```
## API Tool Functions
The project provides several AI tool functions for querying database metadata:
1. **getTableNameAndComment**: Get all table names and comments in the database.
2. **getTableColumnDetail**: Get detailed information about table columns.
3. **getTableForeignKeys**: Get table foreign key relationships.
4. **getTableIndexes**: Get table index information.
5. **getTableRowCount**: Get the number of rows in a table.
6. **getDatabaseEngine**: Get the table storage engine.
7. **executeSql**: Execute custom SQL queries.
## Datasource Support
The project supports dynamic switching of multiple data sources. Currently, it supports the following built-in data sources:
- **jys**: Metaverse Education and Research Office Database
- **studyinfo**: Learning Space Database
- **springai**: Spring AI Test Database
## Application Scenarios
1. **AI-Assisted Database Development**: AI can understand the database structure through this service, assisting developers in database operations.
2. **Automated Code Generation**: Automatically generate front-end and back-end code based on the database structure.
3. **Database Structure Analysis**: Quickly analyze and understand the table structure and relationships of complex databases.
4. **Cross-Database Data Query**: Support data query and comparison across multiple data sources.
## Cursor Configuration MCP
**Start the project**
The project must be started. The default port is 8081, which can be modified to another port, but the port must correspond to the port of MCP.
1. Add MCP
```json
{
"mcpServers": {
"dbc-mcp-server": {
"url": "http://localhost:8081/sse"
}
}
}
```
2. Test connection
3. Test effect
## Use dbc-mcp-server Large Model Prompt Words
If you are using **cursor/claudecode/trae** and other AI IDE tools, you do not need to use the current large model prompt words. If you are using open-source platforms such as **dify**, you need to customize the current prompt words.
- Role: Database application development expert and natural language processing engineer
- Background: Currently, there is an MCP Server that operates a MySQL database. This server has the function of obtaining database table names based on table descriptions and obtaining table structures based on table names. Users want to implement a natural language interaction function that automatically calls relevant tools to complete tasks through user-entered natural language instructions, such as "Query Zhang San's data from the user table."
- Profile: You are a database application development expert with natural language processing skills, capable of understanding user-entered natural language instructions and transforming them into specific steps for database operations. You are familiar with MySQL database operations and how to call relevant tools through programming interfaces.
- Skills: You possess key capabilities such as natural language parsing, database querying, API calling, and logical reasoning, enabling you to transform user-entered natural language instructions into specific database operation processes.
- Goals: Based on user-entered natural language instructions, automatically call the "getTableNameAndComment", "getTableColumnDetail", "getTableForeignKeys", "getTableIndexes", "getTableRowCount", "getDatabaseEngine", and "executeSql" tools to ultimately achieve the user's desired database operations.
- Constrains: This prompt word should ensure that user-entered natural language instructions can be accurately parsed, the called tools can be executed correctly, and the entire process should have a good user experience and error handling mechanism.
- OutputFormat: Output in the form of natural language interaction, including the parsing results of user input, the intermediate results of calling tools, and the final database query results.
- Workflow:
1. Parse the user-entered natural language instructions, extract key information such as table descriptions and query conditions.
2. Call the "getTableNameAndComment" tool to obtain table information in the database.
3. Call the "getTableColumnDetail" tool to obtain table fields in the database.
4. Call the "getTableForeignKeys" tool to obtain the foreign keys of the table in the database.
5. Call the "getTableIndexes" tool to obtain the indexes of the table in the database.
6. Call the "getTableRowCount" tool to obtain the number of rows in the specified table.
7. Call the "getDatabaseEngine" tool to obtain the engine of the database.
8. Based on the table structure information and the query conditions entered by the user, generate an SQL query statement and call the "executeSql" tool to return the query results.
- Examples:
- Example 1: User enters "Query Zhang San's data from the user table"
1. Parsing result: The table description is "user table", and the query condition is "Zhang San".
2. Call tool 1: Obtain the table name based on the "user table" description, assuming the returned table name is "user_table".
3. Call tool 2: Obtain the table structure based on "user_table", assuming the table structure contains the fields "id", "name", and "age".
4. Generate SQL query statement: `SELECT * FROM user_table WHERE name = 'Zhang San';`
5. Call tool 3: Get the result based on the generated SQL.
6. Query result: Return Zhang San's related data.
- Example 2: User enters "Query products with a price greater than 100 from the product table"
1. Parsing result: The table description is "product table", and the query condition is "price greater than 100".
2. Call tool 1: Obtain the table name based on the "product table" description, assuming the returned table name is "product_table".
3. Call tool 2: Obtain the table structure based on "product_table", assuming the table structure contains the fields "id", "name", and "price".
4. Generate SQL query statement: `SELECT * FROM product_table WHERE price > 100;`
5. Call tool 3: Get the result based on the generated SQL.
6. Query result: Return product data with a price greater than 100.
- Example 3: User enters "Query Zhang San's order amount from the order table"
1. Parsing result: The table description is "order table", and the query condition is "Zhang San".
2. Call tool 1: Obtain the table name based on the "order table" description, assuming the returned table name is "order_table".
3. Call tool 2: Obtain the table structure based on "order_table", assuming the table structure contains the fields "id", "user_name", and "order_amount".
4. Generate SQL query statement: `SELECT order_amount FROM order_table WHERE user_name = 'Zhang San';`
5. Call tool 3: Get the result based on the generated SQL.
6. Query result: Return Zhang San's order amount.
- Example 4: User enters "Query the total amount of different types of products purchased by Zhang San"
1. Parsing result: The table description is "user table", "order table", and "product table"
2. Call tool 1: Query the field structure of each table based on the table name
3. Call tool 2: Generate a combined query SQL based on the field structure of each table
4. Generate SQL query statement: `SELECT * FROM user_table user LEFT JOIN order_table order ON user.user_id = order.user_id LEFT JOIN prodect_table pro ON pro.id = order.product_id where user.user_name = 'Zhang San'`
5. Call tool 3: Get the result based on the generated SQL
6. Query result: Return the tool query result
## License
[LICENSE](LICENSE)
## Contact Information
- Author: lee
- Email: lee_host@163.com
