代码之家 › 专栏 › 技术社区 › Josh

循环SQL语句

sql java

Josh · 技术社区 · 7 年前

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

@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();
    }
}

edit:我知道我需要另一个循环,可能靠近SQL语句本身

编辑:我想避免重组工作或一个非常代码繁重的解决方案。我相信这可以通过我的SQL附近的一个简单循环来完成,但是我不能理解逻辑

3 回复 | 直到 7 年前

Mahmoud Ben Hassine 7 年前

chunk-oriented processing model 这就是你要找的。可以使用块大小为5000的面向块的步骤编写Spring批处理作业。

你可以在网上找到许多写这类工作的例子 spring-batch-samples 模块。

Yannick 7 年前

为什么不做一个简单的SQL,检查DB中还剩多少行,然后用while语句检查呢?

while(SELECT COUNT(*) FROM *tablename* > 0 ) {
   *your code*
}

COUNT()

Josh 7 年前

 while (true) {
            MList<Fax> delList = new MList<>(Fax.class, //
                    "CREATE_DATE < ? " //
                            + "order by CREATE_DATE " //
                            + "offset 0 rows " //
                            + "fetch next 5000 rows ONLY", //
                    limitDate);
            if (delList.size() == 0) {
                break;
            }

这将持续执行代码,直到列表返回空。谢谢你的回复和建议。