Sakila Java MCP Server

A Java-based Model Context Protocol (MCP) server built with Spring AI that allows AI tools like Cursor or Claude Desktop to interact directly with a local Sakila SQLite database.

Features

• Automatic Schema Discovery: Lists tables and describes columns for the AI to understand the database structure.
• SQL Query Execution: Runs SELECT queries and returns results in a structured format.
• Spring AI Powered: Built using the official Spring AI MCP starter and @Tool annotations.

Prerequisites

• Java 17 or higher
• Maven
• Cursor or Claude Desktop (for use as an MCP client)

Project Structure

• src/main/java/com/example/mcp/McpServerApplication.java: Main Spring Boot entry point.
• src/main/java/com/example/mcp/DatabaseTools.java: Tool definitions (listTables, describeTable, executeQuery).
• sakila.db: The SQLite database file.
• target/java-db-mcp-server-0.0.1-SNAPSHOT.jar: The executable server JAR.

Build Instructions

If you make changes to the Java code or tools, rebuild the JAR:

mvn clean package -DskipTests

Setup in Cursor

1. Open Cursor Settings (Cmd + Shift + J).
2. Go to Features -> MCP.
3. Click + Add New MCP Server.
4. Enter the following:
   • Name: sakila-java-server
   • Type: command
   • Command:

     java -jar /Users/vjy0/.gemini/antigravity/scratch/java-db-mcp-server/target/java-db-mcp-server-0.0.1-SNAPSHOT.jar
     

Available Tools

Once connected, the AI will have access to:

1. listTables(): Lists all non-internal tables in the Sakila database.
2. describeTable(tableName): Provides column definitions for a specific table.
3. executeQuery(sql): Executes a provided SQL SELECT statement.

Example Queries

Try asking Cursor:

• "List all tables in my Sakila database."
• "Who are the top 5 actors by number of films they appeared in?"
• "What is the most popular film category by rental count?"

Configuration

Database connection settings are located in src/main/resources/application.properties.

spring.datasource.url=jdbc:sqlite:sakila.db
logging.level.root=OFF
spring.main.banner-mode=off

🐳 Dockerized Deployment (Any System)

To run this project on any system (Windows, Mac, Linux) without setting up Java or Kafka manually, use Docker.

1. Start the Kafka Infrastructure

Run the following to start Zookeeper and Kafka:

docker-compose up -d zookeeper kafka

2. Build the MCP Server Image

Build the container for the Java MCP app:

docker build -t java-db-mcp .

3. Registering with an MCP Client (e.g., Claude Desktop)

To use this server in Claude Desktop via Docker, use the following configuration in your claude_desktop_config.json:

For Mac/Windows (Local Kafka):

"java-db-mcp": {
  "command": "docker",
  "args": [
    "run",
    "-i",
    "--rm",
    "-e", "SPRING_KAFKA_BOOTSTRAP_SERVERS=host.docker.internal:9092",
    "java-db-mcp"
  ]
}

For Linux (Local Kafka):

"java-db-mcp": {
  "command": "docker",
  "args": [
    "run",
    "-i",
    "--rm",
    "--network", "host",
    "java-db-mcp"
  ]
}

🏗️ Development & Tools

The following tools are available to the AI assistant:

• listTables: Explore the database schema.
• describeTable: Inspect specific table structures.
• executeQuery: Run analytical SQL queries.
• searchFilmRegistry: Fast keywords search in the in-memory cache.
• deleteFilmById: Delete a film from both the database and cache (safely handles foreign keys).

Example Kafka Deletion Message:

# Push a film ID to Kafka to trigger automated deletion
echo "10" | docker exec -i kafka kafka-console-producer --bootstrap-server localhost:9092 --topic film-deletion-topic