JSON Relational Duality Views are one of my favorite Oracle AI Database features (I’ll call them “duality views” from here on out). Duality views let you model data once, then serve it cleanly to both worlds: JSON-first application code and relational SQL workloads.
In this article, we’ll get hands-on with the json/crud-duality-views code sample to demonstrate CRUD (create, read, update, delete) with duality views using plain Java + the Oracle JDBC driver. The key idea: your application can work with JSON documents, while Oracle keeps the underlying relational tables transactionally in sync.
What you’ll learn:
- Insert serialized Java objects into a duality view
- Read back and map to Java
- Update with
json_transform - Delete and verify referential behavior
Sample overview
This module keeps things intentionally simple: it talks to Oracle AI Database using the Oracle JDBC driver, and stores/reads JSON using Oracle’s binary JSON format, OSON.
On the Java side, you’ll see familiar domain classes like Order and Product annotated with @JsonbProperty metadata so the object shape maps cleanly to JSON. An OSONMapper utility class is provided, converting between:
- Java objects (
Order,Product, etc.) - OSON byte arrays (what gets persisted/retrieved via JDBC)
public class Order {
// Maps the OSON _id field to the Java id field during serialization/deserialization
@JsonbProperty("_id")
private Long id;
// ...rest of the class
}The important architectural choice is that all persistence goes through a single surface area: the orders_dv duality view and corresponding JSON document representation. When using the duality view, you never write multi-table insert/update/delete logic in the application, and you never assemble join results back into nested JSON.
Duality view schema
The underlying relational schema is still there (defined in src/test/resources/init.sql), alongside the orders_dv duality view:
-- relational tables
create table products (
product_id number generated as identity primary key,
name varchar2(255) not null,
price number(10,2) not null
);
create table orders (
order_id number generated as identity primary key,
product_id number not null,
quantity number not null,
order_date date default sysdate,
constraint fk_orders_product foreign key (product_id) references products(product_id) on delete cascade
);
-- duality view that encompasses orders and products tables
create or replace force editionable json relational duality view
-- root json object is the "orders" table
orders_dv as orders @insert @update @delete { -- allow insert, update, and delete on orders
_id : order_id
quantity
orderDate : order_date
-- products table is nested within each order object
-- the database implicitly handles the join server-side,
-- using the orders table foreign key
product : products @insert @update -- allow insert and update on products {
_id : product_id
name
price
}
}
/With this view, inserts/updates routed through orders_dv populate both orders and products.
CRUD in four steps
The Application class orchestrates the full CRUD lifecycle on the orders_dv duality view. It serializes Java records into OSON, sends them to the duality view, and round-trips the generated `_id` value. The same program reads the document back, applies a `json_transform` update, and finally deletes the row.
Here’s how an insert works, retrieving the generated ID from the JSON object:
private static final String INSERT_ORDER_PRODUCT = """
insert into orders_dv (data) values (?)
returning json_value(data, '$._id' returning number) into ?
""";
public static void main(String[] args) throws SQLException {
if (args.length != 3) {
System.out.println("Usage: <JDBC URL> <Username> <Password>");
System.exit(1);
}
System.out.println("Starting JDV Crud Application");
OSONMapper oson = OSONMapper.createDefault();
OracleDataSource ds = new OracleDataSource();
ds.setURL(args[0]);
ds.setUser(args[1]);
ds.setPassword(args[2]);
System.out.println("Connecting to DB: " + args[0]);
long generatedId;
try (Connection conn = ds.getConnection();
OraclePreparedStatement ps = (OraclePreparedStatement) conn.prepareStatement(INSERT_ORDER_PRODUCT)) {
Product product = new Product();
product.setName("my product");
product.setPrice(100.00);
Order order = new Order();
order.setProduct(product);
order.setQuantity(10);
// Serialize to OSON
byte[] data = oson.toOSON(order);
ps.setObject(1, data, OracleTypes.JSON);
// Register the RETURNING bind (2nd bind)
ps.registerReturnParameter(2, OracleTypes.NUMBER);
ps.executeUpdate();
// Get returned JSON document
try (ResultSet rs = ps.getReturnResultSet()) {
if (rs.next()) {
generatedId = rs.getLong(1);
} else {
throw new SQLException("Insert failed: no returned document obtained.");
}
}
}
// ...Rest of the main class, including read, update, and delete
}For completeness, here are read, update, and delete. Note the use of the JsonParser to read OSON into a Java object on read/SELECT:
private static final String SELECT_ORDER_BY_ID = """
select * from orders_dv dv
where dv.data."_id" = ?
""";
private static final String UPDATE_ORDER_QUANTITY =
"""
update orders_dv dv
set dv.data = json_transform(dv.data, set '$.quantity' = ?)
where dv.data."_id" = ?
""";
private static final String DELETE_ORDER_BY_ID = """
delete from orders_dv dv
where dv.data."_id" = ?
""";
public static void main(String[] args) throws SQLException {
// insert code shown above
Order created = new Order();
System.out.println("Created order ID: " + generatedId);
try (Connection conn = ds.getConnection();
PreparedStatement ps = conn.prepareStatement(SELECT_ORDER_BY_ID)) {
ps.setLong(1, generatedId);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
JsonParser parser = rs.getObject(1, JsonParser.class);
created = oson.fromOSON(parser, Order.class);
System.out.println("Retrieved created order by id: " + created);
}
}
try (Connection conn = ds.getConnection();
PreparedStatement ps = conn.prepareStatement(UPDATE_ORDER_QUANTITY)) {
ps.setLong(1, 15);
ps.setLong(2, created.getId());
ps.executeUpdate();
}
System.out.println("Updated order ID: " + created.getId());
try (Connection conn = ds.getConnection();
PreparedStatement ps = conn.prepareStatement(DELETE_ORDER_BY_ID)) {
ps.setLong(1, created.getId());
ps.executeUpdate();
}Note: If you’re already on Spring Boot, you can replace the OSONMapper utility class with the JSONB class from the Oracle Spring Boot JSON Data Tools Maven module.
Run the sample locally with Testcontainers
To take things for a spin, let’s run the JDVCrudTest class locally with Testcontainers. You’ll need the following tools instaled to run the test:
- A docker-compatible environment to run containers
- Java 21+
- Maven 3.9+
The program will:
- Insert a new
Orderdocument intoorders_dvand capture the generated_id. - Query the duality view to deserialize the JSON document back into the
Orderclass. - Update the order quantity with
json_transformand persist the change. - Delete the same order and end the program.
Start the test from the json/crud-duality-views directory:
# pre-pull Oracle AI Database free image
docker pull gvenzl/oracle-free:23.26.1-slim-faststart
mvn testYou should see output similar to the following:
08:29:50.049 [main] INFO tc.gvenzl/oracle-free:23.26.1-slim-faststart -- Container is started (JDBC URL: jdbc:oracle:thin:@localhost:32823/freepdb1)
08:29:50.056 [main] INFO org.testcontainers.ext.ScriptUtils -- Executing database script from init.sql
08:29:50.453 [main] INFO org.testcontainers.ext.ScriptUtils -- Executed database script from init.sql in 397 ms.
Starting JDV Crud Application
Connecting to DB: jdbc:oracle:thin:@localhost:32823/freepdb1
Created order ID: 1
Retrieved created order by id: Order{id=1, product=Product{id=1, name='my product', price=100.0}, quantity=10, orderDate=Tue Feb 24 08:29:50 PST 2026}
Updated order ID: 1
Deleted order ID: 1
[INFO] Tests run: 1, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 10.37 s -- in com.example.jdv.crud.JDVCrudTestWhy it matters
JSON Relational Duality Views allow teams to design a single, canonical model that satisfies both document and relational APIs. When combined with OSON, you get native binary performance, schema evolution with JSON flexibility, and transactional guarantees rooted in Oracle’s relational engine. This sample illustrates the plumbing you need to take advantage of duality views in modern Java applications.
Next steps
- Add REST or GraphQL APIs on top of the duality view using Spring Boot or Helidon.
- Browse JSON and duality view articles to learn more, covering topics like RBAC, performance optimization, data modeling, and advanced syntax.
Leave a Reply