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

Answer 1

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. ​