[Geotools-commits] svn - r38501 - branches/2.7.x/modules/plugin/jdbc/jdbc-sqlserver/src/main/java/org/geotools/data/sqlserver

[<svn...@os...>]

Author: aaime
Date: 2012-01-17 10:40:08 -0800 (Tue, 17 Jan 2012)
New Revision: 38501

Modified:
   branches/2.7.x/modules/plugin/jdbc/jdbc-sqlserver/src/main/java/org/geotools/data/sqlserver/SQLServerDialect.java
Log:
[GEOT-4019] Support native paging in sql server

Modified: branches/2.7.x/modules/plugin/jdbc/jdbc-sqlserver/src/main/java/org/geotools/data/sqlserver/SQLServerDialect.java
===================================================================
--- branches/2.7.x/modules/plugin/jdbc/jdbc-sqlserver/src/main/java/org/geotools/data/sqlserver/SQLServerDialect.java	2012-01-17 18:39:53 UTC (rev 38500)
+++ branches/2.7.x/modules/plugin/jdbc/jdbc-sqlserver/src/main/java/org/geotools/data/sqlserver/SQLServerDialect.java	2012-01-17 18:40:08 UTC (rev 38501)
@@ -328,4 +328,45 @@
         }
     }
     
+    @Override
+    public boolean isLimitOffsetSupported() {
+        return true;
+    }
+    
+    @Override
+    public void applyLimitOffset(StringBuffer sql, int limit, int offset) {
+        // if we have a nested query (used in sql views) we might have a inner order by,
+        // check for the last closed )
+        int lastClosed = sql.lastIndexOf(")");
+        int orderByIndex = sql.lastIndexOf("ORDER BY");
+        CharSequence orderBy;
+        if(orderByIndex > 0 && orderByIndex > lastClosed) {
+            // we'll move the order by into the ROW_NUMBER call
+            orderBy = sql.subSequence(orderByIndex, sql.length());
+            sql.delete(orderByIndex, orderByIndex + orderBy.length());
+        } else {
+            // ROW_NUMBER requires an order by clause, we need to feed it something
+            orderBy = "ORDER BY CURRENT_TIMESTAMP";
+        }
+        
+        // now insert the order by inside the select
+        int fromStart = sql.indexOf("FROM");
+        sql.insert(fromStart - 1, ", ROW_NUMBER() OVER (" + orderBy + ") AS _GT_ROW_NUMBER ");
+        
+        // and wrap inside a block that selects the portion we want
+        sql.insert(0, "SELECT * FROM (");
+        sql.append(") AS _GT_PAGING_SUBQUERY WHERE ");
+        if(offset > 0) {
+            sql.append("_GT_ROW_NUMBER > " + offset);
+        }
+        if(limit >= 0 && limit < Integer.MAX_VALUE) {
+            int max = limit;
+            if(offset > 0) {
+                max += offset;
+                sql.append(" AND ");
+            }
+            sql.append("_GT_ROW_NUMBER <= " + max);
+        }
+    }
+    
 }