How can I create a SELECT EXISTS (subquery) with jOOQ?

Question

I want to build and execute a query like this with jOOQ.

SELECT EXISTS( subquery )

For exemple:

SELECT EXISTS(SELECT 1 FROM icona_etiqueta WHERE pvp IS NULL AND unitat_venda = 'GRAMS')

How can I do it? Can it be done?

Answer 1

Found it. I was looking for a selectExists method and got confused by the DSL.exists() predicate constructor.

There is a much more convenient fetchExists(subquery).

My specific example is resolved like this:

create.fetchExists(
        create.selectOne()
              .from(ICONA_ETIQUETA)
              .where(ICONA_ETIQUETA.PVP.isNull(),
                     ICONA_ETIQUETA.UNITAT_VENDA.eq('GRAMS'))
    );

Which directly returns a boolean.

Answer 2

Your own solution is the most convenient approach to what you want to be doing. A more general approach is to use:

create.select(field(exists(...)))

Where you wrap a Condition (created by DSL.exists(Select)) in a Field using DSL.field(Condition).

As of jOOQ 3.9, Field<Boolean> and Condition are not the same types. This may change in the future with #3867.