Key-Based Data Access¶

Important

Transition API: The Key-Based Data Access API is provided primarily to ease migration from FDBRecordStore API usage to SQL-based queries. While it remains supported, it is not the recommended approach for new applications. For most use cases, SQL queries provide better flexibility, optimization, and maintainability. This API may evolve or be deprecated in future versions as the SQL interface matures.

The Key-Based Data Access API provides programmatic methods for common database operations without writing SQL. These operations work directly with primary keys and support:

Scan: Range queries over primary key prefixes
Get: Single record lookup by complete primary key
Insert: Programmatic record insertion with RelationalStruct
Delete: Key-based deletion (single, batch, and range)

For more complex queries, filtering, joins, or aggregations, use the SQL query language instead.

Getting Started¶

The Key-Based Data Access methods are available on RelationalStatement, which extends the standard JDBC Statement. To access these methods, unwrap your statement:

try (Connection conn = DriverManager.getConnection(url)) {
    try (Statement stmt = conn.createStatement()) {
        // Unwrap to RelationalStatement to access direct access methods
        RelationalStatement relStmt = stmt.unwrap(RelationalStatement.class);

        // Now you can use executeScan, executeGet, executeInsert, executeDelete
    }
}

Scan Operations¶

Scans retrieve multiple records that match a primary key prefix. The API supports pagination through continuations for handling large result sets.

Basic Scan¶

To scan all records in a table, use an empty KeySet:

try (Connection conn = DriverManager.getConnection(url)) {
    try (Statement stmt = conn.createStatement()) {
        RelationalStatement relStmt = stmt.unwrap(RelationalStatement.class);

        // Scan all records in the products table
        KeySet emptyKey = new KeySet();
        try (RelationalResultSet rs = relStmt.executeScan("products", emptyKey, Options.NONE)) {
            while (rs.next()) {
                long id = rs.getLong("id");
                String name = rs.getString("name");
                System.out.println("Product: " + id + " - " + name);
            }
        }
    }
}

Scan with Key Prefix¶

Narrow the scan to records matching a primary key prefix. The key columns must form a contiguous prefix of the primary key:

try (Connection conn = DriverManager.getConnection(url)) {
    try (Statement stmt = conn.createStatement()) {
        RelationalStatement relStmt = stmt.unwrap(RelationalStatement.class);

        // Scan products with a specific category (assuming category is part of the key)
        KeySet keyPrefix = new KeySet()
            .setKeyColumn("category", "Electronics");

        try (RelationalResultSet rs = relStmt.executeScan("products", keyPrefix, Options.NONE)) {
            while (rs.next()) {
                String name = rs.getString("name");
                long price = rs.getLong("price");
                System.out.println(name + ": $" + price);
            }
        }
    }
}

Key Prefix Rules:

Given a table with PRIMARY KEY(a, b, c):

✓ Valid: {a: 1}, {a: 1, b: 2}, {a: 1, b: 2, c: 3}
✗ Invalid: {b: 2} (not a prefix), {a: 1, c: 3} (skips b)

Scan with Continuation¶

For large result sets, use continuations to paginate through results:

try (Connection conn = DriverManager.getConnection(url)) {
    try (Statement stmt = conn.createStatement()) {
        RelationalStatement relStmt = stmt.unwrap(RelationalStatement.class);

        KeySet keyPrefix = new KeySet();
        int batchSize = 10;
        Continuation continuation = null;

        // Initial scan without continuation
        Options options = Options.builder()
            .withOption(Options.Name.MAX_ROWS, batchSize)
            .build();

        int totalRecords = 0;
        try (RelationalResultSet rs = relStmt.executeScan("products", keyPrefix, options)) {
            while (rs.next()) {
                // Process first batch
                System.out.println("Product: " + rs.getString("name"));
                totalRecords++;
            }
            continuation = rs.getContinuation();
        }

        // Continue scanning with continuation
        while (!continuation.atEnd()) {
            Options contOptions = Options.builder()
                .withOption(Options.Name.CONTINUATION, continuation)
                .withOption(Options.Name.MAX_ROWS, batchSize)
                .build();

            int rowCount = 0;
            try (RelationalResultSet rs = relStmt.executeScan("products", keyPrefix, contOptions)) {
                while (rs.next()) {
                    // Process record
                    System.out.println("Product: " + rs.getString("name"));
                    rowCount++;
                }

                // Get continuation for next batch
                continuation = rs.getContinuation();
            }

            totalRecords += rowCount;
            System.out.println("Processed " + rowCount + " records in this batch");
        }

        System.out.println("Total records processed: " + totalRecords);
    }
}

The continuation points to the first unread row after the current position in the ResultSet.

Get Operations¶

Get operations retrieve a single record by its complete primary key. The result set will contain either 0 or 1 row.

Basic Get¶

try (Connection conn = DriverManager.getConnection(url)) {
    try (Statement stmt = conn.createStatement()) {
        RelationalStatement relStmt = stmt.unwrap(RelationalStatement.class);

        // Get a single record by primary key
        KeySet primaryKey = new KeySet()
            .setKeyColumn("id", 1L);

        try (RelationalResultSet rs = relStmt.executeGet("products", primaryKey, Options.NONE)) {
            if (rs.next()) {
                String name = rs.getString("name");
                long price = rs.getLong("price");
                System.out.println("Found: " + name + " - $" + price);
            } else {
                System.out.println("Product not found");
            }
        }
    }
}

Get with Composite Key¶

For tables with composite primary keys, provide all key columns:

try (Connection conn = DriverManager.getConnection(url)) {
    try (Statement stmt = conn.createStatement()) {
        RelationalStatement relStmt = stmt.unwrap(RelationalStatement.class);

        // Get with composite primary key
        KeySet primaryKey = new KeySet()
            .setKeyColumn("store_id", 100L)
            .setKeyColumn("product_id", 1L);

        try (RelationalResultSet rs = relStmt.executeGet("inventory", primaryKey, Options.NONE)) {
            if (rs.next()) {
                int stock = rs.getInt("quantity");
                System.out.println("Stock level: " + stock);
            }
        }
    }
}

Important: Unlike scans, Get requires a complete primary key. Incomplete keys will cause an error.

Insert Operations¶

Insert operations add records to a table using RelationalStruct objects. The struct must contain values for all required columns.

Single Insert¶

Embedded Driver

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

    try (Statement stmt = conn.createStatement()) {
        RelationalStatement relStmt = stmt.unwrap(RelationalStatement.class);

        // Build a record to insert
        RelationalStruct product = EmbeddedRelationalStruct.newBuilder()
            .addLong("id", 100L)
            .addString("name", "New Widget")
            .addString("category", "Electronics")
            .addLong("price", 299L)
            .addInt("stock", 50)
            .build();

        int rowsInserted = relStmt.executeInsert("products", product);
        System.out.println("Inserted " + rowsInserted + " row(s)");

        conn.commit();
    } catch (SQLException e) {
        conn.rollback();
        throw e;
    }
}

Server Driver

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

    try (Statement stmt = conn.createStatement()) {
        RelationalStatement relStmt = stmt.unwrap(RelationalStatement.class);

        // Build a record to insert
        RelationalStruct product = JDBCRelationalStruct.newBuilder()
            .addLong("id", 100L)
            .addString("name", "New Widget")
            .addString("category", "Electronics")
            .addLong("price", 299L)
            .addInt("stock", 50)
            .build();

        int rowsInserted = relStmt.executeInsert("products", product);
        System.out.println("Inserted " + rowsInserted + " rows");

        conn.commit();
    }
}

Batch Insert¶

Insert multiple records in a single operation for better performance:

Embedded Driver

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

    try (Statement stmt = conn.createStatement()) {
        RelationalStatement relStmt = stmt.unwrap(RelationalStatement.class);

        // Build multiple records
        List<RelationalStruct> products = new ArrayList<>();

        products.add(EmbeddedRelationalStruct.newBuilder()
            .addLong("id", 101L)
            .addString("name", "Widget A")
            .addString("category", "Electronics")
            .addLong("price", 199L)
            .addInt("stock", 25)
            .build());

        products.add(EmbeddedRelationalStruct.newBuilder()
            .addLong("id", 102L)
            .addString("name", "Widget B")
            .addString("category", "Electronics")
            .addLong("price", 249L)
            .addInt("stock", 30)
            .build());

        int rowsInserted = relStmt.executeInsert("products", products);
        System.out.println("Inserted " + rowsInserted + " row(s)");

        conn.commit();
    } catch (SQLException e) {
        conn.rollback();
        throw e;
    }
}

Server Driver

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

    try (Statement stmt = conn.createStatement()) {
        RelationalStatement relStmt = stmt.unwrap(RelationalStatement.class);

        List<RelationalStruct> products = new ArrayList<>();
        products.add(JDBCRelationalStruct.newBuilder()
            .addLong("id", 101L)
            .addString("name", "Laptop")
            .addString("category", "Electronics")
            .addLong("price", 999L)
            .addInt("stock", 25)
            .build());

        products.add(JDBCRelationalStruct.newBuilder()
            .addLong("id", 102L)
            .addString("name", "Mouse")
            .addString("category", "Electronics")
            .addLong("price", 29L)
            .addInt("stock", 100)
            .build());

        int rowsInserted = relStmt.executeInsert("products", products);
        System.out.println("Batch inserted " + rowsInserted + " rows");

        conn.commit();
    }
}

Insert with Replace on Duplicate¶

Use the REPLACE_ON_DUPLICATE_PK option to update existing records instead of failing on primary key conflicts:

Embedded Driver

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

    try (Statement stmt = conn.createStatement()) {
        RelationalStatement relStmt = stmt.unwrap(RelationalStatement.class);

        RelationalStruct product = EmbeddedRelationalStruct.newBuilder()
            .addLong("id", 1L)
            .addString("name", "Updated Widget")
            .addString("category", "Electronics")
            .addLong("price", 199L)
            .addInt("stock", 100)
            .build();

        // Replace if the primary key already exists
        Options options = Options.builder()
            .withOption(Options.Name.REPLACE_ON_DUPLICATE_PK, true)
            .build();

        int rowsInserted = relStmt.executeInsert("products", product, options);
        System.out.println("Inserted/replaced " + rowsInserted + " row(s)");

        conn.commit();
    } catch (SQLException e) {
        conn.rollback();
        throw e;
    }
}

Server Driver

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

    try (Statement stmt = conn.createStatement()) {
        RelationalStatement relStmt = stmt.unwrap(RelationalStatement.class);

        RelationalStruct product = JDBCRelationalStruct.newBuilder()
            .addLong("id", 100L) // This might conflict with existing record
            .addString("name", "Updated Widget")
            .addString("category", "Electronics")
            .addLong("price", 349L)
            .addInt("stock", 75)
            .build();

        Options options = Options.builder()
            .withOption(Options.Name.REPLACE_ON_DUPLICATE_PK, true)
            .build();

        int rowsInserted = relStmt.executeInsert("products", product, options);
        System.out.println("Inserted/updated " + rowsInserted + " rows");

        conn.commit();
    }
}

Delete Operations¶

Delete operations remove records by their primary keys. Three variants are supported: single delete, batch delete, and range delete.

Single Delete¶

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

    try (Statement stmt = conn.createStatement()) {
        RelationalStatement relStmt = stmt.unwrap(RelationalStatement.class);

        // Delete by primary key
        KeySet primaryKey = new KeySet()
            .setKeyColumn("id", 100L);

        List<KeySet> keysToDelete = List.of(primaryKey);
        int rowsDeleted = relStmt.executeDelete("products", keysToDelete);
        System.out.println("Deleted " + rowsDeleted + " row(s)");

        conn.commit();
    } catch (SQLException e) {
        conn.rollback();
        throw e;
    }
}

Batch Delete¶

Delete multiple records by providing a collection of keys:

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

    try (Statement stmt = conn.createStatement()) {
        RelationalStatement relStmt = stmt.unwrap(RelationalStatement.class);

        // Delete multiple records by their keys
        List<KeySet> keysToDelete = List.of(
            new KeySet().setKeyColumn("id", 101L),
            new KeySet().setKeyColumn("id", 102L),
            new KeySet().setKeyColumn("id", 103L)
        );

        int rowsDeleted = relStmt.executeDelete("products", keysToDelete);
        System.out.println("Deleted " + rowsDeleted + " row(s)");

        conn.commit();
    } catch (SQLException e) {
        conn.rollback();
        throw e;
    }
}

Range Delete¶

Delete all records matching a primary key prefix using executeDeleteRange:

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

    try (Statement stmt = conn.createStatement()) {
        RelationalStatement relStmt = stmt.unwrap(RelationalStatement.class);

        // Delete all products in a specific category (assuming category is part of key)
        KeySet keyPrefix = new KeySet()
            .setKeyColumn("category", "Discontinued");

        relStmt.executeDeleteRange("products", keyPrefix, Options.NONE);
        System.out.println("Deleted all discontinued products");

        conn.commit();
    } catch (SQLException e) {
        conn.rollback();
        throw e;
    }
}

Warning: Range deletes can affect many records. Use with caution.

Working with KeySet¶

KeySet specifies primary key values for direct access operations. It supports fluent builder-style construction:

KeySet key = new KeySet()
    .setKeyColumn("column1", value1)
    .setKeyColumn("column2", value2);

Key Points:

Column names are case-sensitive and should match your schema
For scans and range deletes, provide a contiguous key prefix
For gets and point deletes, provide the complete primary key
Use KeySet.EMPTY or new KeySet() to match all records (full table scan)

Options¶

The Options class configures execution behavior for direct access operations. Common options include:

CONTINUATION¶

Specifies where to resume a scan operation (see continuation example above).

INDEX_HINT¶

Suggests a specific index for FRL to use:

try (Connection conn = DriverManager.getConnection(url)) {
    try (Statement stmt = conn.createStatement()) {
        RelationalStatement relStmt = stmt.unwrap(RelationalStatement.class);

        // Use a specific index for the scan
        Options options = Options.builder()
            .withOption(Options.Name.INDEX_HINT, "products_by_category")
            .build();

        KeySet keyPrefix = new KeySet()
            .setKeyColumn("category", "Electronics");

        try (RelationalResultSet rs = relStmt.executeScan("products", keyPrefix, options)) {
            while (rs.next()) {
                System.out.println("Product: " + rs.getString("name"));
            }
        }
    }
}

MAX_ROWS¶

Limits the number of rows returned in a single scan before prompting for continuation:

Options options = Options.builder()
    .withOption(Options.Name.MAX_ROWS, 100)
    .build();

REPLACE_ON_DUPLICATE_PK¶

When inserting, replaces existing records with conflicting primary keys instead of failing (see insert example above).

Building Options¶

Use the builder pattern to construct options:

Options options = Options.builder()
    .withOption(Options.Name.INDEX_HINT, "my_index")
    .withOption(Options.Name.MAX_ROWS, 50)
    .build();

// Or use Options.NONE for default behavior
relStmt.executeScan("table", keySet, Options.NONE);

Key-Based Data Access¶

Getting Started¶

Scan Operations¶

Basic Scan¶

Scan with Key Prefix¶

Scan with Continuation¶

Get Operations¶

Basic Get¶

Get with Composite Key¶

Insert Operations¶

Single Insert¶

Batch Insert¶

Insert with Replace on Duplicate¶

Delete Operations¶

Single Delete¶

Batch Delete¶

Range Delete¶

Working with KeySet¶

Options¶

CONTINUATION¶

INDEX_HINT¶

MAX_ROWS¶

REPLACE_ON_DUPLICATE_PK¶

Building Options¶

See Also¶