To ORM or not to ORM – Introduction to jOOQ

What is this about?

Want it or not, at some time your application will need to store something, somewhere. I have spend years working with different relational databases and more recently – few different type of NoSQL solutions like Redis and MongoDB.

For a long time I have been a little frustrated by the options available to me as a Java Developer. In this writing I will share what I think about the major players in the Java Persistence, problems they are trying to solve and introduce jOOQ – one of the more recent and recently gaining popularity solutions.

Who is this for?

I believe many developers will find analysis of accepted ORM and non-ORM solutions in Java world interesting, as they apply to other languages. Ideas behind jOOQ are also quite nice.

Still, this post is obviously targeted at Java Developers. Some experience dealing with relational databases is required to understand it fully.

The state of Java persistence

What persistence tools are used with Java today? Let’s examine two surveys from last year:

Link to the survey

Link to the survey

Chart data looks a bit different, but what both charts show is JPA/Hibernate is ahead of the rest, followed by JDBC.

These two choices hardly could be more different, isn’t it?

One is an ORM, other is just executing SQL you tell it to execute.

Both have it’s pros and cons.

Many developers will just use one or the other exclusively and fiercely defend their choice, because this is the right and only true way to connect any Java project to database.

This is what we, as Java developers, are familiar with.

jOOQ is something a little bit different, so I will say a few words on JPA and JDBC and how jOOQ compares to them.

Problems we are trying to solve with ORM

• Write in Java, not SQL
• Work with Objects, something Java Developers are used to
• Swap your database with another easily
• Stop writing all that code that maps Object to SQL result sets (Java is verbose enough as it is)
• Some performance optimization out of the box

Each of this points of course comes with a price:

• Developers completely ignoring SQL at times
• Mapping to Objects can be hard, sets != object
• Missing on database specific features
• Complex SQL features are hard to use and often not supported at all
• Performance can require a lot of effort

After seeing the above chart data, it is obvious that most Java developers believe pros outweighs the cons.

JDBC – the accepted alternative

Suppose in your particular case, cons for the ORM outweighs the pros. You need complex queries for your analytics, but the tools given to you by your ORM are not sufficient for that.

Now you are “stuck” with JDBC.

Well, not “stuck” really, but this is what majority of people obviously use.

JDBC is what we use when we want control over our SQL.

JDBC may be powerful, but it could be more developer friendly:

• Errors are found at runtime
• It’s verbose, a lot of code to execute SQL and map it to objects
• Reading and reusing SQL requires effort

On those last points is exactly where jOOQ comes for help.

What is jOOQ

jOOQ stands for jOOQ Object Oriented Querying.

• Supports whatever your SQL is supporting
• Type safety and code completion – prevents errors at compile time
• Reusing parts of queries is now easy, just use variables
• Because its DSL is created by jOOQ from db, it checks for discrepancies on every build
• Free for open source databases
• Mature, well documented

But how exactly we are solving this issues?

A peek at jOOQ

Let’s see some code created using jOOQ and see what this is all about.

jOOQ.select(PERSON.FIRST_NAME, EVENT.NAME)
    .from(PERSON)
    .join(PERSON_EVENT_LOG).on(PERSON.ID.equal(PERSON_EVENT_LOG.PERSON_ID))
    .join(EVENT).on(EVENT.ID.equal(PERSON_EVENT_LOG.EVENT_ID))
    .where(EVENT.NAME.equal("?")).getSQL();

This code, when run, will generate the following SQL:

 select
 "public"."person"."first_name", "public"."event"."name"
 from "public"."person"
 join "public"."person_event_log" on "public"."person"."id" = "public"."person_event_log"."person_id"
 join "public"."event" on "public"."event"."id" = "public"."person_event_log"."event_id"
 where
 "event"."name" == ?
 

What is immediately obvious is jOOQ DSL resembles the SQL you want generated. You will pick it up quickly. There was some confusion at the beginning for me, but many times my first guess of what I was supposed to write was the correct one.

When in doubt – say it aloud and write what you just said.

Sample database

In the above example I have used a simple PostreSQL database with a few tables to play with jOOQ capabilities.

CREATE TABLE person (
    id serial constraint pk_person primary key,
    first_name varchar not null,
    last_name varchar not null
);
CREATE TABLE location (
    id serial constraint pk_location primary key,
    name varchar not null
);
CREATE TABLE event (
    id serial constraint pk_event primary key,
    name varchar not null,
    location_id int constraint fk_event_location references location(id)
);
CREATE TABLE person_event_log (
    id serial constraint pk_person_event_log primary key,
    description varchar,
    person_id int constraint fk_person_event_log_person references person(id),
    event_id int constraint fk_person_event_log_event references event(id)
);

Setup

I am not going into much detail here, as a lot of this is self explanatory and I am not writing a step by step guide.

Here is a Github repo with all configuration and examples mentioned here.

Few points about the project setup in the github repo:

• Framework: Spring Boot via spring-boot-jooq
• Build tool: Gradle. It doesn’t really matter what you use, most examples on the web and jOOQ site use Maven
• Repo comes with sample database and setup for it

I have used gradle-jooq-plugin to integrate jOOQ DSL creation in my builds and put the minimal configuration required to connect it to a my test database.

One you have set up everything you have a generateSrcjOOQSchemaSource gradle task available.

If you are curious where are generated DSL files going – default location is builds/generated-src/jOOQ.

One more thing:

@Autowired
DSLContext jOOQ;

All code samples from now on assume you have jOOQ’s DSLContext available. Here I obviously use Spring.

Generate SQL with jOOQ

You can use jOOQ just to generate SQL for you, even without using its DSL.

This code example:

String sql = jOOQ.select().from(table("PERSON")).getSQL();

will (unsurprisingly) generate following SQL:

SELECT * FROM PERSON;


JDBC or whatever you like can now execute it.

Of course using the DSL is much more productive. Here is a more complicated example:

Person p = PERSON.as("p");
Event e = EVENT.as("e");
PersonEventLog pel = PERSON_EVENT_LOG.as("pel");
String sql = jOOQ.
select(p.FIRST_NAME, e.NAME).
from(p).
join(pel).on(p.ID.equal(pel.PERSON_ID)).
join(e).on(e.ID.equal(pel.EVENT_ID)).
where(e.NAME.equal("any_value")).getSQL();

Which will generate:

select "p"."first_name", "e"."name"
from "public"."person" as "p"
join "public"."person_event_log" as "pel" on "p"."id" = "pel"."person_id"
join "public"."event" as "e" on "e"."id" = "pel"."event_id"
where "e"."name" = ?


Some interesting things to note here:

• Use of variables is easy, no need for string concatenation
• where "e"."name" = ? – value for the event name is not seen – this is the SQL prepared for a PreparedStatement execution! 

You can passParamType.INLINED as parameter to getSQL() if you want to change the behavior from the last point.

Execute SQL with jOOQ

Suppose that you want to just execute a DELETE:

jOOQ.deleteFrom(PERSON).execute();


Now what if you want a execute a SELECT and fetch results? You use fetch() instead of execute() and can now map your results in many different ways.

A simple fetch:

Result<PersonRecord> personRecords = jOOQ.selectFrom(PERSON).fetch();


What is interesting here?

• Result<PersonRecord> personRecords is a java.util.List
• PersonRecord is one of the generated DSL classes and is an ActiveRecord representing the Person table from the sample db.

For example you can use the fetched results to print all firstNames like this:

personRecords.stream().forEach((personRecord) -> System.out.println(personRecord.getFirstName()));


Fetch results from executed query

Fetching can save you a lot of boilerplate JDBC code. It will not save you all – while it is easy to work with the Active Records generated by the jOOQ, you cannot modify them. You often need to map them to one of your own objects, so you are not spared from all mapping. Even with this it is still pretty cool feature.

Fetch provides many convenient methods like:
fetchOne():

PersonRecord personRecord = jOOQ.selectFrom(PERSON).where(PERSON.ID.eq(1)).fetchOne();

fetchInto() and fetchOneInto():

String firstName = jOOQ.select(PERSON.FIRST_NAME).from(PERSON).where(PERSON.ID.eq(1)).fetchOneInto(String.class);

fetchMap():

Map<String, String> personEventMap = jOOQ.select(PERSON.FIRST_NAME, EVENT.NAME)
.from(PERSON)
.join(PERSON_EVENT_LOG).on(PERSON.ID.equal(PERSON_EVENT_LOG.PERSON_ID))
.join(EVENT).on(EVENT.ID.equal(PERSON_EVENT_LOG.EVENT_ID))
.fetch()
.intoMap(PERSON.FIRST_NAME, EVENT.NAME);


What if the objects you are trying to put into map do not have on-to-one relationship?

In this example above I map person to event. Obviously many people go to events.

In this case jOOQ will give you this helpful log message:

org.jooq.exception.InvalidResultException: Key JProfessionals is not unique in Result for 
+----------+--------------+
|first_name|name          |
+----------+--------------+
|Ivan      |JProfessionals|
|Kostadin  |JProfessionals|
+----------+--------------+

I have used this as an example to show how developer friendly jOOQ is. When I did a live demo on this people noticed this log and later came to me to tell me how great this is. Well done jOOQ 🙂

Enough with the error – how do you solve the problem? When you need one-to-many just fetch into groups:

fetchGroups():

Map<String, List<String>> groupedMap = jooq...DSL goes here...
        .fetch()
        .intoGroups(EVENT.NAME, PERSON.FIRST_NAME);

fetchResultSet():

There are many other methods and combinations, like fetchOptional(), fetchArray(), fetchAny() and many more.

Lazy fetching

Remember that fetch() will give you a List of records?

You can also return records one by one.

Usually you will need to do this for performance reasons. jOOQ will in this case return a Cursor. 

Iterate over the cursor to get results one by one.

try (Cursor<PersonRecord> cursor = jooq.selectFrom(PERSON).fetchLazy()) {
    while (cursor.hasNext()) {
        PersonRecord personRecord = cursor.fetchOne();
        // doStuff here!
    }
}

CRUD with DSL Active Records

Remember that we fetch ActiveRecords?

You are free to store(), update() and delete() any of the Active Records created by DSL.

Here is a full example:

PersonRecord personRecord = jOOQ.newRecord(PERSON);
personRecord.setFirstName("Ivan");
personRecord.setLastName("Ivanov");
personRecord.store();
personRecord.setLastName("Dimitrov");
personRecord.update();
personRecord.delete();

My db changed, Now what?

My favorite jOOQ feature!

Suppose you change your database and rename column first_name to something else or drop it.

Now clean your build and rebuild it, so your schema will be regenerated.

What will happen?

error: cannot find symbol
PersonRecord emptyRecord = jOOQ.selectFrom(PERSON).where(PERSON.FIRST_NAME.eq("Ivan")).fetchOne();
                                            ^
symbol:   variable FIRST_NAME
location: variable PERSON of type Person

Because DSL is regenerated, your compilation fails!

After you fix it, run clean and rebuild, so it compiles again.

How to start using jOOQ in your projects?

I made a talk on jOOQ and many people asked me this question. There was some confusion, so I’ll try to answer here. First one disclaimer:

There is no one true and only way to connect to you database.

Choose the better tool for the job you want. That means sometimes you want to combine them.

Let’s see what JOOQ offers again:

• Type-safe SQL creation – you get SQL in the end
• Execute SQL
• Map SQL results to something you can use
• CRUD operations with DSL ActiveRecords

Use all or just a few of the list above – whatever you think is good for your project.

JPA projects:

continue using JPA 🙂 Where you feel you need raw SQL – use jOOQ type-safe SQL creation to generate it.

JDBC projects:

here you use raw SQL a lot, so jOOQ type-safe SQL creation is a no-brainer. This will save you a lot of time and effort. Proceed with other features like mapping to objects, etc.

Summary

jOOQ’s simple philosophy is “put SQL first”.

It is an welcome upgrade from JDBC and can help you do things your ORM makes hard or impossible.

It is well build, lightweight and documented tool, one that I am glad I started to use. There is a lot of effort in making it developer friendly and it is surprisingly hard to shoot yourself in the foot with it.

If you need raw SQL, give it a try!

In the end – here is link to the Github repo with the example project again.

Thank you for reading.

If you would like to receive future post updates, please subscribe below.