Hello,
I have created a patch (attached) which allows eclipse link to use the
SQL Server 2005 ROW_NUMBER() function for pagination using the JPA
queries maxRow and firstResult settings. Basically it transforms a SQL from,
/**
+ * Print the SQL representation of the statement on a stream.
+ * Transform a SQL of the form,
+ *
+ * SELECT t0.ID, t0.NAME, t1.NAME, t2.NAME, t3.NAME
+ * FROM DOMAINS t0 LEFT OUTER JOIN TENANT t3
+ * ON (t3.ID = t0.tenant_id), UC t2, REALM t1
+ * WHERE ((t1.ID = t0.realm_id) AND (t2.ID = t0.UC_ID))
+ * ORDER BY t0.NAME
+ *
+ * TO,
+ *
+ * SELECT COL1, COL2, COL3, COL4, COL5
+ * FROM (SELECT ROW_NUMBER() OVER (ORDER BY t0.NAME) AS Row,
+ * t0.ID AS COL1, t0.NAME AS COL2, t1.NAME AS COL3,
+ * t2.NAME AS COL4, t3.NAME AS COL5
+ * FROM DOMAINS t0 LEFT OUTER JOIN TENANT t3
+ * ON (t3.ID = t0.tenant_id), UC t2, REALM t1
+ * WHERE ((t1.ID = t0.realm_id) AND (t2.ID = t0.UC_ID))) AS
Report
+ * WHERE Row >= ? AND Row <= ?
+ *
+ */
The new code will not be triggerd if any of the following is true,
// if ( !(this.shouldUseRownumFiltering()) || ( !(max>0) &&
!(firstRow>0) ) || !statement.hasOrderByExpressions()){
it will fallback to the standard way for doing things.
It has been working on our systems for couple of months now and our
product has gone through multiple QA cycles.
I would like if any of the eclipselink commiters take this up and run
through any tests and promote it.
Some things which are not so nice but unavoidable are,
- a new class has been added to the persistence.internal.expressions
package. This done to access package protected methods. The SQL
transformation for SQL Server was not as straight forward as the Oracle
or the other DB's where you just had to append or prepend something to
the existing SQL to enable paging.
- This ROW_NUMBER() functionality is only available in SQL Server 2005
and later versions. This might break if used with the previous version.
In that case, shouldUseRownumFiltering() should be set to FALSE from the
somewhere, I was not sure from where I could do that.
Any feedback is appreciated.
Thanks,
-Sarva
------------------------------------------------------------------------
Windows Live™ Hotmail®…more than just e-mail. See how it works.
<http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t2_hm_justgotbetter_howitworks_012009>
------------------------------------------------------------------------
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
