Mailing-List: contact user-help@struts.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Struts Users Mailing List" <user@struts.apache.org>
Date: Mon, 14 Jun 2004 22:14:20 +0300
From: Andrey Rogov <andrey.rogov@ukrpost.net>
Reply-To: Andrey Rogov <andrey.rogov@ukrpost.net>
Message-ID: <1991271167.20040614221420@ukrpost.net>
To: "Struts Users Mailing List" <user@struts.apache.org>
Subject: Re[2]: Caching data from resultset
In-Reply-To: <E6F7D2AC7056AE4BA26F8CDD0ABFC6C269468E@nccsr05a.setechusa.com>
References: <E6F7D2AC7056AE4BA26F8CDD0ABFC6C269468E@nccsr05a.setechusa.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit

You can try the following. If there are no data in the cache you can
select
 SQL :
        SELECT *.blah...,
                rownum
         FROM (
              SELECT *.blah...
                FROM portal.dbtables dbt
               WHERE ....
           ORDER BY dbt.datemessage desc
              )
         WHERE ( rownum between #firstrow# and #lastrow# )

   Oracle 9i will do it fast. It has cache of its own. Appropriate lines
   will be picked out of the table. Then you save  bean collection in session scope
   or application scope. Then, if more pages are picked out you can
   find, if there are data in the cache - then show,  if not, add etc. I apply both
   Oracle rownum and a small cache for dynamic data if data update is not so frequent.

   Best regards,

       Andrey.

CC> This works fine when:

CC>   WHERE ROWNUM BETWEEN 1 and 10

CC> But when I use:

CC>   WHERE ROWNUM BETWEEN 11 AND 20

CC> I get no rows returned.

CC> Any ideas why?

CC> -----Original Message-----
CC> From: Kies, Daniel [mailto:dkies@state.mt.us] 
CC> Sent: Monday, June 14, 2004 12:52 PM
CC> To: 'Struts Users Mailing List'
CC> Subject: RE: Caching data from resultset


CC> I recently implemented pagination for resultsets using Oracle 9i.  Instead
CC> of loading up the entire resultset into memory, I just queried based on the
CC> records that the user requested.  

CC> 1) Before getting the records, I first counted the records that were coming
CC> back in the result.  That way I can say to the user...showing results 26-50
CC> of 2,183.  
CC> 2) For the first resultest, I would run my query appended with a "rownum
CC> between 1 and 25" 
CC> 3) If the user paginates, then I throw the pagination numbers into the query
CC> so the query would be appended with "rownum between x and y"

CC> This is a pretty simple solution to pagination with Oracle.  You may have
CC> some issues getting your resultset ordered properly coming from Oracle, but
CC> using inline views should take care of that.

CC> The advantage to doing this is scaleability.  I am running this code on top
CC> of a Data Warehouse where every milisecond counts.  Using rownum for the
CC> resultset off the database limits query execution time and allows for
CC> resultsets of any size.  Using resultsets with a rownum will allow Oracle to
CC> cache the SQL you are using to execute quicker.

CC> Let Oracle do the work.

CC> My $ .02

CC> -----Original Message-----
CC> From: CRANFORD, CHRIS [mailto:chris.cranford@setechusa.com]
CC> Sent: Monday, June 14, 2004 10:39 AM
CC> To: 'Struts Users Mailing List'
CC> Subject: RE: Caching data from resultset


CC> Not sure how much this is going to help because we're averaging about 15
CC> seconds to transform a resultset of 1200 records into dynabeans.  My goal
CC> really is to be able to dynamically traverse only the needed records within
CC> my resultset.
 
CC> So, when my resultset gets returned and I forward to page 1 (assuming we
CC> show 10 hits per page), I would fetch rows 1-10 and cache their data.  Then
CC> when the user hits page 2, I would fetch 10 more rows from the resultset and
CC> append records 11-20 in the cache.  So at this point, records 1-20 are
CC> cached and my resultset it pointing to record 21.
 
CC> Now, if the user hits the previous page button, my application would move
CC> the offset back to current offset - viewable count (11-10=1) and it would
CC> pull records 1-10 from the cache.  This avoids calling the database for
CC> data.  Then hitting next page would check and see that the resultset pointer
CC> is at 21 but offset < resultset offset, so assumes cache.  It pulls records
CC> 11-20 from the cache list again without a call to the database.
 
CC> Is this what I am looking for and didn't know if a package already existed
CC> to do this.  If so, great.  If not, I need to invest some time in developing
CC> one for a project due tomorrow afternoon :-)
 
CC> Thanks
CC> Chris

CC> -----Original Message-----
CC> From: DGraham@EvergreenInvestments.com
CC> [mailto:DGraham@EvergreenInvestments.com] 
CC> Sent: Monday, June 14, 2004 12:02 PM
CC> To: Struts Users Mailing List
CC> Subject: RE: Caching data from resultset


CC> Hmmm...maybe a RowSetDynaClass is what you need?
CC> http://jakarta.apache.org/commons/beanutils/api/org/apache/commons/beanutils
CC> /RowSetDynaClass.html 

CC> It's as simple as: 
CC>     rs = stmt.executeQuery(MyQueryString); 
CC>     // Transform the resultSet to a "disconnected" set of DynaBeans 
CC>     RowSetDynaClassrowSet = new RowSetDynaClass(rs, false); 
CC>     // Transform the DynaBeans to a list object 
CC>     rows = rowSet.getRows(); 

CC> Dennis 


CC> "CRANFORD, CHRIS" <chris.cranford@setechusa.com> 


CC> 06/14/2004 11:47 AM 


CC> Please respond to
CC> "Struts Users Mailing List" <user@struts.apache.org>


CC> To
CC> 'Struts Users Mailing List' <user@struts.apache.org> 

CC> cc

CC> Subject
CC> RE: Caching data from resultset

        
CC> Not a problem because I do have an OTN account.  But I guess the question is
CC> whether it will work with Oracle 8i or only 9?

CC> In the past what I have done is used introspection to convert the resultset
CC> via metadata into a collection of objects that represent a record in the
CC> database and use this "collection" in the JSP layer.  I typically notice
CC> that this is where the MAJORITY of my time is consumed creating these
CC> objects in memory, especially on very large data sets.

CC> Ideally I want to create a business wrapper than extends some form of
CC> caching resultset mechanism that permits me to save already viewed rows in
CC> memory and only "fetch" ahead rows from the resultset as the user jumps to
CC> the next page.  Since oracle 8i only provides forward scrolling in the
CC> resultset, its important that previously viewed records be cached in the
CC> bean.

CC> Would all this be functionality available using CachedResultSet (ocrs12.zip
CC> from oracle) via 8i database and jdk1.2? 

CC> Chris

CC> -----Original Message-----
CC> From: DGraham@EvergreenInvestments.com
CC> [mailto:DGraham@EvergreenInvestments.com] 
CC> Sent: Monday, June 14, 2004 11:07 AM
CC> To: Struts Users Mailing List
CC> Subject: RE: Caching data from resultset


CC> http://otn.oracle.com/software/tech/java/sqlj_jdbc/htdocs/jdbc9201.html 

CC> ocrs12.zip appears to be a library containing Oracles imlementation of
CC> CachedResultSet and there is a JDK 1.2 version.  However, it appears that
CC> you need to be a member of the OTN. 

CC> Dennis 


CC> "CRANFORD, CHRIS" <chris.cranford@setechusa.com> 


CC> 06/14/2004 09:54 AM 


CC> Please respond to
CC> "Struts Users Mailing List" <user@struts.apache.org>


CC> To
CC> 'Struts Users Mailing List' <user@struts.apache.org> 

CC> cc

CC> Subject
CC> RE: Caching data from resultset

                
CC> But CachedRowSet isn't available in JDK 1.2.2_014 right?
CC> I'm limited to this JDK because IBM AIX version our OS runs doesn't support
CC> a JDK after this version.

CC> -----Original Message-----
CC> From: Freddy Villalba Arias [mailto:fvillalba@madrid.bilbomatica.es] 
CC> Sent: Monday, June 14, 2004 8:22 AM
CC> To: Struts Users Mailing List
CC> Subject: RE: Caching data from resultset


CC> Hi Leon,

CC> I suppose that, since you're talking about caching the ResultSet, you've
CC> already given a thought to the amount of data that you'd be handling,
CC> consider it to be feasible and reasonable to cache it.

CC> This said, why don't you take a look at CachedRowSet?

CC> http://java.sun.com/j2se/1.5.0/docs/api/javax/sql/rowset/CachedRowSet.html

CC> http://java.sun.com/developer/Books/JDBCTutorial/chapter5.html

CC> I believe this could be somewhat similar to what you are looking for.

CC> HTH,
CC> Freddy.

CC> -----Mensaje original-----
CC> De: CRANFORD, CHRIS [mailto:chris.cranford@setechusa.com] 
CC> Enviado el: lunes, 14 de junio de 2004 13:45
CC> Para: 'Struts Users Mailing List'
CC> Asunto: RE: Caching data from resultset

CC> This will work for the "paging" aspect, but I'm more concerned with ways to
CC> cache the "resultset" itself in the session to avoid repeative database
CC> calls on each page request.

CC> -----Original Message-----
CC> From: Rosenberg, Leon [mailto:Leon.Rosenberg@friendscout24.de] 
CC> Sent: Monday, June 14, 2004 6:21 AM
CC> To: Struts Users Mailing List
CC> Subject: AW: Caching data from resultset


CC> Take a look at the pager taglib.

CC> http://jsptags.com/tags/navigation/pager/pager-taglib-2.0.html

CC> regards
CC> Leon

>> -----Urspr�ngliche Nachricht-----
>> Von: CRANFORD, CHRIS [mailto:chris.cranford@setechusa.com]
>> Gesendet: Montag, 14. Juni 2004 11:23
>> An: 'user@struts.apache.org'
>> 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: chris.cranford@setechusa.com


CC> ---------------------------------------------------------------------
CC> To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
CC> For additional commands, e-mail: user-help@struts.apache.org


CC> ---------------------------------------------------------------------
CC> To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
CC> For additional commands, e-mail: user-help@struts.apache.org


CC> ---------------------------------------------------------------------
CC> To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
CC> For additional commands, e-mail: user-help@struts.apache.org


CC> ---------------------------------------------------------------------
CC> To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
CC> For additional commands, e-mail: user-help@struts.apache.org


CC> ---------------------------------------------------------------------
CC> To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
CC> For additional commands, e-mail: user-help@struts.apache.org


CC> ---------------------------------------------------------------------
CC> To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
CC> For additional commands, e-mail: user-help@struts.apache.org


-- 
Best regards,
 Andrey                            mailto:andrey.rogov@ukrpost.net


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
For additional commands, e-mail: user-help@struts.apache.org