I'm experimenting with jOOQ and Kotlin and seen some tutorials and docs and it looks really nice.
But if there is something very annoying with jOOQ is the code generation. It seems too complex, and eventually impossible to maintain. I decided to create my own table models (similar to how hibernate works).
I created two table models:
User
data class User(
val id: String = UUID.randomUUID().toString(),
val name: String,
val email: String,
val password: String? = null
) {
companion object {
val TABLE: Table<Record> = DSL.table("user")
val ID: Field<String> = DSL.field("id", String::class.java)
val USER_NAME: Field<String> = DSL.field("user_name", String::class.java)
val EMAIL: Field<String> = DSL.field("email", String::class.java)
val PASSWORD: Field<String> = DSL.field("password", String::class.java)
}
}
Followers
data class Followers(
val id: String,
val followerId: String,
val userId: String
) {
companion object {
val TABLE: Table<Record> = DSL.table("followers")
val ID: Field<String> = DSL.field("id", String::class.java)
val FOLLOWER_ID: Field<String> = DSL.field("follower_id", String::class.java)
val USER_ID: Field<String> = DSL.field("user_id", String::class.java)
}
}
When I did some trivial SQL statements and it worked perfectly, but when I tried the next statement, I'm getting exception.
return dsl.select().from(u.TABLE)
.rightJoin(f.TABLE).on(u.ID.eq(f.FOLLOWER_ID))
.where(u.ID.eq(id)).fetch().into(User::class.java)
The expected statement from this code is:
select *
from user u
right outer join followers f
on u.id = f.follower_id
where u.id = 'e30919bf-5f76-11e8-8c96-701ce7e27f83';
But the statement I got from this code is:
select *
from user
right outer join followers
on id = follower_id
where id = 'e30919bf-5f76-11e8-8c96-701ce7e27f83'
And of course, this givse me (rightfully) the error Column 'id' in where clause is ambiguous
It raises a few questions:
- Is there a better way to declare table model without code generation.
- Why the DSL
select
does not transform to proper SQL statement? What I'm doing wrong?