Difference between fetch().stream().findFirst() vs fetchAnyInto

Question

Can someone let me know what is the difference between the below queries? If both are same which one should be preferred over the other?

Query 1

dslContext
        .selectFrom(AIR_TRANSACTION)
        .where(
            AIR_TRANSACTION
                .TICKET_NUMBER
                .eq(ticketNumber)
                .and(AIR_TRANSACTION.SPOTNANA_PNR_ID.eq(pnrId)))
        .orderBy(AIR_TRANSACTION.TRANSACTION_DATE.desc())
        .fetch()
        .stream()
        .findFirst()
        .map(r -> r.into(AirTransaction.class));

Query 2

Optional.ofNullable(
        dslContext
            .selectFrom(AIR_TRANSACTION)
            .where(
                AIR_TRANSACTION
                    .TICKET_NUMBER
                    .eq(ticketNumber)
                    .and(AIR_TRANSACTION.SPOTNANA_PNR_ID.eq(pnrId)))
            .orderBy(AIR_TRANSACTION.TRANSACTION_DATE.desc())
            .fetchAnyInto(AirTransaction.class));

JOOQ Version - 3.17.7
Postgres Version - 14.6

Answer 1

The two are the same, logically, but not necessarily from a performance perspective. Think about what each operation does:

Query 1

• .fetch() fetches all data from the JDBC ResultSet into memory as a Result<AirTransactionRecord> (this includes going through the ExecuteListener::recordStart lifecycle for every record!)
• .stream() is lazy, implemented by ArrayList::stream and doesn't do anything on its own
• .findFirst() is a terminal operation on the stream that discards all elements after the first
• .map() maps 0..1 AirTransactionRecord into the AirTransaction POJO

Query 2

• fetchAnyInto() fetches only 0..1 elements from the JDBC ResultSet into memory as AirTransactionRecord, then, maps it into the AirTransaction POJO. (this only includes going through the ExecuteListener::recordStart lifecycle for that single record)
• In the future, the intermediate AirTransactionRecord might even be skipped, as it might not be necessary, see #6737

The difference

The difference only manifests when there are more than 1 records in the ResultSet, in case of which Query 2 is much faster than Query 1. This is unlikely what you want, so in such a case, it would be a good idea to add a limit(1) clause to prevent more than 1 records to be sent from SQL.

Other than that, the two approaches are effectively equivalent.

Alternative

There's also .fetchOptionalInto(AirTransaction.class), which you could call, to skip having to manually wrap things in an optional. The semantics of that is slightly different in case the query produces multiple records, though, as it may throw a TooManyRowsException