How to delete alarge amount of data one by one from a table with their relations using transactional annotation

Question

I have a large amount of data that I want to purge from the database, there are about 6 tables of which 3 have a many to many relationship with cascadeType. All the others are log and history tables independent of the 3 others

i want to purge this data one by one and if any of them have error while deleting i have to undo only the current record and show it in console and keep deleting the others

I am trying to use transactional annotation with springboot but all purging stops if an error occurs

how to manage this kind of need?

here is what i did :

@Transactional
    private void purgeCards(List<CardEntity> cardsTobePurge) {

        List<Long> nextCardsNumberToUpdate = getNextCardsWhichWillNotBePurge(cardsTobePurge);

        TransactionTemplate lTransTemplate = new TransactionTemplate(transactionManager);
        lTransTemplate.setPropagationBehavior(TransactionTemplate.PROPAGATION_REQUIRED);
        lTransTemplate.execute(new TransactionCallback<Object>() {

            @Override
            public Object doInTransaction(TransactionStatus status) {
                cardsTobePurge.forEach(cardTobePurge -> {
                    Long nextCardNumberOfCurrent = cardTobePurge.getNextCard();
                    if (nextCardsNumberToUpdate.contains(nextCardNumberOfCurrent)) {
                        CardEntity cardToUnlik = cardRepository.findByCardNumber(nextCardNumberOfCurrent);
                        unLink(cardToUnlik);
                    }
                    log.info(BATCH_TITLE + " Removing card Number : " + cardTobePurge.getCardNumber() + " with Id : "
                            + cardTobePurge.getId());
                    List<CardHistoryEntity> historyEntitiesOfThisCard = cardHistoryRepository.findByCard(cardTobePurge);
                    List<LogCreationCardEntity> logCreationEntitiesForThisCard = logCreationCardRepository
                            .findByCardNumber(cardTobePurge.getCardNumber());
                    List<LogCustomerMergeEntity> logCustomerMergeEntitiesForThisCard = logCustomerMergeRepository
                            .findByCard(cardTobePurge);

                    cardHistoryRepository.deleteAll(historyEntitiesOfThisCard);
                    logCreationCardRepository.deleteAll(logCreationEntitiesForThisCard);
                    logCustomerMergeRepository.deleteAll(logCustomerMergeEntitiesForThisCard);
                    cardRepository.delete(cardTobePurge);
                });
                return Boolean.TRUE;
            }
        });
    }

Answer 1

As a solution to my question:

I worked with TransactionTemplate to be able to manage transactions manually

so if an exception is raised a rollback will only be applied for the current iteration and will continue to process other cards

private void purgeCards(List<CardEntity> cardsTobePurge) {
        int[] counter = { 0 }; //to simulate the exception
        List<Long> nextCardsNumberToUpdate = findNextCardsWhichWillNotBePurge(cardsTobePurge);
        cardsTobePurge.forEach(cardTobePurge -> {

            Long nextCardNumberOfCurrent = cardTobePurge.getNextCard();
            CardEntity cardToUnlik = null;
            counter[0]++; //to simulate the exception

            if (nextCardsNumberToUpdate.contains(nextCardNumberOfCurrent)) {
                cardToUnlik = cardRepository.findByCardNumber(nextCardNumberOfCurrent);
            }

            purgeCard(cardTobePurge, nextCardsNumberToUpdate, cardToUnlik, counter);

        });
    }

    private void purgeCard(@NonNull CardEntity cardToPurge, List<Long> nextCardsNumberToUpdate, CardEntity cardToUnlik,
            int[] counter) {

        TransactionTemplate lTransTemplate = new TransactionTemplate(transactionManager);
        lTransTemplate.setPropagationBehavior(TransactionTemplate.PROPAGATION_REQUIRED);

        lTransTemplate.execute(new TransactionCallbackWithoutResult() {

            @Override
            public void doInTransactionWithoutResult(TransactionStatus status) {
                try {

                    if (cardToUnlik != null)
                        unLink(cardToUnlik);

                    log.info(BATCH_TITLE + " Removing card Number : " + cardToPurge.getCardNumber() + " with Id : "
                            + cardToPurge.getId());

                    List<CardHistoryEntity> historyEntitiesOfThisCard = cardHistoryRepository.findByCard(cardToPurge);
                    List<LogCreationCardEntity> logCreationEntitiesForThisCard = logCreationCardRepository
                            .findByCardNumber(cardToPurge.getCardNumber());
                    List<LogCustomerMergeEntity> logCustomerMergeEntitiesForThisCard = logCustomerMergeRepository
                            .findByCard(cardToPurge);

                    cardHistoryRepository.deleteAll(historyEntitiesOfThisCard);
                    logCreationCardRepository.deleteAll(logCreationEntitiesForThisCard);

                    logCustomerMergeRepository.deleteAll(logCustomerMergeEntitiesForThisCard);
                    cardRepository.delete(cardToPurge);
                    if (counter[0] == 2)//to simulate the exception
                        throw new Exception();//to simulate the exception

                } catch (Exception e) {
                    status.setRollbackOnly();
                    if (cardToPurge != null)
                        log.error(BATCH_TITLE + " Problem with card Number : " + cardToPurge.getCardNumber()
                                + " with Id : " + cardToPurge.getId(), e);
                    else
                        log.error(BATCH_TITLE + "Card entity is null", e);
                }

            }

        });

    }