Groovy SQL Delete Operation Examples

Nov 10, 2016 SQL comments

When a record in a database table is not needed anymore then we may want to delete it. There are two ways of deleting a record. One is called logical delete where the record is still in the table but a marker was set in one of the column to tell the application that uses the table to ignore it. The second one is called physical delete where we really delete the record in the databases. SQL provides the delete statement for physical delete. Below are examples on how to perform delete operation using Groovy SQL.

Delete Operation

The SQL DELETE statement syntax follows the format:

DELETE FROM [table] WHERE [CONDITION]

This statement will delete all records in the database table that satisfies the given where condition. Below is a simple example for this:

package sample
import groovy.sql.Sql
import java.sql.*
/**
 * A simple Groovy example that deletes 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 number of records
        sql.eachRow("SELECT  COUNT(*) FROM PERSON") { p ->
            println "Number of records before delete = ${p[0]}"
        }
        // Delete all person with last name Doe
        sql.execute("DELETE FROM PERSON WHERE LAST_NAME = 'Doe'");
        // Output the number of records again
        sql.eachRow("SELECT  COUNT(*) FROM PERSON") { p ->
            println "Number of records after delete = ${p[0]}"
        }
        // close connection
        sql.close()
    }
}

We used the query "DELETE FROM PERSON WHERE LAST_NAME = 'Doe'" to delete one record from the database table. We inserted one record to the table and printed the number of rows in the table before and after the delete statement. Hence the output will be:

Number of records before delete = 1
Number of records after delete = 0

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

package sample
import groovy.sql.Sql
import java.sql.*
/**
 * A simple Groovy example that deletes 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 number of records
        sql.eachRow("SELECT  COUNT(*) FROM PERSON") { p ->
            println "Number of records before delete = ${p[0]}"
        }
        // Delete all person with last name Doe
        sql.execute("DELETE FROM PERSON WHERE LAST_NAME = :LNAME", [LNAME:'Doe']);
        // Output the number of records again
        sql.eachRow("SELECT  COUNT(*) FROM PERSON") { p ->
            println "Number of records after delete = ${p[0]}"
        }
        // close connection
        sql.close()
    }
}

We used the parameter LNAME in our query to denote we are passing a value through this name. Then we pass the map "[LNAME:'Doe']" that contains the actual value. And the code will have the same output as the first example.