I am using vavr and jOOQ, two fantastic libraries to have come out in the recent times, allowing us to use functional dialects in regular Java server applications.
I am trying to get using jOOQ, what is equivalent of SQL's select count (*).
The query is formed this way:
ResultQuery query = dsl.selectCount()
.from(Tables.SH_PLAYER_REPORT)
.join(Tables.SH_PLAYERS)
.on(Tables.SH_PLAYERS.PLAYER_ID.eq(Tables.SH_PLAYER_REPORT.PLAYER_ID))
.join(Tables.SH_LOCATION)
.on(Tables.SH_LOCATION.LOCATION_ID.eq(Tables.SH_PLAYERS.LOCATION))
.and(Tables.SH_PLAYER_REPORT.START_ON.ge(Timestamp.from(fromWhenInUTCInstant)))
.and(Tables.SH_PLAYER_REPORT.START_ON.le(Timestamp.from(toWhenInUTCInstant)))
.and(Tables.SH_LOCATION.LOCATION_ID.eq(criteriaAllFieldsTeam.getLocation_id()));
jOOQ's generator has worked perfectly, and there isn't any type-mismatch here. So, I guess, the query is correctly formed.
Then, I am using vavr's Try, thus:
Optional<Integer> mayBeCount = Optional.empty();
try (final Connection cn = this.ds.getConnection()) {
DSLContext dsl = DSL.using(cn, this.dialect);
Try<Integer> countFromDBAttempted =
Try
.of(() -> prepareCountOfGamesPlayedQuery(dsl,criteriaAllFieldsTeam))
.map(e -> e.fetchOne(0, Integer.class)) // Here's the problem!
.onFailure(e -> logger.warning(String.format("Count Of Games Played, status=Failed, reason={%s}",e.getMessage())));
mayBeCount = (countFromDBAttempted.isFailure() ? Optional.empty() : Optional.of(countFromDBAttempted.getOrElse(0)));
} catch (SQLException ex) {
logger.warning(
String.format("DB(jOOQ): Failed, counting games played, using criteria {%s},reason={%s}",criteriaAllFieldsTeam.toString(),ex.getMessage()));
}
return (mayBeCount);
The compiler fails to infer the type of the field, despite the help I give to it, by describing the target type: Integer.class!
../ReportByTeamRecordProducerImpl.java:66: error: incompatible types: Try<Object> cannot be converted to Try<Integer>
.onFailure(e -> logger.warning(String.format("Count Of Games Played, status=Failed, reason={%s}",e.getMessage())));
^
Unsurprisingly, when I coerce the type, then the code runs perfectly fine. I just the introduce an explicit cast, at the line that the compiler considers .. er .. distasteful!
Try<Integer> countFromDBAttempted =
Try
// The following function returns the ResultQuery shown above
.of(() -> prepareCountOfGamesPlayedQuery(dsl,criteriaAllFieldsTeam))
// Casting below, because of some incompatibility between vavr and jOOQ
.map(e -> ((Integer) e.fetchOne(0, Integer.class)))
.onFailure(e -> logger.warning(String.format("Count Of Games Played, status=Failed, reason={%s}",e.getMessage())));
I have tried with a couple of other ways, based upon my understanding of jOOQ library and especially, this explanation by @LukasEder.
What I haven't tried so far, is to introduce a Converter, because for a single field value, that seems to be unnecessary, to my eyes! However, if that's the way, then I would like to have a hint.
In response to @LukasEder :
private ResultQuery prepareCountOfGamesPlayedQuery(DSLContext dsl, CriteriaAllFieldsTeam criteriaAllFieldsTeam) {
Instant fromWhenInUTCInstant =
convertToDBCompatibleInstantUTC(
criteriaAllFieldsTeam.getDate_range().getFromWhen(),
criteriaAllFieldsTeam.getDate_range().getInTimeZone());
Instant toWhenInUTCInstant =
convertToDBCompatibleInstantUTC(
criteriaAllFieldsTeam.getDate_range().getToWhen(),
criteriaAllFieldsTeam.getDate_range().getInTimeZone());
ResultQuery query = dsl.selectCount()
.from(Tables.SH_PLAYER_REPORT)
.join(Tables.SH_PLAYERS)
.on(Tables.SH_PLAYERS.PLAYER_ID.eq(Tables.SH_PLAYER_REPORT.PLAYER_ID))
.join(Tables.SH_LOCATION)
.on(Tables.SH_LOCATION.LOCATION_ID.eq(Tables.SH_PLAYERS.LOCATION))
.and(Tables.SH_PLAYER_REPORT.START_ON.ge(Timestamp.from(fromWhenInUTCInstant)))
.and(Tables.SH_PLAYER_REPORT.START_ON.le(Timestamp.from(toWhenInUTCInstant)))
.and(Tables.SH_LOCATION.LOCATION_ID.eq(criteriaAllFieldsTeam.getLocation_id()));
return (query);
}
Following, Lukas' nudge, I have modified the method this way:
private ResultQuery<Record1<Integer>> prepareCountOfGamesPlayedQuery(DSLContext dsl, CriteriaAllFieldsTeam criteriaAllFieldsTeam) {
Instant fromWhenInUTCInstant =
convertToDBCompatibleInstantUTC(
criteriaAllFieldsTeam.getDate_range().getFromWhen(),
criteriaAllFieldsTeam.getDate_range().getInTimeZone());
Instant toWhenInUTCInstant =
convertToDBCompatibleInstantUTC(
criteriaAllFieldsTeam.getDate_range().getToWhen(),
criteriaAllFieldsTeam.getDate_range().getInTimeZone());
ResultQuery<Record1<Integer>> query = dsl.selectCount()
.from(Tables.SH_PLAYER_REPORT)
.join(Tables.SH_PLAYERS)
.on(Tables.SH_PLAYERS.PLAYER_ID.eq(Tables.SH_PLAYER_REPORT.PLAYER_ID))
.join(Tables.SH_LOCATION)
.on(Tables.SH_LOCATION.LOCATION_ID.eq(Tables.SH_PLAYERS.LOCATION))
.and(Tables.SH_PLAYER_REPORT.START_ON.ge(Timestamp.from(fromWhenInUTCInstant)))
.and(Tables.SH_PLAYER_REPORT.START_ON.le(Timestamp.from(toWhenInUTCInstant)))
.and(Tables.SH_LOCATION.LOCATION_ID.eq(criteriaAllFieldsTeam.getLocation_id()));
return (query);
}
.. and, now peace again prevails in the world!
Thanks, Lukas!
Given the code you've provided so far, and assuming there's no typo, this is probably caused because of your raw type reference to ResultQuery. Use ResultQuery<?> or ResultQuery<Record1<Integer>> instead.
Never use raw types, unless you really have to. And you probably don't.