[groovy-user] Handling of collections/arrays in named queries/prepared statements within SQL IN clause

[thekalinga]

I am currently using named queries for executing SQL using groovy.Sql class.

I'm unable to pass an array of Strings to the IN clause of the SQL. When I
pass an array, I see that the sql that gets executed in Mysql has garbled
info

=================

As an example, I want to execute the following sql statement using named
parameters

def sqlQuery = "select * from TABLE where col1 =3D :col1_val and col2 in
(:col2_values)"

when I execute

sql.rows(sqlQuery, [col1_val : '1', col2_values : ['2', '3']])

The sql that gets executed does not look like this

select * from TABLE where col1 =3D '1' and col2 in ('2','3')

instead it looks like

select * from TABLE where col1 =3D '1' and col2 in
(<JAVA_OBJECT_REPORESENTATION_OF_ARRAY_GARBAGE_HERE>)

===================

Is there some one who got around this problem using prepared
statements/named queries.

NOTE: My intent behind using named queries/prepared statements is to avoid
SQL injection

Any help is appreciated.

Thanks
Ashok

--
View this message in context: http://groovy.329449.n5.nabble.com/Handling-of-collections-arrays-in-named-queries-prepared-statements-within-SQL-IN-clause-tp5654098p5654098.html
Sent from the groovy - user mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

[Dinko Srkoc]

On 20 April 2012 14:45, thekalinga <theka...@gmail.com> wrote:
> I am currently using named queries for executing SQL using groovy.Sql class.
>
> I'm unable to pass an array of Strings to the IN clause of the SQL. When I
> pass an array, I see that the sql that gets executed in Mysql has garbled
> info

I believe this is supported by only some JDBC drivers, e.g. Informix'

>
> =================
>
> As an example, I want to execute the following sql statement using named
> parameters
>
> def sqlQuery = "select * from TABLE where col1 =3D :col1_val and col2 in
> (:col2_values)"

You may try something like this:

8<--------------------------------------
def vals = ['1', '2', '3']
def sqlQuery =
"""
select * from TABLE where col1 = ?
and col2 in (${vals.tail().collect {'?'}.join(',') })
"""
sql.rows(sqlQuery, vals)
-------------------------------------->8

Cheers,
Dinko

[thekalinga]

Dinko,

Thanks for your quicker reply and sorry I could not respond to this earlier.

W.r.t the solution you gave, I have tried using it, but the problem is, I
have many duplicate variables in the sql.

For Ex., A sub query in main query looks like

select * from TABLE as alias1 where c1 = :c1 and c2 in (:c2) and c3 in
(:c3)
inner join
select sum(col4) from TABLE as alias2 where col1 = :c1 and c2 in (:c2) and
c3 in (:c3)
on alias1.c1 = alias2.c1 and alias1.c2= alias2.c2 ............

I have implemented the whole thing like you said, but the code looks really
unmanageable. It felt like, code will be easier to understand had I used
direct string replacement in Sql, instead of using Sql prepared statements,
but the problem is inevitable with string replacement, Sql injection.

So I went ahead and used SimpleJDBCTemplate from Spring instead of
groovy.Sql

http://www.mularien.com/blog/2008/03/10/auto-expanding-collections-as-jdbc-parameters-with-spring-simplejdbctemplate/
SimpleJDBCTemplate support expansion of collections for SQL "IN CLAUSE"

This is how I have implemented auto expandable collections using
SimpleJDBCTemplate

resources.groovy

simpleJdbcTemplate(SimpleJdbcTemplate, ref('dataSource')) {}

*Service

final String SQL_WITH_NAMED_PARAMS = '''
...
...
select * from TABLE as alias1 where c1 = :c1 and c2 in (:c2) and c3 in
(:c3)
inner join
select sum(c4) from TABLE as alias2 where col1 = :c1 and c2 in (:c2) and
c3 in (:c3)
on alias1.c1 = alias2.c1 and alias1.c2= alias2.c2
...
...
'''
def simpleJdbcTemplate

def service(.....){

// Assuming all the colections that are to be passed are avaiable here
// c2 & c3 are Collection objects, not arrays
def sqlParamsMap = [:]
sqlParamsMap.c1 = c1
sqlParamsMap.c2 = c2
sqlParamsMap.c3 = c3

return simpleJdbcTemplate.query(SQL_WITH_NAMED_PARAMS, new RowMapper(){
public Object mapRow(ResultSet rs, int index) throws SQLException {
def obj = new Expando()
obj.resultCol1 = rs.getString('resultCol1')
...
...
return obj
}
}, sqlParamsMap)
}

The code looks much cleaner now.

I don't understand why groovy.Sql cannot implement the same thing
SimpleJDBCTemplate has implemented so nicely.

Any clue on whether there is any JIRA bug/feature request on this.

Thanks
Ashok

--
View this message in context: http://groovy.329449.n5.nabble.com/Handling-of-collections-arrays-in-named-queries-prepared-statements-within-SQL-IN-clause-tp5654098p5664456.html

[Dinko Srkoc]

On 25 April 2012 12:36, thekalinga <theka...@gmail.com> wrote:
>[...]

> I don't understand why groovy.Sql cannot implement the same thing
> SimpleJDBCTemplate has implemented so nicely.

groovy.sql.Sql is a wrapper around Java's JDBC API. Since some,
although probably a minority, of the JDBC drivers *do* handle
collections as parameters, it would probably not be a good idea for
groovy.sql.Sql to process such collections on its own.

>
> Any clue on whether there is any JIRA bug/feature request on this.

I'm not aware of any. You are, of course, free to submit one if you
think such (or any other) feature is useful.

Cheers,
Dinko