Connecting to PostgreSQL Database Using Groovy

Here I am posting groovy SQL code for performing the basic database operations.

I have used the PostgreSQL as the database. The classpath contains postgresql-8.4-701.jdbc4.jar

 

package com.binarynovae.gsql;

import groovy.sql.Sql;

def sql = Sql.newInstance("jdbc:postgresql://localhost:" +
		"5432/binarynovae", "postgres", "admin", 
		"org.postgresql.Driver")

/*
 * Display All the records in table Person
 */
println "All Records:"
def query = "select * from person"
sql.eachRow query, {person-> 
	println person.name + " "  + person.person_id +" " +person.age
}

/*
 * Insert a record into Person table
 */
query = "insert into person (name, person_id, age) values (?,?,?)"
def name = "Ryan"
def age = 23
def id = 1289367
sql.executeInsert query, [name, id, age];

/*
 * Display the inserted value
 */
println "Inserted Record:"
query = "select * from person where name = ?"
sql.eachRow(query,[name], {person-> 
	println person.name + " "  + person.person_id +" " +person.age
})

/*
 * Update the inserted record values
 */
query = "update person set age=? where name=?"
name = "Ryan"
age = 30
sql.executeUpdate query, [age,name]

/*
 * Display the updated value
 */
println "Updated Record:"
query = "select * from person where name = ?"
sql.eachRow(query,[name], {person-> 
	println person.name + " "  + person.person_id +" " +person.age
})

/*
 * Delete the inserted value
 */
query = "delete from person where name=?"
name = "Ryan"
sql.execute query, [name]

/*
 * Checking whether the deletion is successful
 */
println "After deletion:"
query = "select count(1) from person where name = ?"
sql.eachRow(query,[name], {row-> 
	println row[0]
})

 

Resources:

Groovy SQL