I’m answering many jOOQ questions on Stack Overflow, and plenty of instances. The issue has the identical trigger: Folks not utilizing jOOQ’s code generator. The principle cause folks 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 at all times repay.

On this article, I wish to briefly summarise what the code generator is, why you need to use it, and what’s the most important cause to not use it (trace: you most likely don’t have that case).

What’s the jOOQ code generator?

jOOQ is an inner DSL that pretends that you may write sort protected, embedded, dynamic SQL instantly in Java. Similar 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 may be very simple, as a result of jOOQ makes SQL “composable”.

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

var creator =
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 embrace quite a lot of data:

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

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

What’s the worth to pay for code technology?

As I perceive, plenty of customers (particularly new ones) are desperate to check out jOOQ instantly and don’t wish to hassle organising 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 in your XSD information (you may not even have an XSD). However with jOOQ, it’s completely different.

Sure, the guide may 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 in your first makes an attempt with jOOQ. Why not simply use jbang. With just some traces of CLI, you can begin straight away.

To check out staple items, it’s also possible to use the DDLDatabase, which generates code from SQL information instantly, with out a stay connection to an precise database, although that isn’t actually a giant difficulty 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 would repay massively (see beneath), will forestall tons of issues (see beneath), and it simply requires just a few extra minutes of organising your venture. Heck, you may even begin taking part in round with the bug reporting template, which incorporates code technology configuration for Java, Scala, and Kotlin!

Let’s begin with the apparent:

1. Compile time sort security

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

However with out code technology, you received’t get sort security in your schema objects. It is a massive 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 operate, each package deal, each schema, each catalog, each area, each enum sort, each object sort 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 have got it proper there in your code. As a primary step, you should utilize this for auto completion in your favorite IDE!

Not simply that. It’s also possible to by no means make a mistake along 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 adjustments, or is deleted, your Java code stops compiling.

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

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

CREATE TABLE creator (
  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 info sort once more in Java? In actual fact, 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 actual fact, it’s an outright unhealthy concept. There ought to solely be one supply of fact in 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 in your frontend, no matter. However everytime you question your database, there’s completely no cause to not use the precise meta mannequin which you’ve already declared in your database.

I believe that’s type of apparent? Why waive all that for a little bit (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’ll be able to at all times rapidly navigate to the column you’re enthusiastic about and skim its SQL feedback within the Javadoc. Perhaps you could have this specified? (Completely underused SQL function!)

COMMENT ON COLUMN creator.first_name IS 'The creator''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 test the decision hierarchy or no matter IDE search software you’re enthusiastic about:

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

3. Runtime meta mannequin: Information sorts

In actual fact, you received’t simply revenue from this kind security at compile time, but additionally at runtime. Belief me. There are such a lot of edge circumstances in some JDBC drivers and/or SQL dialects the place the info sort 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 creator =
ctx.choose(subject("creator.first_name"), subject("creator.last_name"))
   .from("creator")
   .the place(subject("creator.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 a substitute of utilizing code technology, too, and infrequently, that’s simply the suitable factor to do.

When bind variables have inadequate sort contexts

However there are a lot of circumstances the place you need to give the SQL engine extra context concerning 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, you need to CAST.

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

jOOQ at all times provides these casts for you, if we discover out by way of our in depth integraiton checks that in any given state of affairs, a dialect may require it. However jOOQ can solely try this in case you present it with a kind, and infrequently you do, implicitly, however generally, you don’t, after which you need to debug a question that used to work earlier than. That may be very sudden.

When a Area<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 could have 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, Area<T> worth) {}

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

Whereas this works completely wonderful:

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

This name doesn’t compile:

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

Plainly Area<Object> shouldn’t be extra particular than Object on this case, which appears bizarre, however that’s how the JLS designed generic overload decision. In actual fact, 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 technology:

Record2<Object, Object> creator =
ctx.choose(subject("creator.first_name"), subject("creator.last_name"))
   .from("creator")
   .the place(subject("creator.id").eq(10)) // Area<Object>
   .fetchOne();

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

The workaround is to at all times connect sort data to your columns:

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

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

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

4. Emulations

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

You wouldn’t consider what number of instances 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, moderately than simply generate code. With generated code, the next question works in plenty of RDBMS out of the field. Out. Of. The. Field:

AuthorRecord creator =
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 at all times discover a method for you on this case, however provided that it is aware of which column the identification is.

5. Converters

Primary 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 stage sorts, in every single place all through your utility? Do you wish to use BigDecimal in your financial quantities in every single place? Or would it not 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 elaborate FirstName class that fashions varied forms of first names?

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

Sure, you may insist on nonetheless not utilizing code technology and fix that to your plain SQL templating question:

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

Sure, you may extract that subject definition in an auxiliary class for higher reuse:

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

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

And now, it seems virtually like jOOQ’s code generator was used. Besides it wasn’t. Why not? You’ll be able to connect your converter to all your 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, equivalent 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 sort everytime you reference FIRST_NAME
Record2<LeFirstName, String> creator =
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, equivalent to byte code, or meeting both?

6. Kind protected aliasing

Have a posh question and wish to alias your tables? No downside 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, sort protected
var creator =
ctx.choose(a.FIRST_NAME, a.LAST_NAME)
   .from(a)
   .the place(a.ID.eq(10))
   .fetchOne();

Whereas aliasing additionally works with out code technology (the whole lot works with out code technology, which is simply comfort for extra complicated API calls), you received’t get sort protected entry to your column names from aliases just like the above. And you may nonetheless name your sort hierarchy within the IDE to test 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

Certainly one of my favorite jOOQ options is implicit joins. Have a look at this jOOQ question:

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

Ranging from a toddler desk (e.g. BOOK), jOOQ can implicitly (left) be part of your dad or mum tables AUTHOR and LANGUAGE, as a result of life’s too brief to repetitively sort out all the identical joins and be part of predicates repeatedly. 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 elegance, sure. You don’t have to make use of implicit joins in every single place. Some joins can’t be expressed as implicit joins, however fairly often, this model significantly simplifies your queries by decreasing noise.

And it’s solely attainable with generated code!

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

Are you continue to categorically excluding working with the code generator simply because it takes a little bit 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.

While you fetch the UpdatableRecord like this:

BookRecord e book =
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 document once more to generate an UPDATE assertion:

e book.setTitle("New title");
e book.retailer();

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

All obtainable solely with generated code.

9. Saved procedures

In case you have these (and I extremely suggest you do if just for efficiency causes), then utilizing code technology is much more of a no brainer.

In case you 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 creator
  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 could have some RPC expertise. We didn’t care concerning the implementation in CORBA, with SOAP, with RMI, with distant EJBs, and we don’t care learn how to bind to a saved process.

It’s just a few distant callable that accepts a String and returns an Integer. Code technology is your good friend right here, once more. And sure, as at all times, in case you assume this operate is lacking a second parameter, you’ll simply add it and your shopper code stops compiling providing you an opportunity to refactor!

And the most effective factor is: This helps all your bizarre edge circumstances together with:

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

10. Multitenancy

A cool function in jOOQ is that jOOQ helps schema stage 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 case you use plain SQL templates with out code technology. (as a result of a plain SQL template can include any type of SQL string, that’s what they’re for).

Not simply that. With generated code, all your objects are absolutely certified by default, and you’ll omit qualification if you like. With your personal templates, you’ll be able to’t simply change that. Wish to port your schema from A to B? Effectively, you’ll hand-rewrite the whole lot, good luck. With jOOQ’s generated code, it’s simply flags, configurations, and so on.

11. Embedded sorts

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

As a result of if 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 instantly in contrast or mixed in arithmetics.

Embedded sorts are at present solely obtainable 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 if 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 sort security?)
• Embedded domains (you already declared a semantic sort in your database, so that you wish to reuse that title additionally in shopper code)

12. Information change administration

One other factor that folks typically don’t wish to setup accurately 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 venture grows.

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

Your information mannequin adjustments over time, and so does your shopper code engaged on that mannequin. jOOQ’s code technology course of must 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 checks
• You routinely deploy the examined end result as a unit

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

Let me stress this as soon as extra.

Should you don’t cleanly arrange your CI/CD pipeline together with your database early on in your venture, you’ll do it afterward, and it’ll be a lot more durable. However in case you do it, then including jOOQ’s code technology to the sport is a no brainer, and also you get the whole lot above free of charge!

Conclusion

There are a lot of different minor issues that you simply get if 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 all your RDBMS options, together with:

• Complicated fashions (as a result of becoming a member of is way 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 (similar)
• And so on.

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

Exception

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