Groovy SQL Update Operation Examples

Nov 10, 2016 SQL comments

There are instances when data on a record should be modified. SQL provides update statements for this purpose. Below are examples on how to perform update operation using Groovy SQL.

Update Operation

The SQL UPDATE statement syntax follows the format:

UPDATE [column name1] = [new value1], [column name2] = [new value2], ... FROM [table] WHERE [CONDITION]

The statement can have a list of column value pairs to update the value from a table that satisfies the where condition. Of course the where condition is optional if we wish to update all fields in the table. Below is an example on how to use this to update data in a table using Groovy SQL.

package sample
import groovy.sql.Sql
import java.sql.*
/**
 * A simple Groovy example that updates data from a database table.
 */
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")
        // create table
        sql.execute("""
            CREATE TABLE PERSON (
                 FIRST_NAME VARCHAR(50),
                 LAST_NAME VARCHAR(50)
            )""");
        // Create 1 data
        sql.execute("INSERT INTO PERSON (FIRST_NAME, LAST_NAME) values ('John', 'Doe')");
        // Output the first name
        sql.eachRow("SELECT  FIRST_NAME FROM PERSON WHERE LAST_NAME = 'Doe'") { p ->
            println "First Name Before Update= ${p.FIRST_NAME}"
        }
        // Update the first name
        sql.execute("UPDATE PERSON SET FIRST_NAME = 'JANE' WHERE LAST_NAME = 'Doe'");
        // Output the first name again
        sql.eachRow("SELECT  FIRST_NAME FROM PERSON WHERE LAST_NAME = 'Doe'") { p ->
            println "First Name After Update= ${p.FIRST_NAME}"
        }
        // close connection
        sql.close()
    }
}

Here we hardcoded the query to be "UPDATE PERSON SET FIRST_NAME = 'JANE' WHERE LAST_NAME = 'Doe'". The output will display the value of the first name before and after the update statement. Hence the console will display:

First Name Before Update= John
First Name After Update= JANE

We can also use named parameter in our update statements, here is a modified example:

package sample
import groovy.sql.Sql
import java.sql.*
/**
 * A simple Groovy example that updates data from a database table.
 */
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")
        // create table
        sql.execute("""
            CREATE TABLE PERSON (
                 FIRST_NAME VARCHAR(50),
                 LAST_NAME VARCHAR(50)
            )""");
        // Create 1 data
        sql.execute("INSERT INTO PERSON (FIRST_NAME, LAST_NAME) values ('John', 'Doe')");
        // Output the first name
        sql.eachRow("SELECT  FIRST_NAME FROM PERSON WHERE LAST_NAME = 'Doe'") { p ->
            println "First Name Before Update= ${p.FIRST_NAME}"
        }
        // Update the first name
        sql.execute("UPDATE PERSON SET FIRST_NAME = :FNAME WHERE LAST_NAME = :LNAME",
            [FNAME:'Jane', LNAME:'Doe']);
        // Output the first name again
        sql.eachRow("SELECT  FIRST_NAME FROM PERSON WHERE LAST_NAME = 'Doe'") { p ->
            println "First Name After Update= ${p.FIRST_NAME}"
        }
        // close connection
        sql.close()
    }
}

We use the parameter FNAME and LNAME in our query, and set the value using a parameter map "[FNAME:'Jane', LNAME:'Doe']". And the code will have the same output as the first example.