Building a JDBC-based MCP Server: A Developer Guide


Introduction

The Model Context Protocol (MCP) provides a standardized way for AI assistants like Claude to interact with external data sources and tools. In this guide, we'll explore how to build an MCP server that connects to various data sources through JDBC drivers, based on the open-source CData JDBC MCP Server.

This server acts as a bridge between AI assistants and data sources, enabling natural language queries to retrieve information from databases, SaaS applications, and APIs without writing SQL directly. By the end of this guide, you'll understand how to create a similar server that works with any JDBC-compatible driver.

Project Structure and Setup

Let's start by examining the project structure. The server is organized around a core set of Java packages:

com.cdata.mcp
├── config        // Configuration management
├── resources     // MCP resource implementations
├── tools         // MCP tool implementations
└── util          // Utility classes

The project uses Maven for build management. Here's the essential part of the pom.xml:

<dependencies>
    <dependency>
        <groupId>io.modelcontextprotocol.sdk</groupId>
        <artifactId>mcp</artifactId>
        <version>0.8.1</version>
    </dependency>
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-simple</artifactId>
        <version>2.0.16</version>
    </dependency>
    <!-- Test dependencies -->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.13.2</version>
        <scope>test</scope>
    </dependency>
</dependencies>

The main dependency is the MCP Java SDK, which provides the protocol implementation. The project also uses SLF4J for logging.

Core Interfaces

The server defines two key interfaces:

  1. ITool - Represents an MCP tool that can be invoked by the client
  2. IResource - Represents an MCP resource that can be accessed by the client

Here's the ITool interface:

public interface ITool {
  public void register(McpServer.SyncSpec mcp) throws Exception;
  public McpSchema.CallToolResult run(Map<String, Object> args);
}

And the IResource interface:

public interface IResource {
  public void register(McpServer.SyncSpec mcp, Table table);
  public McpSchema.ReadResourceResult run(McpSchema.ReadResourceRequest args);
}

These interfaces define how tools and resources are registered with the MCP server and how they're executed when invoked by a client.

Configuration Management

The Config class handles parsing and validating the server configuration. It loads properties from a .prp file specified when starting the server.

public class Config {
  private static final String PREFIX = "Prefix";
  private static final String DRIVER = "DriverClass";
  private static final String DRIVER_JAR = "DriverPath";
  private static final String JDBC_URL = "JdbcUrl";
  private static final String TABLES = "Tables";
  private static final String LOG_FILE = "LogFile";
  
  // Properties and methods for configuration
  // ...
}

Required configuration properties include:

  • Prefix: The prefix for MCP tools (e.g., "salesforce")
  • DriverClass: The JDBC driver class name (e.g., "cdata.jdbc.salesforce.SalesforceDriver")
  • DriverPath: The path to the JDBC driver JAR file
  • JdbcUrl: The JDBC URL for connecting to the data source
  • Tables: Optional list of tables to expose (empty for all tables)

During initialization, the Config class loads the JDBC driver dynamically and validates the connection, ensuring everything is properly set up before starting the server.

JDBC Integration

The Config class handles loading the JDBC driver and creating connections to the data source. It uses a URLClassLoader to load the driver JAR at runtime:

private void loadDriver() throws Exception {
  URLClassLoader ucl = new URLClassLoader(
      new URL[] {
          new File(this.getDriverJar()).toURI().toURL(),
      },
      this.getClass().getClassLoader()
  );
  Class dc = ucl.loadClass(this.getDriver());
  this.driver = (Driver)dc.getDeclaredConstructor().newInstance();
  
  loadSqlInfo();
}

The newConnection method creates a new connection to the data source:

public Connection newConnection() throws SQLException {
  return this.driver.connect(this.getJdbcUrl(), new Properties());
}

This approach allows the server to work with any JDBC driver, making it highly versatile for connecting to different data sources.

Table Representation

The Table class represents a database table with catalog, schema, and name components:

public class Table {
  private String _catalog;
  private String _schema;
  private String _name;
  
  // Methods for table operations
  // ...
}

It includes methods for parsing table names from different formats and converting them to URL paths for MCP resources. The parseList method parses a comma-separated list of table names:

public static List<Table> parseList(String text) {
  List<Table> list = new ArrayList<>();
  Tokenizer t = new Tokenizer(text);
  while (!t.eof()) {
    if (list.size() > 0) {
      t.skipListDelimiter();
    }
    Table table = parseInt(t);
    if (table != null) {
      list.add(table);
    }
  }
  return list;
}

The class also includes utility methods for converting between different table name formats, including URL paths for MCP resources.

CSV Utilities

The server uses CSV as the output format for all tool results. The CsvWriter and CsvUtils classes handle converting JDBC ResultSet objects to CSV format:

public static String resultSetToCsv(ResultSet rs, String[][] columns) throws SQLException {
  CsvWriter csv = new CsvWriter();
  ResultSetMetaData meta = rs.getMetaData();
  writeMeta(csv, meta, columns);
  while (rs.next()) {
    writeRow(csv, meta, columns, rs);
  }
  rs.close();
  return csv.end();
}

The CsvWriter class provides a simple API for building CSV content:

public class CsvWriter {
  private StringBuilder buffer = new StringBuilder();

  public Row row() {
    return new Row();
  }

  public String end() {
    return this.buffer.toString();
  }
  
  // Inner Row class for building rows
  // ...
}

Tool Implementations

The server implements three MCP tools:

  1. GetTablesTool - Lists available tables in the data source
  2. GetColumnsTool - Lists columns for a specific table
  3. RunQueryTool - Executes a SQL SELECT query

Each tool implements the ITool interface and follows a similar pattern:

  1. Register the tool with the MCP server, defining its name, description, and parameter schema
  2. Implement the run method to handle tool invocation and return results

Here's part of the GetTablesTool implementation:

public class GetTablesTool implements ITool {
  private Config config;
  private Logger logger = LoggerFactory.getLogger(GetTablesTool.class);

  public GetTablesTool(Config config) {
    this.config = config;
  }

  public void register(McpServer.SyncSpec mcp) throws Exception {
    String schema = new JsonSchemaBuilder()
        .addString("catalog", "The catalog name")
        .addString("schema", "The schema name")
        .build();
    mcp.tool(
        new McpSchema.Tool(
            config.getPrefix() + "_get_tables",
            "Retrieves a list of objects, entities, collections, etc. (as tables) available in the data source...",
            schema
        ),
        this::run
    );
  }
  
  // Run method implementation
  // ...
}

The RunQueryTool is particularly interesting as it executes SQL queries provided by the client:

@Override
public McpSchema.CallToolResult run(Map<String, Object> args) {
  String sql = (String)args.get("sql");
  this.logger.info("RunQueryTool({})", sql);
  try {
    try (Connection cn = config.newConnection()) {
      List<McpSchema.Content> content = new ArrayList<>();
      String csv = queryToCsv(cn, sql);

      List<McpSchema.Role> roles = new ArrayList<>();
      roles.add(McpSchema.Role.USER);
      content.add(
          new McpSchema.TextContent(roles, 1.0, csv)
      );
      return new McpSchema.CallToolResult(content, false);
    }
  } catch ( Exception ex ) {
    throw new RuntimeException("ERROR: " + ex.getMessage());
  }
}

Resource Implementations

In addition to tools, the server can expose MCP resources. The TableMetadataResource class implements the IResource interface to provide metadata for database tables:

public class TableMetadataResource implements IResource {
  private Config config;
  private static final String[][] META_COLS = new String[][] {
      new String[] { "TABLE_CAT", "Catalog" },
      new String[] { "TABLE_SCHEM", "Schema" },
      new String[] { "TABLE_NAME", "Table" },
      new String[] { "COLUMN_NAME", "Column" },
      new String[] { "TYPE_NAME", "DataType" }
  };

  // Methods for resource implementation
  // ...
}

Resources are accessed via URLs and provide a way to browse database metadata.

MCP Server Configuration

The Program class serves as the entry point and sets up the MCP server with the appropriate tools and resources:

public class Program {
  private ServerMcpTransport transport;
  private Config config;
  private McpSyncServer mcpServer;
  private static final boolean STDIO = true;

  public void init(String configPath) throws Exception {
    // Initialize configuration
    // ...
    
    this.transport = new StdioServerTransport(new ObjectMapper());
  }

  public void configureMcp() throws Exception {
    McpServer.SyncSpec spec =
        McpServer.sync(this.transport)
            .serverInfo(this.config.getServerName(), this.config.getServerVersion())
            .capabilities(
                McpSchema.ServerCapabilities.builder()
                    .tools(true)
                    .resources(false, true)
                    .build()
            );

    registerResources(this.config, spec);
    registerTools(this.config, spec);

    this.mcpServer = spec.build();
  }
  
  // Main method and other configuration
  // ...
}

The server uses the StdioServerTransport to communicate with the client through standard input and output. This allows it to be easily integrated with Claude Desktop, which spawns the server as a child process.

JSON Schema Building

The JsonSchemaBuilder class provides a fluent API for building JSON Schema documents, which are used to define tool parameter schemas:

public class JsonSchemaBuilder {
  public static final String STRING = "string";
  public static final String INTEGER = "integer";
  public static final String NUMBER = "number";
  public static final String BOOLEAN = "boolean";
  public static final String OBJECT = "object";
  public static final String SCHEMA_REF = "http://json-schema.org/draft-07/schema#";

  private List<Property> properties = new ArrayList<>();
  private List<String> required = new ArrayList<>();

  public JsonSchemaBuilder addString(String name, String description) {
    this.properties.add(new Property(name, STRING, description));
    return this;
  }
  
  // Other methods for building JSON Schema
  // ...
}

This class simplifies the creation of JSON Schema documents for tool parameters, making it easy to define the expected input for each tool.

Testing

The project includes unit tests for key components like the Table class and JsonSchemaBuilder. These tests ensure that core functionality works as expected:

public class TableTests {
  @Test
  public void justName() {
    Table table = Table.parse("mytable");
    Assert.assertEquals("mytable", table.name());
  }
  
  // Other test methods
  // ...
}

Testing is an important part of the development process, ensuring that components work correctly in isolation before being integrated into the full server.

Putting It All Together

The entry point for the application is the main method in the Program class:

public static void main(String[] args) throws Exception {
  if (args.length < 1) {
    System.err.println("Usage: <properties-file-path>");
    System.exit(-1);
  }
  String path = args[0];

  final Program p = new Program();
  p.init(args[0]);
  p.configureMcp();
  if (!STDIO) {
    //p.runHttpServer();
  } else {
    Runtime.getRuntime().addShutdownHook(new Thread() {
      public void run() {
        synchronized (p) {
          p.notify();
        }
      }
    });
    synchronized (p) {
      p.wait();
      p.mcpServer.closeGracefully();
    }
  }
}

This method initializes the configuration, sets up the MCP server, and then waits for the process to be terminated. When running with Claude Desktop, the server will continue running until the client disconnects or terminates the process.

Integration with Claude Desktop

To use the server with Claude Desktop, you need to configure Claude Desktop to connect to the server. This is done by creating a claude_desktop_config.json file with the appropriate MCP server configuration:

{
  "mcpServers": {
    "salesforce": {
      "command": "PATH\\TO\\java.exe",
      "args": [
        "-jar",
        "PATH\\TO\\CDataMCP-jar-with-dependencies.jar",
        "PATH\\TO\\Salesforce.prp"
      ]
    }
  }
}

This configuration tells Claude Desktop how to launch the server process and what configuration file to use.

Conclusion

Building an MCP server that connects to JDBC data sources is a powerful way to extend the capabilities of AI assistants. By following the patterns and techniques outlined in this guide, you can create your own MCP servers that connect to a wide variety of data sources.

The key components include:

  1. Configuration management for JDBC drivers and connections
  2. MCP tool implementations for querying data
  3. CSV formatting for results
  4. MCP server configuration and lifecycle management

With these components in place, you can create a bridge between AI assistants and any data source with a JDBC driver, enabling natural language interaction with your data.

Free Trials & More Information

If you're ready to build your own MCP Server on top of a JDBC Driver, you can check out the GitHub repository and download a free, 30-day trial of any CData JDBC Driver.

If you're looking for a self-contained MCP Server, check out our free beta servers!

In this article

Related articles