Wednesday, April 24, 2024
HomeProgrammingA Transient Overview over the Most Frequent jOOQ Varieties – Java, SQL...

A Transient Overview over the Most Frequent jOOQ Varieties – Java, SQL and jOOQ.

For brand new customers working with jOOQ for the primary time, the variety of sorts within the jOOQ API could be overwhelming. The SQL language doesn’t have many such “seen” sorts, though if you consider SQL the best way jOOQ does, then they’re there simply the identical, however hidden from customers by way of an English model syntax.

This overview will listing a very powerful jOOQ sorts in a cheat sheet kind.

Configuration sorts

The Configuration is the only most essential configuration kind, which incorporates references to all different varieties of configuration, Settings, customized SPI implementations, JDBC or R2DBC Connection, and so forth. SPIs embody:

And lots of extra, which you’ll be able to see from the Configuration Javadoc

It’s made out there from each Scope kind within the API, see beneath for particulars


The Scope sorts are numerous sorts which are created “within the scope” of a Configuration, and as such can present entry to all the Configuration‘s contained objects and SPIs. This design permits for very versatile, programmatic dependency injection all through the internals of jOOQ. A few of the most essential Scope sorts embody:

For different sorts, confer with the Scope Javadoc.


Settings are largely scalar flags that specify detailed behaviour in jOOQ. Some choose examples embody:

As of jOOQ 3.17, there are over 160 such settings, so we are able to’t listing all of them right here. For extra particulars, confer with the Settings Javadoc.

DSL sorts

The DSL API is a very powerful API to work with jOOQ. It is available in 2 flavours.

The static DSL

The static DSL incorporates entry factors to each kind of QueryPart development DSLs, together with:

… and much more. All of those sorts are constructed statically, and as such, they don’t have any Configuration hooked up.

The “context” DSL

The “context” DSL, represented by the DSLContext kind, solely affords developing QueryPart sorts that revenue from being created “within the context” of a Configuration. That is primarily simply together with:

A Question that has been constructed from the DSLContext kind could be executed instantly by utilizing Question.execute() or ResultQuery.fetch(), or many different execution strategies, together with asynchronous or reactive ones.

Step sorts

All through the DSL API, you will note so-called “Step” sorts, i.e. sorts with a “Step” suffix, equivalent to e.g. SelectFromStep, which is the “Step” that provides entry to the Choose DSL’s FROM clause.

You need to by no means reference these sorts instantly, nor see them in your personal code. They’re intermediate DSL artifacts

QueryPart sorts

QueryPart is the frequent base kind of your complete jOOQ expression tree, or mannequin API. Each kind that you just assemble with the DSL API will lengthen QueryPart, for instance:

QueryPart p1 = TABLE;
QueryPart p2 = TABLE.COLUMN;
QueryPart p3 = TABLE.COLUMN.eq(1);

The above expressions produce a extra particular kind than QueryPart, which we’ll clarify after, however all of them lengthen QueryPart.

A QueryPart is a sort that may be rendered within the context of a Configuration utilizing DSLContext::render

String sql = ctx.render(TABLE.COLUMN.eq(1));

An important QueryPart subtypes embody:


A Desk can be utilized in a FROM clause of a SELECT assertion, or as a goal of a DML assertion, and extra. There are numerous totally different desk sorts, together with:

There are various extra potential desk expressions in jOOQ, all implementing the Desk kind. An instance of utilizing Desk expressions is:

Desk<?> joined = CUSTOMER
    .be a part of(ADDRESS)

Whereas most jOOQ statements received’t work with such native variables, it’s all the time attention-grabbing to keep in mind that with jOOQ, each question is a dynamic SQL question, and each SQL fragment is a totally self contained expression tree in Java, which could be assigned to any native variable or returned from a technique, and so forth.


A Subject is a column expression, which can be utilized in a lot of locations all through the jOOQ API, all over the place the place column expressions can be utilized, together with:

And far more.


A Situation is only a Subject<Boolean> with some extra API particular to Situation constructing, together with the opportunity of calling Situation::and, Situation::or, and others. Varied clauses settle for Situation explicitly, together with:

And extra.


A Row or row worth expression is used to mannequin a tuple of values each for:

Such tuples are helpful to create a structural kind that teams Subject expressions into teams of re-usable objects. Some dialects additionally assist nominal variants of this, known as UDT (Person Outlined Kind), and jOOQ can emulate UDTs by way of embeddable sorts.


A Choose is a particular kind of ResultQuery, which can be utilized as:


A ResultQuery is a Question that may produce Report values in numerous assortment types (e.g. Stream, Consequence, Writer, CompletionStage, and so forth.). It may be created from numerous Question sorts by including the RETURNING clause


A Question is a Assertion that may be executed, which means:

  • A SQL string is generated
  • A PreparedStatement is ready
  • Bind values are sure
  • The PreparedStatement is executed
  • Presumably, a ResultSet is fetched.

With the intention to execute a Question, it should be hooked up to a Configuration, which is completed most simply by creating the Question from a DSLContext.


A Assertion (not the JDBC Assertion!) is a QueryPart that represents a procedural assertion in:

All Question implementations can be utilized as Assertion in such a procedural context.

QOM Varieties

The QOM (Question Object Mannequin) sorts are an experimental set of sorts publicly declaring the interior mannequin API, which may be very helpful for tree traversal and SQL transformation

Consequence sorts

When executing a ResultQuery, there are various kinds of Consequence supported by jOOQ, Consequence being the default:


The Consequence kind is a Checklist<Report> with quite a lot of mapping comfort API. It fashions an eagerly fetched JDBC ResultSet, which incorporates all the outcomes from the database and no extra reference to the ResultSet itself. That is helpful when the outcome set is reasonably sized.


The Cursor kind is an Iterable<Report> with comparable mapping comfort API because the Consequence kind, nevertheless it incorporates an open JDBC ResultSet, permitting for fetching information from the server in a lazy method. That is helpful when the outcome set is big.


A Report is a base kind for a database file. It permits discipline primarily based entry of particular person attributes in addition to mapping comfort into customized information sorts. It specialises as:

Bookmark this

Discovered this listing helpful? Bookmark it, as we’ll add extra sorts sooner or later in case new essential ideas come up.



Please enter your comment!
Please enter your name here

Most Popular

Recent Comments