I face a rare trouble in my service: sometimes JOOQ does not cast enum variables for postgres query, my question is – what could be a reason?
I’ve investigated how JOOQ builds queries and found out that it should always cast EnumType
variables for postgres db in the following way(pay attention to a field status
):
select
"model"."some_object"."id",
"model"."some_object"."number",
"model"."some_object"."created_at",
"model"."some_object"."deactivated_at",
"model"."some_object"."status"
from "model"."some_object"
where (
"model"."some_object"."number" = ?
and "model"."some_object"."status" in (?::"model"."entity_status", ?::"model"."entity_status")
)
But sometimes after restart my service starts throwing the following exception:
select
"model"."some_object"."id",
"model"."some_object"."number",
"model"."some_object"."created_at",
"model"."some_object"."deactivated_at",
"model"."some_object"."status"
from "model"."some_object"
where (
"model"."some_object"."number" = ?
and "model"."some_object"."status" in (?, ?)
)
ERROR: operator does not exist: status = character varying
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
When it happens, it’s usually enough just to restart a service to make it working well. (I guess in that case it builds a right prepared statement for the query)
Repository function looks like:
fun findByNumber(number: String, statuses: StatusSet): List<SomeObject> =
dslContext.fetch(
table,
table.NUMBER.eq(number).and(table.STATUS.`in`(statuses.statusList))
).into(SomeObject::class.java)
Jooq version: 3.16.6