1. Getting Started
- 1.1. What Is a JSON Relational Duality View
- 1.2. Usage
2. What you will need
3. Writing the application
4. Writing Tests
5. Testing the Application
6. Running the Application
7. Next Steps
8. License

Use JSON duality view in Oracle

Learn how to use JSON duality views in Micronaut

Authors: Dimitrije Zdravkovic

Micronaut Version: 5.2.0

1. Getting Started

In this guide, we will create a Micronaut application written in Java.

In this guide, we will set up and use an Oracle JSON Relational Duality View with Micronaut Data. The application includes classes that represent a JSON Relational Duality View in Oracle Database. In the background, the SQL CREATE statement for the view is generated and executed.

1.1. What Is a JSON Relational Duality View

A JSON Relational Duality View in Oracle Database lets you map relational tables to JSON documents. This creates a document-style interface for data stored relationally.

JSON Relational Duality is a new data modeling capability that features updatable and consistent JSON document views over relational data. This allows data that is stored efficiently in relational tables to be accessed as simple JSON documents. JSON Relational Duality Views can be accessed with document APIs, such as MongoDB-compatible APIs, REST, and SQL.

1.2. Usage

In Micronaut, JSON duality views are supported by annotating an entity with @JsonView.

2. What you will need

To complete this guide, you will need the following:

Some time on your hands
A decent text editor or IDE
JDK 1.8 or greater installed with JAVA_HOME configured appropriately
Docker installed to run MySQL and to run tests using Testcontainers.

3. Writing the application

In Micronaut, users don’t have to use SQL to create JSON duality views and execute CRUD operations on them. By annotating classes with @JsonView, users specify which views to generate. CRUD operations are executed via PageableRepository.

3.1. High-level diagram of the classes used

duality json view diagram

3.2. Writing an entity class

For each table in the database, we can create a corresponding entity class. For the Student table, we can create the following class:

src/main/java/example/micronaut/domain/Student.java

package example.micronaut.domain;

import com.fasterxml.jackson.annotation.JsonAnyGetter;
import com.fasterxml.jackson.annotation.JsonAnySetter;
import io.micronaut.core.annotation.Nullable;
import io.micronaut.data.annotation.GeneratedValue;
import io.micronaut.data.annotation.Id;
import io.micronaut.data.annotation.MappedEntity;
import io.micronaut.data.annotation.Relation;
import io.micronaut.data.annotation.sql.JoinTable;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.Collections;
import java.util.List;
import java.util.Map;

@MappedEntity(value = "TBL_STUDENT", alias = "s") (1)
public record Student (
        @Id (2)
        @GeneratedValue(GeneratedValue.Type.IDENTITY) (3)
        Long id,
        String name,

        @JoinTable(name = "TBL_STUDENT_CLASSES", alias = "sc") (4)
        @Relation(Relation.Kind.MANY_TO_MANY) (5)
        List<Class> classes,

        @JsonAnyGetter (6)
        @JsonAnySetter
        Map<String, Object> extras
) {}

1	`@MappedEntity` annotation to specify the corresponding table name
2	`@Id` annotation to specify the primary key
3	`@GeneratedValue` annotation to specify that it is an auto-increment field
4	`@JoinTable` annotation to specify the corresponding join table
5	`@Relation` annotation to specify an association
6	`@JsonAnyGetter` and `@JsonAnySetter` expose arbitrary JSON properties through a flex column

3.3. Writing a view class

A class annotated with @JsonView generates the corresponding JSON duality view. The @JsonView annotation has an entity field, which represents the class for which the view will be created; in our case, Student.class. It is a required field.

Create the view class:

src/main/java/example/micronaut/domain/StudentView.java

package example.micronaut.domain;

import com.fasterxml.jackson.annotation.JsonAnyGetter;
import com.fasterxml.jackson.annotation.JsonAnySetter;
import com.fasterxml.jackson.annotation.JsonProperty;
import io.micronaut.data.annotation.GeneratedValue;
import io.micronaut.data.annotation.Id;
import io.micronaut.data.annotation.JsonView;
import io.micronaut.data.annotation.Relation;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

@JsonView(entity = Student.class) (1)
public record StudentView (
        @Id (2)
        @GeneratedValue(GeneratedValue.Type.IDENTITY) (3)
        Long id,

        String name,

        @Relation(Relation.Kind.ONE_TO_MANY) (4)
        List<StudentScheduleSubView> classes,

        @JsonAnyGetter (5)
        @JsonAnySetter
        Map<String, Object> extras
) {}

1	`@JsonView` annotation with the specified entity class
2	`@Id` annotation to specify the primary key
3	`@GeneratedValue` annotation to specify that it is an auto-increment field
4	`@Relation` annotation to specify an association
5	`@JsonAnyGetter` and `@JsonAnySetter` expose arbitrary JSON properties through a flex column

3.4. Using a flex column

The extras map on Student and StudentView is annotated with @JsonAnyGetter and @JsonAnySetter. Micronaut Data maps it to an Oracle JSON flex column: declared properties remain relational fields, while arbitrary properties such as department or remote can be added to the JSON document without adding Java, Groovy, or Kotlin fields or table columns.

3.5. Writing subview classes

As you can see from the StudentView.classes relation, JSON duality views support table joins. They are specified by using the @Relation annotation.

For every relation, a subview is created. A subview class must be created for each relation. To specify a subview class, we use the @JsonSubView annotation. @JsonSubView also has an entity field with the same meaning; it is required as well. These classes represent subviews; CREATE statements are generated only for classes annotated with @JsonView.

Define the following entity and corresponding subview classes:

3.5.1. Entity classes

A student can enroll in multiple classes, and each class can include multiple students, so we model this many-to-many relationship using a join table represented by StudentClass that links Student and Class. That’s why we have two MANY_TO_ONE relations in StudentClass.

src/main/java/example/micronaut/domain/StudentClass.java

package example.micronaut.domain;

import io.micronaut.data.annotation.GeneratedValue;
import io.micronaut.data.annotation.Id;
import io.micronaut.data.annotation.MappedEntity;
import io.micronaut.data.annotation.MappedProperty;
import io.micronaut.data.annotation.Relation;

@MappedEntity(value = "TBL_STUDENT_CLASSES", alias = "sc") (1)
public record StudentClass (
        @Id (2)
        @GeneratedValue(GeneratedValue.Type.IDENTITY) (3)
        Long id,
        @Relation(Relation.Kind.MANY_TO_ONE) (4)
        Student student,
        @Relation(Relation.Kind.MANY_TO_ONE) (4)
        @MappedProperty("CLASS_ID") (5)
        Class clazz
) {}

1	`@MappedEntity` annotation to specify the corresponding table name
2	`@Id` annotation to specify the primary key
3	`@GeneratedValue` annotation to specify that it is an auto-increment field
4	`@Relation` annotation to specify an association
5	`@MappedProperty` annotation to specify the corresponding table’s column name

src/main/java/example/micronaut/domain/Class.java

package example.micronaut.domain;

import org.jspecify.annotations.Nullable;
import io.micronaut.data.annotation.GeneratedValue;
import io.micronaut.data.annotation.Id;
import io.micronaut.data.annotation.MappedEntity;
import io.micronaut.data.annotation.Relation;
import jakarta.validation.constraints.NotNull;

import java.time.LocalTime;

@MappedEntity(value = "TBL_CLASS", alias = "c") (1)
public record Class (
    @Id (2)
    @GeneratedValue(GeneratedValue.Type.IDENTITY) (3)
    Long id,

    @NotNull (4)
    String name
) {}

1	`@MappedEntity` annotation to specify the corresponding table name
2	`@Id` annotation to specify the primary key
3	`@GeneratedValue` annotation to specify that it is an auto-increment field
4	`@NotNull` annotation to specify a required property

3.5.2. Subview classes

src/main/java/example/micronaut/domain/StudentScheduleSubView.java

package example.micronaut.domain;

import com.fasterxml.jackson.annotation.JsonProperty;
import io.micronaut.data.annotation.Id;
import io.micronaut.data.annotation.JsonSubView;
import io.micronaut.data.annotation.Relation;
import io.micronaut.serde.annotation.Serdeable;

@Serdeable
@JsonSubView(entity = StudentClass.class)
public record StudentScheduleSubView (
        @Id
        Long id,

        @JsonProperty("class")
        @Relation(Relation.Kind.ONE_TO_ONE)
        StudentScheduleClassSubView clazz
) {}

src/main/java/example/micronaut/domain/StudentScheduleClassSubView.java

package example.micronaut.domain;

import io.micronaut.data.annotation.Embeddable;
import io.micronaut.data.annotation.Id;
import io.micronaut.data.annotation.Relation;
import io.micronaut.data.annotation.JsonSubView;
import io.micronaut.data.annotation.JsonView;
import io.micronaut.data.annotation.MappedProperty;

import java.time.LocalTime;

@Embeddable
@JsonSubView(entity = Class.class, operations = {JsonView.Operation.INSERT, JsonView.Operation.UPDATE}) (1)
public record StudentScheduleClassSubView (
        @Id
        @MappedProperty(value = "id")
        Long classID,

        String name
) {}

1 Restrict the Class subview to INSERT and UPDATE operations. Class is shared by multiple students through TBL_STUDENT_CLASSES, so deleting it through this view is intentionally disabled. Oracle otherwise rejects the view with ORA-42693; foreign keys still protect relational integrity, and class deletion should be handled separately.

3.6. The resulting SQL CREATE statement

The provided classes will generate the following view at build time:

src/main/resources/db/V1__create_student_view.sql

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW student_view AS
SELECT JSON {
    '_id': s.id,
    'name': s.name,
    s.extras AS FLEX COLUMN,
    'classes': [
        SELECT JSON {
            'id': sc.id,
            'class': (
                SELECT JSON {
                    'classID': c.id,
                    'name': c.name
                }
                FROM TBL_CLASS c
                WITH INSERT UPDATE
                WHERE sc."CLASS_ID"=c."ID"
            )
        }
        FROM TBL_STUDENT_CLASSES sc
        WITH UPDATE INSERT DELETE
        WHERE s."ID"=sc."STUDENT_ID"
    ]
}
FROM TBL_STUDENT s WITH UPDATE INSERT DELETE;

4. Writing Tests

This is a sample repository that uses the StudentView class, including arbitrary flex-column properties:

src/main/java/example/micronaut/StudentViewRepository.java

package example.micronaut;

import io.micronaut.data.jdbc.annotation.JdbcRepository;
import io.micronaut.data.model.query.builder.sql.Dialect;
import io.micronaut.data.repository.PageableRepository;
import example.micronaut.domain.StudentView;
import java.util.Optional;

@JdbcRepository(dialect = Dialect.ORACLE)
public interface StudentViewRepository extends PageableRepository<StudentView, Long> {
    Optional<StudentView> findByName(String name);
}

Create a test to verify CRUD operations using the StudentViewRepository:

src/test/java/example/micronaut/StudentViewRepositoryTest.java

package example.micronaut;

import io.micronaut.test.extensions.junit5.annotation.MicronautTest;
import jakarta.inject.Inject;
import org.junit.jupiter.api.Test;
import example.micronaut.domain.StudentScheduleClassSubView;
import example.micronaut.domain.StudentScheduleSubView;
import example.micronaut.domain.StudentView;
import java.util.Optional;

import java.util.List;
import java.util.Map;

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertTrue;

@MicronautTest
class StudentViewRepositoryTest {

    @Inject
    StudentViewRepository studentViewRepository;

    @Inject
    ClassRepository classRepository;

    @Test
    void testCreateStudentView() {
        example.micronaut.domain.Class mathClass = classRepository.save(new example.micronaut.domain.Class(null, "Math"));
        StudentScheduleClassSubView studentScheduleClassSubView = new StudentScheduleClassSubView(mathClass.id(), mathClass.name());
        StudentScheduleSubView studentScheduleSubView = new StudentScheduleSubView(null, studentScheduleClassSubView);
        StudentView studentView = new StudentView(null, "John", List.of(studentScheduleSubView), Map.of("department", "Research", "remote", true));
        studentViewRepository.save(studentView);
        Optional<StudentView> student = studentViewRepository.findByName(studentView.name());
        assertTrue(student.isPresent());
        StudentView savedStudent = student.orElseThrow();
        assertEquals("Research", savedStudent.extras().get("department"));
        assertEquals(true, savedStudent.extras().get("remote"));
        assertEquals(1, savedStudent.classes().size());
        assertEquals("Math", savedStudent.classes().get(0).clazz().name());
    }
}

5. Testing the Application

To run the tests:

./gradlew test

Then open build/reports/tests/test/index.html in a browser to see the results.

6. Running the Application

To run the application, use the ./gradlew run command, which starts the application on port 8080.

7. Next Steps

Read more about Micronaut Data and JSON Duality Views.

8. License

All guides are released with an Apache License 2.0 for the code and a Creative Commons Attribution 4.0 license for the writing and media (images).

Table of Contents