Archive for the ‘Groovy’ Category

SQLite in Groovy crash course

Tuesday, October 16th, 2012

When creating a small Groovy project lately, I required a good data storage, and plain old text files seemed just too damn inflexible. Yet the project was very small and command-line based, and was supposed to be portable, so installing a full database engine seemed even more inflexible. It was great to find out that using SQLite in Groovy can really be perhaps even more simple than text files.

So here is a basic crash course, the code is so simple and plain that it speaks for itself:

First you will need the SQLite driver. The latest library for this I could find was the Xerial SQLiteJDBC. Based on my browsing around, this is the one that should be used nowadays (end of 2012) for SQLite in Java. But the usage is very simple: just download the .jar (mine was called sqlite-jdbc-3.7.2.jar) and add it to the project.

This .jar somehow magically contains native libraries for Windows, Mac OS X, Linux, and automatically knows when to use each, to get the best performance. If you are on another platform, it will use the pure Java implementation.

You can add the .jar to your project in any way you want, but I’ve found that for a cli groovy script, the easiest way is just to put it in the same folder as the script and add this in the beginning of the code:

this.getClass().classLoader.rootLoader.addURL(
    new File("sqlite-jdbc-3.7.2.jar").toURL())

That’s it, you are ready to use the SQLite features:

Initialize:

import groovy.sql.Sql
def sql = Sql.newInstance( 'jdbc:sqlite:databasefile.sqlite', 
  'org.sqlite.JDBC' )

Replace the “databasefile.sqlite” with whatever name you want.

Check if a table exists:

def metadata = sql.connection.getMetaData()
def tables = metadata.getTables(null, null, "tablename", null)
if (!tables.next()) {
    // table does not exist
} else {
    // table exists.
}

Run SQL:

sql.execute("CREATE TABLE  .......  ")

Select data:

sql.rows("select * from .....").each{
 println(it)
 }

Basically just the same syntax as the rest of SQL operations in Groovy.