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 = 0We 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.