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;
+ }
}
