Groovy concatenate to dynamic query

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

Solution

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.