I have an SQL statement where I would like to pass a list of tuples customerIdSubCustomerPairs so that I can fetch all the rows matching the where clause from the table customers.
This is the SQL for it:
@Language("PostgreSQL")
private const val getExistingRowsSql = """
select customerId, subcustomer
from customer
where (uuid IN (:uuids) AND is_deleted = false)
union
select customerId, subcustomer
from customer
where (customerId, subcustomer) IN (:customerIdSubCustomerPairs) AND is_deleted = false
"""
I have a list of customers from which I am building a list of lists for customerIdSubCustomerPairs.
It looks like this:
val existingCustomers = fetchRows(
ctx, getExistingRowsSql, mapOf(
"uuids" to customers.map { it["uuid"] },
"customerIdSubCustomerPairs" to customers.map { listOf(it["customerId"] as String, it["subCustomer"] as String) }
))
And I have a function where I convert values to Oid types:
fun jdbcConvertValue(session: Session, v: Any?): Any? =
when (v) {
is Collection<*> -> {
when (val type = v.firstOrNull()) {
null -> null
is String -> session.connection.underlying.createArrayOf("text", v.toTypedArray())
is Long -> session.connection.underlying.createArrayOf("bigint", v.toTypedArray())
is Int -> session.connection.underlying.createArrayOf("bigint", v.toTypedArray())
is UUID -> session.connection.underlying.createArrayOf("uuid", v.toTypedArray())
is List<*> -> v.map { jdbcConvertValue(session, it) }.toTypedArray()
else -> throw Exception("You need to map your array type $type ${type.javaClass}")
}
}
else -> v
}
However, when I try to fetch rows like that, I get an error:
org.postgresql.util.PSQLException: Cannot cast an instance of [Ljava.lang.Object; to type Types.ARRAY
I am not sure how to pass values as tuples into a SQL statement?
The fields customerId and subcustomer are both text columns in the DB table.
