Databases are an extremely important part of almost every enterprise application. Yet there is very little support for testing your database, which results in very little tests coverage of database related code out in the wild. In a desperate attempt to change that at least a little the article series starting with this article will describe some of the problems and possible partial solutions based on Hibernate and JUnit.

As an example I use the following little set of Hibernate entity classes and the database schema Hibernate will create from it:

@Entity

public class SuperHero extends AbstractEntity { @NotEmpty

@Column(unique = true)

public String name; @ManyToOne

@NotNull

public SuperPower power; @NotEmpty

public String weakness; @NotEmpty

public String secretIdentity;

}



@Entity

public class SuperPower extends AbstractEntity { @NotEmpty

@Column(unique = true)

public String name; @NotEmpty

public String description; @NotNull

@ManyToOne

public SuperPowerType type;

}

@Entity

public class SuperPowerType extends AbstractEntity { @NotEmpty

@Column(unique = true)

public String name; @NotNull

@Length(min = 30)

public String description;

}



As you can see (and if you don't I tell you) a SuperHero references a SuperPower and a SuperPower references a SuperPowerType. All have some mandatory attributes.

All three entities have a common supertype providing id and version.

@MappedSuperclass

public class AbstractEntity { @SuppressWarnings("unused")

@Id

@GeneratedValue

public Long id;

@SuppressWarnings("unused")

@Version

private Long version;

}



For brevity I skipped on getters and setters.

Finally there is a SuperHeroRepository which I want to test. It has a single method returning a SuperHero based on the a SuperPower

A simple test for the SuperHeroRepository might work like this:

Create a SuperHero and make sure I can retrieve it again using the SuperHeroRepository .

Easy, isn't it? Well ... lets see what you really have to do

create an empty database

create a SuperPowerType , making sure you have all the mandatory fields filled

, making sure you have all the mandatory fields filled create a SuperPower of that type, making sure you have all the mandatory fields filled

of that type, making sure you have all the mandatory fields filled create a SuperHero , making sure you have all the mandatory fields filled

, making sure you have all the mandatory fields filled use the repository to retrieve SuperHero s

s assert you got the expected result.



public class SuperHeroRepository1Test {

private SessionFactory sessionFactory;

private Session session = null; @Before

public void before() {

// setup the session factory

AnnotationConfiguration configuration = new AnnotationConfiguration();

configuration.addAnnotatedClass(SuperHero.class)

.addAnnotatedClass(SuperPower.class)

.addAnnotatedClass(SuperPowerType.class);

configuration.setProperty("hibernate.dialect",

"org.hibernate.dialect.H2Dialect");

configuration.setProperty("hibernate.connection.driver_class",

"org.h2.Driver");

configuration.setProperty("hibernate.connection.url", "jdbc:h2:mem");

configuration.setProperty("hibernate.hbm2ddl.auto", "create"); sessionFactory = configuration.buildSessionFactory();

session = sessionFactory.openSession();

} @Test

public void returnsHerosWithMatchingType() { // create the objects needed for testing

SuperPowerType powerType = new SuperPowerType();

powerType.name = "TheType";

powerType.description = "12345678901234567890aDescription"; SuperPower superpower = new SuperPower();

superpower.name = "SuperPower";

superpower.description = "Description";

superpower.type = powerType; SuperHero hero = new SuperHero();

hero.name = "Name";

hero.power = superpower;

hero.weakness = "None";

hero.secretIdentity = "Mr. Jones"; // storing the objects for the test in the database

session.save(powerType);

session.save(superpower);

session.save(hero); SuperHeroRepository heroRepository = new SuperHeroRepository(session);

List heroes = heroRepository.loadBy(superpower);

assertNotNull(heroes);

assertEquals(1, heroes.size());

} @After

public void after() {

session.close();

sessionFactory.close();

}

}



So we have to fill all the mandatory fields and provide a SuperPowerType (again with all the mandatory fields) although nothing in the test is concerned with SuperPowerTypes. If you still don't believe this results in ugly code have a look at this naive implementation.

There is really only one positive thing I can say about this test: it uses H2 in In-Memory mode so it is reasonable fast. There is the first lesson: *Use an in memory database for testing if possible. It's easily 100*faster then a disc based database*

So obviously we want to refactor this monster, which might after some method extracting result into something like this:

public class SuperHeroRepository2Test { private SessionFactory sessionFactory;

private Session session; @Before

public void before() {

sessionFactory = createSessionFactory();

session = sessionFactory.openSession();

Transaction transaction = session.beginTransaction();

} @Test

public void returnsHerosWithMatchingType() { SuperPowerType powerType = createSuperPowerType();

session.save(powerType); SuperPower superpower = createSuperPower(powerType);

session.save(superpower); SuperHero hero = createSuperHero(superpower);

session.save(hero); SuperHeroRepository heroRepository = new SuperHeroRepository(session);

List heroes = heroRepository.loadBy(superpower); assertNotNull(heroes);

assertEquals(1, heroes.size());

} private SessionFactory createSessionFactory() {

AnnotationConfiguration configuration = new AnnotationConfiguration();

configuration.addAnnotatedClass(SuperHero.class)

.addAnnotatedClass(SuperPower.class)

.addAnnotatedClass(SuperPowerType.class);

configuration.setProperty("hibernate.dialect",

"org.hibernate.dialect.H2Dialect");

configuration.setProperty("hibernate.connection.driver_class",

"org.h2.Driver");

configuration.setProperty("hibernate.connection.url", "jdbc:h2:mem");

configuration.setProperty("hibernate.hbm2ddl.auto", "create"); SessionFactory sessionFactory = configuration.buildSessionFactory();

return sessionFactory;

} private SuperHero createSuperHero(SuperPower superpower) {

SuperHero hero = new SuperHero();

hero.name = "Name";

hero.power = superpower;

hero.weakness = "None";

hero.secretIdentity = "Mr. Jones";

return hero;

} private SuperPower createSuperPower(SuperPowerType powerType) {

SuperPower superpower = new SuperPower();

superpower.name = "SuperPower";

superpower.description = "Description";

superpower.type = powerType;

return superpower;

} private SuperPowerType createSuperPowerType() {

SuperPowerType powerType = new SuperPowerType();

powerType.name = "TheType";

powerType.description = "12345678901234567890aDescription";

return powerType;

}

}



This would be ok, if this would be the only test of this kind. But other similar test need the SessionFactory as well so I will move all the SessionFactory, Session and Transaction stuff into a Rule

public class SessionFactoryRule implements MethodRule {

private SessionFactory sessionFactory;

private Transaction transaction;

private Session session; @Override

public Statement apply(final Statement statement, FrameworkMethod method,

Object test) {

return new Statement() { @Override

public void evaluate() throws Throwable {

sessionFactory = createSessionFactory();

createSession();

beginTransaction();

try {

statement.evaluate();

} finally {

shutdown();

}

} };

} private void shutdown() {

try {

try {

try {

transaction.rollback();

} catch (Exception ex) {

ex.printStackTrace();

}

session.close();

} catch (Exception ex) {

ex.printStackTrace();

}

sessionFactory.close();

} catch (Exception ex) {

ex.printStackTrace();

}

} private SessionFactory createSessionFactory() {

AnnotationConfiguration configuration = new AnnotationConfiguration();

configuration.addAnnotatedClass(SuperHero.class)

.addAnnotatedClass(SuperPower.class)

.addAnnotatedClass(SuperPowerType.class);

configuration.setProperty("hibernate.dialect",

"org.hibernate.dialect.H2Dialect");

configuration.setProperty("hibernate.connection.driver_class",

"org.h2.Driver");

configuration.setProperty("hibernate.connection.url", "jdbc:h2:mem");

configuration.setProperty("hibernate.hbm2ddl.auto", "create"); SessionFactory sessionFactory = configuration.buildSessionFactory();

return sessionFactory;

} public Session createSession() {

session = sessionFactory.openSession();

return session;

} public void commit() {

transaction.commit();

} public void beginTransaction() {

transaction = session.beginTransaction();

} public Session getSession() {

return session;

}

}



Which leaves the test in a somewhat acceptable state.

public class SuperHeroRepository3Test { @Rule

public final SessionFactoryRule sf = new SessionFactoryRule(); @Test

public void returnsHerosWithMatchingType() {

Session session = sf.getSession(); SuperPowerType powerType = createSuperPowerType();

session.save(powerType); SuperPower superpower = createSuperPower(powerType);

session.save(superpower); SuperHero hero = createSuperHero(superpower);

session.save(hero); sf.commit(); SuperHeroRepository heroRepository = new SuperHeroRepository(session);

List heroes = heroRepository.loadBy(superpower); assertNotNull(heroes);

assertEquals(1, heroes.size());

} // ... private methods more or less as before

}

