Fw: svn commit: r1422221 - in /ofbiz/trunk/framework/entity: dtd/ src/org/ofbiz/entity/config/ src/org/ofbiz/entity/datasource/ src/org/ofbiz/entity/test/ src/org/ofbiz/entity/util/

[Jacques Le Roux]

Yes saw that, we can maybe simply comment out the EntityTestSuite.testLimitOffsetOptions() method?

Jacques

Adrian Crum wrote:

> This commit makes the framework dependent on the Content component.
>
> -Adrian
>
> On 12/15/2012 11:20 AM, [hidden email] wrote:
>> Author: jleroux
>> Date: Sat Dec 15 11:20:13 2012
>> New Revision: 1422221
>>
>> URL: http://svn.apache.org/viewvc?rev=1422221&view=rev
>> Log:
>> A patch from Shi Jinghai for "Support MySQL and Postgres's LIMIT and OFFSET options"
>> https://issues.apache.org/jira/browse/OFBIZ-4346 
>>
>> Change the existing code to support the different syntax variations, and add an attribute to the datasource element in the
>> entityengine.xml file so that the proper variation can be chosen for each database. Inspired by Moqui code. Test successful
>>
>> Modified:
>>      ofbiz/trunk/framework/entity/dtd/entity-config.xsd
>>      ofbiz/trunk/framework/entity/src/org/ofbiz/entity/config/DatasourceInfo.java
>>      ofbiz/trunk/framework/entity/src/org/ofbiz/entity/datasource/GenericDAO.java
>>      ofbiz/trunk/framework/entity/src/org/ofbiz/entity/test/EntityTestSuite.java
>>      ofbiz/trunk/framework/entity/src/org/ofbiz/entity/util/EntityFindOptions.java
>>
>> Modified: ofbiz/trunk/framework/entity/dtd/entity-config.xsd
>> URL: http://svn.apache.org/viewvc/ofbiz/trunk/framework/entity/dtd/entity-config.xsd?rev=1422221&r1=1422220&r2=1422221&view=diff
>> ==============================================================================
>> --- ofbiz/trunk/framework/entity/dtd/entity-config.xsd (original)
>> +++ ofbiz/trunk/framework/entity/dtd/entity-config.xsd Sat Dec 15 11:20:13 2012
>> @@ -399,6 +399,15 @@ under the License.
>>                   </xs:restriction>
>>               </xs:simpleType>
>>           </xs:attribute>
>> +        <xs:attribute name="offset-style" default="none">
>> +            <xs:simpleType>
>> +                <xs:restriction base="xs:token">
>> +                    <xs:enumeration value="none"/>
>> +                    <xs:enumeration value="fetch"/>
>> +                    <xs:enumeration value="limit"/>
>> +                </xs:restriction>
>> +            </xs:simpleType>
>> +        </xs:attribute>
>>           <xs:attribute type="xs:string" name="table-type"/>
>>           <xs:attribute type="xs:string" name="character-set"/>
>>           <xs:attribute type="xs:string" name="collate"/>
>>
>> Modified: ofbiz/trunk/framework/entity/src/org/ofbiz/entity/config/DatasourceInfo.java
>> URL:
>> http://svn.apache.org/viewvc/ofbiz/trunk/framework/entity/src/org/ofbiz/entity/config/DatasourceInfo.java?rev=1422221&r1=1422220&r2=1422221&view=diff
>> ============================================================================== ---
>> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/config/DatasourceInfo.java (original) +++
>> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/config/DatasourceInfo.java Sat Dec 15 11:20:13 2012 @@ -72,6 +72,7 @@ public
>>       class DatasourceInfo extends Name public boolean dropFkUseForeignKeyKeyword = false;
>>       public boolean useBinaryTypeForBlob = false;
>>       public boolean useOrderByNulls = false;
>> +    public String offsetStyle = null;
>>       public String tableType = null;
>>       public String characterSet = null;
>>       public String collate = null;
>> @@ -158,7 +159,8 @@ public class DatasourceInfo extends Name
>>               this.dropFkUseForeignKeyKeyword = "true".equals(datasourceElement.getAttribute("drop-fk-use-foreign-key-keyword"));
>>               this.useBinaryTypeForBlob = "true".equals(datasourceElement.getAttribute("use-binary-type-for-blob"));
>>               this.useOrderByNulls = "true".equals(datasourceElement.getAttribute("use-order-by-nulls"));
>> -
>> +
>> +            this.offsetStyle = datasourceElement.getAttribute("offset-style");
>>               this.tableType = datasourceElement.getAttribute("table-type");
>>               this.characterSet = datasourceElement.getAttribute("character-set");
>>               this.collate = datasourceElement.getAttribute("collate");
>>
>> Modified: ofbiz/trunk/framework/entity/src/org/ofbiz/entity/datasource/GenericDAO.java
>> URL:
>> http://svn.apache.org/viewvc/ofbiz/trunk/framework/entity/src/org/ofbiz/entity/datasource/GenericDAO.java?rev=1422221&r1=1422220&r2=1422221&view=diff
>> ============================================================================== ---
>> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/datasource/GenericDAO.java (original) +++
>> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/datasource/GenericDAO.java Sat Dec 15 11:20:13 2012 @@ -763,6 +763,9 @@ public
>>           class GenericDAO { }
>>           sqlBuffer.append(SqlJdbcUtil.makeOrderByClause(modelEntity, orderByExpanded, datasourceInfo));
>>
>> +        // OFFSET clause
>> +        makeOffsetString(sqlBuffer, findOptions);
>> +
>>           // make the final SQL String
>>           String sql = sqlBuffer.toString();
>>
>> @@ -884,6 +887,29 @@ public class GenericDAO {
>>           return havingString;
>>       }
>>
>> +    protected StringBuilder makeOffsetString(StringBuilder offsetString, EntityFindOptions findOptions) {
>> +        if (UtilValidate.isNotEmpty(datasourceInfo.offsetStyle)) {
>> +            if (datasourceInfo.offsetStyle.equals("limit")) {
>> +                // use the LIMIT/OFFSET style
>> +                if (findOptions.getLimit() > -1) {
>> +                    offsetString.append(" LIMIT " + findOptions.getLimit());
>> +                    if (findOptions.getOffset() > -1) {
>> +                        offsetString.append(" OFFSET " + findOptions.getOffset());
>> +                    }
>> +                }
>> +            } else if (datasourceInfo.offsetStyle.equals("fetch")) {
>> +                // use SQL2008 OFFSET/FETCH style by default
>> +                if (findOptions.getOffset() > -1) {
>> +                    offsetString.append(" OFFSET ").append(findOptions.getOffset()).append(" ROWS");
>> +                    if (findOptions.getLimit() > -1) {
>> +                        offsetString.append(" FETCH FIRST ").append(findOptions.getLimit()).append(" ROWS ONLY");
>> +                    }
>> +                }
>> +            }
>> +        }
>> +        return offsetString;
>> +    }
>> +
>>       public List<GenericValue> selectByMultiRelation(GenericValue value, ModelRelation modelRelationOne, ModelEntity
>>           modelEntityOne, ModelRelation modelRelationTwo, ModelEntity modelEntityTwo, List<String> orderBy) throws
>>           GenericEntityException { SQLProcessor sqlP = new SQLProcessor(helperInfo);
>>
>> Modified: ofbiz/trunk/framework/entity/src/org/ofbiz/entity/test/EntityTestSuite.java
>> URL:
>> http://svn.apache.org/viewvc/ofbiz/trunk/framework/entity/src/org/ofbiz/entity/test/EntityTestSuite.java?rev=1422221&r1=1422220&r2=1422221&view=diff
>> ============================================================================== ---
>> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/test/EntityTestSuite.java (original) +++
>> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/test/EntityTestSuite.java Sat Dec 15 11:20:13 2012 @@ -31,6 +31,7 @@ import
>>   java.util.Map; import org.ofbiz.base.util.Debug;
>>   import org.ofbiz.base.util.UtilDateTime;
>>   import org.ofbiz.base.util.UtilMisc;
>> +import org.ofbiz.base.util.UtilValidate;
>>   import org.ofbiz.base.util.UtilXml;
>>   import org.ofbiz.entity.Delegator;
>>   import org.ofbiz.entity.DelegatorFactory;
>> @@ -44,6 +45,8 @@ import org.ofbiz.entity.condition.Entity
>>   import org.ofbiz.entity.condition.EntityOperator;
>>   import org.ofbiz.entity.config.DatasourceInfo;
>>   import org.ofbiz.entity.config.EntityConfigUtil;
>> +import org.ofbiz.entity.datasource.GenericHelperDAO;
>> +import org.ofbiz.entity.model.ModelEntity;
>>   import org.ofbiz.entity.testtools.EntityTestCase;
>>   import org.ofbiz.entity.transaction.GenericTransactionException;
>>   import org.ofbiz.entity.transaction.TransactionUtil;
>> @@ -604,4 +607,94 @@ public class EntityTestSuite extends Ent
>>           strBufTemp.append(iNum);
>>           return strBufTemp.toString();
>>       }
>> +
>> +    /*
>> +     * This test will verify that the LIMIT and OFFSET options can work properly.
>> +     */
>> +    public void testLimitOffsetOptions() throws Exception {
>> +        String entityName = "Content";
>> +        DatasourceInfo datasourceInfo =
>> EntityConfigUtil.getDatasourceInfo(delegator.getEntityHelper(entityName).getHelperName()); +        if
>> (UtilValidate.isEmpty(datasourceInfo.offsetStyle) || datasourceInfo.offsetStyle.equals("none")) { +          
>> Debug.logInfo("The offset-stype configured in datasource is " + datasourceInfo.offsetStyle +  ", this test is skipped.",
>> module); +            return; +        } else {
>> +            Debug.logInfo("The offset-stype configured in datasource is " + datasourceInfo.offsetStyle +  ".", module);
>> +        }
>> +        try {
>> +            EntityFindOptions findOptions = new EntityFindOptions();
>> +            long count = delegator.findCountByCondition("Content", null, null, null);
>> +            Debug.logInfo("Content entity has " + count + " rows", module);
>> +            int rowsPerPage = 10;
>> +            // use rows/page as limit option
>> +            findOptions.setLimit(rowsPerPage);
>> +            int pages = (int) count/rowsPerPage;
>> +            if (count > pages * rowsPerPage) {
>> +                pages += 1;
>> +            }
>> +            Debug.logInfo("These rows will be displayed in " + pages + " pages, each page has " + rowsPerPage + " rows.",
>> module); +            ModelEntity modelEntity = delegator.getModelEntity(entityName);
>> +
>> +            long start = UtilDateTime.nowTimestamp().getTime();
>> +            for (int page = 1; page <= pages; page++) {
>> +                Debug.logInfo("Page " + page + ":", module);
>> +                // set offset option
>> +                findOptions.setOffset((page - 1) * rowsPerPage);
>> +                EntityListIterator iterator = null;
>> +                try {
>> +                    iterator = delegator.getEntityHelper(entityName).findListIteratorByCondition(modelEntity, null, null, null,
>> UtilMisc.toList("lastUpdatedStamp DESC"), findOptions); +                    while (iterator != null) {
>> +                        GenericValue gv = iterator.next();
>> +                        if (gv == null) {
>> +                            break;
>> +                        }
>> +                        Debug.logInfo(gv.getString("contentId") + ": " + gv.getString("contentName") + "       (updated: " +
>> gv.getTimestamp("lastUpdatedStamp") + ")", module); +                    }
>> +                } catch (GenericEntityException e) {
>> +                    Debug.logError(e, module);
>> +                } finally {
>> +                    if (iterator != null) {
>> +                        iterator.close();
>> +                    }
>> +                }
>> +            }
>> +            long end = UtilDateTime.nowTimestamp().getTime();
>> +            long time1 = end - start;
>> +            Debug.logInfo("Time consumed WITH limit and offset option (ms): " + time1, module);
>> +
>> +            start = UtilDateTime.nowTimestamp().getTime();
>> +            for (int page = 1; page <= pages; page++) {
>> +                Debug.logInfo("Page " + page + ":", module);
>> +                EntityListIterator iterator = null;
>> +                try {
>> +                    iterator = ((GenericHelperDAO)
>> delegator.getEntityHelper(entityName)).findListIteratorByCondition(modelEntity, null, null, null,
>> UtilMisc.toList("lastUpdatedStamp DESC"), null); +                    if (iterator == null) { +                        continue;
>> +                    }
>> +                    iterator.setDelegator(delegator);
>> +                    List<GenericValue> gvs = iterator.getCompleteList();
>> +                    int fromIndex = (page - 1) * rowsPerPage;
>> +                    int toIndex = fromIndex + rowsPerPage;
>> +                    if (toIndex > count) {
>> +                        toIndex = (int) count;
>> +                    }
>> +                    gvs = gvs.subList(fromIndex, toIndex);
>> +                    for (GenericValue gv : gvs) {
>> +                        Debug.logInfo(gv.getString("contentId") + ": " + gv.getString("contentName") + "       (updated: " +
>> gv.getTimestamp("lastUpdatedStamp") + ")", module); +                    }
>> +                } catch (GenericEntityException e) {
>> +                    Debug.logError(e, module);
>> +                } finally {
>> +                    if (iterator != null) {
>> +                        iterator.close();
>> +                    }
>> +                }
>> +            }
>> +            end = UtilDateTime.nowTimestamp().getTime();
>> +            long time2 = end - start;
>> +            Debug.logInfo("Time consumed WITHOUT limit and offset option (ms): " + time2, module);
>> +            Debug.logInfo("Time saved (ms): " + (time2 - time1), module);
>> +        } catch (GenericEntityException e) {
>> +            Debug.logError(e, module);
>> +        }
>> +    }
>>   }
>>
>> Modified: ofbiz/trunk/framework/entity/src/org/ofbiz/entity/util/EntityFindOptions.java
>> URL:
>> http://svn.apache.org/viewvc/ofbiz/trunk/framework/entity/src/org/ofbiz/entity/util/EntityFindOptions.java?rev=1422221&r1=1422220&r2=1422221&view=diff
>> ============================================================================== ---
>> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/util/EntityFindOptions.java (original) +++
>> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/util/EntityFindOptions.java Sat Dec 15 11:20:13 2012 @@ -48,6 +48,12 @@ public
>>       class EntityFindOptions implement protected int maxRows = -1;
>>       protected boolean distinct = false;
>>
>> +    /** LIMIT option */
>> +    protected int limit = -1;
>> +
>> +    /** OFFSET option */
>> +    protected int offset = -1;
>> +
>>       /** Default constructor. Defaults are as follows:
>>        *      specifyTypeAndConcur = true
>>        *      resultSetType = TYPE_FORWARD_ONLY
>> @@ -145,4 +151,25 @@ public class EntityFindOptions implement
>>       public void setDistinct(boolean distinct) {
>>           this.distinct = distinct;
>>       }
>> +
>> +
>> +    /** Get the LIMIT number. */
>> +    public int getLimit() {
>> +        return limit;
>> +    }
>> +
>> +    /** Specifies the LIMIT number. */
>> +    public void setLimit(int limit) {
>> +        this.limit = limit;
>> +    }
>> +
>> +    /** Get the OFFSET number. */
>> +    public int getOffset() {
>> +        return offset;
>> +    }
>> +
>> +    /** Specifies the OFFSET number. */
>> +    public void setOffset(int offset) {
>> +        this.offset = offset;
>> +    }
>>   }

[Adrian Crum-3]

Or ask the contributor to fix it.

-Adrian

On 12/15/2012 11:46 AM, Jacques Le Roux wrote:

> Yes saw that, we can maybe simply comment out the EntityTestSuite.testLimitOffsetOptions() method?
>
> Jacques
>
> Adrian Crum wrote:
>> This commit makes the framework dependent on the Content component.
>>
>> -Adrian
>>
>> On 12/15/2012 11:20 AM, [hidden email] wrote:
>>> Author: jleroux
>>> Date: Sat Dec 15 11:20:13 2012
>>> New Revision: 1422221
>>>
>>> URL: http://svn.apache.org/viewvc?rev=1422221&view=rev
>>> Log:
>>> A patch from Shi Jinghai for "Support MySQL and Postgres's LIMIT and OFFSET options"
>>> https://issues.apache.org/jira/browse/OFBIZ-4346
>>>
>>> Change the existing code to support the different syntax variations, and add an attribute to the datasource element in the
>>> entityengine.xml file so that the proper variation can be chosen for each database. Inspired by Moqui code. Test successful
>>>
>>> Modified:
>>>       ofbiz/trunk/framework/entity/dtd/entity-config.xsd
>>>       ofbiz/trunk/framework/entity/src/org/ofbiz/entity/config/DatasourceInfo.java
>>>       ofbiz/trunk/framework/entity/src/org/ofbiz/entity/datasource/GenericDAO.java
>>>       ofbiz/trunk/framework/entity/src/org/ofbiz/entity/test/EntityTestSuite.java
>>>       ofbiz/trunk/framework/entity/src/org/ofbiz/entity/util/EntityFindOptions.java
>>>
>>> Modified: ofbiz/trunk/framework/entity/dtd/entity-config.xsd
>>> URL: http://svn.apache.org/viewvc/ofbiz/trunk/framework/entity/dtd/entity-config.xsd?rev=1422221&r1=1422220&r2=1422221&view=diff
>>> ==============================================================================
>>> --- ofbiz/trunk/framework/entity/dtd/entity-config.xsd (original)
>>> +++ ofbiz/trunk/framework/entity/dtd/entity-config.xsd Sat Dec 15 11:20:13 2012
>>> @@ -399,6 +399,15 @@ under the License.
>>>                    </xs:restriction>
>>>                </xs:simpleType>
>>>            </xs:attribute>
>>> +        <xs:attribute name="offset-style" default="none">
>>> +            <xs:simpleType>
>>> +                <xs:restriction base="xs:token">
>>> +                    <xs:enumeration value="none"/>
>>> +                    <xs:enumeration value="fetch"/>
>>> +                    <xs:enumeration value="limit"/>
>>> +                </xs:restriction>
>>> +            </xs:simpleType>
>>> +        </xs:attribute>
>>>            <xs:attribute type="xs:string" name="table-type"/>
>>>            <xs:attribute type="xs:string" name="character-set"/>
>>>            <xs:attribute type="xs:string" name="collate"/>
>>>
>>> Modified: ofbiz/trunk/framework/entity/src/org/ofbiz/entity/config/DatasourceInfo.java
>>> URL:
>>> http://svn.apache.org/viewvc/ofbiz/trunk/framework/entity/src/org/ofbiz/entity/config/DatasourceInfo.java?rev=1422221&r1=1422220&r2=1422221&view=diff
>>> ============================================================================== ---
>>> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/config/DatasourceInfo.java (original) +++
>>> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/config/DatasourceInfo.java Sat Dec 15 11:20:13 2012 @@ -72,6 +72,7 @@ public
>>>        class DatasourceInfo extends Name public boolean dropFkUseForeignKeyKeyword = false;
>>>        public boolean useBinaryTypeForBlob = false;
>>>        public boolean useOrderByNulls = false;
>>> +    public String offsetStyle = null;
>>>        public String tableType = null;
>>>        public String characterSet = null;
>>>        public String collate = null;
>>> @@ -158,7 +159,8 @@ public class DatasourceInfo extends Name
>>>                this.dropFkUseForeignKeyKeyword = "true".equals(datasourceElement.getAttribute("drop-fk-use-foreign-key-keyword"));
>>>                this.useBinaryTypeForBlob = "true".equals(datasourceElement.getAttribute("use-binary-type-for-blob"));
>>>                this.useOrderByNulls = "true".equals(datasourceElement.getAttribute("use-order-by-nulls"));
>>> -
>>> +
>>> +            this.offsetStyle = datasourceElement.getAttribute("offset-style");
>>>                this.tableType = datasourceElement.getAttribute("table-type");
>>>                this.characterSet = datasourceElement.getAttribute("character-set");
>>>                this.collate = datasourceElement.getAttribute("collate");
>>>
>>> Modified: ofbiz/trunk/framework/entity/src/org/ofbiz/entity/datasource/GenericDAO.java
>>> URL:
>>> http://svn.apache.org/viewvc/ofbiz/trunk/framework/entity/src/org/ofbiz/entity/datasource/GenericDAO.java?rev=1422221&r1=1422220&r2=1422221&view=diff
>>> ============================================================================== ---
>>> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/datasource/GenericDAO.java (original) +++
>>> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/datasource/GenericDAO.java Sat Dec 15 11:20:13 2012 @@ -763,6 +763,9 @@ public
>>>            class GenericDAO { }
>>>            sqlBuffer.append(SqlJdbcUtil.makeOrderByClause(modelEntity, orderByExpanded, datasourceInfo));
>>>
>>> +        // OFFSET clause
>>> +        makeOffsetString(sqlBuffer, findOptions);
>>> +
>>>            // make the final SQL String
>>>            String sql = sqlBuffer.toString();
>>>
>>> @@ -884,6 +887,29 @@ public class GenericDAO {
>>>            return havingString;
>>>        }
>>>
>>> +    protected StringBuilder makeOffsetString(StringBuilder offsetString, EntityFindOptions findOptions) {
>>> +        if (UtilValidate.isNotEmpty(datasourceInfo.offsetStyle)) {
>>> +            if (datasourceInfo.offsetStyle.equals("limit")) {
>>> +                // use the LIMIT/OFFSET style
>>> +                if (findOptions.getLimit() > -1) {
>>> +                    offsetString.append(" LIMIT " + findOptions.getLimit());
>>> +                    if (findOptions.getOffset() > -1) {
>>> +                        offsetString.append(" OFFSET " + findOptions.getOffset());
>>> +                    }
>>> +                }
>>> +            } else if (datasourceInfo.offsetStyle.equals("fetch")) {
>>> +                // use SQL2008 OFFSET/FETCH style by default
>>> +                if (findOptions.getOffset() > -1) {
>>> +                    offsetString.append(" OFFSET ").append(findOptions.getOffset()).append(" ROWS");
>>> +                    if (findOptions.getLimit() > -1) {
>>> +                        offsetString.append(" FETCH FIRST ").append(findOptions.getLimit()).append(" ROWS ONLY");
>>> +                    }
>>> +                }
>>> +            }
>>> +        }
>>> +        return offsetString;
>>> +    }
>>> +
>>>        public List<GenericValue> selectByMultiRelation(GenericValue value, ModelRelation modelRelationOne, ModelEntity
>>>            modelEntityOne, ModelRelation modelRelationTwo, ModelEntity modelEntityTwo, List<String> orderBy) throws
>>>            GenericEntityException { SQLProcessor sqlP = new SQLProcessor(helperInfo);
>>>
>>> Modified: ofbiz/trunk/framework/entity/src/org/ofbiz/entity/test/EntityTestSuite.java
>>> URL:
>>> http://svn.apache.org/viewvc/ofbiz/trunk/framework/entity/src/org/ofbiz/entity/test/EntityTestSuite.java?rev=1422221&r1=1422220&r2=1422221&view=diff
>>> ============================================================================== ---
>>> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/test/EntityTestSuite.java (original) +++
>>> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/test/EntityTestSuite.java Sat Dec 15 11:20:13 2012 @@ -31,6 +31,7 @@ import
>>>    java.util.Map; import org.ofbiz.base.util.Debug;
>>>    import org.ofbiz.base.util.UtilDateTime;
>>>    import org.ofbiz.base.util.UtilMisc;
>>> +import org.ofbiz.base.util.UtilValidate;
>>>    import org.ofbiz.base.util.UtilXml;
>>>    import org.ofbiz.entity.Delegator;
>>>    import org.ofbiz.entity.DelegatorFactory;
>>> @@ -44,6 +45,8 @@ import org.ofbiz.entity.condition.Entity
>>>    import org.ofbiz.entity.condition.EntityOperator;
>>>    import org.ofbiz.entity.config.DatasourceInfo;
>>>    import org.ofbiz.entity.config.EntityConfigUtil;
>>> +import org.ofbiz.entity.datasource.GenericHelperDAO;
>>> +import org.ofbiz.entity.model.ModelEntity;
>>>    import org.ofbiz.entity.testtools.EntityTestCase;
>>>    import org.ofbiz.entity.transaction.GenericTransactionException;
>>>    import org.ofbiz.entity.transaction.TransactionUtil;
>>> @@ -604,4 +607,94 @@ public class EntityTestSuite extends Ent
>>>            strBufTemp.append(iNum);
>>>            return strBufTemp.toString();
>>>        }
>>> +
>>> +    /*
>>> +     * This test will verify that the LIMIT and OFFSET options can work properly.
>>> +     */
>>> +    public void testLimitOffsetOptions() throws Exception {
>>> +        String entityName = "Content";
>>> +        DatasourceInfo datasourceInfo =
>>> EntityConfigUtil.getDatasourceInfo(delegator.getEntityHelper(entityName).getHelperName()); +        if
>>> (UtilValidate.isEmpty(datasourceInfo.offsetStyle) || datasourceInfo.offsetStyle.equals("none")) { +
>>> Debug.logInfo("The offset-stype configured in datasource is " + datasourceInfo.offsetStyle +  ", this test is skipped.",
>>> module); +            return; +        } else {
>>> +            Debug.logInfo("The offset-stype configured in datasource is " + datasourceInfo.offsetStyle +  ".", module);
>>> +        }
>>> +        try {
>>> +            EntityFindOptions findOptions = new EntityFindOptions();
>>> +            long count = delegator.findCountByCondition("Content", null, null, null);
>>> +            Debug.logInfo("Content entity has " + count + " rows", module);
>>> +            int rowsPerPage = 10;
>>> +            // use rows/page as limit option
>>> +            findOptions.setLimit(rowsPerPage);
>>> +            int pages = (int) count/rowsPerPage;
>>> +            if (count > pages * rowsPerPage) {
>>> +                pages += 1;
>>> +            }
>>> +            Debug.logInfo("These rows will be displayed in " + pages + " pages, each page has " + rowsPerPage + " rows.",
>>> module); +            ModelEntity modelEntity = delegator.getModelEntity(entityName);
>>> +
>>> +            long start = UtilDateTime.nowTimestamp().getTime();
>>> +            for (int page = 1; page <= pages; page++) {
>>> +                Debug.logInfo("Page " + page + ":", module);
>>> +                // set offset option
>>> +                findOptions.setOffset((page - 1) * rowsPerPage);
>>> +                EntityListIterator iterator = null;
>>> +                try {
>>> +                    iterator = delegator.getEntityHelper(entityName).findListIteratorByCondition(modelEntity, null, null, null,
>>> UtilMisc.toList("lastUpdatedStamp DESC"), findOptions); +                    while (iterator != null) {
>>> +                        GenericValue gv = iterator.next();
>>> +                        if (gv == null) {
>>> +                            break;
>>> +                        }
>>> +                        Debug.logInfo(gv.getString("contentId") + ": " + gv.getString("contentName") + "       (updated: " +
>>> gv.getTimestamp("lastUpdatedStamp") + ")", module); +                    }
>>> +                } catch (GenericEntityException e) {
>>> +                    Debug.logError(e, module);
>>> +                } finally {
>>> +                    if (iterator != null) {
>>> +                        iterator.close();
>>> +                    }
>>> +                }
>>> +            }
>>> +            long end = UtilDateTime.nowTimestamp().getTime();
>>> +            long time1 = end - start;
>>> +            Debug.logInfo("Time consumed WITH limit and offset option (ms): " + time1, module);
>>> +
>>> +            start = UtilDateTime.nowTimestamp().getTime();
>>> +            for (int page = 1; page <= pages; page++) {
>>> +                Debug.logInfo("Page " + page + ":", module);
>>> +                EntityListIterator iterator = null;
>>> +                try {
>>> +                    iterator = ((GenericHelperDAO)
>>> delegator.getEntityHelper(entityName)).findListIteratorByCondition(modelEntity, null, null, null,
>>> UtilMisc.toList("lastUpdatedStamp DESC"), null); +                    if (iterator == null) { +                        continue;
>>> +                    }
>>> +                    iterator.setDelegator(delegator);
>>> +                    List<GenericValue> gvs = iterator.getCompleteList();
>>> +                    int fromIndex = (page - 1) * rowsPerPage;
>>> +                    int toIndex = fromIndex + rowsPerPage;
>>> +                    if (toIndex > count) {
>>> +                        toIndex = (int) count;
>>> +                    }
>>> +                    gvs = gvs.subList(fromIndex, toIndex);
>>> +                    for (GenericValue gv : gvs) {
>>> +                        Debug.logInfo(gv.getString("contentId") + ": " + gv.getString("contentName") + "       (updated: " +
>>> gv.getTimestamp("lastUpdatedStamp") + ")", module); +                    }
>>> +                } catch (GenericEntityException e) {
>>> +                    Debug.logError(e, module);
>>> +                } finally {
>>> +                    if (iterator != null) {
>>> +                        iterator.close();
>>> +                    }
>>> +                }
>>> +            }
>>> +            end = UtilDateTime.nowTimestamp().getTime();
>>> +            long time2 = end - start;
>>> +            Debug.logInfo("Time consumed WITHOUT limit and offset option (ms): " + time2, module);
>>> +            Debug.logInfo("Time saved (ms): " + (time2 - time1), module);
>>> +        } catch (GenericEntityException e) {
>>> +            Debug.logError(e, module);
>>> +        }
>>> +    }
>>>    }
>>>
>>> Modified: ofbiz/trunk/framework/entity/src/org/ofbiz/entity/util/EntityFindOptions.java
>>> URL:
>>> http://svn.apache.org/viewvc/ofbiz/trunk/framework/entity/src/org/ofbiz/entity/util/EntityFindOptions.java?rev=1422221&r1=1422220&r2=1422221&view=diff
>>> ============================================================================== ---
>>> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/util/EntityFindOptions.java (original) +++
>>> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/util/EntityFindOptions.java Sat Dec 15 11:20:13 2012 @@ -48,6 +48,12 @@ public
>>>        class EntityFindOptions implement protected int maxRows = -1;
>>>        protected boolean distinct = false;
>>>
>>> +    /** LIMIT option */
>>> +    protected int limit = -1;
>>> +
>>> +    /** OFFSET option */
>>> +    protected int offset = -1;
>>> +
>>>        /** Default constructor. Defaults are as follows:
>>>         *      specifyTypeAndConcur = true
>>>         *      resultSetType = TYPE_FORWARD_ONLY
>>> @@ -145,4 +151,25 @@ public class EntityFindOptions implement
>>>        public void setDistinct(boolean distinct) {
>>>            this.distinct = distinct;
>>>        }
>>> +
>>> +
>>> +    /** Get the LIMIT number. */
>>> +    public int getLimit() {
>>> +        return limit;
>>> +    }
>>> +
>>> +    /** Specifies the LIMIT number. */
>>> +    public void setLimit(int limit) {
>>> +        this.limit = limit;
>>> +    }
>>> +
>>> +    /** Get the OFFSET number. */
>>> +    public int getOffset() {
>>> +        return offset;
>>> +    }
>>> +
>>> +    /** Specifies the OFFSET number. */
>>> +    public void setOffset(int offset) {
>>> +        this.offset = offset;
>>> +    }
>>>    }