A Temporary 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 varieties within the jOOQ API could be overwhelming. The SQL language doesn’t have many such “seen” varieties, though if you concentrate on SQL the way in which jOOQ does, then they’re there simply the identical, however hidden from customers through an English type syntax.

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

Configuration varieties

The Configuration is the one most essential configuration sort, which comprises references to all different kinds of configuration, Settings, customized SPI implementations, JDBC or R2DBC Connection, and many others. SPIs embrace:

And plenty of extra, which you’ll see from the Configuration Javadoc

It’s made obtainable from each Scope sort within the API, see beneath for particulars

Scopes

The Scope varieties are numerous varieties which can be 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. Among the most essential Scope varieties embrace:

For different varieties, discuss with the Scope Javadoc.

Settings

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

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

DSL varieties

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

The static DSL

The static DSL comprises entry factors to each sort of QueryPart development DSLs, together with:

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

The “context” DSL

The “context” DSL, represented by the DSLContext sort, solely affords establishing QueryPart varieties 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 sort could be executed instantly through the use of Question.execute() or ResultQuery.fetch(), or many different execution strategies, together with asynchronous or reactive ones.

Step varieties

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

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

QueryPart varieties

QueryPart is the frequent base sort of the complete jOOQ expression tree, or mannequin API. Each sort that you simply 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 sort 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 embrace:

Desk

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 varieties, together with:

There are lots of extra doable desk expressions in jOOQ, all implementing the Desk sort. An instance of utilizing Desk expressions is:

Desk> joined = CUSTOMER
    .be a part of(ADDRESS)
    .on(ADDRESS.CUSTOMER_ID.eq(CUSTOMER.CUSTOMER_ID));

Whereas most jOOQ statements received’t work with such native variables, it’s all the time fascinating to do not forget that with jOOQ, each question is a dynamic SQL question, and each SQL fragment is a completely self contained expression tree in Java, which could be assigned to any native variable or returned from a technique, and many others.

Discipline

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

And way more.

Situation

A Situation is only a Discipline with some further API particular to Situation constructing, together with the opportunity of calling Situation::and, Situation::or, and others. Numerous clauses settle for Situation explicitly, together with:

And extra.

Row

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

Such tuples are helpful to create a structural sort that teams Discipline expressions into teams of re-usable objects. Some dialects additionally help nominal variants of this, known as UDT (Person Outlined Kind), and jOOQ can emulate UDTs through embeddable varieties.

Choose

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

ResultQuery

A ResultQuery is a Question that may produce Document values in numerous assortment types (e.g. Stream, End result, Writer, CompletionStage, and many others.). It may be created from numerous Question varieties by including the RETURNING clause

Question

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
• Probably, a ResultSet is fetched.

So as to execute a Question, it should be hooked up to a Configuration, which is finished most simply by creating the Question from a DSLContext.

Assertion

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) varieties are an experimental set of varieties publicly declaring the inner mannequin API, which may be very helpful for tree traversal and SQL transformation

End result varieties

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

End result

The End result sort is a Checklist<Document> with numerous mapping comfort API. It fashions an eagerly fetched JDBC ResultSet, which comprises all the outcomes from the database and no extra reference to the ResultSet itself. That is helpful when the end result set is reasonably sized.

Cursor

The Cursor sort is an Iterable<Document> with related mapping comfort API because the End result sort, however it comprises an open JDBC ResultSet, permitting for fetching information from the server in a lazy manner. That is helpful when the end result set is large.

Document

A Document is a base sort for a database report. It permits area primarily based entry of particular person attributes in addition to mapping comfort into customized information varieties. It specialises as:

SPI Varieties

jOOQ affords many SPI (Service Supplier Interface) varieties to permit for enhancing jOOQ behaviour. Solely a few of them are listed right here.

Converter

A Converter is a straightforward pair of capabilities changing between a built-in JDBC sort T (e.g. String, Integer, Date) and any arbitrary user-defined sort U. The from (as in “from the database”) Operate converts database information at any time when it’s fetched from the JDBC ResultSet. The to (as in “to the database”) Operate converts the consumer outlined sort again to the database sort at any time when it’s sure to a JDBC PreparedStatement.