# 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:
--------------------------------------------------------------------------------
```
HELP.md
target/
!.mvn/wrapper/maven-wrapper.jar
!**/src/main/**/target/
!**/src/test/**/target/
### STS ###
.apt_generated
.classpath
.factorypath
.project
.settings
.springBeans
.sts4-cache
### IntelliJ IDEA ###
.idea
*.iws
*.iml
*.ipr
### NetBeans ###
/nbproject/private/
/nbbuild/
/dist/
/nbdist/
/.nb-gradle/
build/
!**/src/main/**/build/
!**/src/test/**/build/
### VS Code ###
.vscode/
```
--------------------------------------------------------------------------------
/README.md:
--------------------------------------------------------------------------------
```markdown
# Oracle MCP Server
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.
## Features
- List all tables in the Oracle database
- Describe table structure (columns, data types, constraints)
- Execute SQL statements (SELECT, INSERT, UPDATE, DELETE)
- Secure database connection management
- Real-time communication via SSE
- Built on spring-ai-mcp-server-webmvc-spring-boot-starter
## Technology Stack
### Core Dependencies
- Spring Boot 3.2.0
- spring-ai-mcp-server-webmvc-spring-boot-starter
- Oracle JDBC Driver
- Model Context Protocol (MCP) Framework
- Lombok
### Development Tools
- Java 17
- Maven 3.6+
- Spring Boot Test Framework
- JUnit 5
## Getting Started
### Prerequisites
- JDK 17
- Maven 3.6+
- Oracle Database instance
- MCP Server environment
- Cursor IDE
### Configuration
#### 1. Database Configuration
Create or modify `application.properties` or `application.yml` with the following settings:
```yaml
oracle:
connectionString: jdbc:oracle:thin:@//your-oracle-host:1521/your-service-name
username: your-username
password: your-password
```
#### 2. Cursor MCP Configuration
In your Cursor IDE, configure the MCP server in the settings:
```json
{
"mcpServers": {
"oracle-mcp-server": {
"url": "http://{server-ip}:{server-port}/sse",
"enabled": true
}
}
}
```
This configuration:
- Sets the MCP server name as "oracle-mcp-server"
- Configures the server URL to "http://{server-ip}:{server-port}/sse"
- Replace {server-ip} with your server's IP address
- Replace {server-port} with your server's port number
- Enables the MCP server for use in Cursor
### Building the Project
```bash
mvn clean install
```
### Running the Application
```bash
mvn spring-boot:run
```
## API Tools
### 1. List Tables Tool
- Name: `list_tables`
- Description: Get a list of all tables in Oracle database
- Usage: No parameters required
- Returns: Newline-separated list of table names
### 2. Describe Table Tool
- Name: `describe_table`
- Description: Get structure information of specified table
- Parameter: `tableName` - Name of the table to describe
- Returns: CSV format of table structure including:
- Column names
- Data types
- Nullable status
- Data length
- Primary key information
### 3. Execute SQL Tool
- Name: `execute_sql`
- Description: Execute Oracle SQL statement
- Parameter: `sql` - SQL statement to execute
- Returns:
- For SELECT: CSV format of query results
- For INSERT/UPDATE/DELETE: Number of affected rows
## Implementation Details
### Architecture
```
com.mcp.oracle/
├── config/
│ └── OracleConfig.java # Database configuration
├── service/
│ └── OracleService.java # Core service implementation
└── OracleApplication.java # Application entry point
```
### Key Components
1. **OracleConfig**
- Handles database connection configuration
- Uses Spring's @ConfigurationProperties
- Secure password management
2. **OracleService**
- Implements MCP tools for database operations
- Manages database connections
- Handles SQL execution and result formatting
- Implements error handling and logging
3. **Connection Management**
- Uses Oracle's connection pooling
- Implements auto-closing of resources
- Handles connection errors gracefully
### Security Considerations
- Password encryption in configuration
- Connection pool management
- SQL injection prevention
- Error message sanitization
## Testing
The project includes comprehensive unit tests:
```bash
mvn test
```
Test coverage includes:
- Database connection
- Table listing
- Table structure description
- SQL execution
- Error handling
## Error Handling
The service implements robust error handling:
- Connection failures
- Invalid SQL statements
- Missing tables/columns
- Permission issues
## Contributing
1. Fork the repository
2. Create your feature branch
3. Commit your changes
4. Push to the branch
5. Create a Pull Request
## License
This project is licensed under the MIT License - see the LICENSE file for details.
## Support
For support and questions, please create an issue in the repository.
```
--------------------------------------------------------------------------------
/src/test/resources/application-test.yml:
--------------------------------------------------------------------------------
```yaml
spring:
application:
name: oracle-mcp-server-test
oracle:
connection:
string: jdbc:oracle:thin:@{ip}:{port}:{database}
username: {username}
password: {password}
```
--------------------------------------------------------------------------------
/src/main/resources/application.yml:
--------------------------------------------------------------------------------
```yaml
server:
port: 8080
spring:
application:
name: oracle-mcp-server
ai:
mcp:
server:
enabled: true
oracle:
connection:
string: jdbc:oracle:thin:@{ip}:{port}:{database}
username: {username}
password: {password}
```
--------------------------------------------------------------------------------
/src/main/java/com/mcp/oracle/config/OracleToolConfig.java:
--------------------------------------------------------------------------------
```java
package com.mcp.oracle.config;
import org.springframework.context.annotation.Configuration;
import org.springframework.boot.context.properties.ConfigurationProperties;
import lombok.Data;
/**
* Oracle database configuration class
*
* @author yue9527
*/
@Configuration
@ConfigurationProperties(prefix = "oracle")
@Data
public class OracleToolConfig {
/**
* Oracle database connection string
*/
private String connectionString;
/**
* Oracle database username
*/
private String username;
/**
* Oracle database password
*/
private String password;
public String getConnectionString() {
return connectionString;
}
public String getUsername() {
return username;
}
public String getPassword() {
return password;
}
}
```
--------------------------------------------------------------------------------
/src/main/java/com/mcp/oracle/OracleMcpServerApplication.java:
--------------------------------------------------------------------------------
```java
package com.mcp.oracle;
import org.springframework.ai.tool.ToolCallbackProvider;
import org.springframework.ai.tool.method.MethodToolCallbackProvider;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import com.mcp.oracle.service.OracleToolService;
/**
* Oracle MCP Server Application
* Main entry point for the Oracle MCP server application
*
* @author yue9527
*/
@SpringBootApplication
@EnableConfigurationProperties
public class OracleMcpServerApplication {
/**
* Main method to start the application
*
* @param args command line arguments
* @author yue9527
*/
public static void main(String[] args) {
SpringApplication.run(OracleMcpServerApplication.class, args);
}
@Bean
public ToolCallbackProvider oracleTools(OracleToolService oracleToolService) {
return MethodToolCallbackProvider.builder().toolObjects(oracleToolService).build();
}
}
```
--------------------------------------------------------------------------------
/pom.xml:
--------------------------------------------------------------------------------
```
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.oracle.mcp</groupId>
<artifactId>oracle-mcp-server</artifactId>
<version>1.0-SNAPSHOT</version>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>3.2.0</version>
</parent>
<properties>
<java.version>17</java.version>
<spring-ai.version>1.0.0-M6</spring-ai.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.ai</groupId>
<artifactId>spring-ai-mcp-server-webmvc-spring-boot-starter</artifactId>
<version>${spring-ai.version}</version>
</dependency>
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc11</artifactId>
<version>21.9.0.0</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<!-- Test Dependencies -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter-api</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter-engine</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
</project>
```
--------------------------------------------------------------------------------
/src/test/java/com/mcp/oracle/OracleToolServiceTest.java:
--------------------------------------------------------------------------------
```java
package com.mcp.oracle;
import static org.junit.jupiter.api.Assertions.*;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;
import com.mcp.oracle.service.OracleToolService;
import lombok.extern.slf4j.Slf4j;
/**
* Test class for OracleService
* Contains unit tests for all OracleService methods
*
* @author yue9527
*/
@SpringBootTest
@ActiveProfiles("test")
@Slf4j
public class OracleToolServiceTest {
@Autowired
private OracleToolService oracleToolService;
/**
* Test listTables method
* Verifies that the method returns a non-empty list of tables
*
* @author yue9527
*/
@Test
public void testListTables() {
String result = oracleToolService.listTables();
log.info(result);
assertNotNull(result);
assertFalse(result.isEmpty());
assertFalse(result.startsWith("Error:"));
}
/**
* Test describeTable method
* Verifies that the method returns valid table structure information
*
* @author yue9527
*/
@Test
public void testDescribeTable() {
// First get a table name
String tableName = oracleToolService.listTables().split("\n")[0];
log.info("firstTable---"+tableName);
String result = oracleToolService.describeTable(tableName);
log.info(result);
assertNotNull(result);
assertFalse(result.isEmpty());
assertFalse(result.startsWith("Error:"));
assertTrue(result.contains("COLUMN_NAME"));
}
/**
* Test executeSql method with valid SELECT query
* Verifies that the method returns valid query results
*
* @author yue9527
*/
@Test
public void testExecuteQuery() {
// First get a table name
String tableName = oracleToolService.listTables().split("\n")[0];
String query = "SELECT * FROM " + tableName + " WHERE ROWNUM <= 5";
String result = oracleToolService.executeSql(query);
log.info(result);
assertNotNull(result);
assertFalse(result.isEmpty());
assertFalse(result.startsWith("Error:"));
}
/**
* Test executeSql method with invalid query
* Verifies that the method handles non-SELECT statements appropriately
*
* @author yue9527
*/
@Test
public void testExecuteQueryWithInvalidQuery() {
String result = oracleToolService.executeSql("INSERT INTO test VALUES (1)");
log.info(result);
assertTrue(result.startsWith("Error: Only SELECT statements are supported."));
}
}
```
--------------------------------------------------------------------------------
/src/main/java/com/mcp/oracle/service/OracleToolService.java:
--------------------------------------------------------------------------------
```java
package com.mcp.oracle.service;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;
import org.springframework.ai.tool.annotation.Tool;
import org.springframework.ai.tool.annotation.ToolParam;
import org.springframework.stereotype.Service;
import com.mcp.oracle.config.OracleToolConfig;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;
/**
* Oracle database service implementation
* Provides methods for interacting with Oracle database
*
* @author yue9527
*/
@Service
public class OracleToolService {
private final OracleToolConfig oracleToolConfig;
/**
* Constructor for OracleService
*
* @param oracleToolConfig Oracle database configuration
* @author yue9527
*/
public OracleToolService(OracleToolConfig oracleToolConfig) {
this.oracleToolConfig = oracleToolConfig;
}
/**
* Get Oracle database connection
*
* @return OracleConnection instance
* @throws Exception if connection fails
* @author yue9527
*/
private OracleConnection getConnection() throws Exception {
OracleDataSource ds = new OracleDataSource();
ds.setURL(oracleToolConfig.getConnectionString());
ds.setUser(oracleToolConfig.getUsername());
ds.setPassword(oracleToolConfig.getPassword());
return (OracleConnection) ds.getConnection();
}
/**
* Get a list of all tables in Oracle database
* Returns a newline-separated list of table names
*
* @return String containing list of table names
* @author yue9527
*/
@Tool(name = "list_tables", description = "Get a list of all tables in Oracle database")
public String listTables() {
try (OracleConnection conn = getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT table_name FROM user_tables ORDER BY table_name")) {
List<String> tables = new ArrayList<>();
while (rs.next()) {
tables.add(rs.getString(1));
}
return tables.stream()
.collect(Collectors.joining("\n"));
} catch (Exception e) {
return "Error: " + e.getMessage();
}
}
/**
* Get structure information of specified table in Oracle database
* Returns table structure in CSV format including column names, data types, and
* constraints
*
* @param tableName name of the table to describe
* @return String containing table structure in CSV format
* @author yue9527
*/
@Tool(name = "describe_table", description = "Get structure information of specified table in Oracle database")
public String describeTable(@ToolParam(description = "Table name to describe") String tableName) {
try (OracleConnection conn = getConnection();
Statement stmt = conn.createStatement()) {
StringBuilder result = new StringBuilder();
result.append("COLUMN_NAME,DATA_TYPE,NULLABLE,DATA_LENGTH,PRIMARY_KEY\n");
// Get primary keys
List<String> pkColumns = new ArrayList<>();
try (ResultSet rs = stmt.executeQuery(
"SELECT cols.column_name FROM all_constraints cons, all_cons_columns cols " +
"WHERE cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name " +
"AND cons.owner = cols.owner AND cols.table_name = '" + tableName.toUpperCase() + "'")) {
while (rs.next()) {
pkColumns.add(rs.getString(1));
}
}
// Get column info
try (ResultSet rs = stmt.executeQuery(
"SELECT column_name, data_type, nullable, data_length " +
"FROM user_tab_columns WHERE table_name = '" + tableName.toUpperCase() + "' " +
"ORDER BY column_id")) {
while (rs.next()) {
String colName = rs.getString(1);
result.append(String.format("%s,%s,%s,%d,%s\n",
colName,
rs.getString(2),
rs.getString(3),
rs.getInt(4),
pkColumns.contains(colName) ? "YES" : "NO"));
}
}
return result.toString();
} catch (Exception e) {
e.printStackTrace();
return "Error: " + e.getMessage();
}
}
/**
* Execute Oracle SQL statement
* Supports both query (SELECT) and non-query (INSERT, UPDATE, DELETE)
* statements
*
* @param sql SQL statement to execute
* @return String containing query results or affected rows count
* @author yue9527
*/
@Tool(name = "execute_sql", description = "Execute Oracle SQL statement")
public String executeSql(@ToolParam(description = "SQL statement to execute") String sql) {
try {
if (sql.trim().toUpperCase().startsWith("SELECT")) {
try (OracleConnection conn = getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
StringBuilder result = new StringBuilder();
// Add column headers
int columnCount = rs.getMetaData().getColumnCount();
for (int i = 1; i <= columnCount; i++) {
if (i > 1)
result.append(",");
result.append(rs.getMetaData().getColumnName(i));
}
result.append("\n");
// Add data rows
while (rs.next()) {
for (int i = 1; i <= columnCount; i++) {
if (i > 1)
result.append(",");
Object value = rs.getObject(i);
result.append(value != null ? value.toString() : "NULL");
}
result.append("\n");
}
return result.toString();
}
} else {
// Handle non-query statements (INSERT, UPDATE, DELETE, etc.)
try (OracleConnection conn = getConnection();
Statement stmt = conn.createStatement()) {
int affectedRows = stmt.executeUpdate(sql);
return "Success: " + affectedRows + " rows affected";
}
}
} catch (Exception e) {
return "Error: " + e.getMessage();
}
}
}
```