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)