Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #581

Re: JDBC - move cursor forward in a forward only ResultSet

Show all headers | View raw

On 2014-08-14, Magnus Warker <magnux@mailinator.com> wrote:
> On 08/13/2014 05:23 PM, Kees Nuyt wrote:
>> On Tue, 12 Aug 2014 18:58:31 +0200, Magnus Warker
>> <magnux@mailinator.com> wrote:
>
>>> for a paging functionality ("page i of n") I need to fetch a fixed
>>> number of rows starting at a certain position.
>
>>> Is there another method to skip a certain number of rows or (forward)
>>> move the cursor some how?
>
>> Reduce the size of the result set by using a WHERE clause with the
>> start value(s) of the key colum(s).
>> Don't forget to specify ORDER BY on the same column(s) to
>> guarantee the row order is the same between subsequent SELECT
>> statements.
>
> There is already a WHERE-clause with a (partially complex) condition.
>
> A simple example would be:
>
> 	SELECT <all chess games g> WHERE (g belongs to tournament t);
>
> For the paging functionality to work (page 1 of 3, page 2 of 3, etc.) 
> it's important to not change the condition!

It's also important to specify a non-ambiguous order in an ORDER BY
clause, if you don't the order, and therfore page content may change 
unpredictably.

> For example, if the result set contains 25 rows and the page size is 10, 
> then for page 2 the rows 11-20 are needed.
>
> So my approach is: make the query, skip to the first row (11 here) and 
> fetch the rows for the current page (10 here).

use a LIMIT clause to fit the page size and an OFFSET clause to skip.

so for page 2

  LIMIT 10 OFFSET 10

But don't forget ORDER BY something.

http://www.postgresql.org/docs/9.3/interactive/sql-select.html

-- 
umop apisdn


--- news://freenews.netfront.net/ - complaints: news@netfront.net ---

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

JDBC - move cursor forward in a forward only ResultSet Magnus Warker <magnux@mailinator.com> - 2014-08-12 18:58 +0200
  Re: JDBC - move cursor forward in a forward only ResultSet Kees Nuyt <k.nuyt@nospam.demon.nl> - 2014-08-13 17:23 +0200
    Re: JDBC - move cursor forward in a forward only ResultSet Magnus Warker <magnux@mailinator.com> - 2014-08-14 05:46 +0200
      Re: JDBC - move cursor forward in a forward only ResultSet Jasen Betts <jasen@xnet.co.nz> - 2014-08-14 06:55 +0000
      Re: JDBC - move cursor forward in a forward only ResultSet Kees Nuyt <k.nuyt@nospam.demon.nl> - 2014-08-14 11:04 +0200
      Re: JDBC - move cursor forward in a forward only ResultSet Dimitri Fontaine <dimitri@2ndQuadrant.fr> - 2014-08-15 20:12 +0200
        Re: JDBC - move cursor forward in a forward only ResultSet Jasen Betts <jasen@xnet.co.nz> - 2014-08-16 23:36 +0000

csiph-web