Groups > comp.databases.postgresql > #581

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

Path	csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!news.glorb.com!news.netfront.net!gonzo.reversiblemaps.ath.cx!not-for-mail
From	Jasen Betts <jasen@xnet.co.nz>
Newsgroups	comp.databases.postgresql
Subject	Re: JDBC - move cursor forward in a forward only ResultSet
Date	14 Aug 2014 06:55:41 GMT
Organization	JJ's own news server
Lines	51
Message-ID	<lshmhd$951$1@gonzo.reversiblemaps.ath.cx> (permalink)
References	<lsdh3n$ugq$1@news.m-online.net> <qf0nu9ps55b1tvs8sd7gscaitfdgjjqj7i@dim53.demon.nl> <lshbf8$nl0$1@news.m-online.net>
NNTP-Posting-Host	118.90.53.211
X-Trace	adenine.netfront.net 1407999660 98930 118.90.53.211 (14 Aug 2014 07:01:00 GMT)
X-Complaints-To	news@netfront.net
NNTP-Posting-Date	Thu, 14 Aug 2014 07:01:00 +0000 (UTC)
X-Face	?)Aw4rXwN5u0~$nqKj`xPz>xHCwgi^q+^?Ri+R(&uv2=E1Q0Zk(>h!~o2ID@6{uf8s;a +M[5[U[QT7xFN%^gR"=tuJw%TXXR'Fp~W;(T"1(739R%m0YyyvgkGoPA.$b,D.w:z+<'"=-lVT?6 {T?=R^:W5g\|E2#EhjKCa+nt":4b}dU7GYBHBxn&Td$@f%.kl^:7X8rQWd[NTc"P"u6nkisze/Q;8 "9Z{peQF,w)7UjV$c\|RO/mQW/NMgWfr5$-Z%u46"/00mx-,\R'fLPe.)^
User-Agent	slrn/pre1.0.0-18 (Linux)
Xref	csiph.com comp.databases.postgresql:581

Show key headers only | 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