Open Sourcing Jolo

Jolo, a jOOQ utility library for object loading

Jolo (short for jOOQ Loader) is a small library we created for jOOQ. Jolo builds up the object model of the entities in your jOOQ records. We’re using Jolo in production at Picnic for over a year now. We’re happy to announce that the source code for Jolo is now available on GitHub!

Want to get started right away? Check out the GitHub page.

Why Jolo?

At Picnic, our systems are growing. We want more people to get their groceries delivered and have the lowest prices. To do this, we keep our software quality as high as possible. This means that code is readable, easy to understand, and performs well. That enables us to serve more people, better.

More and more of our projects are switching from MongoDB to Postgres. That means our usage of jOOQ is growing. Many of our queries are straight-forward fetching of entities from the database and mapping them to their equivalent Java model. As this usage grew, we wanted to build a tool to handle the process of building the object-graph model.

jOOQ

jOOQ is a tool to write type-safe SQL in Java. jOOQ generates a model based on the schema that you use in its fluent API to write SQL. When executing your query, jOOQ returns a record based on the fields in the SELECT clause.

Many of Picnic’s services are powered by Postgres

When writing SQL queries, our SELECT clause fetches records from one or more tables. For records from a single table, or representing a single entity, you can usually map the record to an entity in one line with jOOQ’s powerful into() pattern. In the example below we fetch articles and map them to their Java class using this feature.

This works great when fetching single entities. When fetching more than one entity, it takes a bit more work. This is because our entities have relations to each other. Using these relations, we want to rebuild the object-graph model the data represents. We want to do this because it allows us to use a standard way to access and use data in various parts of our application. This helps simplify our logic and write cleaner code.

Let’s look at a query in our warehousing software that does this.

For this query, we would like to treat it in Java as a set of Stock objects and be able to access its Location and Lot as properties on the objects (If you want to know more about what these objects mean, we’re hiring!). How would we go about instantiating and linking these objects together? Here’s one approach:

In this example, we create a map for each entity. Then, as we stream the result set, we map the values in the record to the various Java entities. We use the map to deduplicate the records, and then use the setters of the Stock class to instantiate relationships between these objects.

This approach above works fine but uses a lot of repetition. This causes duplicated code which can be error-prone. If you have many relationships or cyclic references, it gets even more complicated. This is where Jolo comes into play.

Jolo

Jolo is a tool that adds basic object-relational mapping to jOOQ. It is designed to create the object subgraph returned as MultiRecords in your jOOQ queries. It provides an API for defining how your entities should be joined (which PK’s to use) and their arity. Jolo deduplicates the entities returned in records, maps them to their specified Java type, and links them together using the specified setters.

We made the choice to instantiate relations between domain objects in-memory due to a combination of considerations regarding encapsulation, maintainability, and complexity of the business logic. It’s important for us to point out that in many cases you can design your application to not use object graphs in Java altogether. Whether doing this is right for you is subjective. Our recommendation is that you make a conscious choice about this, and to not use Jolo because we are using it, or because it looks fancy. (It is.)

If you are familiar with Hibernate, you know that there is a notion of “fetch join”— a SQL table join that you perform just to fetch extra data with which entities can be populated. Jolo allows you to do a similar thing. If you write a query in which you join multiple tables together in order to retrieve multiple related entities, this tool allows you to easily map the resulting records onto Java objects and instantiate the relations between them.

There is one caveat. The “fetch join” approach means that you are fetching all data into memory first, mapping each record to a Java object, and only then instantiating the relations between the resulting objects. This means it is not usable in any form of stream processing.

Jolo deduplicates entities

Key features

The key things that Jolo does for you are:

Use the built-in object mapper to map records to one or more objects

Deduplicate: based on the primary key of each entity you specified, each record is mapped to an object once. This also means that entities returned by Jolo from a single result set can be compared using the default implementation of Object.equals (although we strongly recommend providing a sensible override).

Instantiate relations: based on primary and foreign keys, setters are called on the mapped objects to create relationships between them. There is support for cyclical relations, and setters can be called with an Optional argument (for optional relations) or a List argument (for *-to-many relations).

Check key constraints: it checks that the relations you specify are defined by foreign keys on the corresponding tables.

Avoid naming confusion: field lookups in records can sometimes give surprising results in jOOQ. If you have a record containing only fields from table FOO, and you look up a field in it from a table BAR, then it may still return a non-null value because it uses a best-effort approach to match the field name. Jolo checks that the records you fetch contain exact matches for the fields that it expects, so the returned entities do not accidentally “borrow” values from another table if you made a mistake in your query.

What Jolo does not do for you:

It does not create a query for you. You are solely responsible for making sure that the query results in records that match the description you give Jolo.

It does not currently support entities with primary keys that are not of type long in Java.

It does not support lazy loading. The only query strategy that it deals with is the “fetch join” approach. There are no plans to add automatic fetching of referred entities by automatically performing extra queries.

Jolo Example

Using our earlier example, we specify a Loader in the fetchInto(). This is where Jolo is used. Separately, you define a loader specifying the entities it should expect and how the entities relate to each other. In this example, a Stock Loader is defined for Stock, Article, Lot, and Location. This loader does the work of mapping records into the various java types, deduplicating them, and adding them via the various setters.

Note that the loader automatically figures out which foreign keys to use to instantiate the relations. If an automatic resolution is not possible because there is more than one foreign key that relates the two tables of a relationship, then you can specify which field to use as the foreign key.

With the loader specified above, our jOOQ query is simply:

For a more in-depth example, check out the README of the project on Github.

Open Sourcing Jolo

By making this tool available, we invite you to give it a try in your own application. We realize the use case for this tool is not applicable to every situation, but it’s been a huge help to us, and we hope some of you find it useful as well.

Interested in Jolo? Get started with it today or chat with us at one of our upcoming meetup events!