Caching a Resultset 2005-02-19 - By zeeshan haider
Back
I have made my custom class but you need to customize the code hope
this solve your problem,
This is my DBPagger class
package com.attc.user;
import java.sql.*;
import javax.sql.DataSource;
import javax.naming.*;
import java.util.List;
import java.util.ArrayList;
/**
* <p>Title: </p>
* <p>Description: </p>
* <p>Copyright: Copyright (c) 2004</p>
* <p>Company: </p>
* @(protected) not attributable
* @(protected) 1.0
*/
public class DBPagger
{
private int pagesize;
private int page;
private int row=0;
private String query;
private ResultSet rs=null;
private ResultSet resultset;
private Connection conn = null;
private PreparedStatement pstm = null;
private Context ctx = null;
private static DataSource ds = null;
private int param1;
private String param2;
int totalcount;
public DBPagger() {
}
public Connection buildConnection()
{
Context ctx = null;
String jndiDSourceName = "java:comp/env/jdbc/documents";
try
{
if (ds == null)
{
ctx = new InitialContext();
ds = (DataSource) ctx.lookup(jndiDSourceName);
}
conn = ds.getConnection();
}
catch (Exception e)
{
System.out.println("Exeception in makeConnection : \n" + e);
}
return conn;
}
/**
* You can also change in this method uppon ur requirement as i am
using boolean *tag because i need to control parameters at run time
*/
public void MySQLPagedResultSet(String query,int pagesize,String
rpage,int param1,String param2,int count,boolean tag)
{
if(rpage==null)
rpage="1";
if(rpage.equals(""))
rpage="1";
int resultpage=Integer.parseInt(rpage);
this.query=query;
this.param1=param1;
this.param2=param2;
this.totalcount=count;
try
{
/**
* get the connection from dbpool
*/
Connection conn = this.buildConnection();
/**
* Query for getting the maximum newsid from news table
*/
pstm = conn.prepareStatement(this.query,ResultSet.TYPE_SCROLL
_INSENSITIVE);
pstm.setInt(1, this.param1);
if(tag)
pstm.setString(2, this.param2);
rs = pstm.executeQuery();
this.resultset=rs;
this.pagesize=pagesize;
if(resultpage<=0){
resultpage=1;
}
if(resultpage>this.getNumPages())
resultpage=(int)this.getNumPages();
this.setPageNum(resultpage);
/**
* Close the db connection
*/
conn.close();
}
catch (Exception e)
{
try
{
conn.close();
}
catch(Exception e1)
{
e1.printStackTrace();
}
e.printStackTrace();
}
finally
{
/***
* Return the db connection to db pool
*/
if (conn != null)
conn = null;
if (pstm != null)
pstm = null;
}
}
public int getPageNum()
{
return this.page;
}
public boolean isLastPage()
{
return this.page>=((int)this.getNumPages());
}
public double getNumPages()
{
if(rs==null)
return 0;
return Math.ceil(this.getRowCount()/(float)this.pagesize);
}
public boolean isFirstPage()
{
return (this.page<=1);
}
public void setPageNum(int pageNum)
{
if((pageNum>this.getNumPages()) || (pageNum<=0))
return;
this.page=pageNum;
this.row=0;
int pos=0;
try
{
pos=(pageNum-1 (See http://Num-1.ora-code.com)) * this.pagesize;
this.resultset.absolute(pos);
}
catch(Exception e)
{
e.printStackTrace();
}
}
public List fetchArray()
{
List docs=new ArrayList();
try
{
while(this.resultset.next())
{
this.row++;
docs.add(new
DocumentView(rs.getInt("docid"),rs.getString("doccode"),rs.getString("docname")
,rs.getString("docdate"),rs.getString("postby"),0));
if(this.row>=this.pagesize)
break;
}
}
catch(Exception e)
{
e.printStackTrace();
}
return docs;
}
public int getRowCount()
{
return this.totalcount;
}
public String getPageNavigation(String pagename,String
queryvars,String navclass)
{
String nav="";
if (!this.isFirstPage())
{
nav += "<a target='_self' class='"+navclass+"'
href=\""+pagename+"?resultpage="+
(this.getPageNum()-1)+"&"+queryvars+"\"><img border=0
src=/docu/images/leftarrow.gif title=Previous></a>";
}
if (this.getNumPages() < 10 ) {
if (this.getNumPages() > 1 )
for (int i=1; i<=this.getNumPages(); i++)
{
if (i==this.page)
nav += "<img border=0 src=/docu/images/"+i+"r.gif> ";
else
nav += "<a target='_self' class='"+navclass+"'
href=\""+pagename+"?resultpage="+i+"&"+
queryvars+"\"><img border=0
src=/docu/images/"+i+".gif></a> ";
}
} else {
if (this.getNumPages() > 1 )
for (int i=1; i<=this.getNumPages(); i++)
{
if (i==this.page)
nav +=i;
else
nav += "<a target='_self' class='"+navclass+"'
href=\""+pagename+"?resultpage="+i+"&"+
queryvars+"\">"+i +"</a> ";
}
}
if (!this.isLastPage())
{
nav += "<a target='_self' class='"+navclass+"' href=\"?resultpage="+
(this.getPageNum()+1)+"&"+ queryvars+ "\"><img
border=0 src=/docu/images/rightarrow.gif title=Next></a>";
}
return nav;
}
}
Now here is the Servlet code from where i am using this code.
String resultpage = req.getParameter("resultpage");
String nav = "";
if (resultpage == null)
resultpage = "1";
UserView uview = (UserView) session.getAttribute("user");
UserDB udb = new UserDB();
int total=udb.getRowCount(Integer.parseInt(typeid),uview.getLoginName());
DBPagger pager = new DBPagger();
pager.MySQLPagedResultSet("select * from documents where
doctypeid=? order by docid desc", pagetab,
resultpage,Integer.parseInt(typeid),uview.getLoginName(),total,false);
if (pager.getNumPages() > 1)
nav = pager.getPageNavigation(serverUrl +
req.getContextPath() + "/users/list","typeid=" +
typeid+"&action=view", "pager");
List docs = pager.fetchArray();
// List
docs=udb.getDocuments(Integer.parseInt(typeid),uview.getLoginName());
req.setAttribute("typeid", typeid);
req.setAttribute("typename",udb.getTypeName(Integer.parseInt
(typeid)));
req.setAttribute("nav", nav);
req.setAttribute("docs", docs);
You have to search for the code which will help you but believe me
every thing is here in this code.
cheers!!!!!!!!!!!!
On Wed, 9 Feb 2005 12:59:50 +0530, arun <ajassiar@(protected)> wrote:
> ther is another easy way of doing it ,
> and it will increase the performance ,
> performance is not hit by number of records you are getting,
> its by scrollable resultset and using rownum like 20-40, 40-60 etc.
> but if u use 1-20, 140, 1-60 your performance will be quite better.
> so u have always to pick the result from first record and make result set
> skip the first 20 records when you are going to result for second page
> this how u can do that
> 1) get the total number of records using
> select count(*) from table where this that
> int totalRows =0;
> if (ewRs.next()) {
> totalRows = ewRs.getInt("totalRows");
> } else {
> totalRows = "0";
> }
>
> now using your actual query you have skip the records
> 20 records for getting 20-40 records
>
> if (totalRows >= (iPage-1 (See http://age-1.ora-code.com))*iRow +1 )
> {
> for (int i=0 ; i< ((iPage-1 (See http://age-1.ora-code.com))*iRow ) ; i++)
> ewRs.next();
> }
> here u might be knowing which page u r going , and iRow ( for u its 20) my
> case it was dynamic
>
> now next step is to get the next 20 records
> for( int j=0 ; j< iRow ; j++)
> {
> if(ewRs.next())
> {
>
> }
> }
>
> i hope this will help
> if you still need any help
> ask me
> Arun jassiar
> -- --Original Message-- --
> From: A mailing list for Java(tm) 2 Platform, Enterprise Edition
> [mailto:J2EE-INTEREST@(protected)]On Behalf Of Sanjeev K.R
> Sent: Tuesday, February 08, 2005 3:24 PM
> To: J2EE-INTEREST@(protected)
> Subject: Caching a Resultset
>
> Hi,
> We have around 20000 - 40000 of record in a table,
> We are using JSP and a front controller(Servlet) and some DAO's
> We have implemented Pagination also....
> We have a performance set back,we are displaying 10 record at a time in a
> page,
> We are using the absolute potion of the ResultSet to fetch the next 10
> records,
> can anyone help me in achiving a better performance,
> I cannot built all the 20000 objects at the same time in my DAO,
> How can i cache the result set? or the query.....
> My code goes some thing like this...
> [
> DBConnection dbConn = manager.getConnection();
> Statement stmt =
> dbConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR
_READ_ONLY);
> EnquireMaintainOrderVO enquireOrder=((EnquireMaintainOrderVO)query);
> ResultSet rs = stmt.executeQuery(getQuery(enquireOrder));
> rs.absolute(((Integer)param).intValue());
> ]
> Please can anyone throw some light on this ASAP..
>
>
> Thanks & regards
> Sanjeev
>
>
> __ ____ ____ ____ ____ ____ ____
> Do you Yahoo!?
> Yahoo! Mail - You care about security. So do we.
> ===========================================================================
> To unsubscribe, send email to listserv@(protected) and include in the body
> of the message "signoff J2EE-INTEREST". For general help, send email to
> listserv@(protected) and include in the body of the message "help".
>
>
>
>
> ===========================================================================
> To unsubscribe, send email to listserv@(protected) and include in the body
> of the message "signoff J2EE-INTEREST". For general help, send email to
> listserv@(protected) and include in the body of the message "help".
>
>
===========================================================================
To unsubscribe, send email to listserv@(protected) and include in the body
of the message "signoff J2EE-INTEREST". For general help, send email to
listserv@(protected) and include in the body of the message "help".
|
|
