BLOBS with Groovy and H2 [or your database of choice – Oracle, MySQL]

I am slowly building a couple of environments and set of applications that will allow me to explore techniques, design patterns (as-well as re-learn those forgotten ones) and test drive shiny new technologies like GWT and Grails. More of a reference set for myself than anything else.

As part of this I've needed to set up a database and application server. I decided on H2 as I feel it might be useful for some other pet projects I have in mind. I grabbed a free dataset from www.freebase.com who provide open source data-sets. I massaged the structure and normalised it into a couple of tables and added the appropriate primary keys. I chose a dataset that most people could relate to, beer.

Just for fun I thought I would try storing logo's against a given beer inside the database to keep everything self contained and to see how H2 performed.

Having dipped in and out of groovy and taking time to learn the rudimentary basics I haven't found much of an opportunity to use it and it seemed a nice fit for
this as I can imagine it is something you might want to script.

The code is as follows :

import groovy.sql.Sql

println "---- A working test of writing a blob into a H2 DB ----";

sql = Sql.newInstance("jdbc:h2:tcp://localhost/~/BEER;MAX_LENGTH_INPLACE_LOB=4096;COMPRESS_LOB=LZF ", "sa","", "org.h2.Driver" );

try {
 File f = new File("/tmp/stella.jpg");
 InputStream fis = new FileInputStream(f);

 def image_type = "image/jpeg";
 def updateStatement = "update beer set image1 = ? , image1_content_type = ? where id = ? ";
 def ps = sql.connection.prepareStatement(updateStatement);
 
// Set the BLOB with the FileInputStream
 ps.setBinaryStream(1, fis, (int)f.length());
 ps.setObject(2, image_type);
 ps.setObject(3, "22")  // Pilsner - 926, Stella - 22
 ps.execute()

 sql.commit()
} catch (Exception e) {
 println "Failed: $e"
 sql.rollback()
} finally {
 sql.connection.close()
}
println("finished");

The uploaded file has some extra comments explaining whats happening but there is a few things to note:

• You can swap the jdbc url and h2 driver for those of your own database
• You need to ensure the jdbc url is correct
• The jdbc drivers must be on the class path
• The jdbc url here specifies the H2 optimisations for working with LOBs
• It is a good idea to store the mime-type in case we decide to serve this up over the web later.
• You'll need a copy of my database for this to work beer db

The sql script was a direct export of my database so you should just be able to run it from the standard web interface that comes with H2 when you start it. You can start H2 by double clicking on the H2*.jar if you have jars associated with the Java 6 runtime.

To run it should be as simple as :

$ groovy -cp "/path/to/h2-XXXXX.jar" write-blob.groovy

Files

• beer.sql
• write-blob.groovy