|
The problem with IBM DB2 is the absence of the equivalent of the Oracle ROWNUM feature, at least until OLAP functionalities where made widely available. Torque 3.1 is a neat package, but is not aware of the OLAP functionalities of DB2, which are now enabled even on small systems DB2 UDB packages. The patch below (you can download it as a separate file by clicking here) fixes this lacking feature, and allows the Torque 3.1 user to use convenient objects like LargeSelect normally. You can apply it the following way:
|
diff -Nur java/org/apache/torque/adapter/DBDB2400.java ../../torque-3.1.vido1/src/java/org/apache/torque/adapter/DBDB2400.java
--- java/org/apache/torque/adapter/DBDB2400.java 2003-09-04 14:52:26.000000000 +0900
+++ ../../torque-3.1.vido1/src/java/org/apache/torque/adapter/DBDB2400.java 2004-06-24 19:49:00.000000000 +0900
@@ -58,6 +58,7 @@
* Torque Database Adapter for DB2/400 on the IBM AS400 platform.
*
* @author Scott Weaver
+ * @author Augustin Vidovic
* @version $Id: DBDB2400.java,v 1.4 2003/01/08 16:43:57 henning Exp $
*/
public class DBDB2400 extends DBDB2App
@@ -111,4 +112,15 @@
{
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;
+ }
}
diff -Nur java/org/apache/torque/adapter/DBDB2App.java ../../torque-3.1.vido1/src/java/org/apache/torque/adapter/DBDB2App.java
--- java/org/apache/torque/adapter/DBDB2App.java 2003-09-04 14:52:26.000000000 +0900
+++ ../../torque-3.1.vido1/src/java/org/apache/torque/adapter/DBDB2App.java 2004-06-24 19:49:18.000000000 +0900
@@ -66,6 +66,7 @@
*
*
* @author Hakan Tandogan
+ * @author Augustin Vidovic
* @version $Id: DBDB2App.java,v 1.5 2002/06/24 18:12:19 mpoeschl Exp $
*/
public class DBDB2App extends DB
@@ -138,4 +139,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;
+ }
}
diff -Nur java/org/apache/torque/adapter/DBDB2Net.java ../../torque-3.1.vido1/src/java/org/apache/torque/adapter/DBDB2Net.java
--- java/org/apache/torque/adapter/DBDB2Net.java 2003-09-04 14:52:26.000000000 +0900
+++ ../../torque-3.1.vido1/src/java/org/apache/torque/adapter/DBDB2Net.java 2004-06-24 19:49:46.000000000 +0900
@@ -66,6 +66,7 @@
*
*
* @author Hakan Tandogan
+ * @author Augustin Vidovic
* @version $Id: DBDB2Net.java,v 1.5 2002/06/24 18:12:19 mpoeschl Exp $
*/
public class DBDB2Net
@@ -139,4 +140,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;
+ }
}
diff -Nur java/org/apache/torque/adapter/DB.java ../../torque-3.1.vido1/src/java/org/apache/torque/adapter/DB.java
--- java/org/apache/torque/adapter/DB.java 2003-09-04 14:52:26.000000000 +0900
+++ ../../torque-3.1.vido1/src/java/org/apache/torque/adapter/DB.java 2004-06-24 19:46:19.000000000 +0900
@@ -88,6 +88,7 @@
* @author Jon S. Stevens
* @author Brett McLaughlin
* @author Daniel Rall
+ * @author Augustin Vidovic
* @version $Id: DB.java,v 1.31 2003/01/08 16:43:57 henning Exp $
*/
public abstract class DB implements Serializable, IDMethod
@@ -107,6 +108,9 @@
/** SELECT ... WHERE ... AND ROWNUM <
SELECT ... WHERE ... AND ROW_NUMBER() OVER() <
+ * 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
+ *
+ *
+ * @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.
*
@@ -988,13 +1036,18 @@
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)
{
+ 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
{
@@ -2549,15 +2602,20 @@
}
}
- String sql;
- if ((limit > 0 || offset > 0)
- && db.getLimitStyle() == DB.LIMIT_STYLE_ORACLE)
+ String sql=null;
+ if (limit > 0 || offset > 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
+ else
{
sql = query.toString();
}