Object Relational Mappers or ORMs. You either love them or hate them. What was once a simple goal of easing data access for relational databases in the object oriented world has become a mature landscape of exceptional tools and standards. For simple cases, a developer hardly needs to think about the database at all and can program as if it was really just an object store. Yet ORMs are not without their detractors. Like any tool, they have pros and cons. Here are some of the most common ones:
Pros
- They let the developer think in terms of objects rather than sets
- They save the developer from having to write SQL code
- They can often generate better SQL than the developer
- They remove the developer from having to write tedious mapping code to get from relational sets to object graphs
- They have advanced features such as transparent lazy loading
- They allow for a certain level of database agnosticism
Cons
- They often have a special query language which developers have to learn
- If it does have a special language, the developer often has to keep those queries and the generated SQL in their head while developing with it, so that performance problems don’t crop up
- The query language is often only a subset of SQL’s functionality
- Traversing large object graphs can be very inefficient unless the developer takes care to design their object model with the ORM in mind and use lazy loading and prefetch options correctly
- There is an inherent impedance mismatch from the set-based relational world to the object-oriented world
- Legacy databases may be hard to map on to, especially if a lot of data transformation needs to take place
Interestingly, you’ll notice a lot of the cons are flip sides of the advantages. As is often the case, you have to give up something to get something.
Depending on your problem domain, the cons may outweigh the pros and so you might just want to stick with straight SQL. Yet without using an ORM, the developer is seemingly stuck with using JDBC, which has its own issues:
- It uses strings of SQL code to execute queries, which can have errors that only become known at runtime
- You have to catch all of its checked exceptions
- The correct data types must be used when you set column values on an insert or extract the values from a result set
- The database schema and code must be kept closely versioned. Changes to the schema can result in runtime errors in the code and vise-versa
- The Statements use an inconvenient and error prone positional argument binding
There are a number of helper libraries, such as Spring’s JDBC Template, which make it easier to work with. However, the API is old and a lot of design decisions, such as the differences between Statement and PreparedStatement and the merging of read and write APIs seem odd today. However, there is a newer library that strikes a balance between JDBC and ORMs, without their drawbacks. It’s called JOOQ.
JOOQ: Java Object Oriented Query
JOOQ is a Java code generation tool and data access library for relational databases. Its basic use-cases are the same as ORMs, yet it embraces SQL rather than trying to hide it. It provides a vast amount of features for working with databases, but there are a number of distinct features which I think make it a superior solution over both straight JDBC and ORMs:
- A fluent DSL that maps one-to-one with SQL. SQL is one of the best fourth-generation purpose specific languages and doesn’t need to be re-invented. With JOOQ you can use all the functionality of your selected database rather than an abstraction or limited subset.
- The DSL it generates allows your IDE to auto-complete while you write your SQL
- It uses Java’s type system to ensure that the SQL it generates is valid
- Type safety. JOOQ’s API does its best to ensure you are using the correct Java data type for each column in your database. For example, through JDBC, it will ensure that you are using a wide enough data type to hold the data in a numeric column.
- An easy to use type converter extension for common data type transformations, such as Timestamp or SQL Date/Time to java.time.ZonedDateTime.
- Where it makes sense, the DSL offers some extensions to SQL, such as the update-statement-like InsertSetStep extension that make inserts as readable as updates.
- No impedence mismatch. There is mapping functionality which helps you map result sets on to POJOs and provides an implementation of the active record pattern for working in an ORM-like way, but it’s clear what’s happening and the magic is minimized.
- Because the API uses Lists for result sets, you can easily use Java 8’s new Streams to transform result sets easily and succinctly
- If someone changed the database schema on you, you’ll get compile errors rather than runtime ones
- It works with all major relational databases: MS Access, MS Access 2013, CUBRID, IBM DB2, Apache Derby, Firebird, H2, Hypersonic, Informix, Ingres, MariaDB, MySQL, Oracle, Oracle, PostgreSQL, SQLite, SQL Server, SQL Server and Sybase
- It’s dual licensed. It’s free to use for all open source databases. Oracle, MS SQL Server, Informix, Ingres, DB2 and Sybase require a commercial license, which also provides support. However, if you are running any of those commercial databases, you can probably afford a license!
Example Code
I’ve set up the project to use PostgreSQL via Homebrew on OSX. You will also need Maven and Java 8 installed. To install Postgres, start it with your own personal database, create the tables, compile and run the project, just cd into the root of the example project and execute this code from the terminal:
brew install postgresql
postgres -D /usr/local/var/postgres &
createdb $USER
psql < database.sql
mvn compile exec:execYou might need to tweak the code a bit if you have the database set up a different way or if you are on a different operating system. I’ll leave that exercise up to the reader.
Enter JOOQ
The first issue with JDBC and many ORMs is that they use strings of SQL code which are sent to the database to be executed. This is clearly a problem because there is no way to know that those statements are correct until they are executed. Though you will have ideally written tests to ensure your database access layer is correct, wouldn’t it be better if the compiler, or better yet, your IDE could do that as you code, just like Java?
JOOQ uses code generation to build a set of Java classes which represent your database. Combined with its runtime library, these classes allow you to write Java code that is almost identical (at least in structure) to SQL. The difference is that the DSL ensures that the SQL you are writing is syntactically correct and also type safe.
First, let’s look at the insert example. JOOQ has a very nice batch insert DSL that is far superior to JDBC’s:
Long evansId = dsl.nextval(AUTHOR_SEQ);
Long vernonId = dsl.nextval(AUTHOR_SEQ);
dsl.batch(
dsl.insertInto(AUTHOR)
.set(AUTHOR.AUTHOR_ID, evansId)
.set(AUTHOR.FULL_NAME, "Eric Evans"),
dsl.insertInto(AUTHOR)
.set(AUTHOR.AUTHOR_ID, vernonId)
.set(AUTHOR.FULL_NAME, "Vaughn Vernon"),
dsl.insertInto(BOOK)
.set(BOOK.BOOK_ID, dsl.nextval(BOOK_SEQ))
.set(BOOK.AUTHOR_ID, evansId)
.set(BOOK.TITLE, "Domain Driven Design"),
dsl.insertInto(BOOK)
.set(BOOK.BOOK_ID, dsl.nextval(BOOK_SEQ))
.set(BOOK.AUTHOR_ID, vernonId)
.set(BOOK.TITLE, "Implementing Domain Driven Design")
).execute();As you can see, the nice DSL allows you to mix and match Java and SQL code very easily. In this example, I’ve reused the sequence value in the insert statements. It also shows JOOQ’s nice update-like insert-set functionality, which saves you from needing to use positional arguments on insert.
Next, let’s take a look at the active record example:
dsl.selectFrom(AUTHOR)
.fetch()
.stream()
.forEach(a -> {
System.out.println("======From Fetch Into =======\n"
+ a.getFullName());
});This example shows how you can select your records into a generated active record class. This shows a few nice aspects of the DSL. First, JOOQ knows which generated active record class is mapped to the table, and it provides a nice type for it, rather than a string identifier in the selectFrom call. Also, it shows how the result set can be transformed into a Java 8 Stream to be iterated over. That functionality is also great for transforming any result set, including doing custom mapping:
dsl.select(
BOOK.TITLE,
AUTHOR.FULL_NAME
)
.from(BOOK)
.join(AUTHOR)
.using(BOOK.AUTHOR_ID)
.fetch()
.stream()
.map(r -> {
return new Book(
r.getValue(BOOK.TITLE),
new Author(r.getValue(AUTHOR.FULL_NAME))
);
})
.forEach(b -> {
System.out.printf(
"=============== From Stream ================\n"
+ "BOOK.TITLE: %s\n"
+ "AUTHOR.FULL_NAME: %s\n",
b.getTitle(), b.getAuthor().getFullName()
);
});What’s great about the stream functionality is you can do custom mapping to any domain model easily or otherwise transforming the result set using lambdas and map, filter, reduce, etc.
Finally, there is an example of using the data type conversion. In the POM, I’ve added a small bit of configuration to the JOOQ area:
...
<customTypes>
<customType>
<name>ZonedDateTime</name>
<type>java.time.ZonedDateTime</type>
<converter>co.cantina.jooq.example.ZonedDateTimeConverter</converter>
</customType>
</customTypes>
<forcedTypes>
<forcedType>
<name>ZonedDateTime</name>
<expression>.*\.date_added.*</expression>
</forcedType>
</forcedTypes>
...Along with the co.cantina.jooq.example.ZonedDateTimeConverter class I implemented, this will force JOOQ to always convert a column called “date_added” to a java.time.ZonedDateTime. So when a query is performed with that column, it will automatically be converted:
ZonedDateTime date = dsl
.select(BOOK.DATE_ADDED)
.from(BOOK)
.where(BOOK.TITLE.like("Domain%"))
.fetchOne(BOOK.DATE_ADDEDSystem.out.printf(
"=============== Data Conversion ================\n"
+ "Date Added: %s\n",
date
);Lastly, try changing the database schema, such as removing one of the columns used in the code, then clean and recompile. You’ll get compile errors rather than runtime ones since the identifiers will be gone.
I hope these examples helped illustrate some of JOOQ’s advantages and I urge you to explore the framework. We used it here at Cantina on a client project where we needed to map a new domain model on to a legacy Oracle database. JOOQ allowed us to do some amazing data transformations using the full power of Oracle and keep the legacy database running while we slowly transitioned to a new system. The kind of custom mapping to the new domain model we developed would have been very difficult to achieve with an ORM. That work is in production now and is working great. Give JOOQ a try on your next project, and you might just want to give up your ORM.