This post originated from an RSS feed registered with Java Buzz by Brian McCallister. Original Post: Two Database Tarballs =) Feed Title: Waste of Time Feed URL: http://kasparov.skife.org/blog/index.rss Feed Description: A simple waste of time and weblog experiment Latest Java Buzz Posts Latest Java Buzz Posts by Brian McCallister Latest Posts From Waste of Time

So some fun code from the Hackathon, though some of it started well before. The first is a slightly more cleaned up (still not full fleshed, but it is so straightforward to do it probably isn't worth full fleshing) version of the inline groovy in Derby statements and stored procedures:

call groovy_proc('
    sql.eachRow("select id, name from something") {
        sql.execute("insert into something_else (id, value) (?, ?)")
    }
')

Or the more fun inline function:

select id, groovy_string('foo = [1, 2, 3]; foo.join(", ")') as wombat from something

Tarball is available to download. You will laugh when you see how simple it is =) I probably won't maintain this package, but it is fun code showing off some fun stuff that can be done pretty easily.

In packaging up the derby-groovy stuff, I realized I should probably share the jdbi library I used for the database access. This one will definately be maintained in an ongoing way as it is just too useful. Basically it attempts to make JDBC nice to use from a client perspective with nice stuff like:

    List rows = handle.query("select name from foo where name like 'Brian%'");
    for (Iterator i = rows.iterator(); i.hasNext();) {
        Map row = (Map)i.next();
        System.out.println(row.get("name"));
    }

Alternately, a callback style interface can be used, where the above snippet would be expressed as:

    handle.query("select name from foo where like 'Brian%'", new RowCallback() {
        public void onRow(Handle handle, Map row) {
            System.out.println(row.get("name"));
        }
    });

Results of queries come back as collections of maps (slightly non-compliant maps as the key "wubbit" is treated the same as "WUBBIT"). This is all well and nice, but sql in java strings leads to too much crap like:

    handle.query("select f.id, b.name from " +
                 "  foo f," +
                 "  bar b " + 
                 "where " + 
                 "  f.bar_id = bar.id and" +
                 "  f.wubbit like ?", new Integer(3));

Which is a nightname to edit and tweak, so it supports named, and externalized named sql, where you can plop

select f.id, b.name
from
    foo f inner join bar b using (f.bar_id = b.id)
where
    f.wubbit like :wubbit

in a file named wubbitses.sql somewhere on the classpath and then execute via handle.query("wubbitses", new Integer(3)). It's more flexible 1000 than that too, but that is a common case. Oh yeah, can do handle.script("many-wubbitses") to have it load and execute a script, with lots of statements, from the classpath.

Oh yeah, named parameters (note the :wubbit), decent statement caching (loaded files, named statements, etc). Lots of people have implemented this kind of "make sql in java convenient" -- most anyone that has had to do tabular stuff. This happens to be mine.

Have fun with it, if you try it. Feedback, flames, and patches appreciated. This library will definately be maintained going forward. The tarball includes lots of things for testing, but the jdbi-1.0.jar has no runtime dependencies on anything but the jvm. Javadocs are available as well.