Groovy concatenate to dynamic query

Question

In a little new to Groovy and am creating a piece of code which executes an sql query using a dynamic sql string.

def executeQuery(String csvQueryInList) {
  def result = sql.rows('SELECT * FROM mySchema.myTable WHERE id IN (?,?)', ['1', '2'])
  return result
}

The above works but now I want to change this code to use the parameter csvQueryInList which is a CSV string.

Something like this....

  def sqlStr = 'SELECT * FROM mySchema.myTable WHERE id IN ('
    def executeQuery(String queryInList) {

        def values = queryInList.tokenize(", ")
        values.eachWithIndex { item, index ->
            sqlStr << '?,'
        }
        sqlStr << ')'

        println "Executing generated query: $sqlStr"
        def result = sql.rows(sqlStr, values)
        return result
    }

But it doesn't quite work.

Can someone give me a hand to correct what I have wrong or suggest an even better way.

thanks

It's a list of ids and can be 1 element long or 20 elements long. Ie it's dynamic.e.g 1,2,3,50. — Richie, Nov 29 '17 at 03:07
Richie, thank you for clarifying. Would you please check if the below solution is helpful? — Rao, Nov 29 '17 at 04:07

Rao · Accepted Answer · 2017-11-29T04:13:38.790

I believe that there is issue while build the query with question marks.

Here you find the fixed one with couple of things to note

Using method without passing sqlStr variable.
Instead of <<, + could be used to concatenate.

Changed the method to pass sqlStr as well.

def sqlStr = 'SELECT * FROM mySchema.myTable WHERE id IN ('
def listStr =  '1,2 , 3, 50'

def executeQuery(String queryInList, String query){
        //Get the list values from the list
        def values = queryInList.split(',')*.trim()
        //Build the question marks string
        def listOfQuestions = values?.inject([]){ list, it -> list << '?';list }.join(',')
        query += listOfQuestions + ')'
        println "Executing generated query: $query"
        def result = sql.rows(query, values)
        return result
}
executeQuery(listStr, sqlStr)

You can quickly try this demo (only query building part) on-line.

Hope the above is useful.

brilliant thanks. I don't understand the inject part fully but rest assured I will be looking at this closely to understand it more. — Richie, Nov 29 '17 at 11:05
It is basically building the list of questions and creating comma separated string. Appreciate if you can upvote the helpful answer. — Rao, Nov 29 '17 at 11:09

Groovy concatenate to dynamic query

1 Answers1