Jan 12 2023
Leave a comment
By kanesee Coding

Jdbi vs jOOQ

What are these 4-letter non-sensical words?

Jdbi and jOOQ are both java libraries that help you access relational data.

Why not just construct a SQL query as a String and execute it via jdbc? Well, then your code would be vulnerable to sql injection by an attacker or more likely, you would have some bug that tries to check age > "blue" at some point. Basically, you need type-safety to ensure no other junk gets intentionally or accidentally injected into your query.

Here’s how they differ

Here’s a Jdbi example

@SqlQuery("SELECT * FROM employees WHERE department IN (<departments>)")
@RegisterRowMapper(JdbiEmployeeMapper.class)
List<Employee> getEmployeesByDepartment(@BindList("departments") List<String> departments);

Here’s a jOOQ example

DSLContext dslContext = ...
Employees EMPLOYEES_TABLE = Employees.EMPLOYEES;
List<String> departments = ...
dslContext.select().from(EMPLOYEES_TABLE).where(EMPLOYEES_TABLE.DEPARTMENT.in(departments).fetch(EmployeeMapper::map)

Both pretty concise, but I would argue that Jdbi wins in terms of readability. Also, I didn’t show it here, but the jOOQ Employees table above needs to be code generated. There’s just more set up involved with jOOQ.

So why the heck would anyone use jOOQ?

Here are a couple of reasons

  • You want a more complicated query with filtering of arbitrary fields
  • You want a more complicated query with sorting of arbitrary fields
  • You want to paginate your results

Arbitrary Filtering

Suppose the client wants to filter employees on either department or status. You might write another jdbi query filtering by status and choose one or the other. What if the client wants to filter by both department and status? It starts to get a bit more complicated as the number and combination of filter fields increases.

You could do this stackoverflow hack.

SELECT * FROM things
WHERE (:foo IS NULL OR foo_column = :foo)
AND (:bar IS NULL or bar_column = :bar)

I don’t think that’s the right approach though. Your query becomes unwieldy. We haven’t even considered performance here. Some irrelevant index (or worse, no index) might be used because it sees foo in the conditional even though it’s not used.

Since jOOQ lets you construct the query, it just more naturally fits the bill

dslContext.select().from(EMPLOYEES_TABLE).
    .where(buildConditional(query))
    .fetch(EmployeeMapper::map);
...
private Condition buildCondition(query) {
  Condition condition = noCondition();
  if (!query.departments().isEmpty()) {
    condition = condition.add(EMPLOYEES_TABLE.DEPARTMENTS.in(query.departments());
  }
  ...
  return condition;
}

Arbitrary Sorting

Can’t really be done easily in Jdbi. Jdbi lets you work with an external template engine. The jdbi stackoverflow link above has an example, but it’s really getting out of the ease and focus of jdbi in my opinion. Also, seems even with the jdbi 3rd-party templating approach, there’s a restriction on a static number of fields to sort on possibly.

Again, jOOQ to the rescue

List<SortField<?>> sortFields = ...
dslContext.select().from(EMPLOYEES_TABLE).orderBy(sortFields);

Pagination

Finally, if you don’t want to return your entire database table at once (probably a good idea), then you’ll need to paginate. In order to paginate, you need to have a deterministic sort order. Otherwise, there’s no telling what the 2nd page of randomly sorted results will hold.

Assuming you will only ever sort on exactly 1 field, you can do it with jdbi. Let’s say we always sort Employees on start_date in descending order and we want the 10th page of 100 results

select * from employees
order by start_date DESC
LIMIT 1000, 100

This is … just bad. It is slow because the database needs to scan through the 1st 10 pages of 100 results.

There’s a better way using seek. The idea is, what if you could just use the where clause to skip to the next record?

Suppose after the 10th page of 100 results, you get back an employee with a start date of Jan 1st, 2023. Well, maybe we can do something like this

select * from employees
where start_date > '2023-01-01'
order by start_date DESC
limit 100

This lets us leverage the indexes to quickly skip through all the previous records. Now, the astute reader will realize that there could have been multiple employees with that start date so we would have skipped them with this query. Let’s suppose the last employee we got back had an id of 4444. This is how we would fix that

select * from employees
order by start_date DESC
where (start_date > '2023-01-01') or (start_date = '2023-01-01' and employee_id > 4444)
limit 100

So you can see how this works logically. However, constructing this query in jdbi would be infeasible. Even with jooq’s declarative api, it would be difficult.

Fortunately, jOOQ does it automagically for you! Simply give it the sort keys and the value of the next row, and jooq’s seek() will construct the query for you

List<String> seekValues = Arrays.asList("2023-01-01", "4444")
dslContext.select().from(EMPLOYEES_TABLE)
    .orderBy(sortFields)
    .seek(seekValues)
    .limit(100)

jOOQ will automatically build the where clause you see above. Just make sure that your sortFields columns match up with your seekValues

So what should I use?

It depends. For simple queries, I highly recommend Jdbi. For more sophisticated queries, I recommend jOOQ. If you foresee needing jOOQ at all, maybe just go with jOOQ all the way

Tagged , ,

Leave a comment