DEV Community

ouryperd
ouryperd

Posted on

4 3

Use SQLite in-memory with Groovy

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()

Enter fullscreen mode Exit fullscreen mode

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
}
Enter fullscreen mode Exit fullscreen mode

Sample output:

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

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

Top comments (0)

👋 Kindness is contagious

Engage with a wealth of insights in this thoughtful article, valued within the supportive DEV Community. Coders of every background are welcome to join in and add to our collective wisdom.

A sincere "thank you" often brightens someone’s day. Share your gratitude in the comments below!

On DEV, the act of sharing knowledge eases our journey and fortifies our community ties. Found value in this? A quick thank you to the author can make a significant impact.

Okay