database - Transaction Management By @@Transactional Annotation

Question

I have one requirement in which i have to insert record in one table and update the record in another table.

There are 100 records for which i have to do above insertion and updation. But If any record fails due to any error in insertion or updation only that record should be rollback not others.Means if There are 100 records and 60 gets success and 61 gets fails due to any error then only 61 records should be rolled back not all other 60 records.

I am using Spring boot JPA and @Transactional Annotation.But if put this annotation on starting of my method,Then in case of any error it roll back all records.

I am attaching my code below.Please suggest how i can achieve this. In below code i have persistRecord() method which have all my code for insertion and updation.I have not marked this with @Transactional annotation insteed below method is annotated with @Transactional annotation

        logger.debug("insertSalesTargetData method called of service class");
        String userid = (String) webSession.getAttribute("username");
        logger.debug("Userid :" + userid);
        HashMap<String, String> persistRecordStatus = new HashMap<String, String>();
        Query qCountCheck = em.createNativeQuery(QueryConstant.CHECK_SALES_TARGET_DATA_QUERY);
        List<Object> existingRecordList = null;
        persistRecordStatus.put("TOTAL_RECORD", String.valueOf(xlsRowslist.size()));
        int failedRecordCount = 0;
        int successRecordCount = 0;
        for (int i = 0; i < xlsRowslist.size(); i++) {
            ArrayList rowList = xlsRowslist.get(i);
            // try {
            double weekNoDouble = Double.parseDouble((String) rowList.get(0));// Week No
            String fromDate = (String) rowList.get(1);// fromDate
            String toDate = (String) rowList.get(2);// toDate
            double catCodeDouble = Double.parseDouble((String) rowList.get(3));// catCode
            int catCode = (int) catCodeDouble;
            int weekNo = (int) weekNoDouble;
            String target = (String) rowList.get(4);// target

            String salesGoalId = fromDate + toDate + catCode;
            salesGoalId = salesGoalId.replace("-", "");

            // Check if the sales goal id already exist in the database or not
            qCountCheck.setParameter(1, salesGoalId);// SALES_GOAL_ID
            existingRecordList = qCountCheck.getResultList();
            logger.debug("Count List Size " + existingRecordList.size());
            if (existingRecordList != null && existingRecordList.size() > 0) {
                if (existingRecordList.get(0) != null) {
                    BigDecimal row = (BigDecimal) existingRecordList.get(0);
                    // try {
                    logger.debug("Persisting record no  " + i);
                    persistRecord(row, salesGoalId, target, fromDate, toDate, userid, catCode, weekNo);
                    logger.debug("Record no  " + i + " persisted");
                    persistRecordStatus.put("SUCCESS_RECORD", String.valueOf(successRecordCount++));
                    /*
                     * } catch (Exception e) { persistRecordStatus.put("FAILED_RECORD",
                     * String.valueOf(failedRecordCount++)); }
                     */

                }
            } else {
                persistRecordStatus.put("FAILED_RECORD",String.valueOf(failedRecordCount++));
            }
            /*
             * } catch (Exception e) { logger.debug("Exception in processing record no " + i
             * + " " + e.toString()); persistRecordStatus.put("FAILED_RECORD",
             * String.valueOf(failedRecordCount++)); }
             */

        }
        return persistRecordStatus;
    }

标签： databasespringspring-bootjpatransactions

Answer 1

你正在做某种批量更新？

在批处理中，您通过将所有涉及的内容（例如输入记录、错误代码……）放在错误存储中以供某些用户随后查看来处理“失败”。并且您将其与所有“成功的”更新一起提交。

或者，将每个插入/更新对放在自己的事务中。（或者，如果您的 DBMS / 事务框架支持保存点，则在每个插入/更新对之后获取一个保存点，并且在失败时，回滚到最后一个保存点，然后提交（并找出一种不丢失剩余 40 个未处理条目的方法您的意见）。无论哪种情况，如果您遇到性能问题，请不要抱怨。

在事务批处理中没有两种方式。