Oracle JSON Relational Duality delivers a capability that provides the benefits of both relational tables and JSON documents, without the trade-offs of either approach. The new feature in Oracle Database 23c that enables this capability is referred to as a JSON Relational Duality View.
Using Duality Views, data is still stored in relational tables in a highly efficient normalized format but is accessed by applications in the form of JSON documents. Developers can thus think in terms of JSON documents for data access while using highly efficient relational data storage, without having to compromise simplicity. In addition, Duality Views hide all the complexities of database level concurrency control from the developer, providing document-level serializability.
In this blog post, we provide an example of using the Micronaut® Framework to create and interact with a JSON Relational Duality View.
The source for the example is available on github, and we'll look at particular snippets to demonstrate how to use Micronaut Data with Duality Views.
Note: To run the application you need a Docker-API compatible container runtime such as Docker, Podman, or Rancher Desktop installed to host an Oracle Database Free container image using Testcontainers.
Our example is a simple relational database application that represents a student course schedule. A student has a course with a name, a time, a location, and a teacher. A simple example like this uses data stored in multiple normalized relational tables: a student table, a teacher table, a course table, and a table mapping students to their courses. But it is not always straightforward for developers, even in a simple example like this, to build the course schedule for one student, say, "Jill". The developer has to retrieve data from all four tables to assemble Jill's schedule. What the developer really wants is to build Jill's schedule using a single database operation.
What if we could use JSON documents to build this application? That would really simplify database access. JSON is very popular as an access and interchange format because it is so simple.
For example, the course schedule could be represented in a JSON document as a simple hierarchy of key-value pairs. So, Jill's schedule could be as simple as a single JSON document, providing details of each of her courses (name, time, location, and teacher).
However, JSON has limitations as a storage format because of data duplication and consistency. Even in the simple example of student schedules, the course and teacher information is stored redundantly in each student's course schedule document. Duplicate data is inefficient to store, expensive to update, and difficult to keep consistent.
JSON Document Relational Duality Views combine the benefits of the Relational and the Document approach.
A duality view declares the recipe for assembling normalized rows into a JSON document using SQL or GraphQL syntax. The structure of the view mirrors the structure of your desired JSON document. Then you can select from the duality view using SQL, and return Jill's course schedule as a JSON document. You can also update the JSON document that represents Jill's course schedule and the duality view updates the underlying database tables.
The application is configured in src/main/resources/application.yml, as follows:
micronaut:
application:
name: OracleJsonDemo
server:
thread-selection: io
datasources: # <2>
default:
schema-generate: none
packages: org.com.example.entity
dialect: oracle
test-resources: # <1>
containers:
oracle:
image-name: gvenzl/oracle-free:latest-faststart
startup-timeout: 360s
db-name: test
flyway: # <3>
datasources:
default:
enabled: true
baseline-version: 0
baseline-on-migrate: true
In addition to the name of the application, the configuration file contains three properties that are required by this example application:
Flyway reads SQL commands in the resources/db/migration/ directory, runs them if necessary, and verifies that the configured data source is consistent with them. The example application contains two files:
COURSE
, STUDENT
, TEACHER
, and STUDENT_COURSE
tables, and adds foreign key constraints between them.STUDENT_SCHEDULE
relational duality view.Let's take a closer look at the second of those two files:
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW "STUDENT_SCHEDULE" AS -- <1>
SELECT JSON{
'studentId': s."ID", -- <2>
'student': s."NAME" WITH UPDATE, -- <3>
'averageGrade': s."AVERAGE_GRADE" WITH UPDATE,
'schedule': [SELECT JSON{'id': sc."ID", -- <4>
'course': (SELECT JSON{'courseId': c."ID", -- <5>
'teacher': (SELECT JSON{'teacherId': t."ID", -- <6>
'teacher': t."NAME"}
FROM "TEACHER" t WITH UPDATE WHERE c."TEACHER_ID" = t."ID"),
'room': c."ROOM",
'time': c."TIME",
'name': c."NAME" WITH UPDATE}
FROM "COURSE" c WITH UPDATE WHERE sc."COURSE_ID" = c."ID")}
FROM "STUDENT_COURSE" sc WITH INSERT UPDATE DELETE WHERE s."ID" = sc."STUDENT_ID"]}
FROM "STUDENT" s WITH UPDATE INSERT DELETE;
StudentScheduleView
class described below.ID
column of the STUDENT
table.NAME
column of the STUDENT
table, which can be updated.schedule
key is the result of a SELECT
SQL operation.course
key is the result of a SELECT
SQL operation. It maps to the CourseView
class described below.teacher
key is the result of a SELECT
SQL operation. It maps to the TeacherView
class described below.The example application consists of domain classes (in the package com.example.micronaut.entity
) corresponding to the database tables (implemented as Java Record
types):
Course
Student
Teacher
StudentCourse
It also includes the following view classes (in the com.example.micronaut.entity.view
package) corresponding to JSON documents (also implemented as Java Record
types):
CourseView
: provides a JSON document view of a row in the COURSE table. It maps to the value of the course
key described above.StudentView
: provides a JSON document view of a row in the STUDENT table.TeacherView
: provides a JSON document view of a row in the TEACHER table. It maps to the value of the teacher
key described above.StudentScheduleView
: maps to the STUDENT_SCHEDULE
view declared above.Within the same package, the class Metadata
is used to control concurrency. (For more information, see Using Optimistic Concurrency Control With Duality Views.)
Finally, the application provides a record named CreateStudentDto
to represent the data transfer object to create a new student. The implementation is in the com.example.micronaut.dto
package.
The application requires interfaces to define operations to access the database. Micronaut Data implements these interfaces at compile time. In the com.example.micronaut.repository package
there is a repository interface corresponding to each table, as follows:
CourseRepository
StudentRepository
TeacherRepository
StudentCourseRepository
There is an additional interface in the com.example.micronaut.repository.view
package named StudentViewRepository
, which provides a repository for instances of StudentView
.
The application controller, StudentController (defined in src/main/java/com/example/micronaut/controller/StudentController.java), provides the API to the application, as follows:
@Controller("/students") // <1>
public final class StudentController {
private final CourseRepository courseRepository;
private final StudentRepository studentRepository;
private final StudentCourseRepository studentCourseRepository;
private final StudentViewRepository studentViewRepository;
public StudentController(CourseRepository courseRepository, StudentRepository studentRepository, StudentCourseRepository studentCourseRepository, StudentViewRepository studentViewRepository) { // <2>
this.courseRepository = courseRepository;
this.studentRepository = studentRepository;
this.studentCourseRepository = studentCourseRepository;
this.studentViewRepository = studentViewRepository;
}
@Get("/") // <3>
public Iterable<StudentView> findAll() {
return studentViewRepository.findAll();
}
@Get("/student/{student}") // <4>
public Optional<StudentView> findByStudent(@NonNull String student) {
return studentViewRepository.findByStudent(student);
}
@Get("/{id}") // <5>
public Optional<StudentView> findById(Long id) {
return studentViewRepository.findById(id);
}
@Put("/{id}/average_grade/{averageGrade}") // <6>
public Optional<StudentView> updateAverageGrade(Long id, @NonNull Double averageGrade) {
//Use a duality view operation to update a student's average grade
return studentViewRepository.findById(id).flatMap(studentView -> {
studentViewRepository.updateAverageGrade(id, averageGrade);
return studentViewRepository.findById(id);
});
}
@Put("/{id}/student/{student}") // <7>
public Optional<StudentView> updateStudent(Long id, @NonNull String student) {
//Use a duality view operation to update a student's name
return studentViewRepository.findById(id).flatMap(studentView -> {
studentViewRepository.updateStudentByStudentId(id, student);
return studentViewRepository.findById(id);
});
}
@Post("/") // <8>
@Status(HttpStatus.CREATED)
public Optional<StudentView> create(@NonNull @Body CreateStudentDto createDto) {
// Use a relational operation to insert a new row in the STUDENT table
Student student = studentRepository.save(new Student(createDto.student(), createDto.averageGrade()));
// For each of the courses in createDto parameter, insert a row in the STUDENT_COURSE table
courseRepository.findByNameIn(createDto.courses()).stream()
.forEach(course -> studentCourseRepository.save(new StudentCourse(student, course)));
return studentViewRepository.findByStudent(student.name());
}
@Delete("/{id}") // <9>
@Status(HttpStatus.NO_CONTENT)
void delete(Long id) {
//Use a duality view operation to delete a student
studentViewRepository.deleteById(id);
}
@Get("/max_average_grade") // <10>
Optional<Double> findMaxAverageGrade() {
return studentViewRepository.findMaxAverageGrade();
}
}
/students
.CourseRepository, StudentRepository,
StudentCourseRepository,
and StudentViewRepository
.GET
request to /students
, which attempts to retrieve a list of students, represented as instances of StudentView
.GET
request to /students/student/{name}
, which attempts to retrieve a student, represented as an instance of StudentView
. This illustrates the use of a URL path variable (student
).GET
request to /students/{id}
, which attempts to retrieve a student, represented as an instance of StudentView
.PUT
request to /students/{id}/average_grade/{averageGrade}
, which attempts to update a student's average grade.PUT
request to /students/{id}/student/{student}
, which attempts to update a student's name.POST
request to /students/
, which attempts to create a new student. (The method uses relational operations to insert rows into the STUDENT
and STUDENT_COURSE
tables.)DELETE
request to /students/{id}
, which attempts to delete a student.GET
request to /students/max_average_grade
, which returns the maximum average grade for all students.Main
ClassLike all Micronaut applications, the entry point for the example application is the the Application
class in the package com.example.micronaut
. It uses constructor injection to inject beans of type CourseRepository
, StudentRepository
, TeacherRepository
, and StudentCourseRepository
. It includes a main()
method (which starts the application) and an init()
method which populates the database tables using relational operations.
Run the application using the following command (it will start the application on port 8080):
./gradlew run
Wait until the application has started and created the database schema. Your output should look something like:
Jul 31, 2023 4:55:27 PM org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory create
INFO: Creating Schema History table "TEST"."flyway_schema_history" ...
Jul 31, 2023 4:55:28 PM org.flywaydb.core.internal.command.DbMigrate migrateGroup
INFO: Current version of schema "TEST": << Empty Schema >>
Jul 31, 2023 4:55:28 PM org.flywaydb.core.internal.command.DbMigrate doMigrateGroup
INFO: Migrating schema "TEST" to version "1 - schema"
Jul 31, 2023 4:55:31 PM org.flywaydb.core.internal.command.DbMigrate doMigrateGroup
INFO: Migrating schema "TEST" to version "2 - view"
Jul 31, 2023 4:55:31 PM org.flywaydb.core.internal.command.DbMigrate logSummary
INFO: Successfully applied 2 migrations to schema "TEST", now at version v2 (execution time 00:00.772s)
16:55:34.164 [main] INFO io.micronaut.runtime.Micronaut - Startup completed in 123859ms. Server Running: http://localhost:8080
Test the application by using curl to call the API, implemented by the StudentController
class. (We recommend using jq to improve the readability of the JSON output.)
1. List all the students and their schedules by running the following command.
curl --silent http://localhost:8080/students | jq '.'
You should see output similar to the following.
[
{
"studentId": 1,
"student": "Denis",
"averageGrade": 8.5,
"schedule": [
{
"id": 1,
"course": {
"courseId": 1,
"name": "Math",
"teacher": {
"teacherId": 2,
"teacher": "Mr. Graeme"
},
"room": "A101",
"time": "10:00:00"
}
},
{
"id": 4,
"course": {
"courseId": 3,
"name": "History",
"teacher": {
"teacherId": 1,
"teacher": "Ms. Olya"
},
"room": "A103",
"time": "12:00:00"
}
}
],
"_metadata": {
"etag": "FF95AEFCF102491B75E75DB54EF1385A",
"asof": "000000000021C4BB"
}
},
...
]
2. Retrieve a schedule by student name.
curl --silent http://localhost:8080/students/student/Jill | jq '.'
3.Retrieve a schedule by student id. The output should look similar to above for the student named "Devjani".
curl --silent http://localhost:8080/students/3 | jq '.'
4. Create a new student with courses (and view that student's schedule). The output should be familiar.
curl --silent \
-d '{"student":"Sandro", "averageGrade":8.7, "courses": ["Math", "English"]}' \
-H "Content-Type: application/json" \
-X POST http://localhost:8080/students | jq '.'
5. Update a student's average grade (by student id).
curl --silent -X PUT http://localhost:8080/students/1/average_grade/9.8| jq '.'
6. Retrieve the maximum average grade.
curl http://localhost:8080/students/max_average_grade
7. Update a student's name (by student id), for example, to correct a typo.
curl --silent -X PUT http://localhost:8080/students/1/student/Dennis | jq '.'
8. Delete a student (by student id) and retrieve the new maximum average grade (to confirm deletion).
curl -X DELETE http://localhost:8080/students/1
curl http://localhost:8080/students/max_average_grade
We can see from the tests above how the view classes (in the com.example.micronaut.entity.view
package) provide the output. Let's look at Jill's schedule in detail. The output is produced by the findByStudent()
method; it returns an instance of StudentView
, which is rendered as a String
. You should see output similar to the following, which we have annotated. You can see that the structure of the output mirrors the structure of the STUDENT_SCHEDULE
relational duality view created in src/main/resources/db/migration/V2__view.sql. If you have time, take a look at the view classes to see how they implement the structure below.
{ // Start of StudentView
"studentId": 2,
"student": "Jill",
"averageGrade": 7.2,
"schedule": [
{ // Start of StudentScheduleView
"id": 2,
"course": { // Start of CourseView
"courseId": 1,
"name": "Math",
"teacher": { // Start of TeacherView
"teacherId": 2,
"teacher": "Mr. Graeme"
}, // End of TeacherView
"room": "A101",
"time": "10:00:00"
} // End of CourseView
}, //End of StudentScheduleView
{ // Start of StudentScheduleView
"id": 5,
"course": { //Start of CourseView
"courseId": 2,
"name": "English",
"teacher": { // Start of TeacherView
"teacherId": 3,
"teacher": "Prof. Yevhen"
}, // End of TeacherView
"room": "A102",
"time": "11:00:00"
} // End of CourseView
} // End of StudentScheduleView
],
"_metadata": {
"etag": "5C51516688936720969FE3DBBAA3CEF5",
"asof": "000000000021F3D4"
}
} // End of StudentView
In this blog post we've explored how to use Micronaut Framework to interact with an Oracle Database using relational and document approaches. We've also seen how Micronaut Data exploits the new Oracle JSON Relational Duality capability. Micronaut Framework gives developers access to the benefits of the latest features provided by Oracle Database.
Photo by Vincent van Zalinge on Unsplash
Micronaut® is a registered trademark of Object Computing, Inc. Use is for referential purposes and does not imply any endorsement or affiliation with any third-party product.
Bernard Horan is a Technical Writer in Oracle Labs
Graeme is one of the founders of the Micronaut Open Source project. He leads the team at Oracle Labs who contribute heavily to the development of the framework, including driving the development of key Micronaut projects such as Micronaut Core, Micronaut Data, Micronaut Serialization and Micronaut Oracle Cloud.
Previous Post
Next Post