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:

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