0

I'm faced with the task of adding "DB pagination" while executing the query and not while serving the user with results. The problem is that each DB engine has it's own approach to SQL for subtracting a set of results from the entire set. To be more precise, if one wants results N to N+d using:

mysql ---> select X from Y where Z limit N, d

oracle --> select * ( select X, ROW_NUMBER() OVER ( ORDER BY Y.colName ) from Y where Z ) WHERE R BETWEEN N and N+d

For now we provide support for Oracle & MySQL, but one can never know the clients requests, so I am trying to have a general implementation available, therefore I am looking for a library that provides some functionality like this:

qWithSubset = performLimitationOverQuery( qNoSubset, offset, amount, sortOnSet )

Any suggestion is welcome. Thank you.

Icarus
  • 63,293
  • 14
  • 100
  • 115
ZZiloS
  • 13
  • 1
  • 3
  • possible duplicate of [Is there a portable way to have "SELECT FIRST 10 * FROM T" semantic?](http://stackoverflow.com/questions/3400589/is-there-a-portable-way-to-have-select-first-10-from-t-semantic) – BalusC Oct 13 '11 at 17:45
  • @Icarus Given that it's tagged Java, I'm guessing not. – Dave Newton Oct 13 '11 at 17:58
  • @DaveNewton hahaha I am guessing, not either... I didn't pay attention to the java tag. – Icarus Oct 13 '11 at 17:59

1 Answers1

0

Any JPA implementation, such as Hibernate for example, provides a level of abstraction between your code and database.

To be fair, it does much more than dealing with pagination. You could, however, look at and / or borrow its implementation of database "dialects" to deal with pagination without changing the way you're dealing with database in general if you were so inclined.

ChssPly76
  • 99,456
  • 24
  • 206
  • 195