How can I get the previous record in Oracle?

[Huaying Feng]

There are many records in my table. The primary key(for example id) is not continuous. Now I know the current id, how can I get the preivous id?
I know,in Oracle, the ResultSet has the function of next(). Is there a function of previous()?
Thanks in advance!

[Riaz Mohamed]

previous
public boolean previous()
throws SQLException <../../java/sql/SQLException.html>
Moves the cursor to the previous row in this ResultSet object.
Returns:
true if the cursor is on a valid row; false if it is off the result set
Throws:
SQLException <../../java/sql/SQLException.html> - if a database access error occurs or the result set type is TYPE_FORWARD_ONLY
Since:
1.2

does this help
regardsr
riaz

[David O'Meara]

"globefish", "riaz",
The Java Ranch has thousands of visitors every week, many with surprisingly similar names. To avoid confusion we have a naming convention, described at http://www.javaranch.com/name.jsp.
We require names to have at least two words, separated by a space, and strongly recommend that you use your full real name. Please edit your profile and select a new name which meets the requirements.
riaz, you have posted 15 times with an invalid name. Please change your display name immediately since accounts with invalid names get deleted!
Thanks.
Dave

[Huaying Feng]

I tried to use the previous function, but it throws exception. How can I deal with it?
[ September 04, 2002: Message edited by: Huaying Feng ]

[Jamie Robertson]

which exception is being thrown? Which database/driver combination are you using? Are you using scrollable resultsets ( or does your driver even support it ) ?
Some possible solutions:
create a scrollable resultset, then use the resultset.previous() method. An example of this can be found here. If your driver does not support scrollable resultsets, you'll have store the resultset in a Collection or, if you only need the previous entry then save only the previous roiw in each iteration.
--a shot in the dark--
"The primary key(for example id) is not continuous"
If you just want to have the resultset in some type of order, you can use the Order By clause in your SQL query. That way you don't need the primary key to be contiguous.
Jamie
If your question is different than the above suggestions

[Huaying Feng]

Hum...I'm not sure if I should use preivous() to resolve the problem.
Let me explain my problem in detail. I want to create an album. After choosing some pic, I want to show the links, such as "next" and "previous", on this page. In that case, I already knew the id of the pic which I chose. I want to get the previous id and the next id. These ids are not continuous, because I deleted some pics which I added before.
How should I deal with this kind of problem?
[ September 06, 2002: Message edited by: Huaying Feng ]
[ September 06, 2002: Message edited by: Huaying Feng ]

[Jamie Robertson]

Some suggestions that come to mind:
1. create a scrollable resultset and just scroll through it. Problem-->you tie up database resources, and it is not really scalable, especially if this a web application.
2. Another way would be to only keep track of current id. When the user presses next() you can re-query the database for the next result.
example if your original query was "select id, picture from emp where ... order by id"
assign current_id to the first id
When the user presses next you can re-query with something like this - "select id, picture from emp where id = ( select min( id ) from emp where ... and id > " + current_id + " ) "
When the user presses previous, you can re-query with "select id, picture from emp where id = ( select max ( id ) from emp where ... and id < " + current_id + " ) "
3. The last way is to query the database once, hold all the id's in a data structure of some kind, then re-query the database for each picture needed, keeping track of where you are in the data structure.
If your using servlets/connection pooling and the likes, the second or third option is probably better as it reduces the amount of resources consumed at the database. If you don't care about tying up database resources then the first suggestion might be easier to implement.
Jamie
[ September 06, 2002: Message edited by: Jamie Robertson ]

[Kel Walker]

Cache the id value sequence. Something along the lines of:
List pictureId = (new ArrayList() );
// Add Ids to list from ResultSet.
pictureId.add("id1");
pictureId.add("id2");
pictureId.add("id3"); // Whatever type your id is?
// etc.
int pictureIdIndex = 0; // Lists are zero indexed.
// if previous was pressed.
pictureIdIndex -= 1;
if (pictureIdIndex < 0) pictureIdIndex = 0;
// if next was pressed.
pictureIdIndex += 1;
if (pictureIdIndex > (pictureId.size() - 1) ) {
pictureIdIndex = (pictureId.size() - 1);
}
// To get the current id.
String currentId = (String)pictureId.get(pictureIdIndex);
Haven't compiled or tested this, but you get the idea.

[Huaying Feng]

Thanks to riaz,Jamie Robertson and Kel Walker.
Your advice is very helpful to me!

[Jamie Robertson]

If it is resultset paging you are looking for, you might be interested in this article written by the serverside.com. It discusses these strategies in detail.
Jamie