CODEWITHSUNDEEP
sqlgroovynamed-parameters
CaptainMango
CaptainMango

Reputation: 17

Groovy SQL named list parameter

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.

Upvotes: 0

Views: 7408

Answers (2)

Marmite Bomber
Marmite Bomber

Reputation: 21085

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.

Upvotes: 5

Chris LR
Chris LR

Reputation: 1

It has worked for me with a little adaptation, I've added the map as a second argument.

def sql = Sql.newInstance("jdbc:mysql://localhost/databaseName", "userid", "pass")
    Map<String,Long> mapProduitEnDelta = new HashMap<>()
    mapProduitEnDelta['key1'] = 1
    mapProduitEnDelta['key2'] = 2
    mapProduitEnDelta['key3'] = 3
    produits : sql.rows("""select id, reference from Produit where id IN (${mapProduitEnDelta.keySet().collect{":$it"}.join(',')})""",mapProduitEnDelta),

Display the 3 products (colums + values from the produit table) of id 1, 2, 3

Upvotes: 0

Related Questions