Hi,
Assuming your jdbc driver supports scrollable results sets you can use code such as the following that will jump to the startRow and return the next rowCount items. If scrollable result sets are not supported you will need to have a loop to skip over the first n rows (which will be slower).
Paul
public Collection ejbFindByForumId(Long id, int startRow, int rowCount) throws FinderException, RemoteException {
if (log.isInfoEnabled()) {
StringBuffer s = new StringBuffer("ejbFindByForumId\t");
s.append(id);
log.info(s);
}
Connection con = null;
PreparedStatement prepStmt = null;
ResultSet rs = null;
ArrayList items = new ArrayList();
try {
String selectStatement = "select thread_id from nh_thread where forum_id = ? order by creation_date";
con = getConnection();
prepStmt = con.prepareStatement(selectStatement);
prepStmt.setLong(1, id.longValue());
rs = prepStmt.executeQuery();
int numRows = 0;
if (rs.absolute(startRow) /* jump to the specified row */ && rowCount > 0) {
do {
Long threadId = new Long(rs.getLong(1));
items.add(threadId);
numRows++;
} while (rs.next() && numRows < rowCount);
}
} catch (SQLException sqle) {
log.error(sqle);
throw new EJBException(sqle);
} finally {
ConnectionManager.close(rs);
ConnectionManager.close(prepStmt);
ConnectionManager.close(con);
}
return items;
}
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of Jonathan
Gibbons
Sent: 20 June 2001 09:07
To: [EMAIL PROTECTED]
Subject: Re: [JBoss-user] JDBC / ResultSet Question
Hi,
Do count(1) - I read this is the fastest a while ago.
i.e. select count(1) from X where col='val' etc.
Jonathan
>I believe it is a known problem (feature?) with Oracle that COUNT(*)
>has to scan the whole table (even *deleted* rows), but if you just
>choose a column name (probably should be an indexed column) and do
>COUNT(colname) you'll get a drastic improvement in performance. I
>haven't tested this but I have heard about it before. Good luck.
>
>-Tom
_________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
_______________________________________________
JBoss-user mailing list
[EMAIL PROTECTED]
http://lists.sourceforge.net/lists/listinfo/jboss-user
