A big chunk of my day job is on a continuously evolving large application we host. Continuously evolving and hosting together mean lots of database migrations. We've put together a wonderfully handy tool to automate database migrations. It is really just a stateful dependency resolver with convenience things for working on a database. Up until now we've supported migration steps in SQL, BeanShell, and Java. After playing with GroovySql -- we do that now too.
<step name="switch-login-and-passcode" type="groovy">
<depends name="version-4.2"/>
<script>
import groovy.sql.Sql
sql = new Sql(connection)
sql.execute("create table foo as select * from user")
sql.execute("delete from foo")
sql.queryEach("select login, password from user") {
sql.execute("insert into foo (login, password) values (?, ?)",
[it.passcode, it.login])
}
sql.queryEach("select * from foo") {
println "Funny Looking Login: ${it.login}"
}
sql.execute("drop table foo")
</script>
</step>
I really like OJB (obviously), but O/R mapping is far from the answer all the time =) Groovy SQL rocks.