Dynamic SQL query with Groovy

Dynamic SQL query with Groovy

Groovy’s groovy-sql module provides a higher-level abstraction over Java’s JDBC technology and it is very easy to use. You can find good examples, using groovy.sql.Sql class in API documentation or Groovy documentation.

It was not clear to me though how to execute SQL queries, that needs to be constructed dynamically, based on some conditions.

Specifically, I needed to add additional where condition, if a certain variable is populated, and update a column, if another variable is set.

In other words, if both oldState and lastWatermark variables are set, my query would look like this:

UPDATE job_control 
SET    
  state = $newstate, 
  modified_dt_tm = Now(), 
  last_watermark = $lastwatermark 
WHERE  
      name = $jobname 
  AND state = $oldstate 

but if they are not set, my query should look like that:

UPDATE job_control 
SET    
  state = $newstate, 
  modified_dt_tm = Now()
WHERE  
      name = $jobname

After spending some time and trying a few different options, I've learned about Sql.expand method. Combined with Groovy's GString conditional expression, I came up with a nice dynamic query:

stmt= """UPDATE job_control
            SET
                state = $newState,
                modified_dt_tm = NOW()
                ${lastWatermark ? Sql.expand(",last_watermark = '$lastWatermark'") : Sql.expand("")}
            WHERE 
                name = $jobName
                ${oldState ? Sql.expand("and state = '$oldState'") : Sql.expand("")}
"""

One word of caution. When you pass variables in GString with Groovy's sql method, like execute(GString stmt), variables like $newState will be expanded and will use JDBC prepared statement, hence protects your code from SQL injection (see Avoiding SQL injection). In the query above, I had to use single quotes around '$lastWatermark' for Sql.expand to work properly. It means that SQL Injection is possible with that line (while it is not a problem with line state = $newState). There is probably a good way to address it, but in my case these two variables are populated from a closed system and it is not a concern.

Here is my code, used for testing with mysql database. Make sure mysql connector jar is on the classpath. For GroovyConsole (it is great for playing with Groovy), you either need to copy mysql connector jar to Groovy's lib folder or create a symlink.

import groovy.sql.Sql
// add mysql driver jar to groovy class path
// if GroovyConsole used to run this code, create symlink like so:
// cd /home/oracle/.sdkman/candidates/groovy/2.4.13/lib
// ln -s /var/lib/jdbc/mysql-connector-java-5.1.40-bin.jar .

def sql = Sql.newInstance('jdbc:mysql://localhost:3306/testdb', 'username', 'password', 'com.mysql.jdbc.Driver')
//println sql.firstRow('SELECT * FROM INFORMATION_SCHEMA.COLUMNS')

def jobName='test'
def newState='ccc'

// uncomment/comment one of the 2 lines below:
//def oldState
def oldState='bbb'

// uncomment/comment one of the 2 lines below:
// def lastWatermark
def lastWatermark=new Date().format("yyyy-MM-dd HH:mm:ss")

stmt= """UPDATE job_control
            SET
                state = $newState,
                modified_dt_tm = NOW()
                ${lastWatermark ? Sql.expand(",last_watermark = '$lastWatermark'") : Sql.expand("")}
            WHERE 
                name = $jobName
                ${oldState ? Sql.expand("and state = '$oldState'") : Sql.expand("")}
"""
print(stmt)
sql.execute(stmt)