You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-dev@db.apache.org by jm...@apache.org on 2002/12/09 22:24:33 UTC
cvs commit: jakarta-turbine-torque/src/rttest/org/apache/torque DataTest.java
jmcnally 2002/12/09 13:24:33
Modified: src/java/org/apache/torque/util BasePeer.java
src/rttest/org/apache/torque DataTest.java
Log:
patch by Rafal Maczewski to properly support native offset and limit in
oracle. Issue#: TRQ47
Others have submitted similar patches in the past, but there has never been
junit tests to go with it. As I now need this functionality, I added a test
case.
Revision Changes Path
1.56 +109 -27 jakarta-turbine-torque/src/java/org/apache/torque/util/BasePeer.java
Index: BasePeer.java
===================================================================
RCS file: /home/cvs/jakarta-turbine-torque/src/java/org/apache/torque/util/BasePeer.java,v
retrieving revision 1.55
retrieving revision 1.56
diff -u -r1.55 -r1.56
--- BasePeer.java 2 Dec 2002 02:58:18 -0000 1.55
+++ BasePeer.java 9 Dec 2002 21:24:33 -0000 1.56
@@ -930,22 +930,67 @@
// Limit the number of rows returned.
int limit = criteria.getLimit();
int offset = criteria.getOffset();
- if (offset > 0 && db.supportsNativeOffset())
- {
- // Now set the criteria's limit and offset to return the
- // full resultset since the results are limited on the
- // server.
+
+ String sql;
+ if ((limit > 0 || offset > 0)
+ && db.getLimitStyle() == DB.LIMIT_STYLE_ORACLE)
+ {
+ // Build Oracle-style query with limit or offset.
+ // If the original SQL is in variable: query then the requlting
+ // SQL looks like this:
+ // SELECT B.* FROM (
+ // SELECT A.*, rownum as TORQUE$ROWNUM FROM (
+ // query
+ // ) A
+ // ) B WHERE B.TORQUE$ROWNUM > offset AND B.TORQUE$ROWNUM <= offset + limit
+ StringBuffer buf = new StringBuffer();
+ buf.append("SELECT B.* FROM ( ");
+ buf.append("SELECT A.*, rownum AS TORQUE$ROWNUM FROM ( ");
+
+ buf.append(query.toString());
+ buf.append(" ) A ");
+ buf.append(" ) B WHERE ");
+
+ if (offset > 0)
+ {
+ buf.append(" B.TORQUE$ROWNUM > ");
+ buf.append(offset);
+ if (limit > 0)
+ {
+ buf.append(" AND B.TORQUE$ROWNUM <= ");
+ buf.append(offset + limit);
+ }
+ }
+ else
+ {
+ buf.append(" B.TORQUE$ROWNUM <= ");
+ buf.append(limit);
+ }
criteria.setLimit(-1);
criteria.setOffset(0);
- }
- else if (limit > 0 && db.supportsNativeLimit())
+
+ sql = buf.toString();
+ }
+ else
{
- // Now set the criteria's limit to return the full
- // resultset since the results are limited on the server.
- criteria.setLimit(-1);
+ if (offset > 0 && db.supportsNativeOffset())
+ {
+ // Now set the criteria's limit and offset to return the
+ // full resultset since the results are limited on the
+ // server.
+ criteria.setLimit(-1);
+ criteria.setOffset(0);
+ }
+ else if (limit > 0 && db.supportsNativeLimit())
+ {
+ // Now set the criteria's limit to return the full
+ // resultset since the results are limited on the server.
+ criteria.setLimit(-1);
+ }
+
+ sql = query.toString();
}
- String sql = query.toString();
category.debug(sql);
return sql;
}
@@ -1269,7 +1314,8 @@
//criteria.setLimit(-1);
//criteria.setOffset(0);
}
- else if (limit > 0 && db.supportsNativeLimit())
+ else if (limit > 0 && db.supportsNativeLimit()
+ && db.getLimitStyle() != DB.LIMIT_STYLE_ORACLE)
{
limitString = String.valueOf(limit);
@@ -1286,14 +1332,7 @@
if (limitString != null)
{
- switch (db.getLimitStyle())
- {
- case DB.LIMIT_STYLE_ORACLE :
- whereClause.add("rownum <= " + limitString);
- break;
- default :
- query.setLimit(limitString);
- }
+ query.setLimit(limitString);
}
return query;
@@ -2439,7 +2478,8 @@
int limit = criteria.getLimit();
int offset = criteria.getOffset();
String limitString = null;
- if (offset > 0 && db.supportsNativeOffset())
+ if (offset > 0 && db.supportsNativeOffset()
+ && db.getLimitStyle() != DB.LIMIT_STYLE_ORACLE)
{
switch (db.getLimitStyle())
{
@@ -2467,7 +2507,8 @@
criteria.setLimit(-1);
criteria.setOffset(0);
}
- else if (limit > 0 && db.supportsNativeLimit())
+ else if (limit > 0 && db.supportsNativeLimit()
+ && db.getLimitStyle() != DB.LIMIT_STYLE_ORACLE)
{
limitString = String.valueOf(limit);
@@ -2480,9 +2521,6 @@
{
switch (db.getLimitStyle())
{
- case DB.LIMIT_STYLE_ORACLE :
- whereClause.add("rownum <= " + limitString);
- break;
/* Don't have a Sybase install to validate this against. (dlr)
case DB.LIMIT_STYLE_SYBASE:
query.setRowcount(limitString);
@@ -2493,7 +2531,51 @@
}
}
- String sql = query.toString();
+ String sql;
+ if ((limit > 0 || offset > 0)
+ && db.getLimitStyle() == DB.LIMIT_STYLE_ORACLE)
+ {
+ // Build Oracle-style query with limit or offset.
+ // If the original SQL is in variable: query then the requlting
+ // SQL looks like this:
+ // SELECT B.* FROM (
+ // SELECT A.*, rownum as TORQUE$ROWNUM FROM (
+ // query
+ // ) A
+ // ) B WHERE B.TORQUE$ROWNUM > offset AND B.TORQUE$ROWNUM <= offset + limit
+ StringBuffer buf = new StringBuffer();
+ buf.append("SELECT B.* FROM ( ");
+ buf.append("SELECT A.*, rownum AS TORQUE$ROWNUM FROM ( ");
+
+ buf.append(query.toString());
+ buf.append(" ) A ");
+ buf.append(" ) B WHERE ");
+
+ if (offset > 0)
+ {
+ buf.append(" B.TORQUE$ROWNUM > ");
+ buf.append(offset);
+ if (limit > 0)
+ {
+ buf.append(" AND B.TORQUE$ROWNUM <= ");
+ buf.append(offset + limit);
+ }
+ }
+ else
+ {
+ buf.append(" B.TORQUE$ROWNUM <= ");
+ buf.append(limit);
+ }
+ criteria.setLimit(-1);
+ criteria.setOffset(0);
+
+ sql = buf.toString();
+ }
+ else
+ {
+ sql = query.toString();
+ }
+
category.debug(sql);
queryString.append(sql);
}
1.4 +49 -1 jakarta-turbine-torque/src/rttest/org/apache/torque/DataTest.java
Index: DataTest.java
===================================================================
RCS file: /home/cvs/jakarta-turbine-torque/src/rttest/org/apache/torque/DataTest.java,v
retrieving revision 1.3
retrieving revision 1.4
diff -u -r1.3 -r1.4
--- DataTest.java 19 Nov 2002 17:36:40 -0000 1.3
+++ DataTest.java 9 Dec 2002 21:24:33 -0000 1.4
@@ -54,10 +54,16 @@
* <http://www.apache.org/>.
*/
+import java.util.Map;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Iterator;
import org.apache.log4j.Category;
import org.apache.torque.BaseTestCase;
import org.apache.torque.test.Author;
import org.apache.torque.test.Book;
+import org.apache.torque.test.BookPeer;
+import org.apache.torque.util.Criteria;
import org.apache.torque.test.MultiPk;
/**
@@ -121,6 +127,48 @@
MultiPk mpk = new MultiPk();
mpk.setPrimaryKey("Svarchar:N5:Schar:");
mpk.save();
+ }
+ catch (Exception ex)
+ {
+ ex.printStackTrace();
+ }
+ }
+
+ private static final String[] validTitles = {
+ "Book 7 - Author 8", "Book 6 - Author 8", "Book 7 - Author 7",
+ "Book 6 - Author 7", "Book 7 - Author 6", "Book 6 - Author 6",
+ "Book 7 - Author 5", "Book 6 - Author 5", "Book 7 - Author 4",
+ "Book 6 - Author 4"};
+
+ /**
+ * test limit/offset which was broken for oracle (TRQ47)
+ */
+ public void testLimitOffset()
+ {
+ Map titleMap = new HashMap();
+ for (int j=0; j<validTitles.length; j++)
+ {
+ titleMap.put(validTitles[j], null);
+ }
+
+ try
+ {
+ Criteria crit = new Criteria();
+ Criteria.Criterion c = crit.getNewCriterion(BookPeer.TITLE, (Object)"Book 6 - Author 1", Criteria.GREATER_EQUAL);
+ c.and(crit.getNewCriterion(BookPeer.TITLE, (Object)"Book 8 - Author 3", Criteria.LESS_EQUAL));
+ crit.add(c);
+ crit.addDescendingOrderByColumn(BookPeer.BOOK_ID);
+ crit.setLimit(10);
+ crit.setOffset(5);
+ List books = BookPeer.doSelect(crit);
+ assertTrue("List should have 10 books, not " + books.size(),
+ books.size() == 10);
+ for (Iterator i=books.iterator(); i.hasNext();)
+ {
+ String title = ((Book)i.next()).getTitle();
+ assertTrue("Incorrect title: " + title,
+ titleMap.containsKey(title));
+ }
}
catch (Exception ex)
{