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 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.