Question 170336 in PostgreSQL Question posted in
The official documentation can be found here.

How do I use row/fields instead of asterisk() in jOOQ for Postgresql?

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?

Tags:

2

Answers


  1. 0

    What about just

    select()
  .from(MY_TABLE)
  .fetchInto(MY_TABLE)

    This generates SQL with only the columns your jooq schema knows about at the time.

    Login or Signup to reply.
  2. 0

    This method can’t be called as you did in kotlin:

    .select(MY_TABLE.fields())

    Because fields() returns Field<?>[] and select() accepts a vararg. So, you need to use the spread operator. This is kotlin specific

    .select(*MY_TABLE.fields())

    Using 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 even selectFrom(MY_TABLE), though I always recommend using explicit projections, see also this information from the jOOQ manual about SELECT *

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search