DataNucleus/JDO has covered this for every RDBMS, perhaps you can learn from their implementation:
"setRange is implemented efficiently for MySQL, Postgresql, HSQL (using the LIMIT SQL keyword) and Oracle (using the ROWNUM keyword), with the query only finding the objects required by the user directly in the datastore. For other RDBMS the query will retrieve all objects up to the "to" record, and will not pass any unnecessary objects that are before the "from" record." [1] [1] http://www.datanucleus.org/products/accessplatform_3_2/jdo/jdoql_typesafe.html On Sun, Feb 3, 2013 at 8:41 PM, Kevin Meyer - KMZ <ke...@kmz.co.za> wrote: > Hi all, > > I just added support to the Isis core and applib for Paged datastore > queries. > > These extensions don't change any existing behaviour, but do expose > a new query type to the user space: QueryFindAllPaged > > A simple usage example: > public List<SimpleClass> someSimpleClasses(final long startIndex, > final long rowCount) { > Query<SimpleClass> query = new > QueryFindAllPaged<SimpleClass>(SimpleClass.class, startIndex, rowCount); > return allMatches(query); > } > > Inside PersistenceSession, "QueryFindAllPaged" is mapped onto > "PersistenceQueryFindPaged", which is available to the data stores. > > The SQL-OS has a simple implementation that adds "LIMIT > <startIndex>, <rowCount>" to the end of the SQL statement. > > This will have to be edited for each database engine: I belive mysql > and postgresql have an incompatible syntax... but I believe it should > be possible to easily define a way to specify the custom string in the > properties file. > > There is a simple test in SqlDomainObjectRepository that just checks > that the expected number of instances are returned. It works against > HSQLDB. > > May you find this extension useful! >
