We are the Dev Teams of
  • brands
  • ebay_main
  • ebay
  • mobile
<
>
BLOG

Testing DB Operations with Spring

by Alex Lindhorst
in Tutorials

As a senior, well-experienced developer you probably pay close attention to good code coverage by unit tests. During your career, this has probably saved your back side a dozen times and you have at least thought a couple of times about test-driven development. And with the huge improvements in this area introduced by frameworks like Mockito, mocking the external dependencies is a piece of cake and has taken the terror out of unit testing.

But, oh hey, what if you want to make sure that your underlying database schema imposes constraints correctly and will keep you from entering invalid data? In this case, mocking will not take you anywhere and you enter the almost unknown land of integration tests. And there is still an abundance of terrors in that land with regards to difficult setups and time-consuming test runs. Or so the senior, well-experienced developer thinks. The expert developer knows better, though.

This is the first article in a series of articles that will show you in a bottom-up approach – starting at the persistence layer – how to easily set up integration tests that will actually run reasonably fast. Fast enough, actually, that I normally run them as part of my normal unit test runs.

This article will focus on using Spring's tools to test database operations. Let's assume that our application is based upon JPA and the underlying database is built according to the structure laid out in your code, not the other way around. This in turn means that we can use any kind of database, as JPA defines the persistence layer and thus abstracts from the DB underneath. That way we can actually use different DB products for real operations and test runs.

By using JPA and annotated Java code, we can easily set up an in-memory database with the same structure as a full-blown production database. But while the latter is normally a database server that is bound to a specific box and may hold gigabytes of databases, the test database is set up in memory where our tests are run and only holds some test data. That will make the setup very fast.

So let's see some code. I'll be showing important parts of the code in this blog entry, but you can pull all of the code at once from https://github.com/alindhorst/mobile-blog.git. This article relates to the tag "Testing_DB_Ops" and the project of interest is moviedbapp.

We will use Maven as the build system for our application, so we'll need a lot of third-party tools. These dependencies are reflected in our pom.xml which already reflects some additional dependencies that we will need further down the road. Please note that org.hsqldb:hsqldb is only declared within the test scope. These are some of the more important dependencies:

  <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>${spring-version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-beans</artifactId>
            <version>${spring-version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${spring-version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context-support</artifactId>
            <version>${spring-version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>${spring-version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-orm</artifactId>
            <version>${spring-version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${spring-version}</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate.javax.persistence</groupId>
            <artifactId>hibernate-jpa-2.0-api</artifactId>
            <version>1.0.0.Final</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>${hibernate-version}</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-entitymanager</artifactId>
            <version>${hibernate-version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>${spring-version}</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.hsqldb</groupId>
            <artifactId>hsqldb</artifactId>
            <version>2.3.0</version>
            <scope>test</scope>
...
        </dependency>

Apart from the usual Spring dependencies, the most interesting dependencies here are org.springframework:spring-test, which brings us a testrunner, org.hibernate.javax.persistence:hibernate-jpa-2.0-api, which brings us JPA, and org.hsqldb:hsqldb, which brings us the driver to connect to Spring's embedded database.

Let's turn to JPA. First, we define an entity together with its JPA structure metadata:

package de.mobile.moviedb.entities;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EnumType;
import javax.persistence.Enumerated;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;

@Entity
@Table(name = "movie")
@NamedQueries(
        @NamedQuery(name = Movie.FIND_ALL_QUERY, query = "SELECT m FROM Movie m"))
public class Movie {

    public static final String FIND_ALL_QUERY = "Movie.findAll";
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id", updatable = false, nullable = false)
    private Long id;
    @Column(name = "title")
    private String title;
    @Column(name = "year")
    private int year;
    @Column(name = "genre")
    @Enumerated(EnumType.STRING)
    private Genre genre;

    protected Movie() {
        //needed by JPA
    }

    public Movie(String title, int year, Genre genre) {
        this.title = title;
        this.year = year;
        this.genre = genre;
    }

    public Long getId() {
        return id;
    }

    public String getTitle() {
        return title;
    }

    public int getYear() {
        return year;
    }

    public Genre getGenre() {
        return genre;
    }

    public static enum Genre {

        ACTION, DRAMA, CRIME, SCIFI;
    }
}

Note how the necessary structure data is given in annotations (@Table and @Column). JPA will use this to come up with a database scheme. Furthermore, a first query to retrieve data from the persistence backend is defined using @NamedQuery. We will use that data later in our first test.

In order to make JPA happy, we'll need to declare a persistence.xml in META-INF:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
    <persistence-unit name="movie-db" transaction-type="RESOURCE_LOCAL">
    </persistence-unit>
</persistence>

This file is just a marker file to tell JPA that we have entities in this part of the classpath. The only bit of information that we will reuse is the name for the persistence unit as defined in line 3.

Finally, we need to configure the Spring context to use in the tests. This the real nitty gritty. In the following configuration class, please note that a datasource is created in just 3 lines and that an entity manager is configured to work on top of that datasource; setting the property “hibernate.hbm2ddl.auto” to "update" makes hibernate create the appropriate data structure in the embedded data source.

NOTE: This is a test configuration.  For real operations, you will definitely want to provide another configuration. Even if you want to use an embedded DB for the real stuff, you most certainly do not want to keep the "update" setting. With this setting, JPA/Hibernate will try to validate the existing DB setting and then update it according to what your annotations are. This could potentially have disastrous effects on your production database. This is topped by "create", which will actually drop your DB no matter what and recreate it. Use "validate" for production environments, please, and do impose a sophisticated role and permissions model on your production database that will keep your normal database user from making changes to the DB structure.

package de.mobile.moviedb.itest.config;

import java.util.Map;
import java.util.Properties;
import javax.sql.DataSource;

import org.hibernate.ejb.HibernatePersistence;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseFactoryBean;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;

@Configuration
public class ITestPersistenceConfig {

    @Bean(name = "dataSource")
    public DataSource getDataSource() {
        EmbeddedDatabaseFactoryBean factory = new EmbeddedDatabaseFactoryBean();
        factory.afterPropertiesSet();
        return factory.getObject();
    }

    @Bean(name = "entityManager")
    public LocalContainerEntityManagerFactoryBean getEntityManagerFactoryBean() {
        LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
        factory.setPersistenceXmlLocation("classpath:META-INF/persistence.xml");
        factory.setPersistenceProviderClass(HibernatePersistence.class);
        factory.setDataSource(getDataSource());
        factory.setJpaProperties(getJpaPropertiesFrom(factory.getJpaPropertyMap()));
        return factory;
    }

    private Properties getJpaPropertiesFrom(Map<String, Object> defaults) {
        Properties properties = new Properties();
        for (Map.Entry<String, Object> entry : defaults.entrySet()) {
            properties.put(entry.getKey(), entry.getValue());
        }
        properties.setProperty("hibernate.connection.driver_class", "org.hsqldb.jdbcDriver");
        properties.setProperty("hibernate.dialect", "org.hibernate.dialect.HSQLDialect");
        properties.setProperty("hibernate.hbm2ddl.auto", "update");
        properties.setProperty("hibernate.show_sql", "true");
        properties.setProperty("hibernate.format_sql", "true");
        return properties;
    }
}

Here we go, we have defined a database for testing and an entity to be stored in the DB. And it's all just code, we never had to leave our IDE to fiddle around with some DB client. That's not really a lot of pain and terror, is it?

So, let's finally get around to our first test to check our setup. In the following code, please note the RunWith annotation, which is set to Spring's test runner. Furthermore, the ContextConfiguration annotation tells Spring which context to use. The test runner will then set up the database and the entity manager, which will create the appropriate structure in the DB. Furthermore look at the way that the EntityManager is injected by referring to the persistence unit from the persistence.xml.

package de.mobile.moviedb.itest;

import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import de.mobile.moviedb.entities.Movie;
import de.mobile.moviedb.itest.config.ITestPersistenceConfig;

import static org.hamcrest.CoreMatchers.is;
import static org.junit.Assert.assertThat;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = {ITestPersistenceConfig.class})
public class DBSetupTest {

    @PersistenceContext(unitName = "movie-db")
    private EntityManager entityManager;

    @Test
    public void checkDBIsAvailable() {
        List resultList = entityManager.createNamedQuery(Movie.FIND_ALL_QUERY).getResultList();
        assertThat(resultList.size(), is(0));
    }
}

Now let's have a look at the output:

-------------------------------------------------------
 T E S T S
-------------------------------------------------------
Running de.mobile.moviedb.itest.DBSetupTest
00:28:27.096 [main] DEBUG o.h.SQL - 
    select
        movie0_.id as id1_0_,
        movie0_.genre as genre2_0_,
        movie0_.title as title3_0_,
        movie0_.year as year4_0_ 
    from
        movie movie0_
Hibernate: 
    select
        movie0_.id as id1_0_,
        movie0_.genre as genre2_0_,
        movie0_.title as title3_0_,
        movie0_.year as year4_0_ 
    from
        movie movie0_
Tests run: 1, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 1.717 sec

Results :

Tests run: 1, Failures: 0, Errors: 0, Skipped: 0

------------------------------------------------------------------------
BUILD SUCCESS
------------------------------------------------------------------------
Total time: 3.258s (Wall Clock)
Finished at: Wed Sep 11 00:28:27 CEST 2013
Final Memory: 5M/410M

Clearly, the appropriate SQL statement for our HQL query defined by annotations in the Movie class is generated in the process, indicating that the correct structure has been created in the DB.

Please note: Running the test takes a few seconds, which seems to be a lot at first. However, the application context will be cached, meaning that any consecutive tests using that context will not experience the same delay. The more tests make use of the context, the better the ratio of setup and test runtime will be.

So: In this article we learned how to set up an embedded database using Spring's EmbeddedDatabaseFactoryBean to create the database and JPA to easily create the database structure. This helps us to easily check if our persistence operations work correctly. By simply providing a different configuration for the “real” environment, we can use that tested code on real data.

In the next article I will point out how to easily add transaction management and thus write some “real” tests.

spring, database, jpa, testing

?>