Working with Databases in Groovy

Building a simple ORM framework

The groovy.sql.Sql class meets the needs of querying and modifying data stored in a relational database. Still, as the name implies, this class requires knowledge of the SQL language and has a strong relationship with the verbosity of the Java's JDBC API.

Wouldn't be great if we could access and insert data into a database table without writing a single line of SQL? The groovy.sql.DataSet class can make that happen.

In this recipe, we are going to cover a simple approach to building a database mapping solution using Groovy facilities.

Getting ready

For this recipe, we are going to create a new table, named EMPLOYEE. Create a new script, named orm.groovy and add the following code:

import static DBUtil.* import groovy.sql.Sql import groovy.sql.DataSet

class Person { Integer id String name String lastName Integer age

Integer department

}

def server = startServer()

def sql = Sql.newInstance(dbSettings)

sql.execute('''CREATE TABLE EMPLOYEE ( ID INTEGER PRIMARY KEY,

NAME VARCHAR(100), LASTNAME VARCHAR(100), AGE INTEGER, DEPARTMENT INTEGER)''')

Similarly to the other recipes in this chapter, we make use of the DBUtil class. Therefore, the script must be invoked by specifying the location of DBUtil.groovy in the classpath, as follows:

groovy -cp /path/to/script/folder orm.groovy

244

www.it-ebooks.info

Chapter 7

The script also contains the definition of the class Person that we will map to the

EMPLOYEE table.

How to do it...

Let's start adding some code after the table creation statement:

1.This is how to insert data into a table without (explicitly) issuing an SQL statement:

def persons = new DataSet(sql, 'EMPLOYEE') person.add(name: 'John',

lastname: 'Willis', id: 1,

age: 40, DEPARTMENT: 100)

person.add(name: 'Alfred', lastname: 'Morris', id: 2,

age: 50, DEPARTMENT: 101)

person.add(name: 'Mickey', lastname: 'Rourke', id: 3,

age: 30, DEPARTMENT: 101)

person.add(name: 'Santo', lastname: 'Mongo', id: 4,

age: 45, DEPARTMENT: 101)

2.This is how to fetch the data from the database using Dataset:

persons.each {

println "employee ${it.id} - ${it.name} ${it.lastname}"

}

This will yield something similar to the following:

console employee 1 - John Willis employee 2 - Alfred Morris

How it works...

The DataSet represents a list, in which each element is a map of key/value pairs, matching the corresponding table row. Row data can be easily accessed through property names.

The name of the property is case-insensitive, but must match the original column name.

The DataSet class issues the relevant SQL statements only when the each method is invoked.

245

www.it-ebooks.info

Working with Databases in Groovy

Here is an example of using the DataSet class with some WHERE conditions:

persons.findAll { it.age > 45 }.each { println it

}

The output may be as follows:

>[ID:2, NAME:Alfred, LASTNAME:Morris, AGE:50, DEPARTMENT:101] >[ID:4, NAME:Santo, LASTNAME:Mongo, AGE:45, DEPARTMENT:101]

In this case, the findAll produces an SQL statement that reflects the expression within the closure. This generated statement is encapsulated in the returned persons data set.

There's more...

Filters inside the Dataset can be combined to create more powerful query conditions:

persons.findAll { it.department == 101 && it.age < 50 }

.sort { it.lastname }

.each { println it

}

The output may be as follows:

>[ID:4, NAME:Santo, LASTNAME:Mongo, AGE:45, DEPARTMENT:101] >[ID:3, NAME:Mickey, LASTNAME:Rourke, AGE:30, DEPARTMENT:101]

Did you spot the sort? As for any other collection in Groovy you can indeed sort the result.

See also

ff http://groovy.codehaus.org/api/groovy/sql/DataSet.html

Using Groovy to access Redis

Redis is an open source data structure server with an in-memory data set. It is called a data structure server, and not simply a key/value store, because Redis implements data structures allowing keys to contain binary safe strings, hashes, sets and sorted sets, as well as lists. This combination of flexibility and speed makes Redis the ideal tool for many applications.

Redis has incredible performance, due to the in-memory data set, but it is still possible to persist the data either by saving a snapshot of the data set to disk once in a while or appending each command to a log.

246

www.it-ebooks.info

Chapter 7

Redis also supports trivial-to-setup master/slave replication, with very fast non-blocking synchronization, auto reconnection on network split and so forth.

Redis first started in early 2009 as a key value store developed by Salvatore Sanfilippo in order to improve the performance of his own LLOOGG, an analytics product. Redis grew in popularity after getting support from people and companies in the developer world and has since been supported by VMWare, who hired Salvatore and Pieter Noordhuis to work full-time on the project.

This recipe will present a way of accessing a Redis data store with the help of existing libraries and Groovy.

Getting ready

In this recipe, we are going to take a look at the most common commands and data structures available in Redis.

Redis is easy to install. The recommended way to install Redis is by compiling the sources, as it doesn't have any external dependencies. Redis sources can be downloaded from the official

website (http://redis.io) or directly from GitHub (https://github.com/antirez/ redis). Build instructions can be found at the Redis quick start page (http://redis.io/

topics/quickstart).

Alternatively, you can download the binaries (including Windows binaries) directly from the Redis download page (http://redis.io/download).

This recipe assumes that you have an instance of Redis 2.6 or higher, up and running on your development machine.

The Redis communication protocol is quite simple. A client needs to open a TCP/IP connection to the server and issue a Redis command terminated by \r\n (CR LF).

The general form is as follows:

*<number of arguments> CR LF

$<number of bytes of argument 1> CR LF <argument data> CR LF

..

$<number of bytes of argument N> CR LF <argument data> CR LF

Dealing with Redis at such a low level is not ideal. In order to simplify working with the commands, several clients have been implemented in the last years for almost every possible language.

There is no pure Groovy client at the moment, but there are several Java clients available. The one we are going to use in this recipe is Jedis, developed by Jonathan Leibiusky and considered the standard Java client for Redis.

247

www.it-ebooks.info

Working with Databases in Groovy

Jedis can be downloaded directly from the project's GitHub page (https://github.com/ xetorthio/jedis) or referenced as dependency with Grape.

For a complete list of Redis clients, please, refer to the official client page on the Redis website (http://redis.io/clients).

How to do it...

Let's see how we can access a Redis instance.

1.As usual while dealing with external libraries in Groovy, the first step is to declare the

Jedis dependency via Grape:

@Grab('redis.clients:jedis:2.1.0') import redis.clients.jedis.*

2.The Jedis API is essentially a mirror of the Redis commands (http://redis.io/ commands). It is exposed by the Jedis class, which can be instantiated by passing the host of the Redis server:

def jedis = new Jedis('localhost')

Jedis connects to the Redis default port, 6379. Should Redis run on a nonstandard port, you can pass the port value too:

def jedis = new Jedis('localhost', 9000)

3.When the client is successfully connected, it's possible to issue the commands to store and retrieve the values:

jedis.set('foo', 'bar')

String value = jedis.get('foo') assert value == 'bar'

4.To increment a value fire this snippet:

jedis.set('counter', '1') jedis.incr('counter') jedis.incr('counter')

assert jedis.get('counter') == '3'

5.To set expiration period for a key you call the expire method passing the key and the number of seconds for the key to live:

jedis.set('short lived', '10') jedis.expire('short lived', 3) Thread.sleep(3000)

assert jedis.exists('short lived') == false

248

www.it-ebooks.info

Chapter 7

6.One of the appealing features of Redis is the presence of proper data structures inside the key store, such as lists, sets, hashes, and sorted sets. Lists are used to store an (ordered) collection of items:

jedis.rpush('myList', 'a', 'b', 'c') assert 3 == jedis.llen('myList')

assert '1' == jedis.lrange('myList', 0,0)[0]

jedis.lpush('myList', '3', '2', '1') assert 6 == jedis.llen('myList')

7.Stacks and queues can be very easily modeled with lists, using the rpop method, which gets and removes the last element of a list:

jedis.del('myQueue') jedis.lpush('myQueue', 'new york') jedis.lpush('myQueue', 'dallas') jedis.lpush('myQueue', 'tulsa')

assert 'new york' == jedis.rpop('myQueue') assert 'dallas' == jedis.rpop('myQueue') assert 'tulsa' == jedis.rpop('myQueue')

How it works...

Each method call of the Jedis API is translated into an actual Redis command. For example, the INCR command, called in the step 4, atomically increments an integer value. It is important to note that this is a string operation, because Redis does not have a dedicated integer type. The EXPIRE command, used in step 5, defines the amount of seconds a key is allowed to "live" before it expires.

Other commands that we demonstrated in the previous examples do the following:

ff The RPUSH command appends values at the start of a list.

ff The LRANGE command returns the specified elements from a list. ff The LPUSH command appends values at the end of a list.

ff The RPOP command returns and removes the last value from a list.

There's more...

One of the reasons Redis is a very popular caching solution for large web applications is the presence of hashes. A hash is essentially a map storing string fields and string values: the perfect data type for storing objects.

249

www.it-ebooks.info

Working with Databases in Groovy

So, let's start by creating a simple User bean.

class User { Long userid

String username String password

}

The User bean is a typical domain object that we want to store in Redis. Groovy has a very handy method exposed on the Object class, getProperties, which returns a Map of the object's properties. The key/value structure is exactly what we need to store the domain object in Redis.

User u = new User() u.userid = 2001 u.username = 'john' u.password = '12345'

After creating an instance of the User class, we proceed to store it in Redis by calling the HMSET command.

jedis.hmset( "user:$u.userid", u.properties.findAll {

!['class', 'userid'].contains(it.key) }.collectEntries { k,v ->

[k, v.toString()]

}

)

The hmset method accepts a String (the key) and a Map, which contains the key/values we want to store. Logically, the key of the map is the primary key of the User, so we set it as user:

$u.userid.

The getProperties method returns in the Map also the actual class of the object on which we call the method on (in our case, User). We don't need to store it, so we filter it out along with the userid key, which would be redundant.

Finally, the collectEntries method, invoked on the filtered Map returns a new Map where all the keys are Strings, as this is the only data type Redis (and Jedis) accepts.

By calling the HGETALL command:

println jedis.hgetAll("user:$u.userid")

250

www.it-ebooks.info

Chapter 7

We get the following output:

> [username:john, password:12345]

One more Redis aspect we are going to touch is atomic operations. Redis doesn't support transactions in the same way as a relational database does (no rollbacks), but it is still possible to execute a number of commands atomically. Enter the MULTI and EXEC commands. The MULTI command marks the beginning of a transaction, in which the following commands are queued and executed atomically.

This is how a MULTI command works when executed in the Redis CLI (Command Line Interface):

>MULTI

OK

>INCR foo QUEUED

>INCR bar QUEUED

>EXEC

Jedis does support the MULTI command, and we can actually implement it in a more Groovy way.

The following atomic method allows executing a group of commands inside the MULTI/EXEC transaction.

jedis = new Jedis('localhost') def atomic(Closure c) {

def results = null try {

def tx = jedis.multi() c.delegate = tx c.call()

results = tx.exec() } catch(e) {

tx.discard()

}

results

}

251

www.it-ebooks.info