db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From mpoes...@apache.org
Subject cvs commit: db-torque/src/java/org/apache/torque/util BasePeer.java
Date Tue, 27 May 2003 20:03:57 GMT
mpoeschl    2003/05/27 13:03:57

  Modified:    src/java/org/apache/torque/util Tag: TORQUE_3_0_BRANCH
                        BasePeer.java
  Log:
  TRQ47: limit and offset patch for Oracle
  
  Revision  Changes    Path
  No                   revision
  
  
  No                   revision
  
  
  1.55.2.1  +110 -28   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.55
  retrieving revision 1.55.2.1
  diff -u -r1.55 -r1.55.2.1
  --- BasePeer.java	2 Dec 2002 02:58:18 -0000	1.55
  +++ BasePeer.java	27 May 2003 20:03:57 -0000	1.55.2.1
  @@ -3,7 +3,7 @@
   /* ====================================================================
    * The Apache Software License, Version 1.1
    *
  - * Copyright (c) 2001-2002 The Apache Software Foundation.  All rights
  + * Copyright (c) 2001-2003 The Apache Software Foundation.  All rights
    * reserved.
    *
    * Redistribution and use in source and binary forms, with or without
  @@ -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);
       }
  
  
  

Mime
View raw message