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[]

There should be two `?` placeholders, and each day should be bound separately. — JB Nizet, Nov 02 '18 at 16:17
Thanks for the reply! What if the array size varies? How do I pass the arguments then? — user2352084, Nov 03 '18 at 17:19
You generate the query dynamically, or you use NamedParameterJdbcTemplate which, AFAIR, supports binding a list for you. — JB Nizet, Nov 03 '18 at 17:48
I can add question marks to the query string dynamically, but I can't think of a way how to add arguments dynamically — user2352084, Nov 03 '18 at 21:51
https://stackoverflow.com/questions/45854994/kotlin-convert-array-to-java-varargs — JB Nizet, Nov 03 '18 at 22:29

score 1 · Answer 1 · answered Dec 20 '18 at 17:12

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

answered Dec 20 '18 at 17:12

Mario GT

13
5

Maybe not beautiful, but just what I needed. Thank you very much - Super Mario. – Twistleton Nov 19 '21 at 11:57

score 0 · Answer 2 · answered Dec 20 '18 at 20:55

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)

JdbcTemplate IN clause in Kotlin

2 Answers2