[PATCH] Torque: Native limits on Oracle

[Bill Schneider <bs...@vecna.com>]

Description: supports Criteria.setLimit() natively in Oracle by adding " 
... rownum <= x " to the SQL WHERE clause.

also makes use of LIMIT_STYLE_SYBASE, which we didn't seem to actually 
support at all before.  (WARNING, haven't tested this!)

My apologies for intermingling this with the date-format patch I 
submitted previously.

-- Bill

Index: src/java/org/apache/torque/adapter/DB.java
===================================================================
RCS file: 
/home/cvspublic/jakarta-turbine-torque/src/java/org/apache/torque/adapter/DB.java,v
retrieving revision 1.18
diff -u -r1.18 DB.java
--- src/java/org/apache/torque/adapter/DB.java	21 Feb 2002 16:58:19 -0000	1.18
+++ src/java/org/apache/torque/adapter/DB.java	11 Mar 2002 16:44:22 -0000
@@ -106,6 +106,9 @@
      /** <code>SET ROWCOUNT &lt;offset&gt; SELECT ... SET ROWCOUNT 
0</code> */
      public static final int LIMIT_STYLE_SYBASE = 3;

+    /** <code>SELECT ... WHERE ... AND ROWNUM < limit **/
+    public static final int LIMIT_STYLE_ORACLE = 4;
+
      /** The database user name. */
      protected String DB_USER;

Index: src/java/org/apache/torque/adapter/DBOracle.java
===================================================================
RCS file: 
/home/cvspublic/jakarta-turbine-torque/src/java/org/apache/torque/adapter/DBOracle.java,v
retrieving revision 1.8
diff -u -r1.8 DBOracle.java
--- src/java/org/apache/torque/adapter/DBOracle.java	22 Aug 2001 20:12:08 -0000	1.8
+++ src/java/org/apache/torque/adapter/DBOracle.java	11 Mar 2002 16:44:22 -0000
@@ -55,6 +55,7 @@
   */

  import java.util.Date;
+import java.text.SimpleDateFormat;
  import java.lang.reflect.Method;

  import java.sql.Connection;
@@ -74,6 +75,9 @@
  public class DBOracle
      extends DB
  {
+    private static final SimpleDateFormat DATE_FORMATTER =
+        new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+
      /**
       * Empty constructor.
       */
@@ -176,6 +180,28 @@
          con.commit();
      }

+    /**
+     * This method is used to check whether the database natively
+     * supports limiting the size of the resultset.
+     *
+     * @return True.
+     */
+    public boolean supportsNativeLimit()
+    {
+        return true;
+    }
+
+    /**
+     * This method is used to check whether the database supports
+     * limiting the size of the resultset.
+     *
+     * @return LIMIT_STYLE_ORACLE.
+     */
+    public int getLimitStyle()
+    {
+        return DB.LIMIT_STYLE_ORACLE;
+    }
+
      public ConnectionPoolDataSource getConnectionPoolDataSource()
          throws java.sql.SQLException
      {
@@ -222,7 +248,7 @@
       */
    public String getDateString(String dateString)
    {
-    return "TO_DATE('" + dateString + "', 'yyyy-mm-dd hh24:mi:ss..' )";
+    return "TO_DATE('" + dateString + "', 'yyyy-mm-dd hh24:mi:ss' )";
    }

      /**
@@ -233,6 +259,7 @@
       */
    public String getDateString(Date date)
    {
-      return "TO_DATE('" + date.toString() + "', 'yyyy-mm-dd 
hh24:mi:ss..' )";
+      return "TO_DATE('" + DATE_FORMATTER.format(date) +
+          "', 'yyyy-mm-dd hh24:mi:ss' )";
    }
  }
Index: src/java/org/apache/torque/util/BasePeer.java
===================================================================
RCS file: 
/home/cvspublic/jakarta-turbine-torque/src/java/org/apache/torque/util/BasePeer.java,v
retrieving revision 1.30
diff -u -r1.30 BasePeer.java
--- src/java/org/apache/torque/util/BasePeer.java	8 Mar 2002 01:19:50 -0000	1.30
+++ src/java/org/apache/torque/util/BasePeer.java	11 Mar 2002 16:44:22 -0000
@@ -1310,7 +1310,20 @@
              criteria.setLimit(-1);
          }

-        if (limitString != null) query.setLimit(limitString);
+        if (limitString != null)
+        {
+            switch (db.getLimitStyle())
+            {
+                case DB.LIMIT_STYLE_ORACLE:
+                    whereClause.add("rownum <= " + limitString);
+                    break;
+                case DB.LIMIT_STYLE_SYBASE:
+                    query.setRowcount(limitString);
+                    break;
+                default:
+                    query.setLimit(limitString);
+            }
+        }

          String sql = query.toString();
          category.debug(sql);



--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>