Spring data doesn't increment after DBsetup

Question

I have such question. I'm using DBsetup for spring boot tests and postgresql database. And I'm using DBsetup to set user, but when I'm trying to set another user by spring data I have the next exception:

Подробности: Key (id)=(1) already exists.

org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [users_pkey];

This is my test class:

@RunWith(SpringRunner.class)
@SpringBootTest
@TestPropertySource("/application-test.properties")
public class UserRepositoryTest {

    @Autowired
    private ApplicationContext applicationContext;
    @Autowired
    private UserRepository userRepository;
    @Autowired
    private DataSource dataSource;

    @Before
    public void insertData() throws SQLException {
        Operation operation = sequenceOf(CommonOperations.DELETE_ALL, CommonOperations.INSERT_USER);
        DbSetup dbSetup = new DbSetup(new DataSourceDestination(dataSource), operation);
        dbSetup.launch();
    }

    @After
    public void cleanPK() throws SQLException {
        DBUtil.resetAutoIncrementColumns(applicationContext, "user");
    }

    @Test
    public void registerUser() {
        val user = new User(null, "Glass", "123123", "glass999@mail.ru");
        assertEquals(user, userRepository.saveAndFlush(user));
    }

}

Operations for DBsetup:

public class CommonOperations {
    public static final Operation DELETE_ALL = deleteAllFrom("article_tag", "article", "tag", "users");
    public static final Operation INSERT_USER =
            insertInto("users")
                    .columns("id", "email", "password", "username")
                    .values(1, "krikkk998@mail.ru", "123123", "Daimon")
                    .build();
}

Class to reset sequence:

@NoArgsConstructor
public final class DBUtil {

    public static void resetAutoIncrementColumns(ApplicationContext applicationContext,
                                                 String... tableNames) throws SQLException {
        DataSource dataSource = applicationContext.getBean(DataSource.class);
        String resetSqlTemplate = "ALTER SEQUENCE %s RESTART WITH 1;";
        try (Connection dbConnection = dataSource.getConnection()) {
            for (String resetSqlArgument: tableNames) {
                try (Statement statement = dbConnection.createStatement()) {
                    String resetSql = String.format(resetSqlTemplate, resetSqlArgument + "_id_seq");
                    statement.execute(resetSql);
                }
            }
        }
    }
}

Does anyone know how to solve this problem?

Answer 1

One thing to look at:

public static final Operation INSERT_USER =
        insertInto("users")
                .columns("id", "email", "password", "username")
                .values(1, "krikkk998@mail.ru", "123123", "Daimon")
                .build();

Here you are using a hard-coded id i.e. 1

Now, when in the test case you are trying to create another user, you passed the id as null, assuming it's supposed to pick from the sequence. It will start from 1 too. Hence, you get a conflict.