Groovy - Database

Groovy’s groovy-sql module provides a higher-level abstraction over the current Java’s JDBC technology. The Groovy sql API supports a wide variety of databases, some of which are shown below.

• HSQLDB
• Oracle
• SQL Server
• MySQL
• MongoDB

In our example, we are going to use MySQL DB as an example. In order to use MySQL with Groovy, the first thing to do is to download the MySQL jdbc jar file from the mysql site. The format of the MySQL will be shown below.

mysql-connector-java-5.1.38-bin

Then ensure to add the above jar file to the classpath in your workstation.

Database Connection

Before connecting to a MySQL database, make sure of the followings −

• You have created a database TESTDB.
• You have created a table EMPLOYEE in TESTDB.
• This table has fields FIRST_NAME, LAST_NAME, AGE, SEX and INCOME.
• User ID "testuser" and password "test123" are set to access TESTDB.
• Ensure you have downloaded the mysql jar file and added the file to your classpath.
• You have gone through MySQL tutorial to understand MySQL Basics

The following example shows how to connect with MySQL database "TESTDB".

import java.sql.*; 
import groovy.sql.Sql 

class Example {
   static void main(String[] args) {
      // Creating a connection to the database
      def sql = Sql.newInstance('jdbc:mysql://localhost:3306/TESTDB', 
         'testuser', 'test123', 'com.mysql.jdbc.Driver')
   
      // Executing the query SELECT VERSION which gets the version of the database
      // Also using the eachROW method to fetch the result from the database
   
      sql.eachRow('SELECT VERSION()'){ row ->
         println row[0]
      }
  
      sql.close()  
   } 
} 

While running this script, it is producing the following result −

5.7.10-log 
The Sql.newInstance method is used to establish a connection to the database.

Creating Database Table

The next step after connecting to the database is to create the tables in our database. The following example shows how to create a table in the database using Groovy. The execute method of the Sql class is used to execute statements against the database.

import java.sql.*; 
import groovy.sql.Sql 

class Example { 
   static void main(String[] args) {
      // Creating a connection to the database
      def sql = Sql.newInstance('jdbc:mysql://localhost:3306/TESTDB', 'testuser',  
         'test123', 'com.mysql.jdbc.Driver')
   
      def sqlstr = """CREATE TABLE EMPLOYEE ( 
         FIRST_NAME CHAR(20) NOT NULL,
         LAST_NAME CHAR(20),
         AGE INT,
         SEX CHAR(1),
         INCOME FLOAT )""" 
       
      sql.execute(sqlstr);
      sql.close() 
   } 
}

Insert Operation

It is required when you want to create your records into a database table.

Example

The following example will insert a record in the employee table. The code is placed in a try catch block so that if the record is executed successfully, the transaction is committed to the database. If the transaction fails, a rollback is done.

import java.sql.*; 
import groovy.sql.Sql 

class Example {
   static void main(String[] args) { 
      // Creating a connection to the database
      def sql = Sql.newInstance('jdbc:mysql://localhost:3306/TESTDB', 'testuser', 
         'test123', 'com.mysql.jdbc.Driver')
   
      sql.connection.autoCommit = false
  
      def sqlstr = """INSERT INTO EMPLOYEE(FIRST_NAME,
         LAST_NAME, AGE, SEX, INCOME) VALUES ('Mac', 'Mohan', 20, 'M', 2000)""" 
      try {
         sql.execute(sqlstr);
         sql.commit()
         println("Successfully committed") 
      }catch(Exception ex) {
         sql.rollback()
         println("Transaction rollback") 
      }
  
      sql.close()
   } 
}

Suppose if you wanted to just select certain rows based on a criteria. The following codeshows how you can add a parameter placeholder to search for values. The above example can also be written to take in parameters as shown in the following code. The $ symbol is used to define a parameter which can then be replaced by values when the sql statement is executed.

import java.sql.*; 
import groovy.sql.Sql
 
class Example {
   static void main(String[] args) {
      // Creating a connection to the database
      def sql = Sql.newInstance('jdbc:mysql://localhost:3306/TESTDB', 'testuser', 
         'test123', 'com.mysql.jdbc.Driver')
   
      sql.connection.autoCommit = false  
      
      def firstname = "Mac"
      def lastname ="Mohan"
      def age = 20
      def sex = "M"
      def income = 2000  
  
      def sqlstr = "INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME, AGE, SEX, 
         INCOME) VALUES " + "(${firstname}, ${lastname}, ${age}, ${sex}, ${income} )"
   
      try {
         sql.execute(sqlstr);
         sql.commit()
         println("Successfully committed") 
      } catch(Exception ex) {
         sql.rollback()
         println("Transaction rollback")
      }
  
      sql.close()
   }
}

READ Operation

READ Operation on any database means to fetch some useful information from the database. Once our database connection is established, you are ready to make a query into this database.
The read operation is performed by using the eachRow method of the sql class.

Syntax

eachRow(GString gstring, Closure closure) 

Performs the given SQL query calling the given Closure with each row of the result set.
Parameters

• Gstring − The sql statement which needs to be executed.
• Closure − The closure statement to process the rows retrived from the read operation. Performs the given SQL query calling the given Closure with each row of the result set.

The following code example shows how to fetch all the records from the employee table.

import java.sql.*; 
import groovy.sql.Sql
 
class Example {
   static void main(String[] args) {
      // Creating a connection to the database
      def sql = Sql.newInstance('jdbc:mysql://localhost:3306/TESTDB', 'testuser', 
         'test123', 'com.mysql.jdbc.Driver')  
   
      sql.eachRow('select * from employee') {
         tp -> 
         println([tp.FIRST_NAME,tp.LAST_NAME,tp.age,tp.sex,tp.INCOME])
      }  
  
      sql.close()
   } 
}

The output from the above program would be −

[Mac, Mohan, 20, M, 2000.0]

Update Operation

UPDATE Operation on any database means to update one or more records, which are already available in the database. The following procedure updates all the records having SEX as 'M'. Here, we increase AGE of all the males by one year.

import java.sql.*; 
import groovy.sql.Sql 

class Example {
   static void main(String[] args){
      // Creating a connection to the database
      def sql = Sql.newInstance('jdbc:mysql://localhost:3306/TESTDB', 'testuser', 
         'test@123', 'com.mysql.jdbc.Driver')
   
      sql.connection.autoCommit = false
      def sqlstr = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = 'M'" 
   
      try {
         sql.execute(sqlstr);
         sql.commit()
         println("Successfully committed")
      }catch(Exception ex) {
         sql.rollback() 
         println("Transaction rollback")
      }
  
      sql.close()
   } 
}

DELETE Operation

DELETE operation is required when you want to delete some records from your database. Following is the procedure to delete all the records from EMPLOYEE where AGE is more than 20.

import java.sql.*; 
import groovy.sql.Sql 

class Example {
   static void main(String[] args) {
      // Creating a connection to the database
      def sql = Sql.newInstance('jdbc:mysql://localhost:3306/TESTDB', 'testuser', 
         'test@123', 'com.mysql.jdbc.Driver')
   
      sql.connection.autoCommit = false
      def sqlstr = "DELETE FROM EMPLOYEE WHERE AGE > 20"
   
      try {
         sql.execute(sqlstr);
         sql.commit()
         println("Successfully committed")
      }catch(Exception ex) {
         sql.rollback()
         println("Transaction rollback")
      }
   
      sql.close()
   } 
}

Performing Transactions

Transactions are a mechanism that ensures data consistency. Transactions have the following four properties −

• Atomicity − Either a transaction completes or nothing happens at all.
• Consistency − A transaction must start in a consistent state and leave the system in a consistent state.
• Isolation − Intermediate results of a transaction are not visible outside the current transaction.
• Durability − Once a transaction was committed, the effects are persistent, even after a system failure.

Here is a simple example of how to implement transactions. We have already seen this example from our previous topic of the DELETE operation.

def sqlstr = "DELETE FROM EMPLOYEE WHERE AGE > 20" 
 
try {
   sql.execute(sqlstr); 
   sql.commit()
   println("Successfully committed") 
}catch(Exception ex) {
   sql.rollback()
   println("Transaction rollback") 
} 
sql.close()

Commit Operation

The commit operation is what tells the database to proceed ahead with the operation and finalize all changes to the database.
In our above example, this is achieved by the following statement −

sql.commit()

Rollback Operation

If you are not satisfied with one or more of the changes and you want to revert back those changes completely, then use rollback method. In our above example, this is achieved by the following statement −

sql.rollback()

Disconnecting Databases

To disconnect Database connection, use the close method.

sql.close()