Testing is a critical aspect of software development, and maintaining a clean test environment ensures accurate and reliable results. This article explores how to effectively manage database states in tests, focusing on how to set up and tear down Java HSQLDB databases.
The article explores the following solutions:
- Using
spring.jpa.hibernate.ddl-autoand@DirtiesContextto reload the application context. - Using
@Sqlannotation to reset the database state after every test with custom SQL scripts.
1. Setting Up the Test Environment
To set up the test environment, include the necessary dependencies in your pom.xml, such as spring-boot-starter-data-jpa, spring-boot-starter-test, and hsqldb. Then, configure the in-memory HSQLDB database in the application.properties file to ensure proper integration with your Spring application.
spring.datasource.url=jdbc:hsqldb:mem:testdb spring.datasource.driver-class-name=org.hsqldb.jdbc.JDBCDriver spring.datasource.username=sa spring.datasource.password= spring.jpa.hibernate.ddl-auto=create-drop spring.jpa.show-sql=true
The spring.jpa.hibernate.ddl-auto=create-drop ensures the database schema is recreated for each test suite.
Example Entity and Repository
A basic Employee entity and repository will be used as examples.
@Entity
@Table(name = "EMPLOYEE")
public class Employee {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public int hashCode() {
int hash = 7;
hash = 37 * hash + Objects.hashCode(this.id);
hash = 37 * hash + Objects.hashCode(this.name);
return hash;
}
@Override
public boolean equals(Object obj) {
if (this == obj) {
return true;
}
if (obj == null) {
return false;
}
if (getClass() != obj.getClass()) {
return false;
}
final Employee other = (Employee) obj;
if (!Objects.equals(this.name, other.name)) {
return false;
}
return Objects.equals(this.id, other.id);
}
}
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
Optional<Employee> findByName(String name);
}
2. Using hibernate.hbm2ddl.auto and @DirtiesContext
This approach uses the hibernate.hbm2ddl.auto property to recreate the schema on application startup and the @DirtiesContext annotation to reload the Spring context after each test.
@SpringBootTest
public class EmployeeServiceDirtiesContextTest {
@Autowired
private JdbcTemplate jdbcTemplate;
@Autowired
private EmployeeRepository employeeRepository;
@BeforeEach
void setup() {
// Insert any required initial data
String insertSql = "INSERT INTO EMPLOYEE (id, name) VALUES (?, ?)";
Employee employee = new Employee();
employee.setId(1L);
employee.setName("JCG User");
jdbcTemplate.update(insertSql, employee.getId(), employee.getName());
}
@AfterEach
void cleanup() {
// Clean up all data
jdbcTemplate.execute("TRUNCATE TABLE EMPLOYEE");
}
@Test
@DirtiesContext
void givenNewEmployee_whenSaved_thenCanBeFound() {
Employee employee = new Employee();
employee.setId(1L);
employee.setName("John Doe");
Employee newEmployee = employeeRepository.save(employee);
Assertions.assertEquals(employee, newEmployee);
}
@Test
@DirtiesContext
void givenExistingEmployee_whenFoundByName_thenReturnEmployee() {
Optional<Employee> employee = employeeRepository.findByName("JCG User");
assertTrue(employee.isPresent(), "Employee should be found");
assertEquals("JCG User", employee.get().getName(), "The employee does match the expected employee.");
}
@Test
@DirtiesContext
void givenNonExistingEmployee_whenFoundByName_thenReturnNull() {
Optional<Employee> employee = employeeRepository.findByName("John Doe");
assertFalse(employee.isPresent(), "Employee should not be found");
}
}
This class uses @SpringBootTest to initialize the entire Spring context, injecting the JdbcTemplate for direct database operations. The @BeforeEach method populates the EMPLOYEE table with initial data before each test, while the @AfterEach method truncates the table after each test to ensure isolation.
The class contains three test cases. The givenNewEmployee_whenSaved_thenCanBeFound test ensures that saving a new employee to the repository works as expected.
The givenExistingEmployee_whenFoundByName_thenReturnEmployee test verifies that an existing employee in the database can be retrieved by name.
Finally, the givenNonExistingEmployee_whenFoundByName_thenReturnNull test confirms that searching for a non-existing employee by name correctly returns no results.
The use of @DirtiesContext ensures that the application context is refreshed after each test, maintaining the reliability of the tests by isolating any context-level changes.
3. Using @Sql Annotation with Custom SQL Scripts
The @Sql annotation provides a way to execute custom SQL scripts before or after each test. This approach is useful for cleaning up the database after every test to ensure data isolation and maintain a consistent state. For example, a script like clean_file.sql can be used to define SQL statements that reset or clear the database after a test completes.
SQL Script (src/main/resources/clean_file.sql)
TRUNCATE TABLE EMPLOYEE;
Test Example
@SpringBootTest
@DirtiesContext
public class EmployeeServiceSQLCleanUpTest {
@Autowired
private JdbcTemplate jdbcTemplate;
@Autowired
private EmployeeRepository employeeRepository;
@BeforeEach
void setup() {
// Insert any required initial data
String insertSql = "INSERT INTO EMPLOYEE (id, name) VALUES (?, ?)";
Employee employee = new Employee();
employee.setId(1L);
employee.setName("JCG User");
jdbcTemplate.update(insertSql, employee.getId(), employee.getName());
}
@Test
@Sql(scripts = "/clean_file.sql", executionPhase = Sql.ExecutionPhase.AFTER_TEST_METHOD)
public void givenNewEmployee_whenSaved_thenCanBeFound() {
Employee employee = new Employee();
employee.setId(2L);
employee.setName("John Doe");
Employee newEmployee = employeeRepository.save(employee);
Assertions.assertEquals(employee, newEmployee);
}
@Test
@Sql(scripts = "/clean_file.sql", executionPhase = Sql.ExecutionPhase.AFTER_TEST_METHOD)
public void givenExistingEmployee_whenFoundByName_thenReturnEmployee() {
Optional<Employee> employee = employeeRepository.findByName("JCG User");
assertTrue(employee.isPresent(), "Employee should be found");
assertEquals("JCG User", employee.get().getName(), "The employee does match the expected employee.");
}
@Test
@Sql(scripts = "/clean_file.sql", executionPhase = Sql.ExecutionPhase.AFTER_TEST_METHOD)
public void givenNonExistingEmployee_whenFoundByName_thenReturnNull() {
Optional<Employee> employee = employeeRepository.findByName("John Doe");
assertFalse(employee.isPresent(), "Employee should not be found");
}
}
The above EmployeeServiceSQLCleanUpTest class demonstrates how to use the @Sql annotation to clean up the database after each test. The test cases validate various scenarios of the EmployeeRepository. The use of @Sql with a cleanup script (/clean_file.sql) after each test method guarantees a clean database state, avoiding data interference across tests.
When the application is executed, the test suite runs all the defined test cases, ensuring the database behaves as expected. For each test, the database is set up with predefined data, and after execution, it is cleaned up using the @Sql script.
4. Conclusion
In this article, we explored various approaches to tear down or reset an HSQLDB database after tests in a Spring application using Hibernate, JPA, and JUnit. From leveraging hibernate.hbm2ddl.auto with @DirtiesContext for schema recreation to employing @Sql annotations with custom scripts for flexible cleanup, each method offers unique benefits tailored to specific use cases.
5. Download the Source Code
This article covered how to tear down an HSQLDB database in Java.
You can download the full source code of this example here: Java hsqldb tear down
