Grails Cookbook - A collection of tutorials and examples

Groovy SQL Insert Operation Examples

Another common operation when working with database is inserting data. This is simple to achieve using SQL's insert statements. Below are some examples on how to insert data to a database table using Groovy SQL.

Insert Operation

The SQL syntax for inserting data to a table should look like:
INSERT INTO TABLE  ( [comma separated list of columns] ) values ( [comma separated list of values] )
Using this, below is an example on how to insert data to a table using Groovy SQL:
package sample
import groovy.sql.Sql
import java.sql.*
/**
 * A simple Groovy example that inserts data to a database.
 */
class Sample {
    static void main(String[] args) {
        // connect to db
        def sql = Sql.newInstance("jdbc:mysql://localhost:3306/test",
                "root", "root", "com.mysql.jdbc.Driver")
        // the sql script that creates a table
        def createTableScript = """
            CREATE TABLE PERSON (
                 FIRST_NAME VARCHAR(50),
                 LAST_NAME VARCHAR(50)
            )"""
        // execute the create table script
        sql.execute(createTableScript);
        // prepare data
        def data = [[fname:'John', lname:'Doe'], [fname:'Juan', lname:'Dela Cruz']]
        // loop and insert data
        data.each { dataItem ->
            sql.execute("INSERT INTO PERSON (FIRST_NAME, LAST_NAME) values ('${dataItem.fname}', '${dataItem.lname}')");
        }
        // close connection
        sql.close()
    }
}
The code prepared two data: John Doe and Juan Dela Cruz. It iterates over the list and prepares the SQL and execute it. Running the code however produces the following error:
Nov 10, 2016 10:09:36 AM groovy.sql.Sql asSql
WARNING: In Groovy SQL please do not use quotes around dynamic expressions (which start with $) as this means we cannot use a JDBC PreparedStatement and so is a security hole. Groovy has worked around your mistake but the security hole is still there. The expression so far is: INSERT INTO PERSON (FIRST_NAME, LAST_NAME) values ('?', '
Nov 10, 2016 10:09:37 AM groovy.sql.Sql asSql
WARNING: In Groovy SQL please do not use quotes around dynamic expressions (which start with $) as this means we cannot use a JDBC PreparedStatement and so is a security hole. Groovy has worked around your mistake but the security hole is still there. The expression so far is: INSERT INTO PERSON (FIRST_NAME, LAST_NAME) values ('?', '
This is because composing queries using variables will not utilize prepared statements. Also, it is prone to SQL injection vulnerability. A better example is by using parameters. Below is an example:
package sample
import groovy.sql.Sql
import java.sql.*
/**
 * A simple Groovy example that inserts data to a database.
 */
class Sample {
    static void main(String[] args) {
        // connect to db
        def sql = Sql.newInstance("jdbc:mysql://localhost:3306/test",
                "root", "root", "com.mysql.jdbc.Driver")
        // the sql script that creates a table
        def createTableScript = """
            CREATE TABLE PERSON (
                 FIRST_NAME VARCHAR(50),
                 LAST_NAME VARCHAR(50)
            )"""
        // execute the create table script
        sql.execute(createTableScript);
        // prepare data
        def data = [[fname:'John', lname:'Doe'], [fname:'Juan', lname:'Dela Cruz']]
        // loop and insert data
        data.each { dataItem ->
            sql.execute("INSERT INTO PERSON (FIRST_NAME, LAST_NAME) values (?, ?)",
                [dataItem.fname, dataItem.lname]);
        }
        // close connection
        sql.close()
    }
}
Notice that instead of using the variables inside the query, we replace them with question marks. And then the data corresponding to the question marks are given as parameters.

A better example is to used named parameter. Below is an example:

package sample
import groovy.sql.Sql
import java.sql.*
/**
 * A simple Groovy example that inserts data to a database.
 */
class Sample {
    static void main(String[] args) {
        // connect to db
        def sql = Sql.newInstance("jdbc:mysql://localhost:3306/test",
                "root", "root", "com.mysql.jdbc.Driver")
        // the sql script that creates a table
        def createTableScript = """
            CREATE TABLE PERSON (
                 FIRST_NAME VARCHAR(50),
                 LAST_NAME VARCHAR(50)
            )"""
        // execute the create table script
        sql.execute(createTableScript);
        // prepare data
        def data = [[fname:'John', lname:'Doe'], [fname:'Juan', lname:'Dela Cruz']]
        // loop and insert data
        data.each { dataItem ->
            sql.execute("INSERT INTO PERSON (FIRST_NAME, LAST_NAME) values (:fname, :lname)",
                [lname:dataItem.lname, fname:dataItem.fname]);
        }
        // close connection
        sql.close()
    }
}
Since we are using named parameters, the map of values we provide does not need to be in order of appearance. it will match the parameters by name!