Felicyta Felicyta - 3 months ago 80
Java Question

Spring test with H2 in memory database truncate all tables

I'm a junior CS major student working on an MVC project using spring and I am quite new to spring.

I set up an H2 in memory db and wrote 2 sql scripts; one to populate the db and another one to flush it empty.

As far as I know, the practical difference between

truncate table <table name>
and
delete from <table name>
is truncate table is ddl thus its faster and it also resets the auto incremented columns. The problem is H2's truncate table does not reset auto incremented columns. Could you point out where I'm messing up?

I'm using spring's SqlGroup annotation to run the scripts.

@SqlGroup({
@Sql(executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD, scripts =
"classpath:populate.sql"),
@Sql(executionPhase = Sql.ExecutionPhase.AFTER_TEST_METHOD, scripts =
"classpath:cleanup.sql")
})


Here's the cleanup.sql that is supposed to truncate every table.

SET FOREIGN_KEY_CHECKS=0;

TRUNCATE TABLE `criteria`;
TRUNCATE TABLE `job_title`;
TRUNCATE TABLE `organization`;
TRUNCATE TABLE `organization_teams`;
TRUNCATE TABLE `organization_users`;
TRUNCATE TABLE `organization_job_titles`;
TRUNCATE TABLE `review`;
TRUNCATE TABLE `review_evaluation`;
TRUNCATE TABLE `team`;
TRUNCATE TABLE `team_members`;
TRUNCATE TABLE `user`;
TRUNCATE TABLE `user_criteria_list`;

SET FOREIGN_KEY_CHECKS=1;


And populate.sql simply inserts a bunch of rows to those tables without violating integrity constraints.

So when i run my test class, only the first method passes. For the rest i get stuff like this:

Referential integrity constraint violation: "FK3MAB5XYC980PSHDJ3JJ6XNMMT: PUBLIC.ORGANIZATION_JOB_TITLES FOREIGN KEY(JOB_TITLES_ID) REFERENCES PUBLIC.JOB_TITLE(ID) (1)"; SQL statement:
INSERT INTO
organization_job_titles
(organization_id, job_titles_id) VALUES(1, 1)

I thought the problem arose from auto incremented columns not being reset. So i written a different test class:

monAutoIncTest.java

@Sql(executionPhase = Sql.ExecutionPhase.AFTER_TEST_METHOD, scripts =
"classpath:truncateTable.sql")
public class monAutoIncTest {
@Autowired
OrganizationService organizationService;

@Test
public void h2truncate_pls() throws BaseException {
organizationService.add(new Organization("Doogle"));
Organization fetched = organizationService.getByName("Doogle");
Assert.assertEquals(1, fetched.getId());
}

@Test
public void h2truncate_plz() throws BaseException {
organizationService.add(new Organization("Zoogle"));
Organization fetched = organizationService.getByName("Zoogle");
Assert.assertEquals(1, fetched.getId());
}
}


With truncateTable.sql

SET FOREIGN_KEY_CHECKS=0;

TRUNCATE TABLE `organization`;

SET FOREIGN_KEY_CHECKS=1;


When the test runs, whichever method that is run first passes, and the other gives me this.

java.lang.AssertionError:
Expected :1
Actual :2

Answer

For now H2 does not support that feature, but we can see such plans in their roadmap

TRUNCATE should reset the identity columns as in MySQL and MS SQL Server (and possibly other databases).

Try to use this statement in composition with TRUNCATE:

ALTER TABLE [table] ALTER COLUMN [column] RESTART WITH [initial value]
Comments