How to make a select max statement in jooq

user_11629964 0
Viewed 2223

I´d like to translate the following SQL-statement to jooq syntax:

select max(table.column) from table group by table.anothercolumn

I've tried:

  • dsl.select(table.colum, max()) and
  • DSL.select(table.colum, max()) and
  • dsl.select(table.colum.max()) and
  • DSL.select(table.colum.max())

but accordingly to Eclipse, those syntaxes are all incorrect...

Any help would be greatly appreciated.

jooq mysql java
1 Answers

The most common mistakes when starting out with jOOQ are:

No code generation

Most of the examples from the jOOQ manual and from other Stack Overflow questions assume that you're using jOOQ's code generator to reverse engineer your schema. This means that you will have generated objects like table (more probably TABLE) in generated classes.

You will need to static import all those objects, e.g. using

import static com.example.generated.Tables.*;

Now, you can reference all your tables frmo that static import, and their columns from the tables respectively

No static import of DSL.*

Another prerequisite for most effective jOOQ usage is to static import all the methods from DSL:

import static org.jooq.impl.DSL.*;

This will allow you to use an unqualified max(Field) aggregate function.

Both of these prerequisites are documented in the section of the manual about how to read the manual. They are both optional, you can work with jOOQ without those static imports and always fully qualify your expressions.

Working example

Once you've set up code generation and static imports, the following query would work:

DSLContext dsl = DSL.using(connection, dialect);

Result<?> result =
dsl.select(max(TABLE.COLUMN))
   .from(TABLE)
   .groupBy(TABLE.ANOTHERCOLUMN)
   .fetch();

select max(table.column) from table group by table.anothercolumn

user_5217990
Related