Advanced JDBC Features¶

Important

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

This guide covers FoundationDB Record Layer-specific JDBC features for working with complex data types like STRUCTs and ARRAYs.

Note

Unified API with Driver-Specific Entry Points: The Record Layer provides a unified API for creating STRUCT and ARRAY values through common interfaces (RelationalStruct, RelationalArray, RelationalStructBuilder, RelationalArrayBuilder). However, you must use the appropriate factory method for your JDBC driver:

Embedded Driver: EmbeddedRelationalStruct.newBuilder() and EmbeddedRelationalArray.newBuilder()
Server Driver: JDBCRelationalStruct.newBuilder() and JDBCRelationalArray.newBuilder()

Both factory methods return the same builder interfaces, so once you obtain a builder, all subsequent code is identical regardless of the driver. This design allows the API to abstract over different implementation backends (in-memory objects vs. gRPC serialization) while providing a consistent developer experience.

Working with STRUCT Types¶

The Record Layer extends standard JDBC to support STRUCT types, which represent nested record structures similar to protobuf messages. STRUCTs allow you to model hierarchical data within your relational schema.

Reading STRUCT Values¶

To read STRUCT values from a query result, unwrap the ResultSet to RelationalResultSet which provides direct access to STRUCTs:

try (Connection conn = DriverManager.getConnection(url)) {
    try (Statement stmt = conn.createStatement()) {
        try (ResultSet rs = stmt.executeQuery("SELECT reviewer FROM reviewers WHERE id = 1")) {
            // Unwrap to RelationalResultSet to access getStruct()
            RelationalResultSet relationalRs = rs.unwrap(RelationalResultSet.class);

            if (relationalRs.next()) {
                RelationalStruct reviewer = relationalRs.getStruct("reviewer");

                // Access STRUCT fields by name
                long id = reviewer.getLong("ID");
                String name = reviewer.getString("NAME");
                String email = reviewer.getString("EMAIL");

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

Accessing Nested STRUCT Fields¶

STRUCTs can contain other STRUCTs, allowing for deeply nested data structures. Here’s how to read a STRUCT with a nested STRUCT:

private static void queryData() throws SQLException {
    try (Connection conn = DriverManager.getConnection(APP_URL)) {
        try (Statement stmt = conn.createStatement()) {
            try (ResultSet rs = stmt.executeQuery("SELECT * FROM orders WHERE id = 1")) {
                if (rs.next()) {
                    long id = rs.getLong("id");
                    System.out.printf("Order %d:%n", id);

                    // Read customer STRUCT
                    RelationalResultSet relationalRs = rs.unwrap(RelationalResultSet.class);
                    RelationalStruct customer = relationalRs.getStruct("customer");
                    if (customer != null) {
                        String name = customer.getString("name");
                        String email = customer.getString("email");

                        System.out.printf("  Customer: %s (%s)%n", name, email);

                        // Access nested address STRUCT
                        RelationalStruct address = customer.getStruct("address");
                        if (address != null) {
                            String street = address.getString("street");
                            String city = address.getString("city");
                            System.out.printf("  Address: %s, %s%n", street, city);
                        }
                    }

Creating STRUCT Values¶

Use the appropriate builder depending on your JDBC driver. Both builders provide identical APIs. Here’s an example inserting a STRUCT into a STRUCT column:

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

    String sql = "INSERT INTO reviewers (id, reviewer) VALUES (?, ?)";
    try (RelationalPreparedStatement pstmt =
            conn.prepareStatement(sql).unwrap(RelationalPreparedStatement.class)) {

        // Build a STRUCT to insert into the reviewer column
        RelationalStruct reviewer = EmbeddedRelationalStruct.newBuilder()
            .addLong("ID", 1L)
            .addString("NAME", "Anthony Bourdain")
            .addString("EMAIL", "abourdain@example.com")
            .build();

        pstmt.setLong(1, 1L);
        pstmt.setObject(2, reviewer);

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

        conn.commit();
    }
}

Creating Nested STRUCTs¶

You can create STRUCTs that contain nested STRUCTs and insert them as a single value. Here’s an example that creates a customer STRUCT with a nested address STRUCT:

private static void insertData() throws SQLException {
    try (Connection conn = DriverManager.getConnection(APP_URL)) {
        conn.setAutoCommit(false);

        String sql = "INSERT INTO orders (id, customer, items, tags) " +
                     "VALUES (?, ?, ?, ?)";
        try (RelationalPreparedStatement pstmt =
                conn.prepareStatement(sql).unwrap(RelationalPreparedStatement.class)) {

            pstmt.setLong(1, 1L);

            // Create a STRUCT with nested STRUCT for customer
            RelationalStruct customer = EmbeddedRelationalStruct.newBuilder()
                .addString("name", "Alice Johnson")
                .addString("email", "alice@example.com")
                .addStruct("address", EmbeddedRelationalStruct.newBuilder()
                    .addString("street", "123 Main St")
                    .addString("city", "Springfield")
                    .build())
                .build();

Handling NULL STRUCT Fields¶

You can set NULL values for individual fields within a STRUCT:

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

    String sql = "INSERT INTO reviewers (id, reviewer) VALUES (?, ?)";
    try (RelationalPreparedStatement pstmt =
            conn.prepareStatement(sql).unwrap(RelationalPreparedStatement.class)) {

        // Create STRUCT with NULL email field
        RelationalStruct reviewer = EmbeddedRelationalStruct.newBuilder()
            .addLong("ID", 1L)
            .addString("NAME", "Anthony Bourdain")
            .addObject("EMAIL", null)  // Explicitly set NULL
            .build();

        pstmt.setLong(1, 1L);
        pstmt.setObject(2, reviewer);

        int inserted = pstmt.executeUpdate();
        conn.commit();
    }
}

Working with ARRAY Types¶

The Record Layer supports ARRAY types containing elements of any SQL type, including primitives, STRUCTs, and even nested ARRAYs.

Reading ARRAY Values¶

Use the getArray() method to retrieve ARRAY values from a ResultSet:

try (Connection conn = DriverManager.getConnection(url)) {
    try (Statement stmt = conn.createStatement()) {
        try (ResultSet rs = stmt.executeQuery("SELECT tags FROM products WHERE id = 1")) {
            if (rs.next()) {
                Array tagsArray = rs.getArray("tags");

                if (tagsArray != null) {
                    // Method 1: Get array as Object[]
                    Object[] tags = (Object[]) tagsArray.getArray();
                    for (Object tag : tags) {
                        System.out.println("Tag: " + tag);
                    }
                }
            }
        }
    }
}

Using ResultSet to Iterate Arrays¶

You can also retrieve array elements as a ResultSet for more structured access:

try (Connection conn = DriverManager.getConnection(url)) {
    try (Statement stmt = conn.createStatement()) {
        try (ResultSet rs = stmt.executeQuery("SELECT tags FROM products WHERE id = 1")) {
            if (rs.next()) {
                Array tagsArray = rs.getArray("tags");

                if (tagsArray != null) {
                    // Method 2: Get array as ResultSet
                    try (ResultSet arrayRs = tagsArray.getResultSet()) {
                        // ResultSet has two columns:
                        // Column 1: index (1-based)
                        // Column 2: value
                        while (arrayRs.next()) {
                            int index = arrayRs.getInt(1);
                            String value = arrayRs.getString(2);
                            System.out.printf("tags[%d] = %s%n", index, value);
                        }
                    }
                }
            }
        }
    }
}

Creating ARRAY Values¶

Use the appropriate builder depending on your JDBC driver. Both builders provide identical APIs:

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

    String sql = "INSERT INTO products (id, name, tags) VALUES (?, ?, ?)";
    try (RelationalPreparedStatement pstmt =
            conn.prepareStatement(sql).unwrap(RelationalPreparedStatement.class)) {

        pstmt.setLong(1, 1L);
        pstmt.setString(2, "Widget");
        pstmt.setArray(3, EmbeddedRelationalArray.newBuilder()
            .addAll("electronics", "gadget", "popular")
            .build());

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

        conn.commit();
    }
}

Working with Different Array Element Types¶

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

    String sql = "INSERT INTO data_table (id, integers, floats, booleans) " +
                 "VALUES (?, ?, ?, ?)";
    try (RelationalPreparedStatement pstmt =
            conn.prepareStatement(sql).unwrap(RelationalPreparedStatement.class)) {

        pstmt.setLong(1, 1L);

        // Array of integers
        pstmt.setArray(2, EmbeddedRelationalArray.newBuilder()
            .addAll(10, 20, 30, 40)
            .build());

        // Array of floats
        pstmt.setArray(3, EmbeddedRelationalArray.newBuilder()
            .addAll(1.5f, 2.5f, 3.5f)
            .build());

        // Array of booleans
        pstmt.setArray(4, EmbeddedRelationalArray.newBuilder()
            .addAll(true, false, true)
            .build());

        pstmt.executeUpdate();
        conn.commit();
    }
}

Handling NULL Arrays¶

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

    String sql = "INSERT INTO products (id, name, tags) VALUES (?, ?, ?)";
    try (RelationalPreparedStatement pstmt =
            conn.prepareStatement(sql).unwrap(RelationalPreparedStatement.class)) {

        pstmt.setLong(1, 1L);
        pstmt.setString(2, "Widget");
        pstmt.setNull(3, Types.ARRAY);  // Set NULL array

        pstmt.executeUpdate();
        conn.commit();
    }
}

Complex Nested Types¶

Arrays of STRUCTs¶

ARRAYs can contain STRUCT elements, allowing for collections of complex records:

Embedded Driver

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

    String sql = "INSERT INTO orders (id, items) VALUES (?, ?)";
    try (RelationalPreparedStatement pstmt =
            conn.prepareStatement(sql).unwrap(RelationalPreparedStatement.class)) {

        pstmt.setLong(1, 1L);

        // Create an array of STRUCT values
        pstmt.setArray(2, EmbeddedRelationalArray.newBuilder()
            .addAll(
                EmbeddedRelationalStruct.newBuilder()
                    .addInt("product_id", 11)
                    .addString("product_name", "Widget A")
                    .addInt("quantity", 2)
                    .build(),
                EmbeddedRelationalStruct.newBuilder()
                    .addInt("product_id", 22)
                    .addString("product_name", "Widget B")
                    .addInt("quantity", 5)
                    .build()
            )
            .build());

        pstmt.executeUpdate();
        conn.commit();
    }
}

Server Driver

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

    String sql = "INSERT INTO orders (id, items) VALUES (?, ?)";
    try (RelationalPreparedStatement pstmt =
            conn.prepareStatement(sql).unwrap(RelationalPreparedStatement.class)) {

        pstmt.setLong(1, 1L);

        // Create an array of STRUCT values
        pstmt.setArray(2, JDBCRelationalArray.newBuilder()
            .addAll(
                JDBCRelationalStruct.newBuilder()
                    .addInt("product_id", 11)
                    .addString("product_name", "Widget A")
                    .addInt("quantity", 2)
                    .build(),
                JDBCRelationalStruct.newBuilder()
                    .addInt("product_id", 22)
                    .addString("product_name", "Widget B")
                    .addInt("quantity", 5)
                    .build()
            )
            .build());

        pstmt.executeUpdate();
        conn.commit();
    }
}

Reading Arrays of STRUCTs¶

When reading arrays that contain STRUCT elements, unwrap the array’s ResultSet to RelationalResultSet:

Array itemsArray = rs.getArray("items");
if (itemsArray != null) {
    System.out.println("  Items:");
    try (ResultSet arrayRs = itemsArray.getResultSet()) {
        // Unwrap to RelationalResultSet to access getStruct()
        RelationalResultSet itemsRelRs = arrayRs.unwrap(RelationalResultSet.class);

        while (itemsRelRs.next()) {
            int index = itemsRelRs.getInt(1);
            RelationalStruct item = itemsRelRs.getStruct(2);

            int productId = item.getInt("product_id");
            String itemName = item.getString("name");
            int quantity = item.getInt("quantity");

            System.out.printf("    - %s (ID: %d) x %d%n",
                itemName, productId, quantity);
        }
    }
}

STRUCTs Containing Arrays¶

STRUCTs can contain ARRAY fields. You can insert the entire STRUCT including its arrays as a single value:

Embedded Driver

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

    String sql = "INSERT INTO products (id, product_info) VALUES (?, ?)";
    try (RelationalPreparedStatement pstmt =
            conn.prepareStatement(sql).unwrap(RelationalPreparedStatement.class)) {

        // Create STRUCT with embedded ARRAY fields
        RelationalStruct product = EmbeddedRelationalStruct.newBuilder()
            .addLong("ID", 1L)
            .addString("NAME", "Multi-tool")
            .addArray("TAGS", EmbeddedRelationalArray.newBuilder()
                .addAll("versatile", "portable", "durable")
                .build())
            .addArray("RATINGS", EmbeddedRelationalArray.newBuilder()
                .addAll(4.5, 4.8, 4.2, 4.9)
                .build())
            .build();

        pstmt.setLong(1, 1L);
        pstmt.setObject(2, product);

        int inserted = pstmt.executeUpdate();
        conn.commit();
    }
}

Server Driver

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

    String sql = "INSERT INTO products (id, product_info) VALUES (?, ?)";
    try (RelationalPreparedStatement pstmt =
            conn.prepareStatement(sql).unwrap(RelationalPreparedStatement.class)) {

        // Create STRUCT with embedded ARRAY fields
        RelationalStruct product = JDBCRelationalStruct.newBuilder()
            .addLong("ID", 1L)
            .addString("NAME", "Multi-tool")
            .addArray("TAGS", JDBCRelationalArray.newBuilder()
                .addAll("versatile", "portable", "durable")
                .build())
            .addArray("RATINGS", JDBCRelationalArray.newBuilder()
                .addAll(4.5, 4.8, 4.2, 4.9)
                .build())
            .build();

        pstmt.setLong(1, 1L);
        pstmt.setObject(2, product);

        int inserted = pstmt.executeUpdate();
        conn.commit();
    }
}

Inspecting Array Metadata¶

try (Connection conn = DriverManager.getConnection(url)) {
    try (Statement stmt = conn.createStatement()) {
        try (ResultSet rs = stmt.executeQuery("SELECT tags FROM products WHERE id = 1")) {
            if (rs.next()) {
                Array tagsArray = rs.getArray("tags");

                if (tagsArray != null) {
                    // Get array metadata
                    int baseType = tagsArray.getBaseType();
                    String baseTypeName = tagsArray.getBaseTypeName();

                    System.out.println("Array base type: " + baseType);
                    System.out.println("Array base type name: " + baseTypeName);

                    // Get array elements
                    Object[] elements = (Object[]) tagsArray.getArray();
                    System.out.println("Array length: " + elements.length);
                }
            }
        }
    }
}

Inspecting STRUCT Metadata¶

try (Connection conn = DriverManager.getConnection(url)) {
    try (Statement stmt = conn.createStatement()) {
        try (ResultSet rs = stmt.executeQuery("SELECT reviewer FROM reviewers WHERE id = 1")) {
            // Unwrap to RelationalResultSet to access getStruct()
            RelationalResultSet relationalRs = rs.unwrap(RelationalResultSet.class);

            if (relationalRs.next()) {
                RelationalStruct reviewer = relationalRs.getStruct("reviewer");

                if (reviewer != null) {
                    // Get STRUCT metadata
                    var metaData = reviewer.getMetaData();
                    int columnCount = metaData.getColumnCount();

                    System.out.println("STRUCT has " + columnCount + " fields:");

                    // Iterate through all fields
                    for (int i = 1; i <= columnCount; i++) {
                        String columnName = metaData.getColumnName(i);
                        String columnTypeName = metaData.getColumnTypeName(i);
                        int columnType = metaData.getColumnType(i);

                        System.out.printf("  Field %d: %s (%s, type=%d)%n",
                            i, columnName, columnTypeName, columnType);
                    }
                }
            }
        }
    }
}

Advanced JDBC Features¶

Working with STRUCT Types¶

Reading STRUCT Values¶

Accessing Nested STRUCT Fields¶

Creating STRUCT Values¶

Creating Nested STRUCTs¶

Handling NULL STRUCT Fields¶

Working with ARRAY Types¶

Reading ARRAY Values¶

Using ResultSet to Iterate Arrays¶

Creating ARRAY Values¶

Working with Different Array Element Types¶

Handling NULL Arrays¶

Complex Nested Types¶

Arrays of STRUCTs¶

Reading Arrays of STRUCTs¶

STRUCTs Containing Arrays¶

Inspecting Array Metadata¶

Inspecting STRUCT Metadata¶

See Also¶