Why You Ought to Use jOOQ With Code Technology – Java, SQL and jOOQ.

I’m answering many jOOQ questions on Stack Overflow, and lots of occasions. The issue has the identical trigger: Folks not utilizing jOOQ’s code generator. The principle motive individuals appear to not be utilizing it, is as a result of it takes some further time to arrange, however as with something effectively designed, the preliminary funding will all the time repay.

On this article, I wish to briefly summarise what the code generator is, why it is best to use it, and what’s the essential motive to not use it (trace: you in all probability don’t have that case).

What’s the jOOQ code generator?

jOOQ is an inner DSL that pretends which you can write kind secure, embedded, dynamic SQL straight in Java. Identical to you are able to do that in PL/SQL, PL/pgSQL, T-SQL, SQL/PSM and all the opposite procedural dialects. Being inner, the large distinction is that dynamic SQL could be very straightforward, as a result of jOOQ makes SQL “composable”.

As an inner DSL, it fashions the SQL language as a series of Java technique calls. The approach behind the fluent API design is described right here. A typical jOOQ question seems like this:

var writer =
ctx.choose(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
   .from(AUTHOR)
   .the place(AUTHOR.ID.eq(10))
   .fetchOne();

Now these AUTHOR desk references, in addition to these AUTHOR.ID column references are generated code. They’re Java representations of your database meta information, and embody a wide range of data:

• The catalog / schema they belong to
• The desk they belong to (if it’s a column)
• The info kind
• Some hooked up converters and information kind bindings
• Different helpful meta information, akin to constraints (main key, distinctive key, international key), identities, default expressions, generated expressions, and so on.
• Auxiliary DSL strategies, akin to kind secure desk aliasing strategies

As you’ll be able to see, you get lots of out of the field options!

What’s the value to pay for code era?

As I perceive, lots of customers (particularly new ones) are wanting to check out jOOQ instantly and don’t wish to trouble establishing the code generator, which appears to be an pointless step at first. E.g. when utilizing JAXB, you don’t must use XJC to generate annotated bindings on your XSD information (you may not even have an XSD). However with jOOQ, it’s completely different.

Sure, the guide might be overwhelming, however the tutorial isn’t, and neither are the examples. You don’t even have to make use of the Maven or Gradle plugins on your first makes an attempt with jOOQ. Why not simply use jbang. With only a few strains of CLI, you can begin straight away.

To check out staple items, you can even use the DDLDatabase, which generates code from SQL information straight, and not using a dwell connection to an precise database, although that isn’t actually an enormous subject anymore with testcontainers.org and/or docker, which lets you spin up a pattern database in a matter of seconds.

What I’m making an attempt to say right here is: Don’t be overwhelmed. Sure, there’s some preliminary funding, however it’ll repay massively (see beneath), will forestall tons of issues (see beneath), and it simply requires just a few extra minutes of establishing your mission. Heck, you possibly can even begin taking part in round with the bug reporting template, which incorporates code era configuration for Java, Scala, and Kotlin!

Let’s begin with the plain:

1. Compile time kind security

You already get some extent of compile time kind security from utilizing jOOQ’s DSL alone, specifically, you can’t mistype key phrases, akin to SELECT, or neglect your parentheses round an IN predicate, akin to A IN (B, C). And also you get auto-completion on your SQL syntax

However with out code era, you received’t get kind security on your schema objects. This can be a huge deal, and when you’ve gotten used to it, you’ll by no means wish to miss it once more!

Each desk, each column, each constraint, each index, each sequence, each process, each perform, each package deal, each schema, each catalog, each area, each enum kind, each object kind will get a generated Java illustration in jOOQ. You don’t must go to your ERD, or database, or wherever it’s to search for your schema. You could have it proper there in your code. As a primary step, you should utilize this for auto completion in your favorite IDE!

Not simply that. You can even by no means make a mistake together with your meta information:

Yep. No extra typos. But in addition! No extra regressions when somebody renames a column within the database. As quickly as a column title modifications, or is deleted, your Java code stops compiling.

No extra incorrect information sorts. See the above screenshot? It says that FIRST_NAME is a TableField<AuthorRecord, String>. So your Java compiler, by jOOQ’s elaborate generic API, will already know that you just’re projecting a String column. It’s not 2003 anymore. We’ve generics to forestall kind casts or unsafe conversions. Take a look at the compilation error on this:

Why ever fear about such information kind issues once more? You already declared the information kind as soon as, whenever you created the desk:

CREATE TABLE writer (
  id INTEGER NOT NULL PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  ...
);

Why undergo all that busywork of re-declaring the information kind once more in Java? In reality, why undergo all that busywork of re-declaring something of the above once more in Java? Nobody enjoys typing all that stuff twice or conserving it in sync. In reality, it’s an outright unhealthy thought. There ought to solely be one supply of reality on your meta mannequin, and that’s the DDL you ran in your database to create the schema.

Certain, you’ll be able to produce other representations of your information, e.g. some DDD model, some JSON model on your frontend, no matter. However everytime you question your database, there’s completely no motive to not use the precise meta mannequin which you’ve already declared in your database.

I feel that’s sort of apparent? Why waive all that for a bit of (preliminary) further setup comfort?

2. Schema introspection

You don’t simply get that profit when writing your queries. You additionally get it when studying them. You possibly can all the time rapidly navigate to the column you’re fascinated by and skim its SQL feedback within the Javadoc. Perhaps you’ve got this specified? (Completely underused SQL function!)

COMMENT ON COLUMN writer.first_name IS 'The writer''s first title';

Now look what jOOQ’s code generator does with it:

Appears apparent, no? Or, you wish to see who’s utilizing this column? Simply examine the decision hierarchy or no matter IDE search software you’re fascinated by:

That is significantly better than simply textual content looking for FIRST_NAME, which may be case delicate and matches all strings like that, not simply that specific column of the AUTHOR desk.

3. Runtime meta mannequin: Information sorts

In reality, you received’t simply revenue from this sort security at compile time, but in addition at runtime. Belief me. There are such a lot of edge circumstances in some JDBC drivers and/or SQL dialects the place the information kind have to be communicated to the database explicitly.

Certain, you’ll be able to write one thing like this in jOOQ, and it really works:

var writer =
ctx.choose(discipline("writer.first_name"), discipline("writer.last_name"))
   .from("writer")
   .the place(discipline("writer.id").eq(10))
   .fetchOne();

The above is utilizing plain SQL templates, a function that’s usually utilized by customers to increase jOOQ with customized performance. Certain, it may be used as an alternative of utilizing code era, too, and sometimes, that’s simply the fitting factor to do.

When bind variables have inadequate kind contexts

However there are numerous circumstances the place it is best to give the SQL engine extra context in regards to the bind worth. For instance, in Derby (or older variations of Db2), you’ll be able to’t simply do that:

choose null
from sysibm.sysdummy1;

You’ll get an error:

SQL Error [30000] [42X01]: Syntax error: Encountered “null” at line 1, column 8.

As an alternative, it’s important to CAST.

choose solid(null as int)
from sysibm.sysdummy1;

jOOQ all the time provides these casts for you, if we discover out by our intensive integraiton assessments that in any given scenario, a dialect would possibly require it. However jOOQ can solely try this in the event you present it with a kind, and infrequently you do, implicitly, however typically, you don’t, after which it’s important to debug a question that used to work earlier than. That may be very sudden.

When a Discipline<Object> results in compilation errors

This one is difficult. The Java language model 8 has made a peculiar resolution round overload decision with generics. Assuming you’ve got overloads like this (and jOOQ is stuffed with these, for comfort causes):

public <T> void setValue(Parameter<T> parameter, T worth) {}
public <T> void setValue(Parameter<T> parameter, Discipline<T> worth) {}

Then there was a backwards incompatible change of compilation behaviour in Java 8.

Whereas this works completely advantageous:

Parameter<String> p1 = ...
Discipline<String> f1 = ...
setValue(p1, f1);

This name doesn’t compile:

Parameter<Object> p2 = ...
Discipline<Object> f2 = ...
setValue(p2, f2);

It appears that evidently Discipline<Object> is just not extra particular than Object on this case, which appears bizarre, however that’s how the JLS designed generic overload decision. In reality, it’s nonetheless extra particular, however one other rule declares the 2 sorts as ambiguous earlier than we get to the specificity decision. Learn the above hyperlink for particulars.

Let’s look once more on the earlier jOOQ question that doesn’t use code era:

Record2<Object, Object> writer =
ctx.choose(discipline("writer.first_name"), discipline("writer.last_name"))
   .from("writer")
   .the place(discipline("writer.id").eq(10)) // Discipline<Object>
   .fetchOne();

It’s all filled with Discipline<Object>, so that you’ll run into this drawback ultimately, belief me, principally with the UPDATE .. SET clause. And it will likely be fairly the puzzler.

The workaround is to all the time connect kind data to your columns:

// Assuming this static import
import static org.jooq.impl.SQLDataType.*;

Record2<String, String> writer =
ctx.choose(
        discipline("writer.first_name", VARCHAR),
        discipline("writer.last_name", VARCHAR))
   .from("writer")
   .the place(discipline("writer.id", INTEGER).eq(10)) // Discipline<Integer>
   .fetchOne();

However why do all that, when the code generator might have already carried out it for you, routinely? And what in the event you change your AUTHOR.ID column to be a BIGINT as an alternative of an INTEGER? Will you manually replace each question?

4. Emulations

Some emulations will not be potential with out generated meta information. They principally contain the emulation of the INSERT .. RETURNING syntax, which depends on figuring out each main key and identification data, however different syntaxes could also be affected as effectively.

You wouldn’t imagine what number of occasions I’ve supported customers who then needed to make use of inner API to inform jOOQ about main key or identification meta data in convoluted methods to make this work, reasonably than simply generate code. With generated code, the next question works in lots of RDBMS out of the field. Out. Of. The. Field:

AuthorRecord writer =
ctx.insertInto(AUTHOR, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
   .values("John", "Doe")
   .returning(AUTHOR.ID)
   .fetchOne();

• Can this be run in a single question (e.g. in Firebird, Oracle, PostgreSQL as above, in SQL Server utilizing OUTPUT, in Db2, H2 utilizing information change delta tables)?
• Can or not it’s run in a single question utilizing JDBC’s Assertion.getGeneratedKeys()?
• Does it require a second question to fetch the identification and/or different columns?

jOOQ can all the time discover a manner for you on this case, however provided that it is aware of which column the identification is.

5. Converters

Fundamental SQL solely actually has just a few built-in information sorts:

• Varied numeric sorts
• Varied string and binary string sorts
• Varied temporal sorts

However is it a good suggestion to work with these low degree sorts, all over the place all through your software? Do you wish to use BigDecimal on your financial quantities in every single place? Or wouldn’t it be higher to create a extra helpful Quantity area class? One the place you’ll be able to connect performance, foreign money, and so on.?

With jOOQ, you should utilize converters for that (or bindings, if that impacts the way you bind the values to JDBC). Have a flowery FirstName class that fashions numerous kinds of first names?

/**
 * Not simply any unusual first title string!
 */
report LeFirstName(String firstName) {}

Sure, you possibly can insist on nonetheless not utilizing code era and fasten that to your plain SQL templating question:

Record2<LeFirstName, String> writer =
ctx.choose(
        discipline("writer.first_name", VARCHAR.asConvertedDataType(
            LeFirstName.class, LeFirstName::new, LeFirstName::firstName
        )), 
        discipline("writer.last_name", VARCHAR))
   .from("writer")
   .the place(discipline("writer.id", INTEGER).eq(10))
   .fetchOne();

Sure, you possibly can extract that discipline definition in an auxiliary class for higher reuse:

class LeAuthor {
    static Discipline<LeFirstName> firstName = discipline("writer.first_name", 
        VARCHAR.asConvertedDataType(
            LeFirstName.class, LeFirstName::new, LeFirstName::firstName
        ));
    static Discipline<String> lastName = discipline("writer.last_name", VARCHAR));
}

Record2<LeFirstName, String> writer =
ctx.choose(LeAuthor.firstName, LeAuthor.lastName)
   .from("writer")
   .the place(discipline("writer.id", INTEGER).eq(10))
   .fetchOne();

And now, it seems virtually like jOOQ’s code generator was used. Besides it wasn’t. Why not? You possibly can connect your converter to your whole FIRST_NAME columns of all of your tables routinely with only a bit of additional configuration within the code generator, so that you’ll always remember. Particularly, in case your converter implements some must-use logic, akin to e.g. hashing or encrypting, and so on. So, the above instance would simply be:

// After tweaking the codegen configuration, you now
// get your customized kind everytime you reference FIRST_NAME
Record2<LeFirstName, String> writer =
ctx.choose(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
   .from(AUTHOR)
   .the place(AUTHOR.ID.eq(10))
   .fetchOne();

Why write manually, what the machine can write for you? You wouldn’t manually write different derived code, akin to byte code, or meeting both?

6. Sort secure aliasing

Have a fancy question and wish to alias your tables? No drawback with jOOQ. Simply declare the aliases up entrance and also you’re all set:

// Declare the alias:
var a = AUTHOR.as("a");

// Use the alias. Columns are nonetheless there, kind secure
var writer =
ctx.choose(a.FIRST_NAME, a.LAST_NAME)
   .from(a)
   .the place(a.ID.eq(10))
   .fetchOne();

Whereas aliasing additionally works with out code era (every little thing works with out code era, which is simply comfort for extra complicated API calls), you received’t get kind secure entry to your column names from aliases just like the above. And you may nonetheless name your kind hierarchy within the IDE to examine the place the FIRST_NAME column is referenced:

Or get compilation errors on typos, or auto completion, and so on. and so on. What’s to not love?

7. Implicit joins

One in all my favorite jOOQ options is implicit joins. Take a look at this jOOQ question:

ctx.choose(
          BOOK.writer().FIRST_NAME,
          BOOK.writer().LAST_NAME,
          BOOK.TITLE,
          BOOK.language().CD.as("language"))
   .from(BOOK)
   .fetch();

Ranging from a baby desk (e.g. BOOK), jOOQ can implicitly (left) be part of your mum or dad tables AUTHOR and LANGUAGE, as a result of life’s too brief to repetitively kind out all the identical joins and be part of predicates time and again. The above is equal to:

ctx.choose(
          AUTHOR.FIRST_NAME,
          AUTHOR.LAST_NAME,
          BOOK.TITLE,
          LANGUAGE.CD.as("language"))
   .from(BOOK)
   .leftJoin(AUTHOR).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
   .leftJoin(LANGUAGE).on(BOOK.LANGUAGE_ID.eq(LANGUAGE.ID))
   .fetch();

It’s a matter of style and magnificence, sure. You don’t have to make use of implicit joins all over the place. Some joins can’t be expressed as implicit joins, however fairly often, this model drastically simplifies your queries by decreasing noise.

And it’s solely potential with generated code!

Why? As a result of these BOOK.writer() and BOOK.language() strategies are generated, they usually return once more situations of generated tables containing generated columns (with implicit desk aliases), and information sorts, and presumably converters, and what not.

Are you continue to categorically excluding working with the code generator simply because it takes a bit of further time to arrange?

8. CRUD

jOOQ isn’t a full fledged ORM like JPA implementations, however you’ll be able to nonetheless get some comfort to keep away from having to spell out CRUD queries on a regular basis. jOOQ calls these UpdatableRecord.

Whenever you fetch the UpdatableRecord like this:

BookRecord guide =
ctx.selectFrom(BOOK)
   .the place(BOOK.ID.eq(17))
   .fetchOne();

Then, you’ll be able to change its values (e.g. in a wealthy shopper), and retailer the report once more to generate an UPDATE assertion:

guide.setTitle("New title");
guide.retailer();

There are just a few choose in further options, like optimistic locking, and also you guessed it:

All accessible solely with generated code.

9. Saved procedures

You probably have these (and I extremely advocate you do if just for efficiency causes), then utilizing code era is much more of a no brainer.

You probably have, in PostgreSQL:

CREATE FUNCTION p_count_authors_by_name (
  author_name VARCHAR, 
  end result OUT INTEGER
)
AS $$
DECLARE
  v_result INT;
BEGIN
  SELECT COUNT(*)
  INTO v_result
  FROM writer
  WHERE first_name LIKE author_name
  OR last_name LIKE author_name;

  end result := v_result;
END;
$$ LANGUAGE plpgsql;

Now jOOQ’s code generator generates the next routine name for you:

public static Integer pCountAuthorsByName(
      Configuration configuration
    , String authorName
) {
    // Do you actually care?
}

Yep, that’s it. Do you actually care what occurs behind the scenes? This binding logic is about as thrilling as sorting your socks. It’s the identical story each time you’ve got some RPC expertise. We didn’t care in regards to the implementation in CORBA, with SOAP, with RMI, with distant EJBs, and we don’t care how you can bind to a saved process.

It’s just a few distant callable that accepts a String and returns an Integer. Code era is your pal right here, once more. And sure, as all the time, in the event you suppose this perform is lacking a second parameter, you’ll simply add it and your shopper code stops compiling providing you an opportunity to refactor!

And the perfect factor is: This helps your whole bizarre edge circumstances together with:

• SQL sorts (enums, arrays, object sorts, UDTs)
• PL/SQL sorts (report sorts, desk sorts, associative arrays)
• Ref cursor sorts
• Desk valued capabilities

10. Multitenancy

A cool function in jOOQ is that jOOQ helps schema degree multi tenancy out of the field for you the place you’ll be able to dynamically rename your catalogs, schemas, and desk names at runtime relying on no matter (e.g. consumer tenant).

This function known as schema mapping, and it clearly… doesn’t work in the event you use plain SQL templates with out code era. (as a result of a plain SQL template can comprise any sort of SQL string, that’s what they’re for).

Not simply that. With generated code, your whole objects are totally certified by default, and you’ll omit qualification whenever you like. With your individual templates, you’ll be able to’t simply change that. Wish to port your schema from A to B? Nicely, you’ll hand-rewrite every little thing, good luck. With jOOQ’s generated code, it’s simply flags, configurations, and so on.

11. Embedded sorts

Keep in mind converters? Much more refined are embedded sorts, i.e. jOOQ’s manner of wrapping a number of database columns right into a single shopper facet worth, pretending your database helps UDTs (akin to Oracle, PostgreSQL).

As a result of whenever you work with currencies, what you actually wish to do is mix an AMOUNT column with a CURRENCY column, as a result of in spite of everything, USD 1.00 isn’t actually the identical factor as EUR 1.00, nor can they be straight in contrast or mixed in arithmetics.

Embedded sorts are at the moment solely accessible utilizing the code generator, which produces all of the meta information required for jOOQ’s runtime to map / unmap your flat end result units.

That is particularly highly effective whenever you use:

• Embedded keys (the one factor you ever examine an BOOK.AUTHOR_ID with is AUTHOR.ID, or possibly one other international key that factors to the identical main key, so why not use a kind to implement kind security?)
• Embedded domains (you already declared a semantic kind in your database, so that you wish to reuse that title additionally in shopper code)

12. Information change administration

One other factor that individuals typically don’t wish to setup appropriately and cleanly up entrance as a result of it does take some further time is information change administration (e.g. utilizing Flyway or Liquibase). And the regrets are comparable as soon as the mission grows.

Utilizing jOOQ’s code generator variety forces you to additionally take into consideration information change administration early on, and that may be a good factor!

Your information mannequin modifications over time, and so does your shopper code engaged on that mannequin. jOOQ’s code era course of needs to be embedded in your CI/CD pipeline:

• You routinely apply a change in your mannequin
• You routinely re-generate your code
• You routinely run your integration assessments
• You routinely deploy the examined end result as a unit

Right here’s how you can do all of that with testcontainers and jOOQ, see an instance in motion right here.

Let me stress this as soon as extra.

In case you don’t cleanly arrange your CI/CD pipeline together with your database early on in your mission, you’ll do it in a while, and it’ll be a lot more durable. However in the event you do it, then including jOOQ’s code era to the sport is a no brainer, and also you get every little thing above without cost!

Conclusion

There are a lot of different minor issues that you just get whenever you use jOOQ’s code generator. It’s such a robust software to get probably the most out of each jOOQ and your database. It actively encourages you to make use of your whole RDBMS options, together with:

• Complicated fashions (as a result of becoming a member of is far easier after you have all of the meta information, and even instruments like implicit joins)
• Views (these are generated too)
• Information sorts (generated too)
• Procedures (identical)
• And so forth.

Utilizing code era in jOOQ is a transparent path in the direction of turning into an influence consumer of your individual database, which you (hopefully) design with lots of love and craftsmanship.

Exception

I promised an exception. You possibly can’t use the code generator when your schema is dynamic (i.e. unknown at compile time). However solely few programs are designed like that.