Use Groovy language JDBC with SQLite database

SQLite is a widely used embedded database, for example, in Android. The Groovy language’s database support makes using SQLite very simple.

Example
The Java example from the sqlite-jdbc project is implement in the Groovy language below. It simply creates a new “person” table and populates it with two rows. Then a query is run to access the data.

To run this example, download and add to the classpath the sqlite-jdbc-*.*.*.jar. I used sqlite-jdbc-3.7.2.jar from the sqlite-jdbc project. This implements a native SQLite JDBC driver. Optionally, you could use the Grape system to automatically download the dependency. I got grape to work by info on this mail thread.

@Grapes([
 @Grab(group='org.xerial',module='sqlite-jdbc',version='3.7.2'),
 @GrabConfig(systemClassLoader=true)
])

import java.sql.*
import org.sqlite.SQLite
import groovy.sql.Sql

//Class.forName("org.sqlite.JDBC")

def sql = Sql.newInstance("jdbc:sqlite:sample.db", "org.sqlite.JDBC")

sql.execute("drop table if exists person")
sql.execute("create table person (id integer, name string)")

def people = sql.dataSet("person")
people.add(id:1, name:"leo")
people.add(id:2,name:'yui')

sql.eachRow("select * from person") {  
  println("id=${it.id}, name= ${it.name}") 
}
Result

id=1, name= leo
id=2, name= yui

Sql facade
The code is very simple since the groovy.sql.Sql class provides:

“A facade over Java’s normal JDBC APIs providing greatly simplified resource management and result set handling. Under the covers the facade hides away details associated with getting connections, constructing and configuring statements, interacting with the connection, closing resources and logging errors. Special features of the facade include using closures to iterate through result sets, a special GString syntax for representing prepared statements and treating result sets like collections of maps with the normal Groovy collection methods available.” — http://groovy.codehaus.org/gapi/groovy/sql/package-summary.html

Eclipse setup
I used the Groovy Eclipse plugin. Note about using the Eclipse plugin. Grape does not work. The automatic formatting seems incorrect. I’m using plugin version 2.8.0.xx-20130215-1600-e42

Click to expand Eclipe project classpath
<?xml version="1.0" encoding="UTF-8"?>
<classpath>
	<classpathentry kind="src" path="src"/>
	<classpathentry kind="con" path="org.eclipse.jdt.launching.JRE_CONTAINER/org.eclipse.jdt.internal.debug.ui.launcher.StandardVMType/jdk1.7.0_13"/>
	<classpathentry exported="true" kind="con" path="GROOVY_SUPPORT"/>
	<classpathentry exported="true" kind="con" path="GROOVY_DSL_SUPPORT"/>
	<classpathentry kind="lib" path="lib/sqlite-jdbc-3.7.2.jar"/>
	<classpathentry kind="output" path="bin"/>
</classpath>

JavaDB?
Since this is a JDBC based example, it should also work with the JDK supplied JavaDB with just changes of the url,driver name, and the ‘flavor’ of SQL.

Click to expand JavaDB version of example
import groovy.sql.Sql

import java.sql.*

def sql = Sql.newInstance("jdbc:derby:sample.db;create=true", "org.apache.derby.jdbc.EmbeddedDriver")
sql.execute("create table person (id integer, name varchar(80))")

def people = sql.dataSet("person")
people.add(id:1, name:"leo")
people.add(id:2,name:'yui')

sql.eachRow("select * from person") {  println("id=${it.id}, name= ${it.name}") }


Links

Similar Posts:

Creative Commons License
This work is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License.

Leave a Reply

Your email address will not be published. Required fields are marked *