Use SQLite in-memory with Groovy

#groovy #sqlite

I have in the past needed to do computations with a data set that I didn't want to keep. An easy way is to create an in-memory SQLite database. This is how to do it in Groovy.

import groovy.sql.Sql
import org.sqlite.JDBC

def sql = Sql.newInstance("jdbc:sqlite::memory:", "org.sqlite.JDBC")

sql.execute("CREATE TABLE data (id INTEGER PRIMARY KEY, col2 TEXT, col3 TEXT);")

def data = sql.dataSet("data")

1.upto(5) { id ->
    data.add(id:id, col2:randNum(9999), col3:randStr(15))
}

data.add(id:6,col2:null,col3:'should be null')

sql.rows('select * from data').each { row ->
    println "${row[0]} | ${row[1]} | ${row[2]}"
}

sql.close()

Bonus - two methods that create random data, used in the example:

def randStr(length) {
    return new Random().with { (1..length)
                    .collect { (('A'..'Z')+('0'..'9')+('a'..'z'))
                .join()[ nextInt( (('A'..'Z')+('0'..'9')+('a'..'z'))
                .join().length() ) ] }
                .join() }
}

def randNum(maxSize) {
    return Math.abs(new Random().nextInt() % maxSize) + 1
}

Sample output:

1 | 1108 | il4qmhfUh1uvAmW
2 | 2707 | L3faFu2a5ISc8nc
3 | 7014 | a7JfVfwjZRIp4bW
4 | 8574 | tTL96UCbkOOyTBl
5 | 2011 | vXjH88yNLiaTvdV
6 | null | should be null

Neat! Remember: at the end of your script, your database is gone, which is what my use case required.