# Directory Structure
```
├── .gitignore
├── pom.xml
├── README.md
└── src
├── main
│ ├── java
│ │ └── com
│ │ └── mcp
│ │ └── oracle
│ │ ├── config
│ │ │ └── OracleToolConfig.java
│ │ ├── OracleMcpServerApplication.java
│ │ └── service
│ │ └── OracleToolService.java
│ └── resources
│ └── application.yml
└── test
├── java
│ └── com
│ └── mcp
│ └── oracle
│ └── OracleToolServiceTest.java
└── resources
└── application-test.yml
```
# Files
--------------------------------------------------------------------------------
/.gitignore:
--------------------------------------------------------------------------------
```
1 | HELP.md
2 | target/
3 | !.mvn/wrapper/maven-wrapper.jar
4 | !**/src/main/**/target/
5 | !**/src/test/**/target/
6 |
7 | ### STS ###
8 | .apt_generated
9 | .classpath
10 | .factorypath
11 | .project
12 | .settings
13 | .springBeans
14 | .sts4-cache
15 |
16 | ### IntelliJ IDEA ###
17 | .idea
18 | *.iws
19 | *.iml
20 | *.ipr
21 |
22 | ### NetBeans ###
23 | /nbproject/private/
24 | /nbbuild/
25 | /dist/
26 | /nbdist/
27 | /.nb-gradle/
28 | build/
29 | !**/src/main/**/build/
30 | !**/src/test/**/build/
31 |
32 | ### VS Code ###
33 | .vscode/
34 |
```
--------------------------------------------------------------------------------
/README.md:
--------------------------------------------------------------------------------
```markdown
1 | # Oracle MCP Server
2 |
3 | A Model Context Protocol (MCP) server implementation for Oracle database operations, built on top of `spring-ai-mcp-server-webmvc-spring-boot-starter`. This service provides a set of tools for interacting with Oracle databases through MCP, utilizing Server-Sent Events (SSE) for real-time communication.
4 |
5 | ## Features
6 |
7 | - List all tables in the Oracle database
8 | - Describe table structure (columns, data types, constraints)
9 | - Execute SQL statements (SELECT, INSERT, UPDATE, DELETE)
10 | - Secure database connection management
11 | - Real-time communication via SSE
12 | - Built on spring-ai-mcp-server-webmvc-spring-boot-starter
13 |
14 | ## Technology Stack
15 |
16 | ### Core Dependencies
17 | - Spring Boot 3.2.0
18 | - spring-ai-mcp-server-webmvc-spring-boot-starter
19 | - Oracle JDBC Driver
20 | - Model Context Protocol (MCP) Framework
21 | - Lombok
22 |
23 | ### Development Tools
24 | - Java 17
25 | - Maven 3.6+
26 | - Spring Boot Test Framework
27 | - JUnit 5
28 |
29 | ## Getting Started
30 |
31 | ### Prerequisites
32 |
33 | - JDK 17
34 | - Maven 3.6+
35 | - Oracle Database instance
36 | - MCP Server environment
37 | - Cursor IDE
38 |
39 | ### Configuration
40 |
41 | #### 1. Database Configuration
42 | Create or modify `application.properties` or `application.yml` with the following settings:
43 |
44 | ```yaml
45 | oracle:
46 | connectionString: jdbc:oracle:thin:@//your-oracle-host:1521/your-service-name
47 | username: your-username
48 | password: your-password
49 | ```
50 |
51 | #### 2. Cursor MCP Configuration
52 | In your Cursor IDE, configure the MCP server in the settings:
53 |
54 | ```json
55 | {
56 | "mcpServers": {
57 | "oracle-mcp-server": {
58 | "url": "http://{server-ip}:{server-port}/sse",
59 | "enabled": true
60 | }
61 | }
62 | }
63 | ```
64 |
65 | This configuration:
66 | - Sets the MCP server name as "oracle-mcp-server"
67 | - Configures the server URL to "http://{server-ip}:{server-port}/sse"
68 | - Replace {server-ip} with your server's IP address
69 | - Replace {server-port} with your server's port number
70 | - Enables the MCP server for use in Cursor
71 |
72 | ### Building the Project
73 |
74 | ```bash
75 | mvn clean install
76 | ```
77 |
78 | ### Running the Application
79 |
80 | ```bash
81 | mvn spring-boot:run
82 | ```
83 |
84 | ## API Tools
85 |
86 | ### 1. List Tables Tool
87 | - Name: `list_tables`
88 | - Description: Get a list of all tables in Oracle database
89 | - Usage: No parameters required
90 | - Returns: Newline-separated list of table names
91 |
92 | ### 2. Describe Table Tool
93 | - Name: `describe_table`
94 | - Description: Get structure information of specified table
95 | - Parameter: `tableName` - Name of the table to describe
96 | - Returns: CSV format of table structure including:
97 | - Column names
98 | - Data types
99 | - Nullable status
100 | - Data length
101 | - Primary key information
102 |
103 | ### 3. Execute SQL Tool
104 | - Name: `execute_sql`
105 | - Description: Execute Oracle SQL statement
106 | - Parameter: `sql` - SQL statement to execute
107 | - Returns:
108 | - For SELECT: CSV format of query results
109 | - For INSERT/UPDATE/DELETE: Number of affected rows
110 |
111 | ## Implementation Details
112 |
113 | ### Architecture
114 |
115 | ```
116 | com.mcp.oracle/
117 | ├── config/
118 | │ └── OracleConfig.java # Database configuration
119 | ├── service/
120 | │ └── OracleService.java # Core service implementation
121 | └── OracleApplication.java # Application entry point
122 | ```
123 |
124 | ### Key Components
125 |
126 | 1. **OracleConfig**
127 | - Handles database connection configuration
128 | - Uses Spring's @ConfigurationProperties
129 | - Secure password management
130 |
131 | 2. **OracleService**
132 | - Implements MCP tools for database operations
133 | - Manages database connections
134 | - Handles SQL execution and result formatting
135 | - Implements error handling and logging
136 |
137 | 3. **Connection Management**
138 | - Uses Oracle's connection pooling
139 | - Implements auto-closing of resources
140 | - Handles connection errors gracefully
141 |
142 | ### Security Considerations
143 |
144 | - Password encryption in configuration
145 | - Connection pool management
146 | - SQL injection prevention
147 | - Error message sanitization
148 |
149 | ## Testing
150 |
151 | The project includes comprehensive unit tests:
152 |
153 | ```bash
154 | mvn test
155 | ```
156 |
157 | Test coverage includes:
158 | - Database connection
159 | - Table listing
160 | - Table structure description
161 | - SQL execution
162 | - Error handling
163 |
164 | ## Error Handling
165 |
166 | The service implements robust error handling:
167 | - Connection failures
168 | - Invalid SQL statements
169 | - Missing tables/columns
170 | - Permission issues
171 |
172 | ## Contributing
173 |
174 | 1. Fork the repository
175 | 2. Create your feature branch
176 | 3. Commit your changes
177 | 4. Push to the branch
178 | 5. Create a Pull Request
179 |
180 | ## License
181 |
182 | This project is licensed under the MIT License - see the LICENSE file for details.
183 |
184 | ## Support
185 |
186 | For support and questions, please create an issue in the repository.
```
--------------------------------------------------------------------------------
/src/test/resources/application-test.yml:
--------------------------------------------------------------------------------
```yaml
1 | spring:
2 | application:
3 | name: oracle-mcp-server-test
4 |
5 | oracle:
6 | connection:
7 | string: jdbc:oracle:thin:@{ip}:{port}:{database}
8 | username: {username}
9 | password: {password}
```
--------------------------------------------------------------------------------
/src/main/resources/application.yml:
--------------------------------------------------------------------------------
```yaml
1 | server:
2 | port: 8080
3 |
4 | spring:
5 | application:
6 | name: oracle-mcp-server
7 | ai:
8 | mcp:
9 | server:
10 | enabled: true
11 |
12 | oracle:
13 | connection:
14 | string: jdbc:oracle:thin:@{ip}:{port}:{database}
15 | username: {username}
16 | password: {password}
17 |
```
--------------------------------------------------------------------------------
/src/main/java/com/mcp/oracle/config/OracleToolConfig.java:
--------------------------------------------------------------------------------
```java
1 | package com.mcp.oracle.config;
2 |
3 | import org.springframework.context.annotation.Configuration;
4 | import org.springframework.boot.context.properties.ConfigurationProperties;
5 | import lombok.Data;
6 |
7 | /**
8 | * Oracle database configuration class
9 | *
10 | * @author yue9527
11 | */
12 | @Configuration
13 | @ConfigurationProperties(prefix = "oracle")
14 | @Data
15 | public class OracleToolConfig {
16 |
17 | /**
18 | * Oracle database connection string
19 | */
20 | private String connectionString;
21 |
22 | /**
23 | * Oracle database username
24 | */
25 | private String username;
26 |
27 | /**
28 | * Oracle database password
29 | */
30 | private String password;
31 |
32 | public String getConnectionString() {
33 | return connectionString;
34 | }
35 |
36 | public String getUsername() {
37 | return username;
38 | }
39 |
40 | public String getPassword() {
41 | return password;
42 | }
43 | }
```
--------------------------------------------------------------------------------
/src/main/java/com/mcp/oracle/OracleMcpServerApplication.java:
--------------------------------------------------------------------------------
```java
1 | package com.mcp.oracle;
2 |
3 | import org.springframework.ai.tool.ToolCallbackProvider;
4 | import org.springframework.ai.tool.method.MethodToolCallbackProvider;
5 | import org.springframework.boot.SpringApplication;
6 | import org.springframework.boot.autoconfigure.SpringBootApplication;
7 | import org.springframework.context.annotation.Bean;
8 | import org.springframework.boot.context.properties.EnableConfigurationProperties;
9 |
10 | import com.mcp.oracle.service.OracleToolService;
11 |
12 | /**
13 | * Oracle MCP Server Application
14 | * Main entry point for the Oracle MCP server application
15 | *
16 | * @author yue9527
17 | */
18 | @SpringBootApplication
19 | @EnableConfigurationProperties
20 | public class OracleMcpServerApplication {
21 |
22 | /**
23 | * Main method to start the application
24 | *
25 | * @param args command line arguments
26 | * @author yue9527
27 | */
28 | public static void main(String[] args) {
29 | SpringApplication.run(OracleMcpServerApplication.class, args);
30 | }
31 |
32 | @Bean
33 | public ToolCallbackProvider oracleTools(OracleToolService oracleToolService) {
34 | return MethodToolCallbackProvider.builder().toolObjects(oracleToolService).build();
35 | }
36 |
37 | }
```
--------------------------------------------------------------------------------
/pom.xml:
--------------------------------------------------------------------------------
```
1 | <?xml version="1.0" encoding="UTF-8"?>
2 | <project xmlns="http://maven.apache.org/POM/4.0.0"
3 | xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
4 | xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
5 | <modelVersion>4.0.0</modelVersion>
6 |
7 | <groupId>com.oracle.mcp</groupId>
8 | <artifactId>oracle-mcp-server</artifactId>
9 | <version>1.0-SNAPSHOT</version>
10 |
11 | <parent>
12 | <groupId>org.springframework.boot</groupId>
13 | <artifactId>spring-boot-starter-parent</artifactId>
14 | <version>3.2.0</version>
15 | </parent>
16 |
17 | <properties>
18 | <java.version>17</java.version>
19 | <spring-ai.version>1.0.0-M6</spring-ai.version>
20 | </properties>
21 |
22 | <dependencies>
23 | <dependency>
24 | <groupId>org.springframework.boot</groupId>
25 | <artifactId>spring-boot-starter-web</artifactId>
26 | </dependency>
27 |
28 | <dependency>
29 | <groupId>org.springframework.ai</groupId>
30 | <artifactId>spring-ai-mcp-server-webmvc-spring-boot-starter</artifactId>
31 | <version>${spring-ai.version}</version>
32 | </dependency>
33 |
34 | <dependency>
35 | <groupId>com.oracle.database.jdbc</groupId>
36 | <artifactId>ojdbc11</artifactId>
37 | <version>21.9.0.0</version>
38 | </dependency>
39 |
40 | <dependency>
41 | <groupId>org.projectlombok</groupId>
42 | <artifactId>lombok</artifactId>
43 | <optional>true</optional>
44 | </dependency>
45 |
46 | <!-- Test Dependencies -->
47 | <dependency>
48 | <groupId>org.springframework.boot</groupId>
49 | <artifactId>spring-boot-starter-test</artifactId>
50 | <scope>test</scope>
51 | </dependency>
52 |
53 | <dependency>
54 | <groupId>org.junit.jupiter</groupId>
55 | <artifactId>junit-jupiter-api</artifactId>
56 | <scope>test</scope>
57 | </dependency>
58 |
59 | <dependency>
60 | <groupId>org.junit.jupiter</groupId>
61 | <artifactId>junit-jupiter-engine</artifactId>
62 | <scope>test</scope>
63 | </dependency>
64 | </dependencies>
65 |
66 | <build>
67 | <plugins>
68 | <plugin>
69 | <groupId>org.springframework.boot</groupId>
70 | <artifactId>spring-boot-maven-plugin</artifactId>
71 | <configuration>
72 | <excludes>
73 | <exclude>
74 | <groupId>org.projectlombok</groupId>
75 | <artifactId>lombok</artifactId>
76 | </exclude>
77 | </excludes>
78 | </configuration>
79 | </plugin>
80 | </plugins>
81 | </build>
82 | </project>
83 |
```
--------------------------------------------------------------------------------
/src/test/java/com/mcp/oracle/OracleToolServiceTest.java:
--------------------------------------------------------------------------------
```java
1 | package com.mcp.oracle;
2 |
3 | import static org.junit.jupiter.api.Assertions.*;
4 |
5 | import org.junit.jupiter.api.Test;
6 | import org.springframework.beans.factory.annotation.Autowired;
7 | import org.springframework.boot.test.context.SpringBootTest;
8 | import org.springframework.test.context.ActiveProfiles;
9 |
10 | import com.mcp.oracle.service.OracleToolService;
11 |
12 | import lombok.extern.slf4j.Slf4j;
13 |
14 | /**
15 | * Test class for OracleService
16 | * Contains unit tests for all OracleService methods
17 | *
18 | * @author yue9527
19 | */
20 | @SpringBootTest
21 | @ActiveProfiles("test")
22 | @Slf4j
23 | public class OracleToolServiceTest {
24 |
25 | @Autowired
26 | private OracleToolService oracleToolService;
27 |
28 | /**
29 | * Test listTables method
30 | * Verifies that the method returns a non-empty list of tables
31 | *
32 | * @author yue9527
33 | */
34 | @Test
35 | public void testListTables() {
36 | String result = oracleToolService.listTables();
37 | log.info(result);
38 | assertNotNull(result);
39 | assertFalse(result.isEmpty());
40 | assertFalse(result.startsWith("Error:"));
41 | }
42 |
43 | /**
44 | * Test describeTable method
45 | * Verifies that the method returns valid table structure information
46 | *
47 | * @author yue9527
48 | */
49 | @Test
50 | public void testDescribeTable() {
51 | // First get a table name
52 | String tableName = oracleToolService.listTables().split("\n")[0];
53 | log.info("firstTable---"+tableName);
54 | String result = oracleToolService.describeTable(tableName);
55 | log.info(result);
56 | assertNotNull(result);
57 | assertFalse(result.isEmpty());
58 | assertFalse(result.startsWith("Error:"));
59 | assertTrue(result.contains("COLUMN_NAME"));
60 | }
61 |
62 | /**
63 | * Test executeSql method with valid SELECT query
64 | * Verifies that the method returns valid query results
65 | *
66 | * @author yue9527
67 | */
68 | @Test
69 | public void testExecuteQuery() {
70 | // First get a table name
71 | String tableName = oracleToolService.listTables().split("\n")[0];
72 |
73 | String query = "SELECT * FROM " + tableName + " WHERE ROWNUM <= 5";
74 | String result = oracleToolService.executeSql(query);
75 | log.info(result);
76 | assertNotNull(result);
77 | assertFalse(result.isEmpty());
78 | assertFalse(result.startsWith("Error:"));
79 | }
80 |
81 | /**
82 | * Test executeSql method with invalid query
83 | * Verifies that the method handles non-SELECT statements appropriately
84 | *
85 | * @author yue9527
86 | */
87 | @Test
88 | public void testExecuteQueryWithInvalidQuery() {
89 | String result = oracleToolService.executeSql("INSERT INTO test VALUES (1)");
90 | log.info(result);
91 | assertTrue(result.startsWith("Error: Only SELECT statements are supported."));
92 | }
93 |
94 | }
```
--------------------------------------------------------------------------------
/src/main/java/com/mcp/oracle/service/OracleToolService.java:
--------------------------------------------------------------------------------
```java
1 | package com.mcp.oracle.service;
2 |
3 | import java.sql.ResultSet;
4 | import java.sql.Statement;
5 | import java.util.ArrayList;
6 | import java.util.List;
7 | import java.util.stream.Collectors;
8 |
9 | import org.springframework.ai.tool.annotation.Tool;
10 | import org.springframework.ai.tool.annotation.ToolParam;
11 | import org.springframework.stereotype.Service;
12 |
13 | import com.mcp.oracle.config.OracleToolConfig;
14 |
15 | import oracle.jdbc.OracleConnection;
16 | import oracle.jdbc.pool.OracleDataSource;
17 |
18 | /**
19 | * Oracle database service implementation
20 | * Provides methods for interacting with Oracle database
21 | *
22 | * @author yue9527
23 | */
24 | @Service
25 | public class OracleToolService {
26 |
27 | private final OracleToolConfig oracleToolConfig;
28 |
29 | /**
30 | * Constructor for OracleService
31 | *
32 | * @param oracleToolConfig Oracle database configuration
33 | * @author yue9527
34 | */
35 | public OracleToolService(OracleToolConfig oracleToolConfig) {
36 | this.oracleToolConfig = oracleToolConfig;
37 | }
38 |
39 | /**
40 | * Get Oracle database connection
41 | *
42 | * @return OracleConnection instance
43 | * @throws Exception if connection fails
44 | * @author yue9527
45 | */
46 | private OracleConnection getConnection() throws Exception {
47 | OracleDataSource ds = new OracleDataSource();
48 | ds.setURL(oracleToolConfig.getConnectionString());
49 | ds.setUser(oracleToolConfig.getUsername());
50 | ds.setPassword(oracleToolConfig.getPassword());
51 | return (OracleConnection) ds.getConnection();
52 | }
53 |
54 | /**
55 | * Get a list of all tables in Oracle database
56 | * Returns a newline-separated list of table names
57 | *
58 | * @return String containing list of table names
59 | * @author yue9527
60 | */
61 | @Tool(name = "list_tables", description = "Get a list of all tables in Oracle database")
62 | public String listTables() {
63 | try (OracleConnection conn = getConnection();
64 | Statement stmt = conn.createStatement();
65 | ResultSet rs = stmt.executeQuery("SELECT table_name FROM user_tables ORDER BY table_name")) {
66 |
67 | List<String> tables = new ArrayList<>();
68 | while (rs.next()) {
69 | tables.add(rs.getString(1));
70 | }
71 |
72 | return tables.stream()
73 | .collect(Collectors.joining("\n"));
74 | } catch (Exception e) {
75 | return "Error: " + e.getMessage();
76 | }
77 | }
78 |
79 | /**
80 | * Get structure information of specified table in Oracle database
81 | * Returns table structure in CSV format including column names, data types, and
82 | * constraints
83 | *
84 | * @param tableName name of the table to describe
85 | * @return String containing table structure in CSV format
86 | * @author yue9527
87 | */
88 | @Tool(name = "describe_table", description = "Get structure information of specified table in Oracle database")
89 | public String describeTable(@ToolParam(description = "Table name to describe") String tableName) {
90 | try (OracleConnection conn = getConnection();
91 | Statement stmt = conn.createStatement()) {
92 |
93 | StringBuilder result = new StringBuilder();
94 | result.append("COLUMN_NAME,DATA_TYPE,NULLABLE,DATA_LENGTH,PRIMARY_KEY\n");
95 |
96 | // Get primary keys
97 | List<String> pkColumns = new ArrayList<>();
98 | try (ResultSet rs = stmt.executeQuery(
99 | "SELECT cols.column_name FROM all_constraints cons, all_cons_columns cols " +
100 | "WHERE cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name " +
101 | "AND cons.owner = cols.owner AND cols.table_name = '" + tableName.toUpperCase() + "'")) {
102 |
103 | while (rs.next()) {
104 | pkColumns.add(rs.getString(1));
105 | }
106 | }
107 |
108 | // Get column info
109 | try (ResultSet rs = stmt.executeQuery(
110 | "SELECT column_name, data_type, nullable, data_length " +
111 | "FROM user_tab_columns WHERE table_name = '" + tableName.toUpperCase() + "' " +
112 | "ORDER BY column_id")) {
113 |
114 | while (rs.next()) {
115 | String colName = rs.getString(1);
116 | result.append(String.format("%s,%s,%s,%d,%s\n",
117 | colName,
118 | rs.getString(2),
119 | rs.getString(3),
120 | rs.getInt(4),
121 | pkColumns.contains(colName) ? "YES" : "NO"));
122 | }
123 | }
124 |
125 | return result.toString();
126 | } catch (Exception e) {
127 | e.printStackTrace();
128 | return "Error: " + e.getMessage();
129 | }
130 | }
131 |
132 | /**
133 | * Execute Oracle SQL statement
134 | * Supports both query (SELECT) and non-query (INSERT, UPDATE, DELETE)
135 | * statements
136 | *
137 | * @param sql SQL statement to execute
138 | * @return String containing query results or affected rows count
139 | * @author yue9527
140 | */
141 | @Tool(name = "execute_sql", description = "Execute Oracle SQL statement")
142 | public String executeSql(@ToolParam(description = "SQL statement to execute") String sql) {
143 | try {
144 | if (sql.trim().toUpperCase().startsWith("SELECT")) {
145 | try (OracleConnection conn = getConnection();
146 | Statement stmt = conn.createStatement();
147 | ResultSet rs = stmt.executeQuery(sql)) {
148 |
149 | StringBuilder result = new StringBuilder();
150 |
151 | // Add column headers
152 | int columnCount = rs.getMetaData().getColumnCount();
153 | for (int i = 1; i <= columnCount; i++) {
154 | if (i > 1)
155 | result.append(",");
156 | result.append(rs.getMetaData().getColumnName(i));
157 | }
158 | result.append("\n");
159 |
160 | // Add data rows
161 | while (rs.next()) {
162 | for (int i = 1; i <= columnCount; i++) {
163 | if (i > 1)
164 | result.append(",");
165 | Object value = rs.getObject(i);
166 | result.append(value != null ? value.toString() : "NULL");
167 | }
168 | result.append("\n");
169 | }
170 |
171 | return result.toString();
172 | }
173 | } else {
174 | // Handle non-query statements (INSERT, UPDATE, DELETE, etc.)
175 | try (OracleConnection conn = getConnection();
176 | Statement stmt = conn.createStatement()) {
177 |
178 | int affectedRows = stmt.executeUpdate(sql);
179 | return "Success: " + affectedRows + " rows affected";
180 | }
181 | }
182 | } catch (Exception e) {
183 | return "Error: " + e.getMessage();
184 | }
185 | }
186 |
187 | }
```