java - 循环SQL语句

Question

我的代码获取 5,000 行并进行一些处理，然后停止。我想循环代码，以便在处理 5,000 行后它将获得另外 5,000 行并再次处理。我希望它这样做，直到没有更多的行为止。

如果有人能给我一个示例或伪代码，那就太棒了，我无法理解这个逻辑。

@Component("Inv226")
@Scope("prototype")
public class Inv226 extends BatchProgram {

    @Override
    public void process() {
        // Make it so the printing goes to the result page.
        this.webOn();
        print(" ", false);
        print("*****************************************", false);
        print("** B.INV226 Begins: " + getTime() + " " + date(YearFormat.YYYY), false);
        print("*****************************************", false);
        // Find the first 5,000 records to remove.

        //TODO find a way to keep looping
        // and obtaining another set of 5,000 records
        // when we're done processing each result set
        Calendar limitDate = Calendar.getInstance();
        limitDate.add(Calendar.DAY_OF_YEAR, -1095);
        MList<Inv> delList = new MList<>(Inv.class, //
                "CREATE_DATE < ? " //
                        + "order by CREATE_DATE " //
                        + "offset 0 rows " //
                        + "fetch next 5000 rows ONLY", //
                limitDate);
        // Set the commit limit.
        int commitLimit = 50;
        int i = 0;
        int delCount = 0;
        // Loop through them...
        for (Inv inv : delList) {
            // Remove each.
            inv.delete();
            if (++i >= commitLimit) {
                try {
                    this.commit();
                    print("Succesfully deleted " + i + " records");
                    delCount += i;
                    i = 0;
                    // Do we want a delay here?
                } catch (MccMidusException mme) {
                    // Is this a timestamp mismatch?
                    if ("0080".equals(mme.getErrorCode())) {
                        // Poke the record so the next invocation of Eddbl will process it correctly,
                        // along with the other 49 records in this block.
                        // Assuming the poke is successful, processing on the remaining Edb003 records in the
                        // block of 5000 will continue.
                        this.getIoHarness().pokeUow(mme.getUow());
                        // Call commit to get a new transaction started.
                        this.commit();
                        // Reset the counter.
                        i = 0;
                    } else {
                        throw mme;
                    }
                }
            }
        }
        delCount += i;
        // Ensure anything else gets committed.
        this.commit();
        print("*****************************************", false);
        print("** B.INV226 TOTAL DELETES OF GBL RECORDS OLDER THAN " + Calendar.DAY_OF_YEAR, -1095, false);
        print("** TOTAL RECORDS DELETED = " + delCount, false);
        print("** EDD003 Cleanup Ends : " + date(YearFormat.YYYY) + " " + getTime(), false);
        print("*****************************************", false);
        print(" ", false);

        this.webDone();
    }
}

编辑:我知道我需要在某个地方另一个循环，可能靠近 SQL 语句本身

编辑:我想避免重组工作或代码量很大的解决方案。我相信这可以通过我的 SQL 附近的一个简单循环来完成，但无法弄清楚逻辑

Answer 1

Spring Batch 提供 chunk-oriented processing model这就是您正在寻找的。您可以使用 block 大小为 5000 的面向 block 的步骤编写 Spring Batch 作业。

您可以在 spring-batch-samples 中找到许多如何编写此类作业的示例。模块。