I got the chance today to get my hands dirty with GSQL (Groovy's Built in support for SQL), and I must say that it was rather easy. What I needed to do was connect to an embedded Derby database, parse a file, and populate the table with the results. Not to hard right. So, here is what my data looked like in the file I needed to parse:
2008/04/06,pubsubproxy,6166,4061
2008/04/07,pubsubproxy,6166,4061
2008/04/08,pubsubproxy,6170,4061
2008/04/09,pubsubproxy,6170,4061
2008/04/10,pubsubproxy,6170,4061
2008/04/11,pubsubproxy,6170,4061
2008/04/12,pubsubproxy,6170,4061
2008/04/13,pubsubproxy,6170,4061
2008/04/14,pubsubproxy,6170,4061
The best part about this was as I read each line in the file I added the data to the dataset for the table I was populating. The following shows a quick example of how to do this:
static parseData(path) {
println("parsing data for the following path: $path")
setDBSystemDir()
def loc = new File(path)
//Get an instance to the derby database
def sql = Sql.newInstance("jdbc:derby:dashboard;create=true", "org.apache.derby.jdbc.EmbeddedDriver")
//For Each line in the file, split the line based on the comma
loc.splitEachLine(',') {fields ->
def pid = fields[1]
def locResult = Double.parseDouble(fields[2])
def tloc = Double.parseDouble(fields[3])
def timeStamp = getDate(fields[0])
//Get the linesofcode dataset from the database
def ds = sql.dataSet("linesofcode")
//Add the properties from the current line in the file to the dataset.
ds.add(
PID:pid,
TIMESTAMP:timeStamp,
LOC:locResult,
TLOC:tloc,
)
}
println "Done Parsing Data"
}
def sql = Sql.newInstance("jdbc:derby:dashboard;create=true", "org.apache.derby.jdbc.EmbeddedDriver")
Next is the best part. I loop over each line in the file, splitting the line based of the "," and then dive right in poplulating my dataset with
ds.add().That was easy right? I thought so.
