groovy sql

def sql(url,user,passqord,driver,yield){
 Sql s = Sql.newInstance(url, user, password, driver)
 s.connection.autoCommit = false
 try {
  yield(s)
  s.commit()
 }
 catch (Exception e) {
  s.rollback()
  throw e
 }
 finally {
  s?.connection.close()
 }
}

def connectionString = '::'
sql("jdbc:oracle:thin:@${connectionString}",'user', 'password','oracle.jdbc.OracleDriver') { sql ->
 sql.execute("insert into qwerty(id,field1,field2) values(my_seq.nextval,?,?)",[val2,val3])
        
 sql.eachRow("select * from my_table") { println it }
 sql.eachRow("select * from my_table") { println it.field1 }
        
 def values = []
 values << Sql.DATE(new java.sql.Date(System.currentTimeMillis()))
 values << Sql.TIME(new java.sql.Date(System.currentTimeMillis()))
 values << Sql.TIMESTAMP(new java.sql.Timestamp(System.currentTimeMillis()))
 values << 'long string'
 values << Sql.DECIMAL //add out parameter
 long id;
 sql.call("""begin
  insert into qwerty(id,field_date,field_date,field_time,field_timestamp,field_clob) values(my_seq.nextval,?,?,?,?) return id into ?;
  end;""",values) {
  id = it
  println "id = ${id}"
 }
        
 def str = """call my_package.my_procedure(
   ?       /*p_param1              in number*/
  ,?      /*p_param2              in out number*/
  ,?      /*p_param3              varchar2*/
 )""".replaceAll(/\s+/, ' ')
 def params = [
   123                             /*p_param1      in number*/
  ,Sql.inout(Sql.DECIMAL(null))   /*p_param2      in out number*/
  ,'my string'                    /*p_param3      varchar2*/
 ]
 println "param in out befor call = ${params[1]}"
 sql.call(str, params)
 println "param in out after call = ${params[1]}"
}

//generate ddl script for insert
def insertRowDDL(tableName, fieldsMap) {
 def into = fieldsMap.keySet().toList().join(',')
 def str = (["?,"]*fieldsMap.size()).join()[0..-2]
 str = "insert into ${tableName}(${into}) values(${str})"
}

//create row and get id, if id auto generated by trigger, for example
def createRowAndGetId(Sql sql, tableName, fieldsMap, idFieldName) {
 def str = insertRowDDL(tableName, fieldsMap)
 def values = fieldsMap.values().toList()
 values << Sql.DECIMAL //add out parameter
 str = """begin
  ${str} return ${idFieldName} into ?;
  end;"""
 long res = 0;
 try {
  sql.call(str,values) { res = it }
 } finally { }
  return res
}

def fieldsMap = [:]
fieldsMap.field1=val1
fieldsMap.field2=val2
fieldsMap.field_time=Sql.TIME(new java.sql.Date(System.currentTimeMillis()))
fieldsMap.field_clob='long string'
println createRowAndGetId(sql, 'qwerty', fieldsMap, 'id')