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 Scott Weaver + * @author Augustin Vidovic * @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 Jon S. Stevens * @author Brett McLaughlin * @author Daniel Rall + * @author Augustin Vidovic * @version $Id$ */ public abstract class DB implements Serializable, IDMethod @@ -68,6 +69,9 @@ /** SELECT ... WHERE ... AND ROWNUM < */ public static final int LIMIT_STYLE_ORACLE = 4; + + /** SELECT ... WHERE ... AND ROW_NUMBER() OVER() < */ + 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 @@ * * * @author Hakan Tandogan + * @author Augustin Vidovic * @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 @@ + + DB2 limit and offset support. Thanks to Augustin Vidovic for the patch. + Fix NOT_LIKE with no wildcard bug. 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 Brett McLaughlin * @author Stephen Haberman * @author Martin Poeschl + * @author Augustin Vidovic * @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: + *

  +     * 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. * @@ -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: torque-dev-unsubscribe@db.apache.org For additional commands, e-mail: torque-dev-help@db.apache.org