Convert Hibernate @Formula (case ) to JOOQ field

I am rewriting entire DB access layer from Hibernate to JOOQ and I face following issue.

One of JPA models is annotated with @Formula annotation as follows:

@Formula("(case"
    + "  when field1 >= 0.5 then 2"
    + "  when field1 >= 0.2 then 1"
    + "  else 0 end)")
private int field2;

I saw the following question : Convert Hibernate @Formula to JOOQ field but it did not really help

How can above query be translated to JOOQ DSL?

Answer

The jOOQ manual’s section about the CASE expression will help you:

http://www.jooq.org/doc/latest/manual/sql-building/column-expressions/case-expressions

Essentially, the expression that you should build will look like this:

Field<Integer> field2 = 
DSL.when(TABLE.FIELD1.ge(0.5), 2)
   .when(TABLE.FIELD1.ge(0.2), 1)
   .otherwise(0);

Of course, jOOQ is not an annotation-based API, so you cannot use an annotation to automatically generate that value on your target entities. Just like in SQL, you would be using the above field2 expression in all the relevant queries, e.g.

DSL.using(configuration)
   .select(TABLE.ID, TABLE.FIELD1, field2)
   .from(TABLE)
   .fetch();

Note that starting from jOOQ 3.17, you can also attach such expressions directly to your generated code to have a more @Formula style experience in jOOQ. The feature is called virtual client side computed columns.