Hi, guys.
I already responded to this kind of thread some times... You could
search archives, you will find lots of messages (from others too).
But lets go:
If you are using Object persistence, please take a look at:
http://java.sun.com/blueprints/corej2eepatterns/Patterns/ValueListHandler.html
If you are using plain JDBC, you could:
1) Execute a query on PK only. Keep result as Vector (uses much less
memory than ArrayList) in memory (or in session, in case of a java web
server), something like:
select ID from PRODUCTS
2) When you need to show a page, take number of PKs you need, and
execute a IN query.
select * from PRODUCTS where ID in (id1, id2, id3, id4, id5...id20)
I made this on tables with 1.000.000 records on a 512Mb VM using "int"
PK without any problem.
If you will use session, you should take care with:
1) New and deleted records could/should invalidate session (you must
define your policy)
2) If you have several users working on same table all the time, put
vector on application area (accessible by all the users). You'll avoid
lot's of memory consumption.
Both method above (OO/Jdbc based) guarantees you are always with most
recent data on screen.
Additional advantage: works with all databases in the market.
Disvantage: if you want to ORDER your records, then you need to add more
fields to query (some databases need ORDER field appear on SELECT
clause. AFAIK Derby too).
Regards,
Richter
Fantry, John escreveu:
Using a scrollable cursor is a possibility. However, you incur a
performance penalty, which in my experience, is quite severe if your
result set is very large. Let's say you need to move the cursor to the
last row in the result set. Derby will iterate through all the rows in
the result set from the current position of the cursor until the last
row is reached. In my application I felt that the amount of time the
user was forced to wait while this took place was unacceptable.
What Sergey, and several others would like to be able to do, is use a
LIMIT command with an OFFSET modifier which will allow you to generate a
result set that only has rows 10 - 20 in it. The result set does not
contain rows 1 - 9, nor does it contain any rows after 20. This is what
Sergey is asking for. This behavior is possible in databases like MySQL
and Postgres, but of course those are not Java based embedded
implementations.
So, Sergey, if you execute your query using a scrollable cursor you can
move the cursor to your desired offset and then read the desired number
or rows (in this case you'll probably want to call setMaxRows() on your
Statement object so you don't retrieve any additional rows that you
don't need). You may find this to be perfectly acceptable for your
needs. This approach didn't work well for me due to the rather large
size of my result set.
-----Original Message-----
From: Michael Segel [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 09, 2006 1:00 PM
To: 'Derby Discussion'
Subject: RE: Results portion
Hmmm...
I realize its been a while since I've read Derby's manual, but doesn't
Derby
support Scrollable cursors?
A scrollable cursor will allow you to run a query and to fetch specific
rows
from the result set. At a minimum, that is what Sergey is originally
asking
and which is supported by multiple RDBMSs.
-----Original Message-----
From: Fantry, John [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 09, 2006 10:00 AM
To: Derby Discussion
Subject: RE: Results portion
This question has bean asked several times already . . . Once by me
;-)
The answer is NO. There is no way to retrieve rows in this fashion.
I
had to create an extra "index" column in my table in order to simulate
this kind of behavior. Big pain in the . . .
-----Original Message-----
From: Sergey Zolotaryov [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 09, 2006 7:43 AM
To: [email protected]
Subject: Results portion
Is there a way to retrieve a portion of results using Derby? For
example, I want to get rows from 10 to 20 from a resultset. All dbs I
know of, have this feature, smth like "LIMIT n, m" , or "ROWS n TO m".
Also is there a way to retrieve the total number of rows in last
select?
Thank you.
****************************************************************
The information transmitted in this message is confidential and may be
privileged. Any review, retransmission, dissemination, or other use
of
this information by persons or entities other than the intended
recipient
is prohibited. If you are not the intended recipient, please notify
Analogic Corporation immediately - by replying to this message or by
sending an email to [EMAIL PROTECTED] - and destroy all
copies
of this information, including any attachments, without reading or
disclosing them.
Thank you.
****************************************************************
The information transmitted in this message is confidential and may be
privileged. Any review, retransmission, dissemination, or other use of this
information by persons or entities other than the intended recipient is
prohibited. If you are not the intended recipient, please notify Analogic
Corporation immediately - by replying to this message or by sending an email to
[EMAIL PROTECTED] - and destroy all copies of this information, including any
attachments, without reading or disclosing them.
Thank you.
begin:vcard
fn:Edson Carlos Ericksson Richter
n:Richter;Edson
org;quoted-printable:MGR Inform=C3=A1tica Ltda;Desenvolvimento
adr:Cristo Redentor;;Assis Brasil, 3257, Sala 409;Porto Alegre;RS;91010007;Brasil
email;internet:[EMAIL PROTECTED]
title:Diretor de Sistemas
tel;work:(51)3347-0446
tel;cell:(51)9259-2993
x-mozilla-html:FALSE
url:http://www.mgrinformatica.com.br
version:2.1
end:vcard
