Issue
I was trying to test a Spring Boot Application. I am using an in-memory H2 DB that I would like to initialize with some data and implement an integration test by comparing the number of data in the database after I have added one element in the project table but the test is failing and when I try to track the execution the data.sql and schema.sql are executed two times.
test class
@ContextConfiguration(classes=ProjectManagementApplication.class)
@RunWith(SpringRunner.class)
@DataJpaTest
@SqlGroup(@Sql( executionPhase = ExecutionPhase.BEFORE_TEST_METHOD, scripts= {"classpath:schema.sql","classpath:data.sql"}))
public class ProjectRepositoryIntegrationTest {
@Autowired
ProjectRepository proRepo;
@Autowired
EmployeeRepository empRepo;
@Test
public void ifNewProjectSaved_thenSuccess() {
Project newProject=new Project("new Project","COMPLETED","test Description");
proRepo.save(newProject);
List<Project> pro= proRepo.findAll();
for(int i=0;i<pro.size();i++) {
System.out.println(pro.get(i));
}
assertEquals(5,pro.size());
}
}
schema file
CREATE SEQUENCE IF NOT EXISTS employee_seq;
CREATE TABLE IF NOT EXISTS employee (
employee_id BIGINT NOT NULL DEFAULT nextval('employee_seq') PRIMARY KEY,
first_name VARCHAR(100) NOT NULL,
lastname VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL
);
CREATE SEQUENCE IF NOT EXISTS project_seq;
CREATE TABLE IF NOT EXISTS project (
project_id BIGINT NOT NULL DEFAULT nextval('project_seq') PRIMARY KEY,
name VARCHAR(100) NOT NULL,
stage VARCHAR(100) NOT NULL,
description VARCHAR(500) NOT NULL
);
CREATE TABLE IF NOT EXISTS employee_project (
project_id BIGINT REFERENCES project,
employee_id BIGINT REFERENCES employee
);
data.sql
-- INSERT EMPLOYEES
insert into employee (employee_id, first_name, lastname, email) values (nextval('employee_seq'), 'John', 'Warton', '[email protected]');
insert into employee (employee_id, first_name, lastname, email) values (nextval('employee_seq'), 'Mike', 'Lanister', '[email protected]');
insert into employee (employee_id, first_name, lastname, email) values (nextval('employee_seq'), 'Steve', 'Reeves', '[email protected]');
insert into employee (employee_id, first_name, lastname, email) values (nextval('employee_seq'), 'Ronald', 'Connor', '[email protected]');
insert into employee (employee_id, first_name, lastname, email) values (nextval('employee_seq'), 'Jim', 'Salvator', '[email protected]');
insert into employee (employee_id, first_name, lastname, email) values (nextval('employee_seq'), 'Peter', 'Henley', '[email protected]');
insert into employee (employee_id, first_name, lastname, email) values (nextval('employee_seq'), 'Richard', 'Carson', '[email protected]');
insert into employee (employee_id, first_name, lastname, email) values (nextval('employee_seq'), 'Honor', 'Miles', '[email protected]');
insert into employee (employee_id, first_name, lastname, email) values (nextval('employee_seq'), 'Tony', 'Roggers', '[email protected]');
-- INSERT PROJECTS
insert into project (project_id, name, stage, description) values (nextval('project_seq'), 'Large Production Deploy', 'NOTSTARTED', 'This requires all hands on deck for the final deployment of the software into production');
insert into project (project_id, name, stage, description) values (nextval('project_seq'), 'New Employee Budget', 'COMPLETED', 'Decide on a new employee bonus budget for the year and figureout who will be promoted');
insert into project (project_id, name, stage, description) values (nextval('project_seq'), 'Office Reconstruction', 'INPROGRESS', 'The office building in Monroe has been damaged due to hurricane in the region. This needs to be reconstructed');
insert into project (project_id, name, stage, description) values (nextval('project_seq'), 'Improve Intranet Security', 'INPROGRESS', 'With the recent data hack, the office security needs to be improved and proper security team needs to be hired for implementation');
-- INSERT PROJECT_EMPLOYEE_RELATION
insert into employee_project (employee_id, project_id) (select e.employee_id, p.project_id from employee e, project p where e.lastname ='Warton' AND p.name = 'Large Production Deploy');
insert into employee_project (employee_id, project_id) (select e.employee_id, p.project_id from employee e, project p where e.lastname ='Warton' AND p.name = 'New Employee Budget');
insert into employee_project (employee_id, project_id) (select e.employee_id, p.project_id from employee e, project p where e.lastname ='Warton' AND p.name = 'Office Reconstruction');
insert into employee_project (employee_id, project_id) (select e.employee_id, p.project_id from employee e, project p where e.lastname ='Reeves' AND p.name = 'Large Production Deploy');
insert into employee_project (employee_id, project_id) (select e.employee_id, p.project_id from employee e, project p where e.lastname ='Warton' AND p.name = 'New Employee Budget');
insert into employee_project (employee_id, project_id) (select e.employee_id, p.project_id from employee e, project p where e.lastname ='Warton' AND p.name = 'Improve Intranet Security');
insert into employee_project (employee_id, project_id) (select e.employee_id, p.project_id from employee e, project p where e.lastname ='Henley' AND p.name = 'Office Reconstruction');
insert into employee_project (employee_id, project_id) (select e.employee_id, p.project_id from employee e, project p where e.lastname ='Henley' AND p.name = 'Improve Intranet Security');
Solution
Spring Boot'sDataSource
initialization will apply classpath:schema.sql
and classpath:data.sql
files automatically without using @SqlGroup
and @Sql
on your test class.
You should either remove @SqlGroup
and @Sql
from your test class or disable DataSource
initialization. To achieve the latter set spring.datasource.initialization-mode=never
(Spring Boot 2.4 and earlier) or spring.sql.init.mode=never
(Spring Boot 2.5 and later).
Answered By - Andy Wilkinson