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", "[email protected]");
        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, "[email protected]", "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, "[email protected]", "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.

Answer 2

You have an issue related to constraint violation. So one thing you can do is in your table change the 'id' column to "auto_increment". The DB will take care of incrementing this column value automatically.

At any point, if you want to reset this id value, you can call "resetAutoIncrementColumns()", and then in your INSERT SQL, you do not have to specify the 'id' column at all. It will always insert a unique value when a new user is saved.

Hope this will help you.