Join eq function not working with Jooq and Kotlin

Question

I'm using:

Jooq 3.13.2
Kotlin 1.3.71
Spring boot 2.2.6.RELESE
Java 11

I was able to generate Jooq classes and execute a simple query:

class StoryCustomRepositoryImpl @Autowired constructor(
    private val dslContext: DSLContext
): StoryCustomRepository {
    override fun findEmployeeStories(pageable: Pageable) {
        return dslContext.select(STORY.ID, STORY.DESCRIPTION)
            .from(STORY)
            .forEach { println($it[STORY.ID]) }
    }
}

When I try to add a bit more complex logic by adding join, compilation is failing:

class StoryCustomRepositoryImpl @Autowired constructor(
    private val dslContext: DSLContext
): StoryCustomRepository {
    override fun findEmployeeStories(pageable: Pageable) {
        return dslContext.select(STORY.ID, STORY.DESCRIPTION)
            .from(STORY)
            .join(USERS).on(USERS.ID.eq(STORY.CREATED_BY))
            .forEach { println($it[STORY.ID]) }
    }
}

Compilation fails on following line .join(USERS).on(USERS.ID.eq(STORY.CREATED_BY))

Error:

None of the following functions can be called with the arguments supplied: 
public abstract fun eq(p0: Int!): Condition! defined in org.jooq.TableField
public abstract fun eq(p0: Field<Int!>!): Condition! defined in org.jooq.TableField
public abstract fun eq(p0: QuantifiedSelect<out Record1<Int!>!>!): Condition! defined in org.jooq.TableField
public abstract fun eq(p0: Select<out Record1<Int!>!>!): Condition! defined in org.jooq.TableField

I was following this tutorial: https://blog.jooq.org/2017/05/18/10-nice-examples-of-writing-sql-in-kotlin-with-jooq/

Edit: It looks like the issue is that STORY.CREATED_BY is type of Long, while USERS.ID is type of Integer. I'm not sure what needs to be changed to be able to fix this.

Thank you

Hi @LiorH, as posted in edit part of the question, it looks like issue is because id was integer while created_by was biginteger in the database (resulting generated code to be integer for id and long for createdBy). I think I found a temporary solution with using cast method: .leftJoin(USERS).on(USERS.ID.cast(Long::class.java).eq(STORY.CREATED_BY)) I think better solution would be to align types in database. I think I'll end up doing that. — Damir Palinić

Lukas Eder Lukas Eder · Accepted Answer · 2020-05-18T13:24:16

You should probably change the type of all of these ID columns and their reference to be the same, i.e. BIGINT.

As a quick workaround, you can use Field.coerce(). I would prefer that over Field.cast(). The difference is that coerce() does not have any effect on the generated SQL (which you want to avoid to get better index usage), whereas cast() translates to the SQL CAST() function.

Join eq function not working with Jooq and Kotlin

1 Answers