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 CruzBut 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.