分类分页显示思路及部分代码

QueryInfo对象（部分重要代码）

。。。。。。。。。。
public int getStartindex() {
startindex=(this.currentpage-1)*this.pagesize;
return startindex;
}
。。。。。。。。。。
public String getWhere() {
if(this.queryname==null||this.queryname.trim().equals("")){
return "";
}else{
this.where="where "+queryname+"=?";
return where;
}

}

PageBean对象

public int[] getPagebar() {
int startpage;
int endpage;
if(this.totalpage<=10){
startpage=1;
endpage=this.totalpage;
}else{
startpage=this.currentpage-4;
endpage=this.currentpage+5;

if(startpage<1){
startpage=1;
endpage=10;
}
if(endpage>totalpage){
endpage=totalpage;
startpage=totalpage-9;
}
}
this.pagebar=new int[endpage-startpage+1];
int index=0;
for(int i=startpage;i<=endpage;i++){
this.pagebar[index++]=i;
}
return pagebar;
}

public int getTotalpage() {
if(this.totalrecord%this.pagesize==0){
this.totalpage=this.totalrecord/this.pagesize;
}else{
this.totalpage=this.totalrecord/this.pagesize+1;
};
//return this.totalpage; //这种写法可以吗？
return totalpage;
}


public int getPreviouspage() {
if(this.currentpage-1>1){
this.previouspage=this.currentpage-1;
}else{
this.previouspage=1;
}
return previouspage;
}

public int getNextpage() {
if(this.currentpage+1>this.totalpage){
this.nextpage=this.totalpage;
}else{
this.nextpage=this.currentpage+1;
}
return nextpage;

}

以下是servlet层，调用service层

public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {
QueryInfo info=WebUtils.request2Bean(request, QueryInfo.class);
String category_id=request.getParameter("category_id");
if(category_id!=null && !category_id.trim().equals("")){    //用于判断是否带有分类参数
info.setQueryname("category_id");
info.setQueryvalue(category_id);
}
List categories=service.getAllCategory();
PageBean pagebean=service.BookpageQuery(info);

request.setAttribute("categories", categories);
request.setAttribute("pagebean", pagebean);
request.getRequestDispatcher("/client/index.jsp").forward(request, response);

}

注意：在实现在分类的情况下，再分页查询，则可在jsp中如下操作，

 <div id="pagebar">
  总共${pagebean.totalpage }页
  当前${pagebean.currentpage }页
  <c:forEach var="pagenum" items="${pagebean.pagebar }">
        <a href="${pageContext.request.contextPath}/client/IndexServlet？currentpage=${pagenum}&category_id= ${param.category_id}" >${pagenum }</a>   
  </c:forEach>
  </div>

以下是service层,service调用dao层，servlet调用 service层

public PageBean BookpageQuery(QueryInfo info){
QueryResult result=bdao.pageQuery(info.getStartindex(),info.getPagesize(),info.getWhere(),info.getQueryvalue());

PageBean bean=new PageBean();
bean.setCurrentpage(info.getCurrentpage());
bean.setList(result.getList());
bean.setPagesize(info.getPagesize());
bean.setTotalrecord(result.getTotalrecord());
return bean;
}

以下是dao层的查询

public QueryResult pageQuery(int startindex,int pagesize,String where,Object param){

List list=bookquery(startindex, pagesize,where, param);
int count=getTotalRecord( where,param);
QueryResult queryresult=new QueryResult();
queryresult.setList(list);
queryresult.setTotalrecord(count);
return queryresult;

}

private List<Book> bookquery(int startindex,int pagesize,String where,Object param){
try {
Connection conn=JdbcUtils.getConnection();
QueryRunner runner=new QueryRunner();
PreparedStatement st=null;
ResultSet rs;
if(where==null||where.trim().equals("")){
//String sql="select * from book limit ?,?"; //适用mysql,但注意，第一个？，代表开始的行数，第二个？是取多少条记录
//String sql="select * from book where id between ? and ?"; //适用sql server,但id要是int型
//String sql="SELECT * FROM(SELECT ROW_NUMBER() OVER (ORDER BY id ASC) AS 'RowNumber', * FROM book) AS UserInfo WHERE RowNumber BETWEEN ( ( ( startindex - 1 ) * pagesize ) + 1 ) AND ( startindex * pagesize)";
String sql="SELECT * FROM(SELECT ROW_NUMBER() OVER (ORDER BY id ASC) AS 'RowNumber', * FROM book) AS UserInfo WHERE RowNumber BETWEEN ( ? +1) AND ( ? +? )";

st=conn.prepareStatement(sql);
st.setInt(1, startindex);
st.setInt(2, startindex);
st.setInt(3, pagesize);
//st.setInt(4, pagesize);
rs=st.executeQuery();
List list=new LinkedList();
while(rs.next()){
Book book=new Book();
book.setId(rs.getString("id"));
book.setName(rs.getString("name"));
book.setAuthor(rs.getString("author"));
book.setPrice(rs.getDouble("price"));
book.setImage(rs.getString("image"));
book.setDescription(rs.getString("description"));
list.add(book);
//book.setCategory(rs.getString("category_id");
}
return list;
     //采用sql server数据时，用dbutils工具，运行到下面两句时，老会报SQL语法错误，不知道为什么？？mysql没有问题
//Object params[]={startindex,startindex,pagesize};
//return (List<Book>) runner.query(conn, sql, params, new BeanListHandler(Book.class));
}else{
//String sql="select * from book "+ where +"limit ?,?";
String sql="SELECT * FROM(SELECT ROW_NUMBER() OVER (ORDER BY id ASC) AS 'RowNumber', * FROM (select * from book "+where+ ") as book_sub) AS UserInfo where RowNumber BETWEEN( ? +1) AND ( ? +? )";
//SELECT * FROM(SELECT ROW_NUMBER() OVER (ORDER BY id ASC) AS 'RowNumber', * FROM (select * from book where category_id='1') as book_sub) AS UserInfo where  RowNumber BETWEEN( 1) AND ( 3 )
st=conn.prepareStatement(sql);
st.setObject(1, param);
st.setInt(2, startindex);
st.setInt(3, startindex);
st.setInt(4, pagesize);
//st.setInt(4, pagesize);
rs=st.executeQuery();
List list=new LinkedList();
while(rs.next()){
Book book=new Book();
book.setId(rs.getString("id"));
book.setName(rs.getString("name"));
book.setAuthor(rs.getString("author"));
book.setPrice(rs.getDouble("price"));
book.setImage(rs.getString("image"));
book.setDescription(rs.getString("description"));
list.add(book);
//book.setCategory(rs.getString("category_id");
}
return list;
 //采用sql server数据时，用dbutils工具，运行到下面两句时，老会报SQL语法错误，不知道为什么？？mysql没有问题
//Object params[]={param,startindex,pagesize};
//return (List<Book>) runner.query(conn, sql, params, new BeanListHandler(Book.class));
}
} catch (Exception e) {
throw new RuntimeException(e);
}

}