JOOQ - Join on condition or not exit

Question

I have the following query:

Map<BusinessRecord, List<BusinessAddressRecord>> resultMap = dslContext
        .select()
        .from(BUSINESS.leftJoin(BUSINESS_ADDRESS).on(BUSINESS.ID.eq(BUSINESS_ADDRESS.BUSINESS_ID)))
        .where(BUSINESS.IDENTIFIER.equal(identifier))
        .and(BUSINESS_ADDRESS.DEACTIVATED_AT.isNull())
        .fetchGroups(
                b -> b.into(BUSINESS),
                a -> a.into(BUSINESS_ADDRESS)
        );

Unfortunately this returns null if a business has no address listed, I managed to resolve this by doing:

  Map<BusinessRecord, List<BusinessAddressRecord>> resultMap = dslContext
          .select()
          .from(BUSINESS.leftJoin(BUSINESS_ADDRESS).on(
                  BUSINESS.ID.eq(BUSINESS_ADDRESS.BUSINESS_ID).and(BUSINESS_ADDRESS.DEACTIVATED_AT.equals(null))
          ))
          .where(BUSINESS.IDENTIFIER.equal(identifier))
          .fetchGroups(
                  b -> b.into(BUSINESS),
                  a -> a.into(BUSINESS_ADDRESS)
          );

But this is saying the 'and' in

and(BUSINESS_ADDRESS.DEACTIVATED_AT.equals(null))

is deprecated, what is the alternative?

Answer 1

About the deprecated API

But this is saying the 'and' in [...] is deprecated

See the deprecation notice on that method? It says:

Deprecated. - 3.8.0 - [#4763] - Use and(Condition) (typically with DSL.trueCondition(), DSL.falseCondition(), or DSL.noCondition() as the parameter) or and(Field) instead. Due to ambiguity between calling this method using Field.equals(Object) argument, vs. calling the other method via a Field.equal(Object) argument, this method will be removed in the future.

You're using Object.equals(), not jOOQ's Field.equal() (or Field.eq()). There's an and(Boolean) overload, which has been causing the kind of trouble you ran into many times, which is why it's deprecated, and will be removed in jOOQ 3.15.0 with #11242. After that, your code using equals() instead of equal() simply won't compile anymore.

Nulls

In SQL (and by consequence in jOOQ), you cannot compare values with NULL using ordinary comparison operators, because the result of such a comparison is always NULL (not TRUE or FALSE). SQL implements three valued logic.

Replace your predicate by:

and(BUSINESS_ADDRESS.DEACTIVATERD_AT.isNull())