#yokohamagroovy #30

build.gradle

buildscript {
  repositories {
    jcenter()
  }
  dependencies {
    classpath 'org.apache.derby:derby:10.11.1.1'
  }
}

def loader = GroovyObject.class.classLoader
buildscript.configurations.classpath.each { f -> loader.addURL(f.toURL()) }

import groovy.sql.*

task groovySqlWithDerby << {
  String url = 'jdbc:derby:memory:testdb;create=true'
  String user = 'user'
  String pass = 'password'
  String driver = 'org.apache.derby.jdbc.EmbeddedDriver'

  def db = Sql.newInstance(url, user, pass, driver)

  db.execute('''create table person (
    id integer not null primary key,
    firstname varchar(20),
    lastname varchar(20),
    location_id integer,
    location_name varchar(30)
  )''')

  def person = [id: 1, first: 'abc', last: 'xyz', locId: 10, locName: 'Yokohama']
  db.execute("""
    insert into person (id, firstname, lastname, location_id, location_name)
    values (${person.id}, ${person.first}, ${person.last}, ${person.locId}, ${person.locName})
  """)

  db.dataSet('person').each { println it }
}