JdbcTemplate IN clause in Kotlin

Question

How is it possible to use the IN clause in Kotlin with JdbcTemplate

val days = arrayOf("TUESDAY", "WEDNESDAY")
jdbcTemplate.query("select * from days where days not in (?), mapper, days)

For the following I get:

ERROR: operator does not exist: text = character varying[]

Answer 1

I have the same problem. I'm looking for a lot but didn't find any "clean" solution. It is my solution:

@Repository
class AnyRepository (@Autowired var template: JdbcTemplate){
fun anyName(someList: Lis<int>): List<Any> = template.query("SELECT * FROM anyTable WHERE anyTable.id IN (${someList.toString().substring(1, someList.toString().length - 1)})"){ rs, _ -> ... }
}

If I find a better solution, I'll post it

Answer 2

The best way to handle the "IN" situations for a JDBC template (in Java or Spring) is to use the NamedParameterJdbcTemplate from Spring. If you're using environment properties to wire up your data source, one of these should already be available as a bean to autowire in. If not, you can create one just by passing in the JdbcTemplate that you have.

val days = arrayOf("TUESDAY", "WEDNESDAY")
val namedParamJdbcTemplate = NamedParameterJdbcTemplate(jdbcTemplate) // or autowired in
// make sure this is not an empty collection! In your example it obviously is not empty.
// but if it is being passed in and could be empty, make sure to not query with it, 
// otherwise the JdbcTemplate will make a where clause of days NOT IN (), which will fail on execution
val paramMap: Map<String, Any?> = mapOf("days" to days) 
namedParamJdbcTemplate.query("select * from days where days not in (:days), paramMap, mapper)