Groovy SQL Read Operation Examples

Nov 10, 2016 SQL comments

Reading data from database tables is one of the most common task in programming. For RDBMS servers that supports SQL, we can use SELECT statements for this purpose. Below are some examples on how to read data from database tables using Groovy SQL.

Read Operation

The syntax for reading data from a table using SQL is something like:

SELECT [list of columns] FROM [table]

Of course the select statements has many other features for filtering data or grouping things together. But for simplicity, let's just consider a simple select statement. Below is a simple illustration for this that reads data from a table using Groovy SQL:

package sample
import groovy.sql.Sql
import java.sql.*
/**
 * A simple Groovy example that reads 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")
        // 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]);
        }
        //Read data
        sql.eachRow('SELECT FIRST_NAME, LAST_NAME FROM PERSON') { p ->
            println "First Name = ${p[0]}, Last Name = ${p[1]}"
         }
        // close connection
        sql.close()
    }
}

To read the data, we executed the SQL query "SELECT FIRST_NAME, LAST_NAME FROM PERSON", which reads all the records in the person table and display on the console. Notice that we use index to access the columns. That is, p[0] holds the first name while p[1] holds the last name. The output is shown below.

First Name = John, Last Name = Doe
First Name = Juan, Last Name = Dela Cruz

But working with index (E.g. p[0] and p[1]) is not very friendly and prone to error. Luckily, we can refer to columns using their actual names! Here is a modified example that has the same output:

package sample
import groovy.sql.Sql
import java.sql.*
/**
 * A simple Groovy example that reads 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")
        // 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]);
        }
        //Read data
        sql.eachRow('SELECT  FIRST_NAME, LAST_NAME FROM PERSON') { p ->
            println "First Name = ${p.FIRST_NAME}, Last Name = ${p.LAST_NAME}"
         }
        // close connection
        sql.close()
    }
}

We now access the data as p.FIRST_NAME and p.LAST_NAME, which is more clear and less prone to errors.