[ofbiz-framework] branch trunk updated: Implemented: EntityBatchIterator which pages through large resultsets (500 at a time by default) and added support for EntityBatchIterator to EntityQuery via 'queryBatchIterator'(OFBIZ-11789) (#191)

[Pawan Verma-2]

This is an automated email from the ASF dual-hosted git repository.

pawan pushed a commit to branch trunk
in repository https://gitbox.apache.org/repos/asf/ofbiz-framework.git


The following commit(s) were added to refs/heads/trunk by this push:
     new 71ac32c  Implemented: EntityBatchIterator which pages through large resultsets (500 at a time by default) and added support for EntityBatchIterator to EntityQuery via 'queryBatchIterator'(OFBIZ-11789) (#191)
71ac32c is described below

commit 71ac32c89b29a56cb76952e641e006eb120b5e8b
Author: Pawan Verma <[hidden email]>
AuthorDate: Sat Jul 4 23:02:47 2020 +0530

    Implemented: EntityBatchIterator which pages through large resultsets (500 at a time by default) and added support for EntityBatchIterator to EntityQuery via 'queryBatchIterator'(OFBIZ-11789) (#191)
   
    Modified GenericDao#makeOffsetString to have OFFSET/FETCH style by default
    Modified entityengine's mysql and postgres datasources to have LIMIT/OFFSET style, rest can work with default OFFSET/FETCH style
   
    Thanks: Scott for the help.
---
 framework/entity/config/entityengine.xml           | 19 +++--
 .../apache/ofbiz/entity/datasource/GenericDAO.java |  2 +-
 .../ofbiz/entity/util/EntityBatchIterator.java     | 92 ++++++++++++++++++++++
 .../org/apache/ofbiz/entity/util/EntityQuery.java  |  5 ++
 4 files changed, 111 insertions(+), 7 deletions(-)

diff --git a/framework/entity/config/entityengine.xml b/framework/entity/config/entityengine.xml
index 83341c8..8e4301c 100644
--- a/framework/entity/config/entityengine.xml
+++ b/framework/entity/config/entityengine.xml
@@ -347,7 +347,8 @@ access. For a detailed description see the core/docs/entityconfig.html file.
             drop-fk-use-foreign-key-keyword="true"
             table-type="InnoDB"
             character-set="utf8"
-            collate="utf8_general_ci">
+            collate="utf8_general_ci"
+            offset-style="limit">
         <read-data reader-name="tenant"/>
         <read-data reader-name="seed"/>
         <read-data reader-name="seed-initial"/>
@@ -380,7 +381,8 @@ access. For a detailed description see the core/docs/entityconfig.html file.
             drop-fk-use-foreign-key-keyword="true"
             table-type="InnoDB"
             character-set="utf8"
-            collate="utf8_general_ci">
+            collate="utf8_general_ci"
+            offset-style="limit">
         <read-data reader-name="tenant"/>
         <read-data reader-name="seed"/>
         <read-data reader-name="seed-initial"/>
@@ -413,7 +415,8 @@ access. For a detailed description see the core/docs/entityconfig.html file.
             drop-fk-use-foreign-key-keyword="true"
             table-type="InnoDB"
             character-set="utf8"
-            collate="utf8_general_ci">
+            collate="utf8_general_ci"
+            offset-style="limit">
         <read-data reader-name="tenant"/>
         <read-data reader-name="seed"/>
         <read-data reader-name="seed-initial"/>
@@ -446,7 +449,8 @@ access. For a detailed description see the core/docs/entityconfig.html file.
             drop-fk-use-foreign-key-keyword="true"
             table-type="InnoDB"
             character-set="utf8"
-            collate="utf8_general_ci">
+            collate="utf8_general_ci"
+            offset-style="limit">
         <read-data reader-name="tenant"/>
         <read-data reader-name="seed"/>
         <inline-jdbc
@@ -472,6 +476,7 @@ access. For a detailed description see the core/docs/entityconfig.html file.
             join-style="ansi"
             use-binary-type-for-blob="true"
             use-order-by-nulls="true"
+            offset-style="limit"
             result-fetch-size="50"> <!-- Comment out the result-fetch-size attribute for jdbc driver versions older than 8.0.
             Not recommended to use those though. They are archived unsupported versions: http://jdbc.postgresql.org/download.html -->
         
@@ -510,7 +515,8 @@ access. For a detailed description see the core/docs/entityconfig.html file.
             join-style="ansi"
             result-fetch-size="50"
             use-binary-type-for-blob="true"
-            use-order-by-nulls="true">
+            use-order-by-nulls="true"
+            offset-style="limit">
         <read-data reader-name="tenant"/>
         <read-data reader-name="seed"/>
         <read-data reader-name="seed-initial"/>
@@ -546,7 +552,8 @@ access. For a detailed description see the core/docs/entityconfig.html file.
             join-style="ansi"
             result-fetch-size="50"
             use-binary-type-for-blob="true"
-            use-order-by-nulls="true">
+            use-order-by-nulls="true"
+            offset-style="limit">
         <read-data reader-name="tenant"/>
         <read-data reader-name="seed"/>
         <read-data reader-name="seed-initial"/>
diff --git a/framework/entity/src/main/java/org/apache/ofbiz/entity/datasource/GenericDAO.java b/framework/entity/src/main/java/org/apache/ofbiz/entity/datasource/GenericDAO.java
index 2044e19..0c7f1d3 100644
--- a/framework/entity/src/main/java/org/apache/ofbiz/entity/datasource/GenericDAO.java
+++ b/framework/entity/src/main/java/org/apache/ofbiz/entity/datasource/GenericDAO.java
@@ -835,7 +835,7 @@ public class GenericDAO {
                         offsetString.append(" OFFSET " + findOptions.getOffset());
                     }
                 }
-            } else if ("fetch".equals(datasource.getOffsetStyle())) {
+            } else {
                 // use SQL2008 OFFSET/FETCH style by default
                 if (findOptions.getOffset() > -1) {
                     offsetString.append(" OFFSET ").append(findOptions.getOffset()).append(" ROWS");
diff --git a/framework/entity/src/main/java/org/apache/ofbiz/entity/util/EntityBatchIterator.java b/framework/entity/src/main/java/org/apache/ofbiz/entity/util/EntityBatchIterator.java
new file mode 100644
index 0000000..20274e8
--- /dev/null
+++ b/framework/entity/src/main/java/org/apache/ofbiz/entity/util/EntityBatchIterator.java
@@ -0,0 +1,92 @@
+/*******************************************************************************
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ *******************************************************************************/
+package org.apache.ofbiz.entity.util;
+
+import java.util.Iterator;
+import java.util.List;
+
+import org.apache.ofbiz.base.util.Debug;
+import org.apache.ofbiz.entity.GenericEntityException;
+import org.apache.ofbiz.entity.GenericValue;
+import org.apache.ofbiz.entity.util.EntityQuery;
+
+public class EntityBatchIterator implements Iterator<GenericValue> {
+    public static final String MODULE = EntityBatchIterator.class.getName();
+
+    private EntityQuery query;
+    List<GenericValue> currentResultSet;
+    int currentIndex = 0;
+    private int currentOffset = 0;
+
+    public EntityBatchIterator(EntityQuery query) {
+        this.query = query;
+        if (query.getLimit() == null) {
+            query.limit(500);
+        }
+        if (query.getOffset() == null) {
+            query.offset(0);
+        }
+        // Just in case the query already has a non-zero offset, we need to continue from that point
+        currentOffset = query.getOffset();
+    }
+
+    @Override
+    public boolean hasNext() {
+        try {
+            return hasNextWithException();
+        } catch (GenericEntityException e) {
+            throw new RuntimeException(e);
+        }
+    }
+
+    public boolean hasNextWithException() throws GenericEntityException {
+        if (needNextBatch()) {
+            getNextBatch();
+        }
+        return !currentResultSet.isEmpty() && currentResultSet.size() > currentIndex;
+    }
+
+    @Override
+    public GenericValue next() {
+        try {
+            return nextWithException();
+        } catch (GenericEntityException e) {
+            throw new RuntimeException(e);
+        }
+    }
+
+    public GenericValue nextWithException() throws GenericEntityException {
+        return hasNextWithException() ? currentResultSet.get(currentIndex++) : null;
+    }
+
+    private void getNextBatch() throws GenericEntityException {
+        Debug.logInfo("Getting next batch with offset: " + currentOffset, MODULE);
+        currentResultSet = this.query.offset(currentOffset).queryList();
+        Debug.logInfo("Retreived row count: " + currentResultSet.size(), MODULE);
+        currentOffset += query.getLimit();
+        currentIndex = 0;
+    }
+
+    private boolean needNextBatch() {
+        // Return true if we haven't fetched anything yet, or
+        // if we're about to go out of bounds on the current batch
+        return currentResultSet == null || currentIndex >= currentResultSet.size();
+    }
+
+}
diff --git a/framework/entity/src/main/java/org/apache/ofbiz/entity/util/EntityQuery.java b/framework/entity/src/main/java/org/apache/ofbiz/entity/util/EntityQuery.java
index 2564908..7fcb67c 100644
--- a/framework/entity/src/main/java/org/apache/ofbiz/entity/util/EntityQuery.java
+++ b/framework/entity/src/main/java/org/apache/ofbiz/entity/util/EntityQuery.java
@@ -39,6 +39,7 @@ import org.apache.ofbiz.entity.GenericPK;
 import org.apache.ofbiz.entity.GenericValue;
 import org.apache.ofbiz.entity.condition.EntityCondition;
 import org.apache.ofbiz.entity.model.DynamicViewEntity;
+import org.apache.ofbiz.entity.util.EntityBatchIterator;
 
 /**
  * Used to setup various options for and subsequently execute entity queries.
@@ -419,6 +420,10 @@ public class EntityQuery {
         }
     }
 
+    public EntityBatchIterator queryBatchIterator() {
+        return new EntityBatchIterator(this);
+    }
+
     /** Executes the EntityQuery and returns the first result
      *
      * @return GenericValue representing the first result record from the query