I tring found out how work hibernate with sql scripts in testcontainers.

I create class User and set up Sequence Generator.

@Entity(name = "users")
@Getter
@Setter
public class User {
    @Id
    @Column(unique = true, nullable = false, name = "user_id")
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "user_seq")
    @SequenceGenerator(name = "user_seq", sequenceName = "user_seq", allocationSize = 50)
    private Long id;

// other fields

Also created CRUD UserRepository

public interface UserRepository extends JpaRepository<User, Long> {}

Now i want cover code with tests (currently only for CRUD methods).

So i created UserRepositoryTest

@SpringBootTest
@Sql(value = "classpath:script/user_repository.sql", executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD)
class UserRepositoryTest {
    //hard coded field

    @Autowired
    private UserRepository userRepository;

    private User expected;
    private User actual;

    @Test
    void testCreateUser_ShouldCreateExpectedUser_AndReturnUserWithId() {
        final long expectedId = 3;
        expected = EXPECTED_USER;
        expected.setId(expectedId);

        actual = userRepository.save(EXPECTED_USER);

        assertNotNull(actual.getId());
        assertEquals(expected, actual);
    }

As you can see i use user_repository.sql BEFORE_TEST_METHOD

How looks user_repository.sql:

TRUNCATE TABLE users CASCADE;

ALTER SEQUENCE user_seq INCREMENT BY 1 RESTART WITH 1;

INSERT INTO users VALUES (nextval('user_seq'), 'First Name', 'First Lastname', 'First Nickname', '[email protected]', 'First pass', 'USER_PRESENTER'),
                         (nextval('user_seq'), 'Second Name', 'Second Lastname', 'Second Nickname', '[email protected]', 'Second pass', 'USER_ELECTOR');

So as you can see i change INCREMENT BY from 50 to 1 because nextval IN POSTGRESQL just increment current sequence (user_seq was 1 after nextval will be 50).

But the problem is that i had strange error:

Expected :User{id=3, firstName='Expected Name', lastName='Expected Lastname', nickname='Expected Nickname', email='[email protected]', password='Expected pass', role=USER_SPECTATOR}
Actual   :User{id=-46, firstName='Expected Name', lastName='Expected Lastname', nickname='Expected Nickname', email='[email protected]', password='Expected pass', role=USER_SPECTATOR}

So as i expected before test method sql script will be called. And it will truncate table, restart user_seq with 1 and change INCREMENT BY to 1. After INSERT INTO user_seq will be 3.

But I don’t understand how this happens, but the actual ID after INSERT INTO is -46.

Maybe i should cleanup Hibernate cache?

I’m tried to do this:

@Autowired
private EntityManagerFactory entityManagerFactory;

@AfterEach
void dropHibernateCache() {
    entityManagerFactory.getCache().evictAll();
}

But it didn’t have any effect.

Maybe i did something wrong in Initial Schema?

CREATE SEQUENCE IF NOT EXISTS user_seq START WITH 1 INCREMENT BY 50;

CREATE TABLE users
(
    user_id        BIGINT NOT NULL,
    user_firstname VARCHAR(50),
    user_lastname  VARCHAR(50),
    user_nickname  VARCHAR(100),
    user_email     VARCHAR(255),
    user_password  VARCHAR(255),
    user_role      VARCHAR(20),
    CONSTRAINT pk_users PRIMARY KEY (user_id)
);

SOLUTION I FOUND

1. Dividing the Population in the Database:

TRUNCATE TABLE users CASCADE;

ALTER SEQUENCE user_seq RESTART WITH 1;
INSERT INTO users VALUES (nextval('user_seq'), 'First Name', 'First Lastname', 'First Nickname', '[email protected]', 'First pass', 'USER_PRESENTER');

ALTER SEQUENCE user_seq RESTART WITH 2;
INSERT INTO users VALUES (nextval('user_seq'), 'Second Name', 'Second Lastname', 'Second Nickname', '[email protected]', 'Second pass', 'USER_ELECTOR');

2. If this does not resolve the issue in certain tests, you can use:

@DirtiesContext(methodMode = DirtiesContext.MethodMode.BEFORE_METHOD)

Yes it’s big performance issue but it is what it is.