Write Groovy Scripts

Many database tasks require more logic than simple SQLs. We do this using Groovy, a pure Java scripting language with closures. Groovy code can be started with the keyword groovy and ends with the first line with only a slash '/'.

Everything which works in Java works also in Groovy, with this three major improvements:

  • Multiline Strings (also called GString, started and end with """ )
  • Variables in GStrings, ${...} are replaced
  • Closures sql.eachRow(...){r->...} are similar with Java Lambda
Here is an example with Groovy:
connect db1

int days = 5
sql.eachRow( """
    SELECT personid, firstname, lastname FROM persons p WHERE p.created < sysdate-${days} AND
      EXISTS ( SELECT 1 FROM sport_club s WHERE s.personid=p.personid )
      ORDER BY personid ASC
    """.toString() ) { r ->
    println "${r.personid} ${r.firstname} ${r.lastname}"
Groovy scripts receive two objects :

  • sql is the database connection as Sql object.
  • connector is the DbConsole object for a defined connection