Yes, that fits the bill. I did some experiments with StartAtIndex and EndAtIndex and indeed it works faster than I expected.
The user gets results quite fast, faster then if I first materialize lots of objects in a big resultsbuffer collection (a Value List Handler) Of course the database each time has to create these big temporary tables for joining and sorting the results, but that probably is done in memory and is not so slow that I need to avoid it. Reading the Sun desing patterns blueprints I wondered how to implement this (http://java.sun.com/blueprints/patterns/ValueListHandler.html) and thought that it would be best to get either to whole collection at once or maintain a cursor on the database. But both options do not scale well, because you can never be sure if and when a user retrieves the next page. So I will no use StartAtIndex and EndAtIndex to limit the size of the resultset and simply re-submit the query for each resultset of non-trivial size. Thanks, Theo Niemeijer > -----Oorspronkelijk bericht----- > Van: Scott Howlett [mailto:[EMAIL PROTECTED] > Verzonden: maandag 24 maart 2003 17:28 > Aan: OJB Users List > Onderwerp: RE: Webapp google queries: What is a good way to present > multiple web pages with query results ? > > > Mail list archive seems not to be working properly, otherwise I'd point > you to previous postings.... > > 1. Use query.setStartAtIndex() and setEndAtIndex() to specify which > records you actually want to retrieve in the query. > > 2. Implement the functionality that translates these parameters into > something your database can understand by providing your own > SqlGenerator class and pointing to it OJB.properties. > > For example, I did it like this for PostgreSQL: > > My OJB.properties entry is: > > SqlGeneratorClass=PostgreSqlStatementGenerator > > > My subclass code is: > > import org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl; > import org.apache.ojb.broker.metadata.ClassDescriptor; > import org.apache.ojb.broker.platforms.Platform; > import org.apache.ojb.broker.query.Query; > > public class PostgreSqlStatementGenerator extends > SqlGeneratorDefaultImpl { > > public SqlStatementGenerator(Platform pf) { > super(pf); > } > > public String getPreparedSelectStatement( > Query query, > ClassDescriptor cld) { > String result = super.getPreparedSelectStatement(query, cld); > return addOffsetLimit(query, result); > } > > public String getSelectStatementDep(Query query, ClassDescriptor > cld) { > String result = super.getSelectStatementDep(query, cld); > return addOffsetLimit(query, result); > } > > private String addOffsetLimit(Query q, String stmt) { > int startIndex = q.getStartAtIndex(); > int endIndex = q.getEndAtIndex(); > if (endIndex > 0) { > if (startIndex < 0 || startIndex >= endIndex) { > startIndex = 0; > } > stmt += " LIMIT " + (endIndex - startIndex); > } > if (startIndex > 0) { > stmt += " OFFSET " + startIndex; > } > return stmt; > } > > } > > Hope that helps, > Scott Howlett > > > > -----Original Message----- > From: Theo Niemeijer [mailto:[EMAIL PROTECTED] > Sent: Friday, March 21, 2003 5:08 AM > To: OJB Users List > Subject: Webapp google queries: What is a good way to present multiple > web pages with query results ? > > > > Maybe someone has a suggestion for me: > > The problem I am facing is that a user can have a very long list of > results, and that list of items is presented in multiple pages of HTML > (like Google :->) > > Let's assume that a query will have 10.000 resultitems, and I display 20 > items per HTML page with a Next and a Previous button. > > - When I use a QueryIterator and for each next page just iterate 20 > items further > I assume that it will tie the database connection up for quite some > time, > and I can not even be sure that the user will ask for the next page of > results ! > > - When I just get all the items in a large collection it will cost me a > lot of "materialisation" time and a lot of memory. > > - When I only ask for the object identities and then get the pages by > making a query > for 20 identies in a kind of "select ... where id's in [....]" then the > user would have to wait for that query, but it may be mcuh faster than > the original query. > > - When I just re-submit the query for each page, and iterate to the > correct 20 items then the user would have to wait for the query each > time, but it may actually be fast because only 20 items would be > "materialised". > > > Anyone having experience with this problem and wanting to share best > practices ? > > Regards, > Theo Niemeijer > > --------------------------------------------------------------------- > 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]
