Do you Connect with my Groove?
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:


Extract the query string to a variable
Extract a method, which takes the params as input and performs the query
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&lt;Object&gt; 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&lt;Object&gt; params -&gt;
    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)