Embed Derby DB in Groovy

       
 @Grapes([
        @Grab(group='org.apache.derby', module='derby', version='10.10.1.1'),
      @GrabConfig(systemClassLoader=true)
 ])
 
import groovy.sql.*
import java.sql.*
import java.sql.DriverManager

protocol = 'jdbc:derby:'
def props = new Properties()
props.put('user', 'user1')
props.put('password', 'user1')


DriverManager.registerDriver(new org.apache.derby.jdbc.EmbeddedDriver())
//DriverManager.registerDriver()
def sql = Sql.newInstance("${protocol}derbyDB;create=true", props)

/* Creating table, adding few lines, updating one */
sql.execute('create table people(id int, name varchar(40), second_name varchar(40), phone varchar(30), email varchar(50))')

println("Created table 'people'")

sql.execute("insert into people values (1,'John', 'Doe', '123456','johndoe@company.com')")
sql.execute("insert into people values (2,'Bill', 'Brown', '324235','billbrown@company.com')")
sql.execute("insert into people values (3,'Jack', 'Daniels', '443323','jackdaniels@company.com')")

println('Inserted people')

sql.execute("update people set phone='443322', second_name='Daniel''s'where id=3")

println('Updated person')

/* Simple query */
def rows = sql.rows('SELECT * FROM people ORDER BY id')
rows.each {println it}

/* Dropping table 'people' */
sql.execute('drop table people')
println ("Table 'people' dropped")

try{
DriverManager.getConnection('jdbc:derby:;shutdown=true')
}
catch (SQLException se){
   gotSQLExc = true
}

println('Finish!')