Here is where the inline views are needed. Try it like this:
select * from ( select rownum r, --add your other records from (SELECT * FROM some_table)) WHERE r between 11 and 20 I can't remeber why you can't use: WHERE ROWNUM BETWEEN 11 AND 20 It has something to do with rownum being a sequential number coming back from the resultset. That's why you need the inline views to order your resultset properly. Here is a real query that I am running: select * from ( select rownum r, INSURED_NAME, POLICY_NUMBER, POLICY_EFFECTIVE_DATE, WRITTEN_PREMIUM_W_PC, EARNED_PREMIUM_W_PC, TOTAL_LOSSES_INCURRED, INCURRED_LOSS_RATIO*100, commission_expense from (select trim(t.INSURED_NAME) INSURED_NAME, t.POLICY_NUMBER, t.POLICY_EFFECTIVE_DATE, t.WRITTEN_PREMIUM_W_PC, t.EARNED_PREMIUM_W_PC, t.TOTAL_LOSSES_INCURRED, t.INCURRED_LOSS_RATIO, t.commission_expense from some_insurance_table t where t.ACCIDENT_YEAR = '2003' order by trim(t.INSURED_NAME) ASC, t.POLICY_EFFECTIVE_DATE desc )) WHERE r between 11 and 20 If you still have problems getting your query running, send it to me directly and I'll see if can't get it going. Dan -----Original Message----- From: CRANFORD, CHRIS [mailto:[EMAIL PROTECTED] Sent: Monday, June 14, 2004 11:00 AM To: 'Struts Users Mailing List' Subject: RE: Caching data from resultset This works fine when: WHERE ROWNUM BETWEEN 1 and 10 But when I use: WHERE ROWNUM BETWEEN 11 AND 20 I get no rows returned. Any ideas why? -----Original Message----- From: Kies, Daniel [mailto:[EMAIL PROTECTED] Sent: Monday, June 14, 2004 12:52 PM To: 'Struts Users Mailing List' Subject: RE: Caching data from resultset I recently implemented pagination for resultsets using Oracle 9i. Instead of loading up the entire resultset into memory, I just queried based on the records that the user requested. 1) Before getting the records, I first counted the records that were coming back in the result. That way I can say to the user...showing results 26-50 of 2,183. 2) For the first resultest, I would run my query appended with a "rownum between 1 and 25" 3) If the user paginates, then I throw the pagination numbers into the query so the query would be appended with "rownum between x and y" This is a pretty simple solution to pagination with Oracle. You may have some issues getting your resultset ordered properly coming from Oracle, but using inline views should take care of that. The advantage to doing this is scaleability. I am running this code on top of a Data Warehouse where every milisecond counts. Using rownum for the resultset off the database limits query execution time and allows for resultsets of any size. Using resultsets with a rownum will allow Oracle to cache the SQL you are using to execute quicker. Let Oracle do the work. My $ .02 -----Original Message----- From: CRANFORD, CHRIS [mailto:[EMAIL PROTECTED] Sent: Monday, June 14, 2004 10:39 AM To: 'Struts Users Mailing List' Subject: RE: Caching data from resultset Not sure how much this is going to help because we're averaging about 15 seconds to transform a resultset of 1200 records into dynabeans. My goal really is to be able to dynamically traverse only the needed records within my resultset. So, when my resultset gets returned and I forward to page 1 (assuming we show 10 hits per page), I would fetch rows 1-10 and cache their data. Then when the user hits page 2, I would fetch 10 more rows from the resultset and append records 11-20 in the cache. So at this point, records 1-20 are cached and my resultset it pointing to record 21. Now, if the user hits the previous page button, my application would move the offset back to current offset - viewable count (11-10=1) and it would pull records 1-10 from the cache. This avoids calling the database for data. Then hitting next page would check and see that the resultset pointer is at 21 but offset < resultset offset, so assumes cache. It pulls records 11-20 from the cache list again without a call to the database. Is this what I am looking for and didn't know if a package already existed to do this. If so, great. If not, I need to invest some time in developing one for a project due tomorrow afternoon :-) Thanks Chris -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, June 14, 2004 12:02 PM To: Struts Users Mailing List Subject: RE: Caching data from resultset Hmmm...maybe a RowSetDynaClass is what you need? http://jakarta.apache.org/commons/beanutils/api/org/apache/commons/beanutils /RowSetDynaClass.html It's as simple as: rs = stmt.executeQuery(MyQueryString); // Transform the resultSet to a "disconnected" set of DynaBeans RowSetDynaClassrowSet = new RowSetDynaClass(rs, false); // Transform the DynaBeans to a list object rows = rowSet.getRows(); Dennis "CRANFORD, CHRIS" <[EMAIL PROTECTED]> 06/14/2004 11:47 AM Please respond to "Struts Users Mailing List" <[EMAIL PROTECTED]> To 'Struts Users Mailing List' <[EMAIL PROTECTED]> cc Subject RE: Caching data from resultset Not a problem because I do have an OTN account. But I guess the question is whether it will work with Oracle 8i or only 9? In the past what I have done is used introspection to convert the resultset via metadata into a collection of objects that represent a record in the database and use this "collection" in the JSP layer. I typically notice that this is where the MAJORITY of my time is consumed creating these objects in memory, especially on very large data sets. Ideally I want to create a business wrapper than extends some form of caching resultset mechanism that permits me to save already viewed rows in memory and only "fetch" ahead rows from the resultset as the user jumps to the next page. Since oracle 8i only provides forward scrolling in the resultset, its important that previously viewed records be cached in the bean. Would all this be functionality available using CachedResultSet (ocrs12.zip from oracle) via 8i database and jdk1.2? Chris -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, June 14, 2004 11:07 AM To: Struts Users Mailing List Subject: RE: Caching data from resultset http://otn.oracle.com/software/tech/java/sqlj_jdbc/htdocs/jdbc9201.html ocrs12.zip appears to be a library containing Oracles imlementation of CachedResultSet and there is a JDK 1.2 version. However, it appears that you need to be a member of the OTN. Dennis "CRANFORD, CHRIS" <[EMAIL PROTECTED]> 06/14/2004 09:54 AM Please respond to "Struts Users Mailing List" <[EMAIL PROTECTED]> To 'Struts Users Mailing List' <[EMAIL PROTECTED]> cc Subject RE: Caching data from resultset But CachedRowSet isn't available in JDK 1.2.2_014 right? I'm limited to this JDK because IBM AIX version our OS runs doesn't support a JDK after this version. -----Original Message----- From: Freddy Villalba Arias [mailto:[EMAIL PROTECTED] Sent: Monday, June 14, 2004 8:22 AM To: Struts Users Mailing List Subject: RE: Caching data from resultset Hi Leon, I suppose that, since you're talking about caching the ResultSet, you've already given a thought to the amount of data that you'd be handling, consider it to be feasible and reasonable to cache it. This said, why don't you take a look at CachedRowSet? http://java.sun.com/j2se/1.5.0/docs/api/javax/sql/rowset/CachedRowSet.html http://java.sun.com/developer/Books/JDBCTutorial/chapter5.html I believe this could be somewhat similar to what you are looking for. HTH, Freddy. -----Mensaje original----- De: CRANFORD, CHRIS [mailto:[EMAIL PROTECTED] Enviado el: lunes, 14 de junio de 2004 13:45 Para: 'Struts Users Mailing List' Asunto: RE: Caching data from resultset This will work for the "paging" aspect, but I'm more concerned with ways to cache the "resultset" itself in the session to avoid repeative database calls on each page request. -----Original Message----- From: Rosenberg, Leon [mailto:[EMAIL PROTECTED] Sent: Monday, June 14, 2004 6:21 AM To: Struts Users Mailing List Subject: AW: Caching data from resultset Take a look at the pager taglib. http://jsptags.com/tags/navigation/pager/pager-taglib-2.0.html regards Leon > -----Ursprüngliche Nachricht----- > Von: CRANFORD, CHRIS [mailto:[EMAIL PROTECTED] > Gesendet: Montag, 14. Juni 2004 11:23 > An: '[EMAIL PROTECTED]' > Betreff: Caching data from resultset > > I am working with Oracle 8i and JDBC and was curious how others have > implemented paging through large record resultsets from a JSP > application without making a SQL call from page to page to retreive > the data and looping through records to place the cursor at the right > offset in the resultset. > > What I would prefer to do is make the database call once and cache the > resultset object and as the user navigates forward, fetch row-by-row > of only those required for the display. Then if the user navigates > backward, those records are in a cache inside this bean so the cache > is referenced for the data (due to the fact Oracle's resultset is > forward step only). > > Is anyone familiar or done anything like this in the past? > > _______________________________________________________ > Chris Cranford > Programmer/Developer > SETECH Inc. & Companies > 6302 Fairview Rd, Suite 201 > Charlotte, NC 28210 > Phone: (704) 362-9423, Fax: (704) 362-9409, Mobile: (704) 650-1042 > Email: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]