We’ve got a pattern that has worked in the past for insert statements, but I’m attempting to reuse it for a select statement and seeing what I’d consider to be unexpected behavior. Assume everything that isn’t delineated here is correctly defined; I’m trying to keep this brief, not cover the nitty-gritty details.

So I have an entity (all code is Groovy):

class SomeEntity {
    
    static class COLUMNS {
        public static final String COL_1 = 'col_1'
        public static final String COL_2 = 'col_2'
        // ...elided
    }

    static class FIELDS {
        public static final Field<String> COL_1 = DSL.field(COLUMNS.COL_1, String)
        public static final Field<String> COL_2 = DSL.field(COLUMNS.COL_2, String)
        // ...elided
    }

    static List<Field<?>> ENTITY_FIELDS = [FIELDS.COL_1, FIELDS.COL_2]

    @Column(name = COLUMNS.COL_1)
    String col1

    @Column(name = COLUMNS.COL_2)
    String col2
}

(This is severely truncated as a minimal example. There are also Boolean, LocalDateTime, and BigDecimal Fields.)

And then a repo like this:

@Repository
class SomeEntityRepo {

    DSLContext jooq

    SomeEntityRepo(DSLContext jooq) {
        this.jooq = jooq
    }

    SomeEntity findEntityDate(String id) {
        return jooq.select(ENTITY_FIELDS)
                   .from(table)
                   .where(COL_1.eq(id))
                   .fetchOneInto(SomeEntity)
    }

}

(also truncated, we use constructor injection… there’s a join that will happen, etc.)

What I’m seeing is that jOOQ is executing a select * from ... rather than a select col_1, col_2 from .... The minute I move the declaration of ENTITY_FIELDS into the repository class, it works as expected.

The main issue here is that this should actually end up as select table.col_1, table.col_2 from ... because there’s a join and, of course, both tables have an id column. I can, of course, define the List<Field> in the repo class, but I have more than one such list, so it’s kind of nice to have them declared (and hidden away) in the entity classes. Also, there’s another query that’s more join-heavy and ultimate what got me hunting down this issue.

Again, using these entity-defined Lists works just fine for insert statements, so I’m confused why they are not recognized correctly by the select. I’ve read the documentation and noticed that * is substituted when columns are not recognized, but I’ve even gone so far as to reduce the entity-defined List to just the ID field and it still does a select *.

Am I missing something? Is there a reason for this? Is the behavior caused by jOOQ or is this a Spring issue? This project is using Spring Boot 3.2.2 and jOOQ 3.17.6.

P.S. Update:

I’ve continued to mess around here and think I may have found a reason for this, but it also boggles my mind. The SomeEntity class also has this going on:

class SomeEntity {

    public static final String TABLE_NAME = 'table_name'
    public static final org.jooq.Table ENTITY_TABLE = DSl.table(TABLE_NAME)

    public static final org.jooq.Table OTHER_TABLE = DSL.table('other_table')
    // and two more for other entities

    static class FIELDS {
        // previous entries elided
        public static final Field<String> OTHER_ID = DSL.field("$ENTITY_TABLE.other_id") // this is not exactly right, but gives the general idea of what we do in many places
        // repeat for a handful of other fields.
    }
}

If I move those additional entries to the repo class, everything suddenly works as expected (with the ENTITY_FIELDS list in-use). I can leave the “additional Tables” and it’s fine. If I also move the additional tables, it starts doing select * again.

I’m going to continue to experiment; these additional tables/fields technically don’t belong in SomeEntity, so I’ll see if refactoring them, together, works, but I’m not hold my breath given the above information.