seade 2004/08/06 05:14:16
Modified: src/java/org/apache/torque/adapter DBDB2400.java DB.java
DBDB2Net.java DBDB2App.java
xdocs changes.xml
src/java/org/apache/torque/util BasePeer.java
Log:
DB2 limit and offset support. Thanks to Augustin Vidovic for the patch.
Revision Changes Path
1.6 +13 -1 db-torque/src/java/org/apache/torque/adapter/DBDB2400.java
Index: DBDB2400.java
===================================================================
RCS file: /home/cvs/db-torque/src/java/org/apache/torque/adapter/DBDB2400.java,v
retrieving revision 1.5
retrieving revision 1.6
diff -u -r1.5 -r1.6
--- DBDB2400.java 22 Feb 2004 06:19:07 -0000 1.5
+++ DBDB2400.java 6 Aug 2004 12:14:15 -0000 1.6
@@ -20,6 +20,7 @@
* Torque Database Adapter for DB2/400 on the IBM AS400 platform.
*
* @author <a href="mailto:[EMAIL PROTECTED]">Scott Weaver</a>
+ * @author <a href="mailto:[EMAIL PROTECTED]">Augustin Vidovic</a>
* @version $Id$
*/
public class DBDB2400 extends DBDB2App
@@ -72,5 +73,16 @@
private String formatCase(String in)
{
return new StringBuffer(UCASE + "(").append(in).append(")").toString();
+ }
+
+ /**
+ * This method is used to check whether the database supports
+ * limiting the size of the resultset.
+ *
+ * @return LIMIT_STYLE_DB2.
+ */
+ public int getLimitStyle()
+ {
+ return DB.LIMIT_STYLE_DB2;
}
}
1.33 +5 -1 db-torque/src/java/org/apache/torque/adapter/DB.java
Index: DB.java
===================================================================
RCS file: /home/cvs/db-torque/src/java/org/apache/torque/adapter/DB.java,v
retrieving revision 1.32
retrieving revision 1.33
diff -u -r1.32 -r1.33
--- DB.java 22 Feb 2004 06:19:07 -0000 1.32
+++ DB.java 6 Aug 2004 12:14:15 -0000 1.33
@@ -50,6 +50,7 @@
* @author <a href="mailto:[EMAIL PROTECTED]">Jon S. Stevens</a>
* @author <a href="mailto:[EMAIL PROTECTED]">Brett McLaughlin</a>
* @author <a href="mailto:[EMAIL PROTECTED]">Daniel Rall</a>
+ * @author <a href="mailto:[EMAIL PROTECTED]">Augustin Vidovic</a>
* @version $Id$
*/
public abstract class DB implements Serializable, IDMethod
@@ -68,6 +69,9 @@
/** <code><pre>SELECT ... WHERE ... AND ROWNUM < <limit></pre></code> */
public static final int LIMIT_STYLE_ORACLE = 4;
+
+ /** <code><pre>SELECT ... WHERE ... AND ROW_NUMBER() OVER() <
<limit></pre></code> */
+ public static final int LIMIT_STYLE_DB2 = 5;
/**
* Empty constructor.
1.7 +13 -1 db-torque/src/java/org/apache/torque/adapter/DBDB2Net.java
Index: DBDB2Net.java
===================================================================
RCS file: /home/cvs/db-torque/src/java/org/apache/torque/adapter/DBDB2Net.java,v
retrieving revision 1.6
retrieving revision 1.7
diff -u -r1.6 -r1.7
--- DBDB2Net.java 22 Feb 2004 06:19:07 -0000 1.6
+++ DBDB2Net.java 6 Aug 2004 12:14:15 -0000 1.7
@@ -28,6 +28,7 @@
* </a>
*
* @author <a href="mailto:[EMAIL PROTECTED]">Hakan Tandogan</a>
+ * @author <a href="mailto:[EMAIL PROTECTED]">Augustin Vidovic</a>
* @version $Id$
*/
public class DBDB2Net
@@ -100,5 +101,16 @@
*/
public void unlockTable(Connection con, String table) throws SQLException
{
+ }
+
+ /**
+ * This method is used to check whether the database supports
+ * limiting the size of the resultset.
+ *
+ * @return LIMIT_STYLE_DB2.
+ */
+ public int getLimitStyle()
+ {
+ return DB.LIMIT_STYLE_DB2;
}
}
1.7 +12 -1 db-torque/src/java/org/apache/torque/adapter/DBDB2App.java
Index: DBDB2App.java
===================================================================
RCS file: /home/cvs/db-torque/src/java/org/apache/torque/adapter/DBDB2App.java,v
retrieving revision 1.6
retrieving revision 1.7
diff -u -r1.6 -r1.7
--- DBDB2App.java 22 Feb 2004 06:19:07 -0000 1.6
+++ DBDB2App.java 6 Aug 2004 12:14:15 -0000 1.7
@@ -100,4 +100,15 @@
public void unlockTable(Connection con, String table) throws SQLException
{
}
+
+ /**
+ * This method is used to check whether the database supports
+ * limiting the size of the resultset.
+ *
+ * @return LIMIT_STYLE_DB2.
+ */
+ public int getLimitStyle()
+ {
+ return DB.LIMIT_STYLE_DB2;
+ }
}
1.125 +3 -0 db-torque/xdocs/changes.xml
Index: changes.xml
===================================================================
RCS file: /home/cvs/db-torque/xdocs/changes.xml,v
retrieving revision 1.124
retrieving revision 1.125
diff -u -r1.124 -r1.125
--- changes.xml 19 Jun 2004 04:24:00 -0000 1.124
+++ changes.xml 6 Aug 2004 12:14:15 -0000 1.125
@@ -8,6 +8,9 @@
<body>
<release version="3.2-alpha" date="in CVS">
+ <action dev='seade' type='add'>
+ DB2 limit and offset support. Thanks to Augustin Vidovic for the patch.
+ </action>
<action dev='seade' type='fix'>
Fix NOT_LIKE with no wildcard bug.
</action>
1.79 +77 -13 db-torque/src/java/org/apache/torque/util/BasePeer.java
Index: BasePeer.java
===================================================================
RCS file: /home/cvs/db-torque/src/java/org/apache/torque/util/BasePeer.java,v
retrieving revision 1.78
retrieving revision 1.79
diff -u -r1.78 -r1.79
--- BasePeer.java 22 Feb 2004 06:16:35 -0000 1.78
+++ BasePeer.java 6 Aug 2004 12:14:15 -0000 1.79
@@ -72,6 +72,7 @@
* @author <a href="mailto:[EMAIL PROTECTED]">Brett McLaughlin</a>
* @author <a href="mailto:[EMAIL PROTECTED]">Stephen Haberman</a>
* @author <a href="mailto:[EMAIL PROTECTED]">Martin Poeschl</a>
+ * @author <a href="mailto:[EMAIL PROTECTED]">Augustin Vidovic</a>
* @version $Id$
*/
public abstract class BasePeer implements java.io.Serializable
@@ -925,6 +926,53 @@
}
/**
+ * Build DB2 (OLAP) -style query with limit or offset.
+ * If the original SQL is in variable: query then the requlting
+ * SQL looks like this:
+ * <pre>
+ * SELECT B.* FROM (
+ * SELECT A.*, row_number() over() as TORQUE$ROWNUM FROM (
+ * query
+ * ) A
+ * ) B WHERE B.TORQUE$ROWNUM > offset AND B.TORQUE$ROWNUM
+ * <= offset + limit
+ * </pre>
+ *
+ * @param query the query
+ * @param limit
+ * @param offset
+ * @return oracle-style query
+ */
+ private static String createDB2LimitOffsetQuery(Query query,
+ int limit, int offset)
+ {
+ StringBuffer buf = new StringBuffer();
+ buf.append("SELECT B.* FROM ( ");
+ buf.append("SELECT A.*, row_number() over() 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);
+ }
+ return buf.toString();
+ }
+
+ /**
* Method to create an SQL query for actual execution based on values in a
* Criteria.
*
@@ -942,13 +990,21 @@
int limit = criteria.getLimit();
int offset = criteria.getOffset();
- String sql;
- if ((limit > 0 || offset > 0)
- && db.getLimitStyle() == DB.LIMIT_STYLE_ORACLE)
+ String sql = null;
+ if (limit > 0 || offset > 0)
{
- sql = createOracleLimitOffsetQuery(query, limit, offset);
- criteria.setLimit(-1);
- criteria.setOffset(0);
+ if (db.getLimitStyle() == DB.LIMIT_STYLE_ORACLE)
+ {
+ sql = createOracleLimitOffsetQuery(query, limit, offset);
+ criteria.setLimit(-1);
+ criteria.setOffset(0);
+ }
+ else if (db.getLimitStyle() == DB.LIMIT_STYLE_DB2)
+ {
+ sql = createDB2LimitOffsetQuery(query, limit, offset);
+ criteria.setLimit(-1);
+ criteria.setOffset(0);
+ }
}
else
{
@@ -2486,13 +2542,21 @@
}
}
- String sql;
- if ((limit > 0 || offset > 0)
- && db.getLimitStyle() == DB.LIMIT_STYLE_ORACLE)
+ String sql = null;
+ if (limit > 0 || offset > 0)
{
- sql = createOracleLimitOffsetQuery(query, limit, offset);
- criteria.setLimit(-1);
- criteria.setOffset(0);
+ if ( db.getLimitStyle() == DB.LIMIT_STYLE_ORACLE)
+ {
+ sql = createOracleLimitOffsetQuery(query, limit, offset);
+ criteria.setLimit(-1);
+ criteria.setOffset(0);
+ }
+ else if ( db.getLimitStyle() == DB.LIMIT_STYLE_DB2)
+ {
+ sql = createDB2LimitOffsetQuery(query, limit, offset);
+ criteria.setLimit(-1);
+ criteria.setOffset(0);
+ }
}
else
{
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
