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

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)