Data Access Module using Groovy with Spock testing

This blog is more of a tutorial where we describe the development of a simple data access module, more for fun and learning than anything else. All code can be found here for those who don’t want to type along: https://github.com/ricston-git/tododb

As a heads-up, we will be covering the following:

• Using Groovy in a Maven project within Eclipse
• Using Groovy to interact with our database
• Testing our code using the Spock framework
• We include Spring in our tests with ContextConfiguration

A good place to start is to write a pom file as shown here. The only dependencies we want packaged with this artifact are groovy-all and commons-lang. The others are either going to be provided by Tomcat or are only used during testing (hence the scope tags in the pom). For example, we would put the jar with PostgreSQL driver in Tomcat’s lib, and tomcat-jdbc and tomcat-dbcp are already there. (Note: regarding the postgre jar, we would also have to do some minor configuration in Tomcat to define a DataSource which we can get in our app through JNDI – but that’s beyond the scope of this blog. See here for more info). Testing-wise, I’m depending on spring-test, spock-core, and spock-spring (the latter is to get spock to work with spring-test).

Another significant addition in the pom is the maven-compiler-plugin. I have tried to get gmaven to work with Groovy in Eclipse, but I have found the maven-compiler-plugin to be a lot easier to work with.

With your pom in an empty directory, go ahead and mkdir -p src/main/groovy src/main/java src/test/groovy src/test/java src/main/resources src/test/resources. This gives us a directory structure according to the Maven convention.

Now you can go ahead and import the project as a Maven project in Eclipse (install the m2e plugin if you don’t already have it). It is important that you do not mvn eclipse:eclipse in your project. The .classpath it generates will conflict with your m2e plugin and (at least in my case), when you update your pom.xml the plugin will not update your dependencies inside Eclipse. So just import as a maven project once you have your pom.xml and directory structure set up.

Okay, so our tests are going to be integration tests, actually using a PostgreSQL database. Since that’s the case, lets set up our database with some data. First go ahead and create a tododbtest database which will only be used for testing purposes. Next, put the following files in your src/test/resources:

Note, fill in your username/password:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
	xmlns:jdbc="http://www.springframework.org/schema/jdbc" xmlns:p="http://www.springframework.org/schema/p"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="
		http://www.springframework.org/schema/beans
		http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
		http://www.springframework.org/schema/context
		http://www.springframework.org/schema/context/spring-context-3.0.xsd">

	<bean id="dataSource" class="org.apache.tomcat.jdbc.pool.DataSource">
		<property name="driverClassName" value="org.postgresql.Driver" />
		<property name="url" value="jdbc:postgresql://localhost:5432/tododbtest" />
		<property name="username" value="fillin" />
		<property name="password" value="fillin" />
	</bean>
</beans>

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:jdbc="http://www.springframework.org/schema/jdbc"
	xsi:schemaLocation="
		http://www.springframework.org/schema/beans 
		http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
		http://www.springframework.org/schema/jdbc 
		http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd">

<!--Intialize the database schema with test data -->
	<jdbc:initialize-database data-source="dataSource">
	   <jdbc:script location="classpath:schema.sql"/>
	   <jdbc:script location="classpath:test-data.sql"/>
	</jdbc:initialize-database>
</beans>

DROP TABLE IF EXISTS todouser CASCADE;

CREATE TABLE todouser
(
  id SERIAL,
  email varchar(80) UNIQUE NOT NULL,
  password varchar(80),
  registered boolean DEFAULT FALSE,
  confirmationCode varchar(280),
  CONSTRAINT todouser_pkey PRIMARY KEY (id)
);

insert into todouser (email, password, registered, confirmationCode) values ('abc.j123@gmail.com', 'abc123', FALSE, 'abcdefg')
insert into todouser (email, password, registered, confirmationCode) values ('def.123@gmail.com', 'pass1516', FALSE, '123456')
insert into todouser (email, password, registered, confirmationCode) values ('anon@gmail.com', 'anon', FALSE, 'codeA')
insert into todouser (email, password, registered, confirmationCode) values ('anon2@gmail.com', 'anon2', FALSE, 'codeB')

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:jdbc="http://www.springframework.org/schema/jdbc"
	xmlns:p="http://www.springframework.org/schema/p"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="
		http://www.springframework.org/schema/beans 
		http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
		http://www.springframework.org/schema/context 
		http://www.springframework.org/schema/context/spring-context-3.0.xsd">

	<import resource="classpath:datasource.xml"/>
	<import resource="classpath:initdb.xml"/>

</beans>

Basically, testContext.xml is what we’ll be configuring our test’s context with. The sub-division into datasource.xml and initdb.xml may be a little too much for this example… but changes are usually easier that way. The gist is that we configure our data source in datasource.xml (this is what we will be injecting in our tests), and the initdb.xml will run the schema.sql and test-data.sql to create our table and populate it with data.

So lets create our test, or should I say, our specification. Spock is specification framework that allows us to write more descriptive tests. In general, it makes our tests easier to read and understand, and since we’ll be using Groovy, we might as well make use of the extra readability Spock gives us.

package com.ricston.blog.sample.model.spec;

import javax.sql.DataSource

import org.springframework.beans.factory.annotation.Autowired
import org.springframework.test.annotation.DirtiesContext
import org.springframework.test.annotation.DirtiesContext.ClassMode
import org.springframework.test.context.ContextConfiguration

import spock.lang.Specification

import com.ricston.blog.sample.model.data.TodoUser
import com.ricston.blog.sample.model.dao.postgre.PostgreTodoUserDAO


// because it supplies a new application context after each test, the initialize-database in initdb.xml is
// executed for each test/specification
@DirtiesContext(classMode=ClassMode.AFTER_EACH_TEST_METHOD)
@ContextConfiguration('classpath:testContext.xml')
class PostgreTodoUserDAOSpec extends Specification {

	@Autowired
	DataSource dataSource
	
	PostgreTodoUserDAO postgreTodoUserDAO
	
	def setup() {
		postgreTodoUserDAO = new PostgreTodoUserDAO(dataSource)
	}

	def "findTodoUserByEmail when user exists in db"() {
		given: "a db populated with a TodoUser with email anon@gmail.com and the password given below"
		String email = 'anon@gmail.com'
		String password = 'anon'

		when: "searching for a TodoUser with that email"
		TodoUser user = postgreTodoUserDAO.findTodoUserByEmail email

		then: "the row is found such that the user returned by findTodoUserByEmail has the correct password"
		user.password == password
	}
	
}

One specification is enough for now, just to make sure that all the moving parts are working nicely together. The specification itself is easy enough to understand. We’re just exercising the findTodoUserByEmail method of PostgreTodoUserDAO – which we will be writing soon. Using the ContextConfiguration from Spring Test we are able to inject beans defined in our context (the dataSource in our case) through the use of annotations. This keeps our tests short and makes them easier to modify later on. Additionally, note the use of DirtiesContext. Basically, after each specification is executed, we cannot rely on the state of the database remaining intact. I am using DirtiesContext to get a new Spring context for each specification run. That way, the table creation and test data insertions happen all over again for each specification we run.

Before we can run our specification, we need to create at least the following two classes used in the spec: TodoUser and PostgreTodoUserDAO

package com.sample.data

import org.apache.commons.lang.builder.ToStringBuilder

class TodoUser {
	long id;
	String email;
	String password;
	String confirmationCode;
	boolean registered;
	
	@Override
	public String toString() {
		ToStringBuilder.reflectionToString(this);
	}
}

package com.ricston.blog.sample.model.dao.postgre

import groovy.sql.Sql

import javax.sql.DataSource

import com.ricston.blog.sample.model.dao.TodoUserDAO
import com.ricston.blog.sample.model.data.TodoUser

class PostgreTodoUserDAO implements TodoUserDAO {
	private Sql sql

	public PostgreTodoUserDAO(DataSource dataSource) {
		sql = new Sql(dataSource)
	}

	/**
	 *
	 * @param email
	 * @return the TodoUser with the given email
	 */
	public TodoUser findTodoUserByEmail(String email) {
		sql.firstRow """SELECT * FROM todouser WHERE email = $email"""
	}
}

package com.ricston.blog.sample.model.dao;

import com.ricston.blog.sample.model.data.TodoUser;

public interface TodoUserDAO {

	/**
	 * 
	 * @param email
	 * @return the TodoUser with the given email
	 */
	public TodoUser findTodoUserByEmail(String email);

}

We’re just creating a POGO in TodoUser, implementing its toString using common’s ToStringBuilder.

In PostgreTodoUserDAO we’re using Groovy’s SQL to access the database, for now, only implementing the findTodoUserByEmail method. PostgreTodoUserDAO implements TodoUserDAO, an interface which specifies the required methods a TodoUserDAO must have.

Okay, so now we have all we need to run our specification. Go ahead and run it as a JUnit test from Eclipse. You should get back the following error message:

org.codehaus.groovy.runtime.typehandling.GroovyCastException: Cannot cast object '{id=3, email=anon@gmail.com, password=anon, registered=false, confirmationcode=codeA}' with class 'groovy.sql.GroovyRowResult' to class 'com.ricston.blog.sample.model.data.TodoUser' due to: org.codehaus.groovy.runtime.metaclass.MissingPropertyExceptionNoStack: No such property: confirmationcode for class: com.ricston.blog.sample.model.data.TodoUser
Possible solutions: confirmationCode
	at com.ricston.blog.sample.model.dao.postgre.PostgreTodoUserDAO.findTodoUserByEmail(PostgreTodoUserDAO.groovy:23)
	at com.ricston.blog.sample.model.spec.PostgreTodoUserDAOSpec.findTodoUserByEmail when user exists in db(PostgreTodoUserDAOSpec.groovy:37)

Go ahead and connect to your tododbtest database and select * from todouser;

As you can see, our confirmationCode varchar(280), ended up as the column confirmationcode with a lower case ‘c’.

In PostgreTodoUserDAO’s findTodoUserByEmail, we are getting back GroovyRowResult from our firstRow invocation. GroovyRowResult implements Map and Groovy is able to create a POGO (in our case TodoUser) from a Map. However, in order for Groovy to be able to automatically coerce the GroovyRowResult into a TodoUser, the keys in the Map (or GroovyRowResult) must match the property names in our POGO. We are using confirmationCode in our TodoUser, and we would like to stick to the camel case convention. What can we do to get around this?

Well, first of all, lets change our schema to use confirmation_code. That’s a little more readable. Of course, we still have the same problem as before since confirmation_code will not map to confirmationCode by itself. (Note: remember to change the insert statements in test-data.sql too).

One way to get around this is to use Groovy’s propertyMissing methods as show below:

	def propertyMissing(String name, value) {
		if(isConfirmationCode(name)) {
			this.confirmationCode = value
		} else {
			unknownProperty(name)
		}
	}

	def propertyMissing(String name) {
		if(isConfirmationCode(name)) {
			return confirmationCode
		} else {
			unknownProperty(name)
		}
	}

	private boolean isConfirmationCode(String name) {
		'confirmation_code'.equals(name)
	}

	def unknownProperty(String name) {
		throw new MissingPropertyException(name, this.class)
	}

By adding this to our TodoUser.groovy we are effectively tapping in on how Groovy resolves property access. When we do something like user.confirmationCode, Groovy automatically calls getConfirmationCode(), a method which we got for free when declared the property confirmationCode in our TodoUser. Now, when user.confirmation_code is invoked, Groovy doesn’t find any getters to invoke since we never declared the property confirmation_code, however, since we have now implemented the propertyMissing methods, before throwing any exceptions it will use those methods as a last resort when resolving properties. In our case we are effectively checking whether a get or set on confirmation_code is being made and mapping the respective operations to our confirmationCode property. It’s as simple as that. Now we can keep the auto coercion in our data access object and the property name we choose to have in our TodoUser.

Assuming you’ve made the changes to the schema and test-data.sql to use confirmation_code, go ahead and run the spec file and this time it should pass.

That’s it for this tutorial. In conclusion, I would like to discuss some finer points which someone who’s never used Groovy’s SQL before might not know. As you can see in PostgreTodoUserDAO.groovy, our database interaction is pretty much a one-liner. What about resource handling (e.g. properly closing the connection when we’re done), error logging, and prepared statements? Resource handling and error logging are done automatically, you just have to worry about writing your SQL. When you do write your SQL, try to stick to using triple quotes as used in the PostgreTodoUserDAO.groovy example. This produces prepared statements, therefore protecting against SQL injection and avoids us having to put ‘?’ all over the place and properly lining up the arguments to pass in to the SQL statement.

Note that transaction management is something which the code using our artifact will have to take care of.

Finally, note that a bunch of other operations (apart from findTodoUserByEmail) are implemented in the project on GitHub: https://github.com/ricston-git/tododb. Additionally, there is also a specification test for TodoUser, making sure that the property mapping works correctly. Also, in the pom.xml, there is some maven-surefire-plugin configuration in order to get the surefire-plugin to pick up our Spock specifications as well as any JUnit tests which we might have in our project. This allows us to run our specifications when we, for example, mvn clean package.

After implementing all the operations you require in PostgreTodoUserDAO.groovy, you can go ahead and compile the jar or include in a Maven multi-module project to get a data access module you can use in other applications.