"The more difficulties one has to encounter, within and without, the more significant and the higher in inspiration his life will be" - Horace Bushnell   |    "The more you learn, the more you need to learn." - Robert Heinlein   |    "Good luck' follows careful preparation; 'bad luck' comes from sloppiness." - Robert Heinlein   |    "Every artist was first an amateur." - Ralph Waldo Emerson   |    "We are what we repeatedly do. Excellence, therefore, is not an act but a habit." - Aristotle   |    "Nothing can stop the man with the right mental attitude from achieving his goal; nothing on earth can help the man with the wrong mental attitude." - Thomas Jefferson   |    "Nothing will ever be attempted if all possible objections must first be overcome." - Samuel Johnson   |    "There is no such thing as luck; there is only adequate or inadequate preparation to cope with a statistical universe." - Robert Heinlein   |    "If Java had true garbage collection, most programs would delete themselves upon execution." - Robert Sewell   |    "Perl - The only language that looks the same before and after RSA encryption." - Keith Bostic   |    Hofstadter's Law: It always takes longer than you expect, even when you take into account Hofstadter's Law.   |    "The talent of success is nothing more than doing what you can do, well." - Henry W. Longfellow   |    "Success is the sum of small efforts, repeated day in and day out." - Robert Collier   |    "Nothing great was ever achieved without enthusiasm." - Ralph Waldo Emerson   |    "We are still masters of our fate. We are still captains of our souls." - Winston Churchill   |    "The big secret in life is that there is no big secret. Whatever your goal, you can get there if you're willing to work." - Oprah Winfrey   |    "Take calculated risks. That is quite different from being rash." - George S. Patton   |    "Our doubts are traitors, and make us lose the good we oft might win, by fearing to attempt." - William Shakespeare   |   

Code Reactor

The best of modern Web development

SQLite in Groovy crash course

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:

    new File("sqlite-jdbc-3.7.2.jar").toURL())

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


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{

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

Leave a Reply

You must be logged in to post a comment.

Your connection appears to be too slow, automatically disabling HeavyAjax (TM) for better performance...
You seem to run a browser without JavaScript support or it has been disabled. To fully experience Code Reactor please enable JavaScript. (It is not 1995 anymore :)
You seem to be using Internet Explorer. If you want to experience both Code Reactor and the rest of the web to their fullest and fastest, you are advised to download and install a real browser, like Opera, Firefox or Google Chrome.
You seem not only to use Internet Explorer, which is by far a joke when it comes to browsers, but to even use an old version of it!
If you want to experience both Code Reactor and the rest of the web to their fullest and fastest, you are STRONGLY advised to download and install a real browser, like Opera, Firefox or Google Chrome.