At 11:47 AM 3/26/99 -0500, you wrote:
>Hi
>Does anyone have a example of using cursors with JDBC.
>The issue we are trying to solve is using a servlet to display only
>10 rows at a time to the user from a table that may contain a million
>records.
>The table is in an oracle database.
>The oracle driver apis have not been very helpful.
>Any information will be greatly appreciated
>
Beena,
This is not an easy problem to solve, especially with Oracle. Oracle does
not have the concept of scrolling cursors. Also, JDBC does not have
scrolling cursors due to the fact that very few RDBMS products support
them. There are several approaches. The brute force method is to open a
cursor on the entire table with an order by clause on the primary key. Then
fetch the first ten records and save the last key value. Then close the
cursor. When the next page is asked for submit a query that asks for the
data that is greater than the last saved key. Repeat for paging. The
problem with this approach is that each cursor that you open will create an
image of the complete query. This has too much overhead with a million
records. It can also be slow.
Another approach works if your table is very static. You can make another
table based on your main table that only contains a sequence number and the
primary key of the main table. You then query the data asking for records
1-10. The next page gets records 11-20 and so forth. This works great if
your table doesn't change very often, like every night. You then just have
to rebuild the lookup table. If your table changes all the time in a
transaction environment this won't work.
The approach that I'm using is to use the Oracle ConText cartridge. This
wonderful add-on to Oracle allows me to perform many useful functions that
are very dificult to do with just plain Oracle. Some of those are very low
overhead query hit counts, the ability to do next/previous sections of a
query result set, intelligent queries on the text content within large text
fields, thesaurus expansion of keyword searching, to name just a few. The
major drawbacks of the ConText cartridge is that you have to pay extra for
it and in order to use it you MUST go to Oracle training for it. There just
isn't any good documentation on ConText. I have been to the training
recently and it is of high quality.
-Eric
___________________________________________________________________________
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff SERVLET-INTEREST".
Archives: http://archives.java.sun.com/archives/servlet-interest.html
Resources: http://java.sun.com/products/servlet/external-resources.html
LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
