Constraint violation when reading data

Question

I am writing integration tests with junit-jupiter and something very strange is happening -> Constraint violation exception occurs when I am reading (not saving the data)

storesTemplateRepository.findByCountryOrderByTemplateName(country, pageable); raises the following exception:

could not execute statement; SQL [n/a]; constraint ["PRIMARY KEY ON PUBLIC.STORES_TEMPLATE(ID)"; SQL statement:
insert into stores_template (country, stores, template_name, id) values (?, ?, ?, ?) [23505-196]]

Entity:

@Entity
@Getter
@Setter
@NoArgsConstructor
@Table(name = "STORES_TEMPLATE")
public class StoresTemplate {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "STORES_TEMPLATE_ID_SEQ")
    @SequenceGenerator(name = "STORES_TEMPLATE_ID_SEQ", sequenceName = "STORES_TEMPLATE_ID_SEQ", allocationSize = 1)
    private long id;

    @Enumerated(EnumType.STRING)
    private CountryEnum country;

    private String templateName;

    @Lob
    private String stores;

    public void setStores(List<String> stores) {
        this.stores = String.join(",", stores);
    }

    @JsonIgnore
    public List<String> getStoresAsList() {
        return Arrays.stream(stores.split(","))
                .distinct()
                .collect(Collectors.toList());
    }

}

Test

@Slf4j
@Transactional
@SpringBootTest
public class StoresTemplateControllerTest {

    @Autowired
    private WebApplicationContext context;

    @Autowired
    private ObjectMapper objectMapper;

    @Autowired
    private StoresTemplateRepository storesTemplateRepository;

    private MockMvc mockMvc;

    @BeforeEach
    public void setUp() {
        mockMvc = MockMvcBuilders
                .webAppContextSetup(context)
                .apply(SecurityMockMvcConfigurers.springSecurity())
                .build();
    }

    @Test
    public void fullApiTest() {
        OAuth2AuthenticationToken dePrincipal = new TestDataBuilder()
                .createOAuth2AuthenticationToken()
                .setDefaultStoreUser()
                .setRoles(SiamRoles.DE_CREATOR_ADMIN)
                .build();

        CreateStoresTemplateDto createStoresTemplateDeDto = CreateStoresTemplateDto.builder()
                .country(CountryEnum.DE)
                .stores(List.of("de1000", "de1100"))
                .templateName("de template")
                .build();

        CreateStoresTemplateDto createStoresTemplateBgDto = CreateStoresTemplateDto.builder()
                .country(CountryEnum.BG)
                .stores(List.of("bg2000", "bg2100"))
                .templateName("bg template")
                .build();

        CreateStoresTemplateDto createStoresTemplateDefaultDto = CreateStoresTemplateDto.builder()
                .country(null)
                .stores(List.of("de3000", "de3100"))
                .templateName("default template")
                .build();

        try {

            // find all existing by predefined insertion script
            for (long id: findAll(dePrincipal, CountryEnum.DE).map(e -> e.id)) {
                storesTemplateRepository.deleteById(id);
            }


            storesTemplateRepository.save(StoresTemplateMapper.toStoresTemplate(createStoresTemplateDeDto));
            storesTemplateRepository.findByCountryOrderByTemplateName(CountryEnum.DE);

            storesTemplateRepository.save(StoresTemplateMapper.toStoresTemplate(createStoresTemplateBgDto));
            storesTemplateRepository.findByCountryOrderByTemplateName(CountryEnum.DE); // Here the exception occurs

            storesTemplateRepository.save(StoresTemplateMapper.toStoresTemplate(createStoresTemplateDefaultDto));
            storesTemplateRepository.findByCountryOrderByTemplateName(CountryEnum.DE);
            

        } catch (Exception e) {
            e.printStackTrace();
            fail(e.getMessage());
        }
    }

}

Answer 1

• JPA/Hibernate queues the operations in its session whenever possible, does not call the database instantly and then just before the transaction is completing, order those operations based on type and execute them. This is called Transactional write-behind in hibernate. As you can see, even though you called the delete first, hibernate will order it as last if it was queued.

  1. Inserts, in the order they were performed
  2. Updates
  3. Deletion of collection elements
  4. Insertion of collection elements
  5. Deletes, in the order they were performed

• So even though you do delete first as you can see hibernate will do it last. If you want to control the order, you need to flush it. So do the following.

    for (long id: findAll(dePrincipal, CountryEnum.DE).map(e -> e.id)) {
        storesTemplateRepository.deleteById(id);
    }
    storesTemplateRepository.flush();

Reference

• Executions Order