Do you Connect with my Groove?

Question

I'm new at groovy, and this is my first time writing a database connection script. It may seem like example code, but it is more like test code. It works as posted, and I'm planning to use this as a model for a larger business application, so I want to make sure I do it right.

Are there any groovy features that it could benefit from? Did I miss any edge cases, or anything else I could improve?

import groovy.sql.Sql
def dbUrl = "jdbc:postgresql://localhost/GroovyTest"
def dbUser = "Phrancis"
def dbPassword = "test"
def dbDriver = "org.postgresql.Driver"
def sql = Sql.newInstance(dbUrl, dbUser, dbPassword, dbDriver)
println "Sql Instance: " + sql
sql.execute """SET SEARCH_PATH TO groovy_test;"""
sql.execute """
 START TRANSACTION;
 DROP TABLE IF EXISTS test;
 CREATE TABLE test (
 id SERIAL,
 string TEXT,
 number INTEGER,
 decimal NUMERIC,
 datetime TIMESTAMP
 );
 COMMIT;"""
def params1 = ['''');DROP TABLE test;--''', 42, 3.14159, 'NOW()']
def params2 = ['Hello, World!', 99999999, 0.1209823098234, '2015-06-25']
sql.execute """
 START TRANSACTION;
 INSERT INTO test (string, number, decimal, datetime)
 VALUES (?, ?, ?, CAST(? AS TIMESTAMP));
 COMMIT;""", params1
sql.execute """
 START TRANSACTION;
 INSERT INTO test (string, number, decimal, datetime)
 VALUES (?, ?, ?, CAST(? AS TIMESTAMP));
 COMMIT;""", params2
sql.eachRow("SELECT * FROM test;") { row ->
 println """
The row Id is: ${row.id}
The string is: ${row.string}
The number is: ${row.number}
The decimal is: ${row.decimal}
The date-time is: ${row.datetime}"""
}
sql.close()

Here is the stdout after this runs:

Sql Instance: groovy.sql.Sql@385c9627
The row Id is: 1
The string is: ');DROP TABLE test;--
The number is: 42
The decimal is: 3.14159
The date-time is: 2015年06月25日 19:49:25.603991
The row Id is: 2
The string is: Hello, World!
The number is: 99999999
The decimal is: 0.1209823098234
The date-time is: 2015年06月25日 00:00:00.0
Process finished with exit code 0

• Michal M: Like Simon said, one normally uses the " quote in groovy when using GStrings and you can actually use them by replacing the "Sql Instance: " + sql part with "Sql Instance: $sql". It surely won't make a dramatic difference, and if it is even better or worse is more of a matter of taste, but since you are just starting with groovy you can treat it as a small excercise in GStrings.

Answer 1

Defining database connection parameters

It is not recommended to hard-code your connection parameters within the script.

You could for example parse the database properties from an external Json source. There are other methods available in Groovy as well, such as reading properties from XML, or from a database.properties file, or from a *.groovy file, but I think Json might be the one you would feel most comfortable with.

def dbProperties = new JsonSlurper().parseFile(new File('db.json'))

Where db.json is something like this:

{
 "url": "jdbc:postgresql://localhost/GroovyTest",
 "user": "Phrancis",
 "password": "test",
 "driver": "org.postgresql.Driver"
}

And then instantiating the Sql object:

def sql = Sql.newInstance(dbProperties.url, dbProperties.user, dbProperties.password, dbProperties.driver)

Strings

""" are used for multi-line strings, for single-line strings it is overkill.

sql.execute """SET SEARCH_PATH TO groovy_test;"""

Can become:

sql.execute "SET SEARCH_PATH TO groovy_test;"

Additionally, " is used for GString, that will work just fine here but is not required, a normal String would be enough. So we can use ' insteand so that it becomes:

sql.execute 'SET SEARCH_PATH TO groovy_test;'

Here you have a multi-line GString, but as you're not using any GString-specific features (variable interpolation for example), you can replace the """ with '''

sql.execute """
 START TRANSACTION;
 DROP TABLE IF EXISTS test;
 (...)

Duplicated SQL query string

This code is essentially repeated twice:

sql.execute """
 START TRANSACTION;
 INSERT INTO test (string, number, decimal, datetime)
 VALUES (?, ?, ?, CAST(? AS TIMESTAMP));
 COMMIT;""", params1

The only difference is that the second time params1 is exchanged for params2.

There are three ways to fix this:

1. Extract the query string to a variable
2. Extract a method, which takes the params as input and performs the query
3. Extract a (削除) method (削除ここまで) closure. (Very similar to extracting a method)

First approach would look like this:

def query = """
 START TRANSACTION;
 INSERT INTO test (string, number, decimal, datetime)
 VALUES (?, ?, ?, CAST(? AS TIMESTAMP));
 COMMIT;"""
sql.execute query, params1
sql.execute query, params2

Second approach:

void sqlInsert(List<Object> params) {
 sql.execute """
 START TRANSACTION;
 INSERT INTO test (string, number, decimal, datetime)
 VALUES (?, ?, ?, CAST(? AS TIMESTAMP));
 COMMIT;""", params
}
sqlInsert query, params1
sqlInsert query, params2

Note that sqlInsert query, params1 is the same as sqlInsert(query, params1)

Third approach:

def sqlInsert = { List<Object> params ->
 sql.execute """
 START TRANSACTION;
 INSERT INTO test (string, number, decimal, datetime)
 VALUES (?, ?, ?, CAST(? AS TIMESTAMP));
 COMMIT;""", params
}
sqlInsert.call(params1)
sqlInsert.call(params2)
// or alternatively
sqlInsert(params1)
sqlInsert(params2)