Java JOOQ multiple tables query

Question

I have a problem.

I have the following query:

SELECT
    Agents.Owner,
    Orders.*
FROM
    Orders
INNER JOIN Agents ON Agents.id = Orders.agentid
WHERE
    Agents.botstate = 'Active' AND Orders.state = 'Active' AND(
        Orders.status = 'Failed' OR Orders.status = 'Processing' AND Orders.DateTimeInProgressMicro < DATE_SUB(NOW(), INTERVAL 10 SECOND))
    ORDER BY
        Orders.agentid

But now I need to convert this to JOOQ language. This is what I came up with:

create.select()
        .from(DSL.table("Orders"))
        .join(DSL.table("Agents"))
        .on(DSL.table("Agents").field("Id").eq(DSL.table("Orders").field("AgentId")))
        .where(DSL.table("Agents").field("botstate").eq("Active")
        .and(DSL.table("Orders").field("state").eq("Active"))
        .and((DSL.table("Orders").field("status").eq("Failed"))
        .or(DSL.table("Orders").field("status").eq("Processing")))).fetch().sortAsc(DSL.table("Orders").field("AgentId"));

Now the first problem is that it doesn't like all the .eq() statements, because it gives me the error: Cannot resolve method: eq(Java.lang.String). And my second problem is that I don't know how to write this statement in JOOQ: Orders.DateTimeInProgressMicro < DATE_SUB(NOW(), INTERVAL 10 SECOND).

The first problem is caused by the fact that I can't just use:

.on(Agents.Id).eq(Orders.AgentId)

But instead I need to enter for every table:

DSL.table("table_name")

And for every column:

DSL.field("column_name")

Without that it doesn't recognize my tables and columns

How can I write the SQL in the JOOQ version correctly or an alternative solution is that I can use normal SQL statements?

I never got that to work, so I thought that using this code was a good solution.... — A. Vreeswijk
– A. Vreeswijk, Commented Apr 23, 2021 at 16:58
I'll be happy to answer additional questions on how to get the code generator to work, here on Stack Overflow. I really recommend you try to get that to work. Your jOOQ experience will be much better that way. See also my answer. — Lukas Eder
– Lukas Eder, Commented Apr 25, 2021 at 9:09

Lukas Eder · Accepted Answer · 2021-04-25 08:42:06Z

Why doesn't your code work?

Table.field(String) does not construct a path expression of the form table.field. It tries to dereference a known field from Table. If Table doesn't have any known fields (e.g. in the case of using DSL.table(String), then there are no fields to dereference.

Correct plain SQL API usage

There are two types of API that allow for working with dynamic SQL fragments:

The plain SQL API to construct plain SQL fragments and templates
The Name API to construct identifiers and jOOQ types from identifiers

Most people use these only when generated code isn't possible (see below), or jOOQ is missing some support for vendor-specific functionality (e.g. some built-in function). Here's how to write your query with each:

Plain SQL API

The advantage of this API is that you can use arbitrary SQL fragments including vendor specific function calls that are unknown to jOOQ. There's a certain risk of running into syntax errors, SQL injection (!), and simple data type problems, because jOOQ won't know the data types unless you tell jOOQ explicitly

// as always, this static import is implied:
import static org.jooq.impl.DSL.*;

And then:

create.select()
      .from("orders") // or table("orders")
      .join("agents") // or table("agents")
      .on(field("agents.id").eq(field("orders.id")))
      .where(field("agents.botstate").eq("Active"))
      .and(field("orders.state").eq("Active"))
      .and(field("orders.status").in("Failed", "Processing"))
      .orderBy(field("orders.agentid"))
      .fetch();

Sometimes it is useful to tell jOOQ about data types explicitly, e.g. when using these expressions in SELECT, or when creating bind variables:

// Use the default SQLDataType for a Java class
field("agents.id", Integer.class);

// Use an explicit SQLDataType
field("agents.id", SQLDataType.INTEGER);

Name API

This API allows for constructing identifiers (by default quoted, but you can configure that, or use unquotedName()). If the identifiers are quoted, the SQL injection risk is avoided, but then in most dialects, you need to get case sensitivity right.

create.select()
      .from(table(name("orders")))
      .join(table(name("agents")))
      .on(field(name("agents", "id")).eq(field(name("orders", "id"))))
      .where(field(name("agents", "botstate")).eq("Active"))
      .and(field(name("orders", "state")).eq("Active"))
      .and(field(name("orders", "status")).in("Failed", "Processing"))
      .orderBy(field(name("orders", "agentid")))
      .fetch();

Using the code generator

Some use cases prevent using jOOQ's code generator, e.g. when working with dynamic schemas that are only known at runtime. In all other cases, it is very strongly recommended to use the code generator. Not only will building your SQL statements with jOOQ be much easier in general, you will also not run into problems like the one you're presenting here.

Your query would read:

create.select()
      .from(ORDERS)
      .join(AGENTS)
      .on(AGENTS.ID.eq(ORDERS.ID))
      .where(AGENTS.BOTSTATE.eq("Active"))
      .and(ORDERS.STATE.eq("Active"))
      .and(ORDERS.STATUS.in("Failed", "Processing"))
      .orderBy(ORDERS.AGENTID)
      .fetch();

Benefits:

All tables and columns are type checked by your Java compiler
You can use IDE auto completion on your schema objects
You never run into SQL injection problems or syntax errors
Your code stops compiling as soon as you rename a column, or change a data type, etc.
When fetching your data, you already know the data type as well
Your bind variables are bound using the correct type without you having to specify it explicitly

Remember that both the plain SQL API and the identifier API were built for cases where the schema is not known at compile time, or schema elements need to be accessed dynamically for any other reason. They are low level APIs, to be avoided when code generation is an option.

Collectives™ on Stack Overflow

Java JOOQ multiple tables query

1 Answer 1

Why doesn't your code work?

Correct plain SQL API usage

Using the code generator

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Why doesn't your code work?

Correct plain SQL API usage

Using the code generator

Comments

Your Answer

Sign up or log in

Post as a guest

Related