Groovy Coolness - How to save files to database

I had a dilemna today. As part of a code upgrade I moved the storing of some template files out of the database and over to cache store, or directly from resource. Before this we had to manually load new templates into database for each release. Gets quite messy... Anyway....

I needed to get all the files back into the database, but needed to do it automatically... Because the table was altered to store the actual file name instead of the file, it makes it quite simple to do... And here comes groovy to the rescue.

With this small amount of code, I was able to read all records in database, get the file associated to that record and store the actual file contents back to the database. Sweet :)

def db=Sql.newInstance("jdbc:db2://192.168.20.9:50000/TESTDB","foo","bar","com.ibm.db2.jcc.DB2Driver")




db.eachRow("SELECT template_name FROM form_version") { row ->

println "Reading file " + row.template_name

def f= new File("d:/testing/resources/$row.template_name")

println "Found $f"

if (f.canRead()) {

def b = f.getText("UTF-8")

db.executeUpdate "UPDATE form_version SET dcs_form_layout=$b WHERE template_name=$row.template_name"

println "All done"

}

else {

println "Cannot read file $f. This could be real bad!!"

}

}

Groovy makes database and file manipulation a real breeze. If I had used java there would be a lot of lines of code. Makes my day so much quicker!