Not able to reset sequence for the primary key of an entity

Question

I'm implementing some tests for REST endpoints that manipulate a database (in test context, I use an H2 database) via spring data JPA, and I would like to have all the tables clean (no data in it, and the id restarting to 1 when id is numerical) before every test.

However, there is an entity with annotation @GeneratedValue(strategy = GenerationType.AUTO) and id of type Long where I'm unable to reset the sequence associated to the underlying table.

To erase data from tables, I'm using

entityManager.createNativeQuery("TRUNCATE TABLE table_name RESTART IDENTITY").executeUpdate();

What I tried so far :

String query = "ALTER TABLE table_name " +
 "DROP PRIMARY KEY;" +
 "ALTER TABLE table_name " +
 "ALTER COLUMN id BIGINT IDENTITY PRIMARY KEY";
entityManager.createNativeQuery(query);

This doesn't make the first id in beginning of test to 1.

entityManager.createNativeQuery("ALTER SEQUENCE TABLE_NAME_SEQ RESTART WITH 1").executeUpdate();

Gives me too high id values (for example 43).

Answer 1

you are using TRUNCATE ... and this command only resets IDENTITY columns. not sequence. truncate a table wont rest related sequence. in your test class you can use @BeforEach annotation. and then truncate and alter sequence. like this:

@BeforeEach
void clean(JdbcTemplate jdbc) {
 jdbc.execute("TRUNCATE TABLE table_name RESTART IDENTITY");
 jdbc.execute("ALTER SEQUENCE IF EXISTS hibernate_sequence RESTART WITH 1");
}

this method before each test delete data and rest sequnce ,hope it works for you.

• Skinay: Hey, thanks for our answer, unfortunately this doesn't work for me, even if I'm using JdbcTemplate instead of EntityManager. But I found a solution where I just retrieve the last id used and adapt my tests with this value.