hsqldb demo example with groovy

hsqldb_demo.groovy

@Grab('org.hsqldb:hsqldb:2.3.3')
@GrabConfig(systemClassLoader=true)

import groovy.sql.*

def sql = Sql.newInstance('jdbc:hsqldb:mem:yourDB', 'org.hsqldb.jdbcDriver')

sql.execute '''
  CREATE TABLE IF NOT EXISTS Author (
    id          INTEGER GENERATED BY DEFAULT AS IDENTITY,
    firstname   VARCHAR(64),
    lastname    VARCHAR(64)
  );
'''

sql.execute '''
  INSERT INTO Author (firstname, lastname)VALUES ('Paul', 'KING');
  INSERT INTO Author (firstname, lastname) VALUES ('Graeme', 'ROCHER');
'''

sql.query('SELECT firstname, lastname FROM Author') { resultSet ->
  while (resultSet.next()) {
    def first = resultSet.getString(1)
    def last = resultSet.getString('lastname')
    println( "$first $last" )
  }
}

println( sql.firstRow('SELECT COUNT(*) AS num FROM Author').num )

sql.eachRow('SELECT * FROM Author;'){def author ->
    println(author.firstname)
}


sql.execute 'DELETE FROM Author;'


sql.close()