I have the same problem as the user in this issue https://github.com/jOOQ/jOOQ/issues/8080. When our generated jOOQ code does not exactly match our DB schema, queries using select(alias.asterisk())
throw errors because columns do not line up correctly.
org.postgresql.util.PSQLException: Bad value for type long : 633473bd597addd971f5b27e
at org.postgresql.jdbc.PgResultSet.toLong(PgResultSet.java:3328)
at org.postgresql.jdbc.PgResultSet.getLong(PgResultSet.java:2540)
at com.zaxxer.hikari.pool.HikariProxyResultSet.getLong(HikariProxyResultSet.java)
at org.jooq.tools.jdbc.DefaultResultSet.getLong(DefaultResultSet.java:149)
at org.jooq.impl.CursorImpl$CursorResultSet.getLong(CursorImpl.java:562)
at org.jooq.impl.DefaultBinding$DefaultLongBinding.get0(DefaultBinding.java:3071)
at org.jooq.impl.DefaultBinding$DefaultLongBinding.get0(DefaultBinding.java:3032)
at org.jooq.impl.DefaultBinding$InternalBinding.get(DefaultBinding.java:1071)
at org.jooq.impl.CursorImpl$CursorRecordInitialiser.setValue(CursorImpl.java:1581)
at org.jooq.impl.CursorImpl$CursorRecordInitialiser.apply(CursorImpl.java:1517)
at org.jooq.impl.CursorImpl$CursorRecordInitialiser.apply(CursorImpl.java:1432)
at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:144)
at org.jooq.impl.CursorImpl$CursorIterator.fetchNext(CursorImpl.java:1389)
at org.jooq.impl.CursorImpl$CursorIterator.hasNext(CursorImpl.java:1365)
at org.jooq.impl.CursorImpl.fetchNext(CursorImpl.java:173)
at org.jooq.impl.AbstractCursor.fetch(AbstractCursor.java:177)
at org.jooq.impl.AbstractCursor.fetch(AbstractCursor.java:88)
at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:265)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:357)
at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:290)
at org.jooq.impl.SelectImpl.fetch(SelectImpl.java:2838)
The queries work normally when I use .asterisk()
. The GitHub issue lists two workaounds, one using TABLE.fields()
and another using row(...)
. However, I’m unable to get these workarounds to work. Either I get a compilation error on the select statement:
context
.select(MY_TABLE.fields())
...
None of the following functions can be called with the arguments supplied.
select((MutableCollection<out SelectFieldOrAsterisk!>..Collection<SelectFieldOrAsterisk!>?)) defined in org.jooq.DSLContext
select(vararg SelectFieldOrAsterisk!) defined in org.jooq.DSLContext
select(SelectField<TypeVariable(T1)!>!) where T1 = TypeVariable(T1) for fun <T1 : Any!> select(field1: SelectField<T1!>!): SelectSelectStep<Record1<T1!>!> defined in org.jooq.DSLContext
Or I get a result back with all null values:
private companion object {
private const val MY_TABLE_ALIAS = "table"
private val MY_TABLE = TABLE
.`as`(MY_TABLE_ALIAS)
}
...
context
.select(MY_TABLE.fieldsRow())
.from(MY_TABLE)
.fetchInto(MY_TABLE)
// values are all null in the toApi call
.map(AvailabilityRegionsRecord::toApi)
Dialect: PostgreSQL
jOOQ version: 3.18.3
What am I missing here, and/or is there a different approach I can take?
2
Answers
What about just
This generates SQL with only the columns your jooq schema knows about at the time.
This method can’t be called as you did in kotlin:
Because
fields()
returnsField<?>[]
andselect()
accepts a vararg. So, you need to use the spread operator. This is kotlin specificUsing
fieldsRow()
will nest your record, so this would require an adaptation to the record mapper.As suggested here, you can just omit the explicit projection using
select()
or evenselectFrom(MY_TABLE)
, though I always recommend using explicit projections, see also this information from the jOOQ manual aboutSELECT *