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()));
      }
  }
}

Output

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

Loaded Persons: [Person{id=1, firstName='Robin', lastName='Cash', address='64 Zella Park'}]

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