This is the last part in this little series about testing of database code.
In the first part I extracted the session handling for the tests into a JUnit Rule.
In the second part I introduced ObjectMothers for easy creation of instances in the database.
In this part I'll simplify the implementation of new ObjectMothers by extracting two superclasses and I'll finish by sketching some further ideas for further development.

Lets have a look at the SuperHeroMother

public class SuperHeroMother {
private final Session session;

private String secretIdentity = "Mr. Jones";
private String name = "Name";
private String weakness = "None";
private SuperPower power = null;

public SuperHeroMother(Session s) {
session = s;
power = new SuperPowerMother(session).instance();
}

public SuperHero instance() {
SuperHero hero = loadInstance();
if (hero == null) {
hero = createInstance();
}
hero.power = power;
hero.weakness = weakness;
hero.secretIdentity = secretIdentity;
session.save(hero);
return hero;
}

private SuperHero loadInstance() {
return (SuperHero) session.createCriteria(SuperHero.class)
.add(Restrictions.eq("name", name)).uniqueResult();

}

private SuperHero createInstance() {
SuperHero hero = new SuperHero();
hero.name = name;
return hero;
}

public SuperHeroMother name(String aName) {
name = aName;
return this;
}

public SuperHeroMother secretIdentity(String aSecretIdentity) {
secretIdentity = aSecretIdentity;
return this;
}

public SuperHeroMother power(SuperPower aPower) {
power = aPower;
return this;
}

public SuperHeroMother weaknes(String aWeakness) {
weakness = aWeakness;
return this;
}
}

There is still quite some code wich will get repeated over and over again for each ObjectMother. So lets extract some of the commonalities into a superclass ObjectMother:

/** create instances of type T */
public abstract class ObjectMother {
private final Session session;

public ObjectMother(Session s) {
session = s;
}

/** returns an instance based on the configuration of this object mother */
public T instance() {
T t = loadInstance(session);
if (t == null)
t = createInstance();
configureInstance(t);
session.save(t);
return t;
}

/**
* configure the instance t according to the configuration of this
* ObjectMother
*/
abstract protected void configureInstance(T t);

/**
* try to load an instance based on the alternate key. Returns null if no
* such instance exists
*/
abstract protected T loadInstance(Session session);

/**
* create a fresh instance with the alternate key set according to the
* configuration of this ObjectMother
*/
abstract protected T createInstance();
}

ObjectMother uses the Template Method Pattern in order to coordinate the provisioning of intances: try to load a matching instance from the database, if this doesn't work, create it from scratch, configure all attributes of the instance, store it in the database and return it. The loading, creating and configuring needs to get implemented in subclasses.

For the three entities used in this series we can extract even more code from the various ObjectMothers because loading the instance from the database works always in exactly the same way. But this is only the case because we have an alternate key based on a single attribute. This will be often the case but not always, so I don't want to tie this into the ObjectMother. Instead I'll create another super class for this special kind of ObjectMother: SingleAlternateKeyObjectMother

public abstract class SingleAlternateKeyObjectMother>
extends ObjectMother {

private final Class objectType;
private final String alternateKeyName;
private A alternateKey;

public SingleAlternateKeyObjectMother(Session s, Class theObjectType,
A defaultAlternateKey, String theAlternateKeyName) {
super(s);
objectType = theObjectType;
alternateKeyName = theAlternateKeyName;
alternateKey(defaultAlternateKey);
}

@SuppressWarnings("unchecked")
@Override
final protected T loadInstance(Session session) {
return (T) session.createCriteria(objectType)
.add(Restrictions.eq(alternateKeyName, getAlternateKey()))
.uniqueResult();

}

@SuppressWarnings("unchecked")
public S alternateKey(A theAlternateKey) {
alternateKey = theAlternateKey;
return (S) this;
}

public A getAlternateKey() {
return alternateKey;
}
}

With this a SuperHeroMother contains only a couple of extremly simple methods with hardly any code duplication left:

public class SuperHeroMother extends
SingleAlternateKeyObjectMother {

private String secretIdentity = "Mr. Jones";
private String weakness = "None";
private SuperPower power;

public SuperHeroMother(Session s) {
super(s, SuperHero.class, "Name", "name");
power = new SuperPowerMother(s).instance();
}

@Override
protected void configureInstance(SuperHero hero) {
hero.power = power;
hero.weakness = weakness;
hero.secretIdentity = secretIdentity;
}

@Override
protected SuperHero createInstance() {
SuperHero hero = new SuperHero();
hero.name = getAlternateKey();
return hero;
}

public SuperHeroMother secretIdentity(String aSecretIdentity) {
secretIdentity = aSecretIdentity;
return this;
}

public SuperHeroMother power(SuperPower aPower) {
power = aPower;
return this;
}

public SuperHeroMother weaknes(String aWeakness) {
weakness = aWeakness;
return this;
}
}

Note that the three last methods are only necessary when you want to control these three attributes in your tests. Also these methods actually do suffer from code duplication, but I don't see a way to abstract over this duplication without using lots of reflection which I don't consider worthwhile in this case.

With this infrastructure setup there shouldn't be a reason to leave any SQL statement untested. There are some special cases though.

As mentioned in the first part of this series you should use an in memory database for these tests. It is by orders of magnitudes faster then a normal persistent database. With an in memory database (HSQLDB) we execute about 400 tests in about 5 minutes in a current project. I consider this slow as mud for unit test standards, but compare it to 90 minutes for the same tests against an Oracle database. But sometimes you might have SQL statements that are specific for a database. For example we use analytic functions which are extremely powerfull, but not supported by HSQLDB and as far as I know by no other free in memory database. In order to test these statements and still have a fast running test suite, we further extended our session factory rule. It checks if a the test method or test class is annotated with a special annotation (@OracleTest) and also checks the SqlDialect of the HibernateConfiguration. If the annotation is present, but the SqlDialect is not an Oracle dialect it does not execute the test. Our continuos integration system (Jenkins) has two jobs for the test, one configured with an oracle database and one with a HSQLDB in memory database. The later gives a fast feedback for each check in into version control and the second runs the slow, but more complete tests.

While the stuff presented here is in my opinion extremely helpfull for unit tests of database access there are other things that need testing in the context of databases. You should especially consider tests for your ddl statements and performance tests. For tests of ddl statements I provided ideas in a former article. For performance tests the approach of ObjectMothers is at least in its current form mostly useless, because creating the large amounts of data would be way to slow. So special techniques are needed here. Maybe somebody can provide helpfull links in the comments?