Spring - Simplifying Jdbc Unit Testing with JdbcTestUtils

The class JdbcTestUtils has utility methods which can be used with JdbcTemplate to simplify standard database testing scenarios.

Example using JdbcTestUtils

Creating Spring Jdbc application

public class JdbcTemplatePersonDao implements Dao<Person> {
    @Autowired
    private DataSource dataSource;
    private JdbcTemplate jdbcTemplate;

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

    @Override
    public long save(Person person) {
        String sql = "insert into Person (first_Name, Last_Name, Address) values (?, ?, ?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(
                connection -> {
                    PreparedStatement ps = connection.prepareStatement(sql, new String[]{"ID"});
                    ps.setString(1, person.getFirstName());
                    ps.setString(2, person.getLastName());
                    ps.setString(3, person.getAddress());
                    return ps;
                }, keyHolder);
        Number key = keyHolder.getKey();
        return key.longValue();
    }

    @Override
    public Person load(long id) {
        List<Person> persons = jdbcTemplate.query("select * from Person where id =?",
                new Object[]{id}, (resultSet, i) -> {
                    return toPerson(resultSet);
                });

        if (persons.size() == 1) {
            return persons.get(0);
        }
        return null;
    }

    @Override
    public int delete(long id) {
        return jdbcTemplate.update("delete from PERSON where id = ?", id);
    }

    @Override
    public int update(Person person) {
        String updateSql = "UPDATE Person SET FIRST_NAME = ?, LAST_NAME = ?, "
                + "ADDRESS = ? WHERE id = ?";
        return jdbcTemplate.update(updateSql, person.getFirstName(), person.getLastName(),
                person.getAddress(), person.getId());
    }

    @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;
    .............
}

@Configuration
public class AppConfig {

    @Bean
    public Dao<Person> jdbcPersonDao() {
        return new JdbcTemplatePersonDao();
    }

    @Bean
    public DataSource h2DataSource() {
        return new EmbeddedDatabaseBuilder()
                .setType(EmbeddedDatabaseType.H2)
                .addScript("createPersonTable.sql")
                .build();
    }
}

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

@RunWith(SpringRunner.class)
@ContextConfiguration(classes = AppConfig.class)
public class PersonDaoTests {

    @Autowired
    private JdbcTemplatePersonDao personDao;

    @Test
    public void testCrudOperations() {
        JdbcTestUtils.deleteFromTables(personDao.getJdbcTemplate(), "PERSON");

        //insert
        Person person = Person.create("Dana", "Whitley", "464 Gorsuch Drive");
        long generatedId = personDao.save(person);
        System.out.println("Generated Id: " + generatedId);

        //read
        Person loadedPerson = personDao.load(generatedId);
        System.out.println("Loaded Person: " + loadedPerson);
        Assert.assertNotNull(loadedPerson);
        Assert.assertTrue("Dana".equals(loadedPerson.getFirstName()));

        int c = JdbcTestUtils.countRowsInTable(personDao.getJdbcTemplate(), "PERSON");
        Assert.assertTrue(c == 1);

        //updating address
        Person toBeUpdated = Person.create("Dana", "Whitley", "345 Move Dr, Shine Hill");
        toBeUpdated.setId(generatedId);
        int updated = personDao.update(toBeUpdated);
        Assert.assertTrue(updated==1);

        //read again
        Person loadedPerson2 = personDao.load(generatedId);
        System.out.println("Loaded Person after update: " + loadedPerson2);
        Assert.assertNotNull(loadedPerson2);
        Assert.assertTrue("345 Move Dr, Shine Hill".equals(loadedPerson2.getAddress()));

        //loading all
        List<Person> list = personDao.loadAll();
        System.out.println("All loaded: " + list);
        Assert.assertTrue(list.size() == 1);

        //delete
        int affectedRows = personDao.delete(generatedId);
        Assert.assertTrue(affectedRows == 1);

        c = JdbcTestUtils.countRowsInTable(personDao.getJdbcTemplate(), "PERSON");
        Assert.assertTrue(c == 0);
    }
}

The test passes with following output:

Generated Id: 1
Loaded Person: Person{id=1, firstName='Dana', lastName='Whitley', address='464 Gorsuch Drive'}
Loaded Person after update: Person{id=1, firstName='Dana', lastName='Whitley', address='345 Move Dr, Shine Hill'}
All loaded: [Person{id=1, firstName='Dana', lastName='Whitley', address='345 Move Dr, Shine Hill'}]

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