I followed Scott's example and put one together for MySql, with setting up
start index, end index with limit:
public final class MySqlGenerator extends SqlGeneratorDefaultImpl
{
/**
* @param arg0
*/
public MySqlGenerator(Platform arg0)
{
super(arg0);
}
public String getPreparedSelectStatement(Query q, ClassDescriptor
cd)
{
String result = super.getPreparedSelectStatement(q, cd);
return this.addOffsetLimit(q, result);
}
public String getSelectStatementDep(Query q, ClassDescriptor cld)
{
String result = super.getSelectStatementDep(q, cld);
return this.addOffsetLimit(q, result);
}
private String addOffsetLimit(Query q, String stmt)
{
int startIndex = q.getStartAtIndex();
int endIndex = q.getEndAtIndex() - startIndex;
if (endIndex <= 0)
endIndex = -1;
StringBuffer sb = new StringBuffer(stmt.length() + 16);
sb.append(stmt);
sb.append(" LIMIT ");
sb.append(startIndex);
sb.append(",");
sb.append(endIndex);
System.out.println(sb.toString());
return sb.toString();
}
}
| -----Original Message-----
| From: Scott Howlett [mailto:[EMAIL PROTECTED]
| Sent: Monday, March 24, 2003 10:28 AM
| To: OJB Users List
| Subject: 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]
