Close

Spring - Simplifying Jdbc Unit Testing with JdbcTestUtils

[Updated: Aug 28, 2017, Created: Aug 28, 2017]

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

Spring Jdbc Test Utils Select All Download
  • jdbc-test-utils-example
    • src
      • main
        • java
          • com
            • logicbig
              • example
        • resources
      • test
        • java
          • com
            • logicbig
              • example
                • PersonDaoTests.java

    See Also