You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@turbine.apache.org by Dan Bachelder <ch...@chowda.net> on 2001/08/20 00:44:40 UTC
another question... paging..
Is there a facility available in turbine anywhere for paging of data from
Peers? If not is there a standard/best practice? Barring that, does anyone
who has implemented paging have any incite or words of wisdom as I move
forward?
thanks,
dan
---------------------------------------------------------------------
To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: turbine-user-help@jakarta.apache.org
Re: another question... paging..
Posted by Costas Stergiou <cs...@aias.gr>.
Paging with oracle:
There are 3 classes: One that is implemented as a callback by classes
that wish to execute a query, one that is used as a tagging interface for
paging-implementors (future use maybe) and the last one that does the
actually
paging. What is important to note is that the records that are fetched from
the database are exactly the ones that correspond to the page of interest.
No open cursors, no open resutset. The processing of the records in order
to find the ones of interest is totally done in the database and the client
only gets the exact amount of records that he requested (page number).
This method is used for paging results at http://www.discuss.gr (take a
look at a forum)
I include the code (it is self-explained) so read it and ask me for any
details.
The code has been tested with large enough result-sets. From test
I have performed I concluded that the faster way to do paging is to
let the database perform it (e.g. discard unnecessary records and
only return the ones you want).
Here is a sample code using these classes:
public Page getForums(int number) throws SQLException{
String sql="SELECT
f.id_forum,f.description,b.descr,f.id_bcat,trunc(f.lastreplydate),f.nooftopi
cs,f.status,f.name"+
" FROM forums f, boardcateg b"+
" WHERE f.active='Y' and f.status='V' and f.ID_BCAT=b.id_bcat (+)"+
" AND f.ID_BOARD=?"+
" order by f.id_bcat asc,f.seq asc,f.creation desc";
PagedRecordExtractor pre = new PagedRecordExtractor() {
public final long idboard=id;
public void prepare(PreparedStatement ps)throws SQLException{
ps.setLong(1,idboard);
}
public PagedRecord extractRecord(ResultSet rs)throws
SQLException{
LiteForum lt = new LiteForum();
lt.setId(rs.getLong(1));
lt.setDescription(rs.getString(2));
lt.setNoOfTopics(rs.getInt(6));
lt.setBoardCategory(rs.getString(4));
lt.setLastReplyDate(rs.getString(5));
lt.setCategDesc(rs.getString(3));
lt.setStatus(rs.getString(7));
lt.setName(rs.getString(8));
return lt;
}
};
Page p = new Page(number, sql, pre);
return p;
}
The returned Page object can be used to get the PagedRecords (actually any
class
that you like that just implements the tagging interface)...
You can use the getRecords() methods to get the results. Notice that the
actual
query occurs on first call of a getRecords method (the Page object
construction
has no overhead). Thus, you can also use your own connection object (taken
from a connection-pool maybe)...
If you look at the Page object, you will see that there are many interesting
attributes
that can be used to find almost anything (page number, records per page,
etc)
Also, included is a method that only counts the number of results (without
bringing them
of course).
Give me your opionion or any further question.
I hope this helps
Costas
The classes:
first class:
package com.acn.discuss.model.paging;
import java.sql.*;
/**
* This interface is used by implementors of page-able queries
*/
public interface PagedRecordExtractor {
/**
* This method is actually a callback from the Page class that
* allows the caller to set the parameters for the sql query
* in wants to use paging.
*/
public void prepare(PreparedStatement ps) throws SQLException;
/**
* Another callback method from the Page class.
* Implementors should know how to extract the current ResultSet
* row in a Java object that implements the PagedRecord interface
* but is actually of the specific type the implementor
* wishes to return
*/
public PagedRecord extractRecord(ResultSet rs)throws SQLException;
}
second class:
package com.acn.discuss.model.paging;
/**
* This interface should be implemented by classes that wish to
* participate in queries, the results of which can be pages using
* the paging.* classes.
*/
public interface PagedRecord extends java.io.Serializable {
}
third class:
package com.acn.discuss.model.paging;
import java.sql.*;
/**
* This class can be used in order to handle multi-rows queries in pages.
* The actual row fetching is occuring within this class upon invocation of
* the getXXXRecord() methods. Just constucting a Page objects and setting
its
* attributes does no database io.
*/
public class Page {
//Number of records per page. Can be set at any time
protected int records = 10;
//This is the current page number of this Page Object.
//Each Page object actually represents a snapshot in a query (e.g. a
page)
protected int number = 0;
//The sql String this Page is handling
protected String sql = null;
//the RecordExtractor used for converting the ResultSet rows to
//actual java objects
protected PagedRecordExtractor pre = null;
//The number of pages that this query represents. This is only
//calculated at first invocation of a getXXX() method and cached
//afterwards
protected int calculatedPageCount = -1;
//The number of rows that this query represents. This is only
//calculated at first invocation of a getXXX() method and cached
//afterwards
protected int calculatedResultCount = -1;
/**
* @param records number of records for each page
* @param sql the query that the result set is coming from
* @param pre the Extractor used to convert a ResultSet into a Java
object
*/
public Page(int records, String sql, PagedRecordExtractor pre) {
this.records = records;
this.sql = sql;
this.pre = pre;
}
/**
* Returns the specified page
* A zero length array is returned if there are no records at this page
* @param pageNumber the page number to return
* @return The array of records for the designated page
*/
public PagedRecord[] getRecords(Connection con, int pageNumber) throws
SQLException{
number = pageNumber;
return getRecords(con);
}
/**
* Returns an array of Records belonging to the next available page.
* A zero length array is returned if there are no more records
*/
public PagedRecord[] getNextRecords(Connection con) throws SQLException{
number++;
return getRecords(con);
}
/**
* Returns an array of Records belonging to the previous available page.
* A zero length array is returned if there are no more records
*/
public PagedRecord[] getPreviousRecords(Connection con) throws
SQLException{
if ( number > 1 ){
number--;
return getRecords(con);
}
else
return new PagedRecord[0];
}
/**
* Returns the records of the current page
*/
private PagedRecord[] getRecords(Connection con) throws SQLException{
if ( number == 0 ) number = 1;
PreparedStatement ps = con.prepareStatement(getSqlString());
pre.prepare(ps);
ResultSet rs = ps.executeQuery();
PagedRecord[] results = new PagedRecord[records];
int rownumFieldPos = rs.findColumn("paged_tabled_rownum");
int rowCount = 0;
for(int i=0; i<records; i++){
if ( rs.next() ){
int rownum = rs.getInt(rownumFieldPos);
int thisRowNum = records * number - i;
int arrayPos = records - i - 1;
//if true, then the number of records fetched are less
than
//this page can take (we are at the end)
if ( rownum != thisRowNum ){
arrayPos = arrayPos - ( thisRowNum - rownum);
}
//System.err.println("Actual: " + rownum + " Calculated: " +
thisRowNum + " ArrayPos: " + arrayPos);
if ( arrayPos >= 0 ){
rowCount++;
results[(int)arrayPos] = pre.extractRecord(rs);
}
else
break;
}
else
break;
}
rs.close();
ps.close();
//less records than the ones requested in page were returned, so
//cut down the array with the results.
if ( rowCount < records ){
PagedRecord newRes[] = new PagedRecord[rowCount];
System.arraycopy(results, 0, newRes, 0, rowCount);
return newRes;
}
else
return results;
}
/** Recostruct the sql string to handle paging */
private String getSqlString(){
StringBuffer sb = new StringBuffer(300);
sb.append("select paged_table.*, rownum paged_tabled_rownum from ");
sb.append('(');
sb.append(sql);
sb.append(") paged_table where rownum < ");
sb.append( records * number + 1 );
sb.append(" order by paged_tabled_rownum desc ");
return sb.toString();
}
/** Construct a query that counts the records of the actual query */
private String getCountSqlString(){
StringBuffer sb = new StringBuffer(300);
sb.append("select count(*) from (");
sb.append(sql);
sb.append(')');
return sb.toString();
}
/**
* Returns the number of pages for this query. The actual number of
records
* on which this calculation occurs is only searched once and cached
later.
* If you call the setRecordsPerPage() method, the new value will be
* effectively used, though
*/
public int getPageCount(Connection con) throws SQLException{
if ( calculatedPageCount == -1 ){
int rscount = getResultCount(con);
calculatedPageCount = (int)(rscount / records) + ( rscount %
records == 0 ? 0 : 1) ;
}
return calculatedPageCount;
}
/**
* Returns the number of rows for this query. This number is only
* calculated once.
*/
public int getResultCount(Connection con) throws SQLException{
if ( calculatedResultCount == -1 ){
PreparedStatement ps =
con.prepareStatement(getCountSqlString());
pre.prepare(ps);
ResultSet rs = ps.executeQuery();
rs.next();
calculatedResultCount = rs.getInt(1);
rs.close();
ps.close();
}
return calculatedResultCount;
}
/**
* Sets the number of records per page for this Page object
*/
public void setRecordsPerPage(int recordsPerPage){
this.records = recordsPerPage;
}
/**
* Returns the current page number that this object represents.
* A call to getNextRecords() will return the records at the
* sub-sequent page.
* <p>
* To get the records for this page number you should call
* getRecords( getCurrentPageNumber() ).
*/
public int getCurrentPageNumber(){
return this.number;
}
public static void main(String a[]){
// Page p = new Page(5, 10, "select username from piauthuser order by
username ");
// System.out.println("<" + p.getSqlString() + ">");
}
}
----- Original Message -----
From: "Andrew & Erin Freeman" <ae...@earthlink.net>
To: <tu...@jakarta.apache.org>
Sent: Monday, August 20, 2001 11:22 AM
Subject: RE: another question... paging..
> I would be interested in seeing this.
>
> -----Original Message-----
> From: Costas Stergiou [mailto:csterg@aias.gr]
> Sent: Monday, August 20, 2001 3:20 AM
> To: turbine-user@jakarta.apache.org
> Subject: Re: another question... paging..
>
>
> I have implemented a paging solution for oracle. It works
> well and is not dependent on the query (e.g. the 'user' can
> define any query he wants). If interested, I can give you the
> solution or more details.
> Costas
>
> ----- Original Message -----
> From: "Warner Onstine" <wa...@warneronstine.com>
> To: <tu...@jakarta.apache.org>
> Sent: Monday, August 20, 2001 2:25 AM
> Subject: Re: another question... paging..
>
>
> > what I've done in the past is used a variable on the page to track what
> > records we are looking at and then using that to set the select limit
> > clause. I am currently implementing this for turbine/velocity.
> >
> > Is this something that we want for contribution?
> >
> > -warner
> >
> > ----- Original Message -----
> > From: "Dan Bachelder" <ch...@chowda.net>
> > To: <tu...@jakarta.apache.org>
> > Sent: Sunday, August 19, 2001 3:44 PM
> > Subject: another question... paging..
> >
> >
> > > Is there a facility available in turbine anywhere for paging of data
> from
> > > Peers? If not is there a standard/best practice? Barring that, does
> anyone
> > > who has implemented paging have any incite or words of wisdom as I
move
> > > forward?
> > >
> > > thanks,
> > > dan
> > >
> > >
> > > ---------------------------------------------------------------------
> > > To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
> > > For additional commands, e-mail: turbine-user-help@jakarta.apache.org
> > >
> > >
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: turbine-user-help@jakarta.apache.org
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: turbine-user-help@jakarta.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: turbine-user-help@jakarta.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: turbine-user-help@jakarta.apache.org
RE: another question... paging..
Posted by Andrew & Erin Freeman <ae...@earthlink.net>.
I would be interested in seeing this.
-----Original Message-----
From: Costas Stergiou [mailto:csterg@aias.gr]
Sent: Monday, August 20, 2001 3:20 AM
To: turbine-user@jakarta.apache.org
Subject: Re: another question... paging..
I have implemented a paging solution for oracle. It works
well and is not dependent on the query (e.g. the 'user' can
define any query he wants). If interested, I can give you the
solution or more details.
Costas
----- Original Message -----
From: "Warner Onstine" <wa...@warneronstine.com>
To: <tu...@jakarta.apache.org>
Sent: Monday, August 20, 2001 2:25 AM
Subject: Re: another question... paging..
> what I've done in the past is used a variable on the page to track what
> records we are looking at and then using that to set the select limit
> clause. I am currently implementing this for turbine/velocity.
>
> Is this something that we want for contribution?
>
> -warner
>
> ----- Original Message -----
> From: "Dan Bachelder" <ch...@chowda.net>
> To: <tu...@jakarta.apache.org>
> Sent: Sunday, August 19, 2001 3:44 PM
> Subject: another question... paging..
>
>
> > Is there a facility available in turbine anywhere for paging of data
from
> > Peers? If not is there a standard/best practice? Barring that, does
anyone
> > who has implemented paging have any incite or words of wisdom as I move
> > forward?
> >
> > thanks,
> > dan
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: turbine-user-help@jakarta.apache.org
> >
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: turbine-user-help@jakarta.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: turbine-user-help@jakarta.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: turbine-user-help@jakarta.apache.org
Re: another question... paging..
Posted by Costas Stergiou <cs...@aias.gr>.
I have implemented a paging solution for oracle. It works
well and is not dependent on the query (e.g. the 'user' can
define any query he wants). If interested, I can give you the
solution or more details.
Costas
----- Original Message -----
From: "Warner Onstine" <wa...@warneronstine.com>
To: <tu...@jakarta.apache.org>
Sent: Monday, August 20, 2001 2:25 AM
Subject: Re: another question... paging..
> what I've done in the past is used a variable on the page to track what
> records we are looking at and then using that to set the select limit
> clause. I am currently implementing this for turbine/velocity.
>
> Is this something that we want for contribution?
>
> -warner
>
> ----- Original Message -----
> From: "Dan Bachelder" <ch...@chowda.net>
> To: <tu...@jakarta.apache.org>
> Sent: Sunday, August 19, 2001 3:44 PM
> Subject: another question... paging..
>
>
> > Is there a facility available in turbine anywhere for paging of data
from
> > Peers? If not is there a standard/best practice? Barring that, does
anyone
> > who has implemented paging have any incite or words of wisdom as I move
> > forward?
> >
> > thanks,
> > dan
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: turbine-user-help@jakarta.apache.org
> >
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: turbine-user-help@jakarta.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: turbine-user-help@jakarta.apache.org
Re: another question... paging..
Posted by Warner Onstine <wa...@warneronstine.com>.
But I've seen the limit clause in other SQL docs (not that I've used other
SQL servers).
-warner
----- Original Message -----
From: "Jon Stevens" <jo...@latchkey.com>
To: "Turbine-user" <tu...@jakarta.apache.org>
Sent: Sunday, August 19, 2001 10:30 PM
Subject: Re: another question... paging..
> on 8/19/01 4:25 PM, "Warner Onstine" <wa...@warneronstine.com> wrote:
>
> > what I've done in the past is used a variable on the page to track what
> > records we are looking at and then using that to set the select limit
> > clause. I am currently implementing this for turbine/velocity.
> >
> > Is this something that we want for contribution?
> >
> > -warner
>
> I believe select limit is MySQL specific...
>
> -jon
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: turbine-user-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: turbine-user-help@jakarta.apache.org
Re: another question... paging..
Posted by Jon Stevens <jo...@latchkey.com>.
on 8/19/01 4:25 PM, "Warner Onstine" <wa...@warneronstine.com> wrote:
> what I've done in the past is used a variable on the page to track what
> records we are looking at and then using that to set the select limit
> clause. I am currently implementing this for turbine/velocity.
>
> Is this something that we want for contribution?
>
> -warner
I believe select limit is MySQL specific...
-jon
---------------------------------------------------------------------
To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: turbine-user-help@jakarta.apache.org
Re: another question... paging..
Posted by Warner Onstine <wa...@warneronstine.com>.
what I've done in the past is used a variable on the page to track what
records we are looking at and then using that to set the select limit
clause. I am currently implementing this for turbine/velocity.
Is this something that we want for contribution?
-warner
----- Original Message -----
From: "Dan Bachelder" <ch...@chowda.net>
To: <tu...@jakarta.apache.org>
Sent: Sunday, August 19, 2001 3:44 PM
Subject: another question... paging..
> Is there a facility available in turbine anywhere for paging of data from
> Peers? If not is there a standard/best practice? Barring that, does anyone
> who has implemented paging have any incite or words of wisdom as I move
> forward?
>
> thanks,
> dan
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: turbine-user-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: turbine-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: turbine-user-help@jakarta.apache.org