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.
1. The Example Application
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.
1.1. Application Configuration
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:
1. Test resources: an oracle database container image.
2. Datasources: to indicate the database dialect, and the package(s) to be used.
3. Flyway: to automate the creation of the database schema, including the tables and relational duality view. Micronaut integration with Flyway automatically triggers schema migration before the Micronaut application starts.
1.2 Application Schema
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:
- src/main/resources/db/migration/V1__schema.sql: this creates the COURSE, STUDENT, TEACHER, and STUDENT_COURSE tables, and adds foreign key constraints between them.
- src/main/resources/db/migration/V2__view.sql: this creates the 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;
- Create a duality view named STUDENT_SCHEDULE. It maps to the StudentScheduleView class described below.
- The ID column of the STUDENT table.
- The NAME column of the STUDENT table, which can be updated.
- The value of the schedule key is the result of a SELECT SQL operation.
- The value of the course key is the result of a SELECT SQL operation. It maps to the CourseView class described below.
- The value of the teacher key is the result of a SELECT SQL operation. It maps to the TeacherView class described below.
1.3. Application Domain
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.
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.
1.4. Database Operations
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.
1.5. Application Controller
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();
}
}
- The class is defined as a controller with the @Controller annotation mapped to the path /students.
- Use constructor injection to inject beans of types CourseRepository, StudentRepository, StudentCourseRepository, and StudentViewRepository.
- The @Get annotation maps a GET request to /students, which attempts to retrieve a list of students, represented as instances of StudentView.
- The @Get annotation maps a 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).
- The @Get annotation maps a GET request to /students/{id}, which attempts to retrieve a student, represented as an instance of StudentView.
- The @Put annotation maps a PUT request to /students/{id}/average_grade/{averageGrade}, which attempts to update a student's average grade.
- The @Put annotation maps a PUT request to /students/{id}/student/{student}, which attempts to update a student's name.
- The @Post annotation maps a 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.)
- The @Delete annotation maps a DELETE request to /students/{id}, which attempts to delete a student.
- The @Get annotation maps a GET request to /students/max_average_grade, which returns the maximum average grade for all students.
1.6. Main Class
Like 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.
2. Run the Application
Run the application using the following command (it will start the application on port 8080):
Copy code snippet
Copied to ClipboardError: Could not CopyCopied to ClipboardError: Could not Copy
./gradlew run
./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
3. Test the Application
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
Discussion
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
Source: oracle.com
Posts
0 comments:
Post a Comment