JDBC Guide

Important

The JDBC interface is experimental and not production-ready at this stage. APIs and behaviors are subject to change.

This guide covers using JDBC to interact with the FoundationDB Record Layer’s SQL interface.

Driver Setup

The Record Layer provides two JDBC drivers depending on your deployment architecture:

  • Embedded Driver: For applications running in the same JVM as the Record Layer

  • Server Driver: For client applications connecting to a remote Record Layer server via gRPC

The embedded driver class com.apple.foundationdb.relational.api.EmbeddedRelationalDriver is registered with Java’s Service Loader and automatically discovered by the JDBC DriverManager when on the classpath.

Maven Dependency:

<dependency>
    <groupId>org.foundationdb</groupId>
    <artifactId>fdb-relational-core</artifactId>
    <version>VERSION_NUMBER</version>
</dependency>

Replace VERSION_NUMBER with the appropriate version for your project.

The server driver class com.apple.foundationdb.relational.jdbc.JDBCRelationalDriver connects to a remote Record Layer server via gRPC. It is also registered with Java’s Service Loader.

Maven Dependency:

<dependency>
    <groupId>org.foundationdb</groupId>
    <artifactId>fdb-relational-jdbc</artifactId>
    <version>VERSION_NUMBER</version>
</dependency>

Replace VERSION_NUMBER with the appropriate version for your project.

Connection Strings

The connection string format differs between the embedded and server drivers:

JDBC URLs use the jdbc:embed: prefix followed by the database path and query parameters:

jdbc:embed:/<database_path>?schema=<schema_name>

Examples:

Connecting to the system database __SYS using the CATALOG schema:

String url = "jdbc:embed:/__SYS?schema=CATALOG";
Connection connection = DriverManager.getConnection(url);

Connecting to a custom database:

String url = "jdbc:embed:/FRL/YCSB?schema=YCSB";
Connection connection = DriverManager.getConnection(url);

Connection Parameters:

  • Database path: The path to the database (e.g., /__SYS, /FRL/YCSB)

  • schema: (Required) The schema to use for the connection

JDBC URLs use the jdbc:relational:// prefix followed by the server host, database path, and query parameters:

jdbc:relational://<host>:<port>/<database_path>?schema=<schema_name>

Examples:

Connecting to a Record Layer server:

String url = "jdbc:relational://localhost:7243/__SYS?schema=CATALOG";
Connection connection = DriverManager.getConnection(url);

Connecting to a custom database on a remote server:

String url = "jdbc:relational://server.example.com:7243/FRL/YCSB?schema=YCSB";
Connection connection = DriverManager.getConnection(url);

Connection Parameters:

  • host: The hostname or IP address of the Record Layer server

  • port: The port number (default: 7243)

  • Database path: The path to the database (e.g., /__SYS, /FRL/YCSB)

  • schema: (Required) The schema to use for the connection

Basic Usage

Once you have a connection, all JDBC operations work the same way regardless of which driver you’re using.

Creating a Connection

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class JDBCExample {
    public static void main(String[] args) {
        String url = "jdbc:embed:/mydb?schema=myschema";

        try (Connection conn = DriverManager.getConnection(url)) {
            // Use the connection
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class JDBCExample {
    public static void main(String[] args) {
        String url = "jdbc:relational://localhost:7243/mydb?schema=myschema";

        try (Connection conn = DriverManager.getConnection(url)) {
            // Use the connection
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Executing Queries

Use Statement for simple queries:

try (Connection conn = DriverManager.getConnection(url)) {
    try (Statement stmt = conn.createStatement()) {
        try (ResultSet rs = stmt.executeQuery("SELECT * FROM products WHERE price > 100")) {
            while (rs.next()) {
                long id = rs.getLong("id");
                String name = rs.getString("name");
                long price = rs.getLong("price");
                System.out.println(id + ": " + name + " - $" + price);
            }
        }
    }
}

Executing Updates

try (Connection conn = DriverManager.getConnection(url)) {
    try (Statement stmt = conn.createStatement()) {
        int rowsAffected = stmt.executeUpdate(
            "INSERT INTO products (id, name, price) VALUES (1, 'Widget', 100)"
        );
        System.out.println("Rows affected: " + rowsAffected);
    }
}

Prepared Statements

Prepared statements provide better security by using parameter binding to prevent SQL injection:

String sql = "INSERT INTO products (id, name, price, stock) VALUES (?, ?, ?, ?)";

try (Connection conn = DriverManager.getConnection(url)) {
    try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
        pstmt.setLong(1, 1);
        pstmt.setString(2, "Widget A");
        pstmt.setLong(3, 100);
        pstmt.setInt(4, 50);

        int rowsAffected = pstmt.executeUpdate();
        System.out.println("Rows inserted: " + rowsAffected);
    }
}

Querying with Prepared Statements

String sql = "SELECT * FROM products WHERE category = ? AND price >= ?";

try (Connection conn = DriverManager.getConnection(url)) {
    try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
        pstmt.setString(1, "Electronics");
        pstmt.setLong(2, 100);

        try (ResultSet rs = pstmt.executeQuery()) {
            while (rs.next()) {
                System.out.println(rs.getString("name") + ": $" + rs.getLong("price"));
            }
        }
    }
}

Transaction Management

Auto-Commit Mode

By default, JDBC connections operate in auto-commit mode where each statement is automatically committed. You can check and modify this behavior:

try (Connection conn = DriverManager.getConnection(url)) {
    System.out.println("Auto-commit: " + conn.getAutoCommit());

    // Disable auto-commit for manual transaction control
    conn.setAutoCommit(false);
}

Manual Transaction Control

For operations that need to execute as a unit, disable auto-commit and manually control transactions:

try (Connection conn = DriverManager.getConnection(url)) {
    conn.setAutoCommit(false);

    try (Statement stmt = conn.createStatement()) {
        stmt.executeUpdate("INSERT INTO accounts (id, balance) VALUES (1, 1000)");
        stmt.executeUpdate("INSERT INTO accounts (id, balance) VALUES (2, 500)");
        stmt.executeUpdate("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
        stmt.executeUpdate("UPDATE accounts SET balance = balance + 100 WHERE id = 2");

        conn.commit();
        System.out.println("Transaction committed successfully");
    } catch (SQLException e) {
        conn.rollback();
        System.out.println("Transaction rolled back due to error: " + e.getMessage());
        throw e;
    }
}

Working with ResultSets

Retrieving Data

try (Connection conn = DriverManager.getConnection(url)) {
    try (Statement stmt = conn.createStatement()) {
        try (ResultSet rs = stmt.executeQuery("SELECT * FROM products ORDER BY price")) {
            while (rs.next()) {
                long id = rs.getLong("id");
                String name = rs.getString("name");
                long price = rs.getLong("price");

                System.out.printf("ID: %d, Name: %s, Price: %d%n", id, name, price);
            }
        }
    }
}

Handling Null Values

try (Connection conn = DriverManager.getConnection(url)) {
    try (Statement stmt = conn.createStatement()) {
        try (ResultSet rs = stmt.executeQuery("SELECT * FROM products")) {
            while (rs.next()) {
                long price = rs.getLong("price");
                if (rs.wasNull()) {
                    System.out.println("Price is NULL");
                } else {
                    System.out.println("Price: " + price);
                }
            }
        }
    }
}

ResultSet Metadata

try (Connection conn = DriverManager.getConnection(url)) {
    try (Statement stmt = conn.createStatement()) {
        try (ResultSet rs = stmt.executeQuery("SELECT * FROM products")) {
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();

            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i);
                String columnType = metaData.getColumnTypeName(i);
                System.out.println(columnName + " (" + columnType + ")");
            }
        }
    }
}

Error Handling

Proper error handling is essential for robust JDBC applications:

try (Connection conn = DriverManager.getConnection(url)) {
    try (Statement stmt = conn.createStatement()) {
        stmt.executeUpdate("INSERT INTO products (id, name) VALUES (1, 'Widget')");
    }
} catch (SQLException e) {
    System.err.println("SQL Error: " + e.getMessage());
    System.err.println("SQL State: " + e.getSQLState());
    System.err.println("Error Code: " + e.getErrorCode());

    // Handle specific error conditions
    if (e.getSQLState().startsWith("23")) {
        System.err.println("Integrity constraint violation");
    }
}

Database Metadata

Retrieve information about the database structure:

try (Connection conn = DriverManager.getConnection(url)) {
    DatabaseMetaData metaData = conn.getMetaData();

    System.out.println("Database: " + metaData.getDatabaseProductName());
    System.out.println("Driver: " + metaData.getDriverName());
    System.out.println("Driver Version: " + metaData.getDriverVersion());

    // List tables
    try (ResultSet tables = metaData.getTables(null, null, "%", new String[]{"TABLE"})) {
        while (tables.next()) {
            System.out.println("Table: " + tables.getString("TABLE_NAME"));
        }
    }
}

Complete Example

Here’s a complete example demonstrating common JDBC operations:

/**
 * Example demonstrating basic JDBC operations with the embedded driver.
 * This example is from the JDBC Guide documentation.
 */
public class ProductManagerEmbedded {
    private static final String CATALOG_URL = "jdbc:embed:/__SYS?schema=CATALOG";
    private static final String APP_URL = "jdbc:embed:/FRL/shop?schema=SHOP";

    public static void main(String[] args) {
        try {
            setupDatabase();
            insertProducts();
            queryProducts();
            updateProduct();
            deleteProduct();
            System.out.println("All operations completed successfully!");
        } catch (SQLException e) {
            e.printStackTrace();
            System.exit(1);
        }
    }

    private static void setupDatabase() throws SQLException {
        // Connect to CATALOG to create database objects
        try (Connection conn = DriverManager.getConnection(CATALOG_URL)) {
            try (Statement stmt = conn.createStatement()) {
                // Drop existing objects if they exist
                stmt.executeUpdate("DROP DATABASE IF EXISTS \"/FRL/shop\"");
                stmt.executeUpdate("DROP SCHEMA TEMPLATE IF EXISTS shop_template");

                // Create schema template with table definition
                stmt.executeUpdate(
                    "CREATE SCHEMA TEMPLATE shop_template " +
                    "CREATE TABLE products (" +
                    "  id BIGINT PRIMARY KEY, " +
                    "  name STRING, " +
                    "  category STRING, " +
                    "  price BIGINT, " +
                    "  stock INTEGER" +
                    ")"
                );

                // Create database and schema
                stmt.executeUpdate("CREATE DATABASE \"/FRL/shop\"");
                stmt.executeUpdate("CREATE SCHEMA \"/FRL/shop/SHOP\" WITH TEMPLATE shop_template");

                System.out.println("Database and schema created successfully");
            }
        }
    }

    private static void insertProducts() throws SQLException {
        String sql = "INSERT INTO products (id, name, category, price, stock) VALUES (?, ?, ?, ?, ?)";

        try (Connection conn = DriverManager.getConnection(APP_URL)) {
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                conn.setAutoCommit(false);

                Object[][] products = {
                    {1L, "Widget A", "Electronics", 100L, 50},
                    {2L, "Widget B", "Electronics", 150L, 30},
                    {3L, "Gadget X", "Electronics", 200L, 20}
                };

                for (Object[] product : products) {
                    pstmt.setLong(1, (Long) product[0]);
                    pstmt.setString(2, (String) product[1]);
                    pstmt.setString(3, (String) product[2]);
                    pstmt.setLong(4, (Long) product[3]);
                    pstmt.setInt(5, (Integer) product[4]);
                    pstmt.executeUpdate();
                }

                conn.commit();
                System.out.println("Products inserted successfully");
            }
        }
    }

    private static void queryProducts() throws SQLException {
        String sql = "SELECT * FROM products WHERE category = ? ORDER BY price";

        try (Connection conn = DriverManager.getConnection(APP_URL)) {
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setString(1, "Electronics");

                try (ResultSet rs = pstmt.executeQuery()) {
                    System.out.println("\nProducts in Electronics category:");
                    while (rs.next()) {
                        System.out.printf("  %d: %s - $%d (Stock: %d)%n",
                            rs.getLong("id"),
                            rs.getString("name"),
                            rs.getLong("price"),
                            rs.getInt("stock")
                        );
                    }
                }
            }
        }
    }

    private static void updateProduct() throws SQLException {
        String sql = "UPDATE products SET price = ?, stock = ? WHERE id = ?";

        try (Connection conn = DriverManager.getConnection(APP_URL)) {
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setLong(1, 120L);
                pstmt.setInt(2, 45);
                pstmt.setLong(3, 1L);

                int rowsAffected = pstmt.executeUpdate();
                System.out.println("\nUpdated " + rowsAffected + " product(s)");
            }
        }
    }

    private static void deleteProduct() throws SQLException {
        String sql = "DELETE FROM products WHERE id = ?";

        try (Connection conn = DriverManager.getConnection(APP_URL)) {
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setLong(1, 3L);

                int rowsAffected = pstmt.executeUpdate();
                System.out.println("Deleted " + rowsAffected + " product(s)");
            }
        }
    }
}

See Also