Using Oracle JSON-Relational Duality Views with Spring Boot. The ORM Killer?

Oracle Database 23ai introduced a powerful feature called JSON-Relational Duality Views. These views let you work with relational data in a document-oriented way. In this post, I’ll show you how to use this feature with Spring Boot.

What Problem Does It Solve?

We often need to handle relational data and objects or JSON documents when building applications. Traditionally, this meant using ORM frameworks like JPA with Hibernate or manually managing the mapping between JSON and relations.

JSON Relational Duality Views solves this by letting you define a view that automatically handles the conversion between relational tables and JSON documents, which can be directly mapped to Java objects. The documents are generated—not stored as such—and updates to the underlying table data are automatically reflected in the JSON documents. This means that we can use JSON to read and write data. This JSON, in turn, can be mapped to Java classes.

Project Setup

First, add these two dependencies to use JSON Relational Duality Views with Spring Boot. The oracle-spring-boot-starter-json-collections dependency helps to map JSON documents to Java objects, and ojdbc11 is the JDBC driver for the Oracle database.

<dependency>
    <groupId>com.oracle.database.spring</groupId>
    <artifactId>oracle-spring-boot-starter-json-collections</artifactId>
    <version>24.4.0</version>
</dependency>
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc11</artifactId>
</dependency>

Creating the Database Structure

Let’s look at a typical order management system with these tables:

The magic happens when we create the JSON Relational Duality View. purchase_order and order_item are marked WITH INSERT UPDATE DELETE to indicate that we can insert, update, and delete records in those tables, whereas customer and product are implicitly read-only. For testing purposes I explicitly marked the order_date WITH NOUPDATE to prevent changes on the order date.

CREATE JSON RELATIONAL DUALITY VIEW purchase_order_view AS
SELECT JSON {
    '_id': o.id,
    'orderDate': o.order_date WITH NOUPDATE,
    'customer': (
        SELECT JSON {
            '_id': c.id,
            'firstName': c.first_name,
            'lastName': c.last_name,
            'street': c.street,
            'postalCode': c.postal_code,
            'city': c.city
        }
        FROM customer c
        WHERE c.id = o.customer_id
    ),
    'items': [
        SELECT JSON {
            '_id': i.id,
            'quantity': i.quantity,
            'product': (
                SELECT JSON {
                    '_id': p.id,
                    'name': p.name,
                    'price': p.price
                }
                FROM product p
                WHERE p.id = i.product_id
            )
        }
        FROM order_item i WITH INSERT UPDATE DELETE
        WHERE i.purchase_order_id = o.id
    ]
}
FROM purchase_order o WITH INSERT UPDATE DELETE;

Working with the View in Java

The Java classes to map to this view are as follows:

To simplify the access to the view I created a BaseRepository as a generic class that handles basic database operations. Here’s how it works:

@Transactional(readOnly = true)
public class BaseRepository<T, ID> {
    private final Class<T> clazz;
    protected final String viewName;
    @Autowired
    protected DataSource dataSource;
    @Autowired
    protected JdbcClient jdbcClient;
    @Autowired
    protected JSONB jsonb;
    protected JSONBRowMapper<T> rowMapper;
}

Key components:

• T: The entity type (like PurchaseOrder)
• ID: The type of the ID (like Long)
• viewName: Name of the JSON Relational Duality View
• jsonb: Oracle’s JSONB handler for JSON-object mapping
• rowMapper: Maps database rows to Java objects

Finding Records

public List<T> findAll(int offset, int limit) {
    return jdbcClient.sql("""
            SELECT v.data FROM %s v OFFSET ? ROWS FETCH NEXT ? ROWS ONLY
            """.formatted(viewName))
            .param(1, offset)
            .param(2, limit)
            .query(rowMapper)
            .list();
}

public Optional<T> findById(ID id) {
    return jdbcClient.sql("""
            SELECT v.data FROM %s v WHERE v.data."_id" = ?
            """.formatted(viewName))
            .param(1, id)
            .query(rowMapper)
            .optional();
}

Note that the SQL uses v.data to access the JSON document.

Inserting Records

The primary key is generated from a sequence as a default value. Therefore, I want to get the primary key back when inserting it. To do so, I must use a CallableStatement with an out parameter like in the code below. It’s also important to mention that the object must be converted into OSON (Oracle binary JSON format). Finally, the primary key is converted to the ID type because BaseRepository is generic.

@Transactional
public ID insert(T object) {
    byte[] oson = jsonb.toOSON(object);

    try (CallableStatement cs = dataSource.getConnection().prepareCall("""
            BEGIN
                INSERT INTO %s v (data) VALUES (?) RETURNING v.data."_id" INTO ?;
            END;
            """.formatted(viewName))) {
        cs.setObject(1, oson);
        cs.registerOutParameter(2, OracleTypes.NUMBER);
        cs.execute();

        Object result = cs.getObject(2);
        if (result == null) {
            return null;
        }

        @SuppressWarnings("unchecked")
        ID id = (ID) convertToIdType(result);
        return id;
    }
}

Updating Records

@Transactional
public void update(T object, ID id) {
    byte[] oson = jsonb.toOSON(object);
    jdbcClient.sql("""
            UPDATE %s v
            SET v.data = ?
            WHERE v.data."_id" = ?""".formatted(viewName))
            .param(1, oson)
            .param(2, id)
            .update();
}

Deleting Records

@Transactional
public void deleteById(ID id) {
    jdbcClient.sql("""
            DELETE FROM %s v
            WHERE v.data."_id" = ?""".formatted(viewName))
            .param(1, id)
            .update();
}

ID Type Handling

The repository includes smart ID type conversion. This handles the conversion between Oracle’s NUMBER type and Java’s numeric types:

private Object convertToIdType(Object value) {
    if (value instanceof BigDecimal bigDecimal) {
        if (bigDecimal.scale() == 0) {
            if (bigDecimal.longValue() == bigDecimal.doubleValue()) {
                return bigDecimal.longValue();
            }
            return bigDecimal.intValue();
        }
    }
    return value;
}

How to Use the BaseRepository

To create a new repository, extend BaseRepository and pass the root class and the view name:

@Repository
public class PurchaseOrderRepository extends BaseRepository<PurchaseOrder, Long> {
    
    public PurchaseOrderRepository() {
        super(PurchaseOrder.class, "purchase_order_view");
    }
    
    // Add custom queries here
}

This class gives you all CRUD operations for free, and you can add custom queries as needed.

The main benefits of using BaseRepository are:

• Reusable CRUD operations
• Type-safe operations
• Transaction handling
• Proper JSON-object mapping
• ID type conversion
• Pagination support

Testing Time

The PurchaseOrderRepositoryTest shows how to use the PurchaseOrderRepository. It uses Testcontainers to spin up an Oracle database container. The TestcontainersConfiguration uses the Oracle free image provided by Gerald Venzl gvenzl/oracle-free:23-slim-faststart. This image starts in just a few seconds and is very convenient for testing.

@Import(TestcontainersConfiguration.class)
@SpringBootTest
class PurchaseOrderRepositoryTest {

    private static final Logger log = LoggerFactory.getLogger(PurchaseOrderRepositoryTest.class);

    @Autowired
    private PurchaseOrderRepository purchaseOrderRepository;

    /**
     * Test the find, update and create operations.
     */
    @Test
    void findUpdateCreate() {
        List<PurchaseOrder> purchaseOrders = purchaseOrderRepository.findAll(0, 100);

        assertThat(purchaseOrders).hasSize(2);

        log.info(purchaseOrders.toString());

        PurchaseOrder purchaseOrder = purchaseOrders.getFirst();
        purchaseOrder.getItems().getFirst().setQuantity(3);

        purchaseOrderRepository.update(purchaseOrder, purchaseOrder.get_id());

        PurchaseOrder newPurchaseOrder = new PurchaseOrder();
        newPurchaseOrder.setOrderDate(LocalDateTime.now());
        newPurchaseOrder.setCustomer(purchaseOrder.getCustomer());

        Long id = purchaseOrderRepository.insert(newPurchaseOrder);
        assertThat(id).isNotNull();
        assertThat(id).isEqualTo(1000L);

        purchaseOrders = purchaseOrderRepository.findAll(0, 100);

        assertThat(purchaseOrders).hasSize(3);

        log.info(purchaseOrders.toString());
    }

    /**
     * Test the update operation on a read-only field.
     * This should fail with an ORA-40940 indicating that the column is read-only.
     */
    @Test
    void updateReadOnlyField() {
        Optional<PurchaseOrder> optionalPurchaseOrder = purchaseOrderRepository.findById(1L);
        assertThat(optionalPurchaseOrder).isPresent();

        PurchaseOrder purchaseOrder = optionalPurchaseOrder.get();
        purchaseOrder.setOrderDate(LocalDateTime.now());

        try {
            purchaseOrderRepository.update(purchaseOrder, purchaseOrder.get_id());
        } catch (UncategorizedSQLException e) {
            assertThat(e.getSQLException().getMessage()).startsWith("ORA-40940: Cannot update field 'orderDate' corresponding to column 'ORDER_DATE' of table 'PURCHASE_ORDER' in JSON Relational Duality View 'PURCHASE_ORDER_VIEW': Missing UPDATE annotation or NOUPDATE annotation specified.");
        }
    }

    /**
     * Test the find by customer operation.
     */
    @Test
    void findByCustomer() {
        List<PurchaseOrder> purchaseOrders = purchaseOrderRepository.findByCustomer(1L);

        assertThat(purchaseOrders).hasSize(1);
    }

    /**
     * Test the delete operation.
     */
    @Test
    void deleteById() {
        purchaseOrderRepository.deleteById(1L);
    }
}

Conclusion

Oracle JSON Relational Duality Views brings a fresh approach to an old challenge: working with relational and document-based data models. It supports modern application development patterns while maintaining the strengths of relational databases. Spring Boot’s simplicity and Oracle’s JSON Relational Duality Views offer a powerful toolkit for building modern business applications.

The Main Benefits

1. Simpler Code: Instead of complex ORM mappings or manual JSON handling, you can work directly with JSON documents while keeping your relational database structure.
2. It’s the best of Both Worlds: You keep the benefits of relational databases (data consistency, transactions, foreign keys) while getting the flexibility of JSON documents.
3. Spring Boot Integration: Integration is straightforward with the Oracle Spring Boot Starter for JSON Collections. My BaseRepository makes it easy to build type-safe CRUD operations.
4. Performance: Since the JSON-relational mapping happens at the database level, fewer database roundtrips are needed.
5. Optimistic locking: JSON-relational duality view provides lock-free concurrency control.

When to Use It

JSON Relational Duality Views are particularly useful when:

• Your application needs both relational and document-based access or Java objects.
• You want to modernize existing applications without changing the database structure.
• You need to provide JSON APIs over relational data.
• You want to reduce the complexity of your ORM mappings.

Will it Make ORMs Obsolete?

As always in software development, it depends. The answer is yes if you can map the database model one-to-one to the JSON or class model. But if you need additional mapping, like Java enums, or you have legacy datatypes like Y/N in a char column instead of a boolean, then you probably need an ORM.

Links

• The full example code is available on my GitHub repository
• Official documentation