Author: hannes
Date: 2009-04-01 17:49:59 +0200 (Wed, 01 Apr 2009)
New Revision: 9562
Modified:
helma/helma/trunk/src/helma/objectmodel/db/NodeManager.java
helma/helma/trunk/src/helma/objectmodel/db/Relation.java
Log:
Implement limit/offset support for oracle and use the occasion to refactor and simplify Relation.buildQuery() a bit.
Details at http://dev.helma.org/trac/helma/changeset/9562
Modified: helma/helma/trunk/src/helma/objectmodel/db/NodeManager.java
===================================================================
--- helma/helma/trunk/src/helma/objectmodel/db/NodeManager.java 2009-04-01 13:13:38 UTC (rev 9561)
+++ helma/helma/trunk/src/helma/objectmodel/db/NodeManager.java 2009-04-01 15:49:59 UTC (rev 9562)
@@ -918,11 +918,8 @@
query = b.append(" ").append(home.getSubnodeRelation()).toString();
} else {
// let relation object build the query
- query = b.append(rel.buildQuery(home,
- home.getNonVirtualParent(),
- null,
- " WHERE ",
- true)).toString();
+ rel.buildQuery(b, home, null, " WHERE ", true);
+ query = b.toString();
}
stmt = con.createStatement();
@@ -1014,11 +1011,7 @@
b.append(home.getSubnodeRelation());
} else {
// let relation object build the query
- b.append(rel.buildQuery(home,
- home.getNonVirtualParent(),
- null,
- " WHERE ",
- true));
+ rel.buildQuery(b, home, null, " WHERE ", true);
}
query = b.toString();
@@ -1132,17 +1125,9 @@
if (updateCriteria != null) {
b.append (" WHERE ");
b.append (updateCriteria);
- b.append (rel.buildQuery(home,
- home.getNonVirtualParent(),
- null,
- " AND ",
- true));
+ rel.buildQuery(b, home, null, " AND ", true);
} else {
- b.append (rel.buildQuery(home,
- home.getNonVirtualParent(),
- null,
- " WHERE ",
- true));
+ rel.buildQuery(b, home, null, " WHERE ", true);
}
q = b.toString();
}
@@ -1253,7 +1238,7 @@
dbm.addJoinConstraints(b, " AND ");
if (rel.groupby != null) {
- rel.renderConstraints(b, home, home.getNonVirtualParent(), " AND ");
+ rel.renderConstraints(b, home, " AND ");
if (rel.order != null) {
b.append(" ORDER BY ");
@@ -1398,11 +1383,8 @@
query = b.append(" ").append(home.getSubnodeRelation()).toString();
} else {
// let relation object build the query
- query = b.append(rel.buildQuery(home,
- home.getNonVirtualParent(),
- null,
- " WHERE ",
- false)).toString();
+ rel.buildQuery(b, home, null, " WHERE ", false);
+ query = b.toString();
}
stmt = con.createStatement();
@@ -1472,11 +1454,7 @@
b.append(" ").append(home.getSubnodeRelation());
} else {
// let relation object build the query
- b.append(rel.buildQuery(home,
- home.getNonVirtualParent(),
- null,
- " WHERE ",
- true));
+ rel.buildQuery(b, home, null, " WHERE ", true);
}
stmt = con.createStatement();
@@ -1637,12 +1615,7 @@
b.append(")");
}
} else {
- b.append(rel.buildQuery(home,
- home.getNonVirtualParent(),
- dbm,
- kstr,
- " WHERE ",
- false));
+ rel.buildQuery(b, home, dbm, kstr, " WHERE ", false);
}
stmt = con.createStatement();
Modified: helma/helma/trunk/src/helma/objectmodel/db/Relation.java
===================================================================
--- helma/helma/trunk/src/helma/objectmodel/db/Relation.java 2009-04-01 13:13:38 UTC (rev 9561)
+++ helma/helma/trunk/src/helma/objectmodel/db/Relation.java 2009-04-01 15:49:59 UTC (rev 9562)
@@ -876,21 +876,20 @@
* Build the second half of an SQL select statement according to this relation
* and a local object.
*/
- public String buildQuery(INode home, INode nonvirtual,
- String kstr, String pre, boolean useOrder)
+ public void buildQuery(StringBuffer q, Node home, String kstr, String pre, boolean useOrder)
throws SQLException, ClassNotFoundException {
- return buildQuery(home, nonvirtual, otherType, kstr, pre, useOrder);
+ buildQuery(q, home, otherType, kstr, pre, useOrder);
}
/**
* Build the second half of an SQL select statement according to this relation
* and a local object.
*/
- public String buildQuery(INode home, INode nonvirtual, DbMapping otherDbm,
- String kstr, String pre, boolean useOrder)
+ public void buildQuery(StringBuffer q, Node home, DbMapping otherDbm, String kstr,
+ String pre, boolean useOrder)
throws SQLException, ClassNotFoundException {
- StringBuffer q = new StringBuffer();
String prefix = pre;
+ Node nonvirtual = home.getNonVirtualParent();
if (kstr != null && !isComplexReference()) {
q.append(prefix);
@@ -919,14 +918,25 @@
q.append(" ORDER BY ").append(order);
}
- if (maxSize > 0 && !ownType.isOracle()) {
- q.append(" LIMIT ").append(maxSize);
- if (offset > 0) {
- q.append(" OFFSET ").append(offset);
+ if (maxSize > 0) {
+ if (otherType.isOracle()) {
+ // see http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html
+ int minRow = offset;
+ int maxRow = minRow + maxSize;
+ if (minRow > 0) {
+ q.insert(0, "SELECT * FROM ( SELECT /*+ FIRST_ROWS(n) */ a.*, ROWNUM rnum FROM (");
+ q.append(") a WHERE ROWNUM <= ").append(maxRow).append(") WHERE rnum > ").append(minRow);
+ } else {
+ q.insert(0, "SELECT /*+ FIRST_ROWS(n) */ * FROM (");
+ q.append(") WHERE ROWNUM <= ").append(maxRow);
+ }
+ } else {
+ q.append(" LIMIT ").append(maxSize);
+ if (offset > 0) {
+ q.append(" OFFSET ").append(offset);
+ }
}
}
-
- return q.toString();
}
protected void appendAdditionalTables(StringBuffer q) {
@@ -991,16 +1001,14 @@
*
* @param q the query string
* @param home our home node
- * @param nonvirtual our non-virtual home node
* @param prefix the prefix to use to append to the existing query (e.g. " AND ")
*
* @throws SQLException sql related exception
* @throws ClassNotFoundException driver class not found
*/
- public void renderConstraints(StringBuffer q, INode home, INode nonvirtual,
- String prefix)
+ public void renderConstraints(StringBuffer q, Node home, String prefix)
throws SQLException, ClassNotFoundException {
- renderConstraints(q, home, nonvirtual, otherType, prefix);
+ renderConstraints(q, home, home.getNonVirtualParent(), otherType, prefix);
}
/**
@@ -1015,7 +1023,7 @@
* @throws SQLException sql related exception
* @throws ClassNotFoundException driver class not found
*/
- public void renderConstraints(StringBuffer q, INode home, INode nonvirtual,
+ public void renderConstraints(StringBuffer q, Node home, Node nonvirtual,
DbMapping otherDbm, String prefix)
throws SQLException, ClassNotFoundException {
lmpx.com only provides a reader for public news (NNTP) servers. It is not
affiliated with the servers or forums shown here and is not responsible for
the content of articles, which is written by their respective authors.