View-API for JOOQ Application

In this blog post I show how to build a read-only view-API for Oracle’s HR sample schema. And I will use this view-API in a JOOQ application. This application will fully comply with the Pink Database Paradigm (PinkDB). This means the application executes set-based SQL and retrieves data with as few network roundtrips as possible.

The following topics are covered:

1. Install Sample Schemas
2. Build the View-API
3. Create the View-API Database Role
4. Create the Connect User
5. Install JOOQ
6. Teach JOOQ the View-API
7. Run a Simple Query
8. Using Joins & Aggregations
9. Using Bind Variables
10. Run a Top N Query
11. Using Row Pattern Matching
12. Conclusion

This is not meant to be a tutorial. However, you should be able to build the application based on the information in this blog post and the referenced links.

It’s the first time I’ve done anything with JOOQ. I read about it, understood the high-level concepts, but I have never used JOOQ before. This is one reason why this blog post became a bit more verbose. I hope it is helpful.

1. Install Sample Schemas

See the Oracle documentation.

Starting with Oracle Database 12c Release 2, the latest version of the sample schema scripts are available on GitHub at https://github.com/oracle/db-sample-schemas/releases/latest. (…)

2. Build the View-API

1:1 Views

When building a view API I start with a 1:1 mapping to the table. There are usually some discussions on different topics.

Expose Surrogate Keys

One topic is to include surrogate keys or just business keys in the view-API. Nowadays, I tend to expose surrogate keys. The main reason is to avoid joins when business key columns are stored in related (parent) tables. Forcing the application to join views via such business key columns is a bad idea from a performance point of view.

Convenience Views – Not a Replacement for 1:1 Views

Another topic is the simplification of the model. For example, provide data from various tables to simplify the usage. Simplification means here sparing the consuming application to use joins. This is sometimes a good idea, but it does not replace the 1:1 views, which are the key to the optimal path to the data. I know about join elimination, but in reality they often do not work. One reason is that unnecessary columns are queried as well. Another problem is, to force the view to apply selective predicates as early as possible. Sometimes it is necessary to introduce an application context, just for that purpose, making the usage of the view not that simple anymore.

Value of a 1:1 View-API

The logical next question is this: Why do we need a view layer when it just provides data the same way as the underlying tables? That’s an excellent question. I strongly recommend to introduce a layer only, when it provides more value than the additional effort. So, what is the value of a 1:1 view layer? Most products evolve during their life cycle. Hence, the data model will most probably change as well. When we have a view layer, we have the option to change the physical data model and keep the existing view layer the same for the consuming applications. From that point on, the views are not a 1:1 representation of tables, at least not in every case.

As long as we keep the interface to the application unchanged, we do not have to coordinate changes with the consuming applications. This gives us effectively room for refactoring and simplifies going live scenarios of new releases. Providing additional data and functionality is usually not a problem. But adapting applications to new interfaces needs time. A view-API, especially if it implements some versioning concept, provides an excellent value in this area.

View-API of HR Schema

I’ve generated the initial version of the 1:1 view layer with oddgen‘s 1:1 View generator.

View Constraints

When building a view-API it is a good idea to also define primary key, foreign key and unique constraints for these views. They will not be enforced by the Oracle database, but they are an excellent documentation. Furthermore, they will be used by JOOQ as we see later.

View-API Model

I created the following relational model with Oracle’s SQL Developer. SQL Developer automatically took into account all constraints defined on these 7 views. Most of the work was to rearrange the view boxes on the diagram, but with only 7 views it was no big deal.

 

3. Create the View-API Database Role

We grant read access to all views as part of the API to the database role HR_API_ROLE. This is easier to maintain, especially when access to more than one connect user is granted.

Please note that just READ access is granted on the views. We do not grant SELECT access to ensure that data can’t be locked by the connect users via SELECT FOR UPDATE.  The READ privilege was introduced in Oracle Database 12c.

In this cases it does not make sense to define more than one role. But for larger projects it might be helpful to create more roles. For example to distinguish between read and write roles or to manage access to sensitive data explicitly.

4. Create the Connect User

Now we create a connect user named JOOQ. It will have the right to connect, access the view-API and execute the procedures in the SYS.DBMS_MONITOR package. The access to DBMS_MONITOR is given only to enable SQL Trace for some analysis on the database server.

5. Install JOOQ

Download the trial version of JOOQ Professional for free. The trial period is 30 days. Registration is not required.

JOOQ comes with an excellent documentation. In fact I kept the PDF version open during all my tests and found everything I needed using the search function. After extracting the downloaded ZIP file, I run the following commands on my macOS system:

This deployed JOOQ in my local Maven repository. Run ./maven-deploy.sh -h for command line options, e.g. when you want to deploy it on your Sonatype Nexus repository.

6. Teach JOOQ the View-API

JOOQ allows to build SQL and execute SQL statements in a typesafe manner. It’s similar to SQL within PL/SQL. You avoid to build SQL using string concatenation. Instead you use the JOOQ domain specific language (DSL) that knows about SQL and can learn about your database model. For example, JOOQ must learn that JOBS_V is a view and JOB_ID is its primary key column.

Technically JOOQ reads the data dictionary and generates a set of Java classes for you. You may call the generator via command line using a XML configuration file or use the JOOQ Maven plugin to configure the generator directly in your pom.xml. You may also generate these classes via Ant or Gradle. I’ve used the Maven plugin to include the code generation process in my project build.

On line 36-38 we define the JDBC driver.

On line 43-46 we configure the JDBC URL and the credentials of the connect user.

Line 50 tells JOOQ to use the Oracle meta model and line 51 is a regular expression to define the generation scope. In our case the visible objects in the HR schema and the PL/SQL package SYS.DBMS_MONITOR. Using the default would generate Java code for all public objects.

Finally, on line 54-55 we define the target directory for the generated Java classes and their Java package name.

Here’s the excerpt of the console output (of mvn package) regarding the generation run:

7. Run a Simple Query

The following code is a complete Java program. It contains a bit more than the simple query, because I will need these parts in the next chapters and would like to focus then on the JOOQ query only.

Java Program (JOOQ Query)

On line 3 the view-API is imported statically. This allows to reference the view JOBS_V instead of  JobsV.JOBS_V or Hr.JOBS_V. It’s just convenience, but it makes the code much more readable.

The next static import on line 4 allows for example to use SQL functions such as count(), sum(), etc. without the DSL. prefix. Again, it’s just convenience, but makes to code shorter and improves readability.

On line 27-28 the DSL context is initialized.  The context holds the connection to the database and some configuration. In this case I’ve configured JOOQ to produce formatted SQL statements and fetch data with an array size of 30 from the database. The JDBC default is 10, which is not bad, but 30 is in our case better, since it reduces the network roundtrips to 1 for all SQL query results.

On line 64-67 we build the SQL statement using the JOOQ DSL. The statement is equivalent to SELECT * FROM hr.jobs_v ORDER BY job_id.

Finally, on line 68 the function fetchAndPrint is called. This function prints the query produced by JOOQ, all used bind variables and the query result. See lines 51, 59 and 60.

Java Program Output (SQL Query & Result)

The program produces this output:

Two things are interesting.

First, the generated SELECT statement lists all columns, even if no column was defined in the query on program line 65.

Second, the output is formatted nicely. Where did this happen? On line 59 the query is executed and the result is saved in the local variable named result. This is a result set and contains all rows. For example, you may get the number of rows via result.size() as on line 60. I may also loop through the result set and do whatever I want. In this case I just used a convenience function format to format the result set as text. There are other convenience functions such as formatCSV, formatHTML, formatJSON or formatXML.  You may guess by the name what they are doing. Nice!

SQL Trace Output

The Java program produces a SQL Trace file by default. See program line 40. Here’s the relevant tkprof excerpt:

Interesting is line 5 of the output. There was just one fetch to retrieve 19 rows. A default configuration of the JDBC driver would have caused two fetches. Our configuration on program line 28 worked. All data fetched in a single network roundtrip.

8. Using Joins & Aggregations

In the next example we query the salaries per location. The idea is to look at a JOOQ query with joins and aggregations.

JOOQ Query

On line 6-8 you see how aggregate functions are used in JOOQ and how to define an alias for the resulting column.

Line 10 is interesting. It defines the join using the onKey function. This function requires a parameter when multiple join paths are possible. In this case EMPLOYEE_V and DEPARTMENTS_V may be joined either via DEPARTMENTS_V.MANAGER_ID or via EMPLOYEES_V.DEPARTMENT_ID. In this case we defined the latter.

On line 11 the onKey function has no parameters, since only one join path exists to LOCATIONS. This clearly shows how JOOQ uses our referential integrity constraints on the view-API to build the query. The idea is similar to a NATURAL JOIN, but the implementation is better, since it relies on constraints and not naming conventions. I already miss this feature in SQL.

The use of the functions groupBy and orderBy on line 14 and 15 should be self-explanatory.

SQL Query & Result

No surprises. The query looks as expected.

SQL Trace Output

In the tkprof output we see that parsing took more than a second. This is a bit too long for this kind of query. However, that is not a problem of JOOQ. Beside that, the output looks good. Single network roundtrip as expected.

9. Using Bind Variables

Parsing can be costly, as we have seen in the previous example. Using bind variables can reduce parsing. In this case I’d like to see how I can avoid hard- and soft-parsing. See Oracle FAQs for good parsing definitions.

JOOQ Query

JOOQ automatically creates bind variables for the usages of fromSalary and toSalary on line 23. This means that JOOQ eliminates unnecessary hard-parses by design. To eliminate unnecessary soft-parses we have to ensure that the Java statement behind the scenes is not closed. JOOQ provides the keepStatement function for that purpose as used on line 25. When using this function we are responsible to close the statement. We do so on line 7.

We call the query twice. See lines 36 and 37. On the first call the bind variables are set automatically by JOOQ when building the preparedQuery. On subsequent calls the preparedQuery is reused and its bind variable values are changed. See line 27 and 28. Fetching and printing works as for the other JOOQ queries. The only difference is, that the statement is not closed after the last row is fetched.

SQL Query & Result

We are executing the query twice. Hence we see two output sets.

On line 13/45 you see the bind variable placeholders (?).

On line 17-18/49-50 you see the values of the bind variables.

SQL Trace Output

The tkprof excerpt on line 4 shows that the query is executed twice. Line 3 reveals that the query is parsed only once. The total of 20 rows are read in two network roundtrips. That’s perfect.

10. Run a Top N Query

In the next example we query the 4 employees with the lowest salary along with some general salary information. It’ll be interested to see if JOOQ uses Oracle’s native top n query syntax.

JOOQ Query

On line 8 we see that JOOQ has no problems to deal with analytic functions.

On line 14 the top n query is defined. Limit the result to 4 rows with ties, this means rows with the same order by values are considered as well.

SQL Query & Result

The query crafted by JOOQ is a native Oracle top n query. See line 16. Very good.

Interesting is, that JOOQ also creates a bind variable for the literal 4. This could be good or bad. If you want to use a literal instead of a bind variable, you can simply use inline(4)  instead of 4. The default behaviour of JOOQ reminds a little of CURSOR_SHARING=FORCE. But since you may control the behaviour on statement level, I think it is a good and sensible default.

We got a result of 5 rows, because Landry and Gee both have a salary of 2400. That’s the result of WITH TIES.

SQL Trace Output

The tkprof excerpt looks good. Good performance, single network roundtrip.

11. Using Row Pattern Matching

What about match_recognize, does JOOQ support that? No, not currently. JOOQ has some limitations regarding SQL support. match_recognize is one and the model_clause is another. This is something to be expected, since the SQL grammar is still evolving. However, the question is, how do we deal with queries that must apply some SQL which cannot be created by JOOQ’s query builder? The solution is simple. We simply pass the pure SQL to JOOQ.

 JOOQ Query

There are other ways to produce the same result without using match_recognize. However, if you would like to use JOOQ and match_recognize than you have to build the SQL yourself and pass it to JOOQ as on line 58.

It’s sad that Java still does not support multiline strings. The code is much simpler in other JVM languages that support multiline strings, such as Scala, Groovy, Kotlin or Xtend.

SQL Query & Result

On line 49 you see that {\??\}  is used instead of ??. This is a necessity for the JDBC driver. Otherwise, the driver would expect bind variables for these question marks. All Java-based tools such as SQL Developer and SQLcl do have the same “problem”.

SQL Trace Output

The tkprof excerpt shows that most of the time is spent parsing the query, but it is still fast. All 25 result rows are fetched in a single network roundtrip.

12. Conclusion

A view-API has a value. You can change the physical data model and keep the existing view layer compatible for the consuming applications. This makes the database application independent, at least to a certain extend. That’s a great value when you think of testing interfaces or releasing new versions. Building an initial 1:1 view-API is no big deal, especially since it can be generated. The maintenance costs for such a view-API depend on many things. But I can’t imagine a scenario that isn’t worth it.

I was positively surprised by the feature richness and good usability of JOOQ. Things like the deep data model awareness including referential integrity constraints to make joins simpler and less error prone are impressive. Ok, the DSL needs some getting used to, but the excellent documentation helps a lot. Although I have only scratched the surface of JOOQ, I am convinced that it is very well suited for developing high-performing PinkDB applications. Because JOOQ developers have control over the SQL statements sent to the database.