Groovy and SQL

Today I needed to do some boring import into db for some predefined structure and decided to do it some new way, just to stretch brain a little bit. I decided to use Groovy, as it seems that it was made for such things.

GSQL is built-in feature in Groovy so it does not require any additional lib, but database driver lib is still needed.

Connecting to dabase is as easy as:

import groovy.sql.Sql
sql = Sql.newInstance("jdbc:oracle:thin:@########", "login", "password", "oracle.jdbc.driver.OracleDriver")

To iterate table it is only needed

    sql.eachRow("select * from get_data_from_table") {goal-> 
        ...
    }

And you can access table fields just as parameters.

To insert new row you just need to

    def display = sql.dataSet("add_to_table") 
    display.add(id:goal.goal_id, country:it, type:'P') 

Quick and simple, just what is needed for such kind of scripts.

My only dissapointment with Groovy today was Grape. My idea was to inject it into script and download Oracle drivers automatically, but for some reasons it didn't worked out and I didn't had time to investigate and fix it. I hope to try it next time.

import groovy.sql.Sql
sql = Sql.newInstance("jdbc:oracle:thin:@########", "login", "password", "oracle.jdbc.driver.OracleDriver")
langs = ['LV', 'LT', 'EE']

def update() { 
    def display = sql.dataSet("add_to_table") 
    sql.eachRow("select * from get_data_from_table") {goal-> 
        langs.each{ 
            display.add(id:goal.goal_id, country:it, type:'P') 
            display.add(id:goal.goal_id, country:it, type:'B') 
        } 
    }
}

update()