Groovy SQL named list parameter

Question

I want to use a keyset of a Map as a list parameter in a SQL query:

query = "select contentid from content where spaceid = :spaceid and title in (:title)"
sql.eachRow(query, [spaceid: 1234, title: map.keySet().join(',')]) {
    rs ->
        println rs.contentid
}

I can use single values but no Sets or Lists. This is what I've tried so far:

map.keySet().join(',')
map.keySet().toListString()
map.keySet().toList()
map.keySet().toString()

The map uses Strings as key

Map<String, String> map = new TreeMap<>(String.CASE_INSENSITIVE_ORDER);

Also, I don't get an error. I just get nothing printed like have an empty result set.

Answer 1

You appoach will not give the expected result.

Logically you are using a predicate such as

 title = 'value1,value2,value3'

This is the reason why you get no exception but also no data.

Quick search gives a little evidence, that a mapping of a collections to IN list is possible in Groovy SQL. Please check here and here

So very probably you'll have to define the IN list in a proper length and assign the values from your array.

 title in (:key1, :key2, :key3)

Anyway something like this works fine:

Data

create table content as 
select 1 contentid, 1 spaceid, 'AAA' title from dual union all
select 2 contentid, 1 spaceid, 'BBB' title from dual union all
select 3 contentid, 2 spaceid, 'AAA' title from dual;

Groovy Script

map['key1'] = 'AAA'
map['key2'] = 'BBB'

query = "select contentid from content where spaceid = :spaceid and title in (${map.keySet().collect{":$it"}.join(',')})"
println query
map['spaceid'] = 1
sql.eachRow(query, map) {
    rs ->
        println rs.contentid
}

Result

select contentid from content where spaceid = :spaceid and title in (:key1,:key2)
1
2

The key step is to dynamicall prepare the IN list with proper names of the bind variable using the experssion map.keySet().collect{":$it"}.join(',')

Note

You may also want to check the size if the map and handle the case where it is greater than 1000, which is an Oracle limitation of a single IN list.