jooq throws NPE when fetchOne is used

Question

I have a simple query on a table which queries with the primary key.

            dslContext.select(XXX.NAME)
                .from(XXX)
                .where(
                    XXX.ID.eq(id)
                        .and(XXX.LEVEL.eq(2))
                        .and(XXX.NAME.isNotNull())
                )
                .fetchOne().into(String.class);

In my case for a particular id, the query results in a empty set. But jooq seems to throw a NPE. When I further investigated, fetchOne() calls CursorImpl.fetchOne(). This checks the size of the result and if it is not 1, it returns null. I have a chained into(String.class) which gets called on this null value and hence resulting in NPE.

I don't want to call fetch() and iterate over the results/get the first element of the list.

Is there an alternative way of writing the query such that it will throw a org.jooq.exception.NoDataFoundException if there is no data?

Answer 1

Why a NullPointerException is being thrown

Technically, jOOQ doesn't throw a NullPointerException. Your calling into(Class) on a potentially null record does, as documented also in the Javadoc of ResultQuery.fetchOne()

Returns:
The resulting record or null if the query returns no records.

Throwing a NoDataFoundException.

You could use fetchOptional() and then use orElseThrow():

String string =
dslContext.select(XXX.NAME)
    .from(XXX)
    .where(
        XXX.ID.eq(id)
            .and(XXX.LEVEL.eq(2))
            .and(XXX.NAME.isNotNull())
    )
    .fetchOptional()
    .orElseThrow(() -> new NoDataFoundException("..."))
    .into(String.class);

Note, there's a pending feature request for such a built-in fetch method: https://github.com/jOOQ/jOOQ/issues/5411

In jOOQ 3.10, you will be able to write:

String string =
dslContext.select(XXX.NAME)
    .from(XXX)
    .where(
        XXX.ID.eq(id)
            .and(XXX.LEVEL.eq(2))
            .and(XXX.NAME.isNotNull())
    )
    .fetchSingle() // Might throw a NoDataFoundException but never returns null
    .into(String.class);