Spring - Jdbc Unit Testing with an embedded database

EmbeddedDatabaseBuilder can be used for unit testing without manipulating main application database. Following example shows how to perform unit testing with an embedded database which is used without Spring context environment and is good for testing the current unit test class. If a single embedded database is needed to be shared within a test suite, then we should use full spring integration tests.

Example

Creating Spring Jdbc application

public class JdbcTemplatePersonDao implements Dao<Person> {

    private DataSource dataSource;
    private JdbcTemplate jdbcTemplate;

    @Autowired
    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @PostConstruct
    public void postConstruct() {
        jdbcTemplate = new JdbcTemplate(dataSource);
    }


    @Override
    public void save(Person person) {
        String sql = "insert into Person (FIRST_NAME, LAST_NAME, ADDRESS) values (?, ?, ?)";
        jdbcTemplate.update(sql, person.getFirstName(), person.getLastName(),
                person.getAddress());
    }

    @Override
    public Person load(long id) {
        //todo
        return null;
    }

    @Override
    public void delete(long id) {
        //todo
    }

    @Override
    public void update(Person person) {
        //todo
    }

    @Override
    public List<Person> loadAll() {
        return jdbcTemplate.query("select * from Person", (resultSet, i) -> {
            return toPerson(resultSet);
        });
    }

    private Person toPerson(ResultSet resultSet) throws SQLException {
        Person person = new Person();
        person.setId(resultSet.getLong("ID"));
        person.setFirstName(resultSet.getString("FIRST_NAME"));
        person.setLastName(resultSet.getString("LAST_NAME"));
        person.setAddress(resultSet.getString("ADDRESS"));
        return person;
    }

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }
}

public class Person {
    private long id;
    private String firstName;
    private String lastName;
    private String address;
    .............
}

src/main/resources/createPersonTable.sql

CREATE TABLE PERSON
(
ID BIGINT  PRIMARY KEY AUTO_INCREMENT,
FIRST_NAME VARCHAR(255),
LAST_NAME VARCHAR(255),
ADDRESS VARCHAR(255)
);

The JUnit Test class

public class JdbcTemplateTest {

    private JdbcTemplatePersonDao jdbcTemplatePersonDao;

    @Before
    public void setup() {
        EmbeddedDatabase db = new EmbeddedDatabaseBuilder()
                .setType(EmbeddedDatabaseType.H2)
                .addScript("createPersonTable.sql")
                .build();
        jdbcTemplatePersonDao = new JdbcTemplatePersonDao();
        jdbcTemplatePersonDao.setDataSource(db);
        jdbcTemplatePersonDao.postConstruct();
    }

    @Test
    public void testJdbcTempalte() {
        Person person = Person.create("Robin", "Cash", "64 Zella Park");
        jdbcTemplatePersonDao.save(person);

        List<Person> loadedPersons = jdbcTemplatePersonDao.loadAll();
        System.out.println("Loaded Persons: " + loadedPersons);
        for (Person loadedPerson : loadedPersons) {
            Assert.assertTrue("Robin".equals(loadedPerson.getFirstName()));
            Assert.assertTrue("Cash".equals(loadedPerson.getLastName()));
            Assert.assertTrue("64 Zella Park".equals(loadedPerson.getAddress()));
        }
    }
}

---- IntelliJ IDEA coverage runner ---- 

Example Project

Dependencies and Technologies Used:

• spring-context 4.3.10.RELEASE: Spring Context.
• spring-jdbc 4.3.10.RELEASE: Spring JDBC.
• spring-test 4.3.10.RELEASE: Spring TestContext Framework.
• junit 4.12: JUnit is a unit testing framework for Java, created by Erich Gamma and Kent Beck.
• h2 1.4.196: H2 Database Engine.
• JDK 1.8
• Maven 3.3.9