对jsp的一个小结（6）实现分页查询、GO跳转

最新推荐文章于 2024-08-14 10:34:21 发布

杨宏林

最新推荐文章于 2024-08-14 10:34:21 发布

阅读量3k

点赞数

分类专栏： jsp

本文链接：https://blog.csdn.net/sgyhl18/article/details/44699079

版权

jsp 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

本文总结了在Java Web应用中实现分页查询的步骤，包括计算数据总数量、编写分页SQL、创建公用Page类、执行存储过程以及如何进行页面跳转（GO）。通过实例讲解了在DAO层如何处理分页逻辑，并展示了在业务层调用这些功能的方法。

摘要由CSDN通过智能技术生成

9实现分页查询（上）

1解决问题

2计算显示数据的总数量

newsdao添加方法

	//获取新闻总数量
	public int getTotalCount();

newsdaoimpl实现

	/**
	 * 获取新闻总数量
	 * */
	public int getTotalCount() {
		int totalCount=0;
		String sql="select count(*) from news_detail";
		Object[] params={};
		ResultSet rs=this.executeSQL(sql, params);
		try {
			while(rs.next()){
				totalCount=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			this.closeResource();
		}
		return totalCount;
	}

3计算显示的页数

提取公共类到util下page类中

package com.pb.news.util;

public class Page {
	//总页数
	private int totalPageCount=1;
	//页面大小，即每页显示记录数
	private int pageSize=0;
	//记录总数
	private int recordCount=0;
	
	public int getTotalPageCount() {
		return totalPageCount;
	}
	public void setTotalPageCount(int totalPageCount) {
		this.totalPageCount = totalPageCount;
	}
	public int getPageSize() {
		return pageSize;
	}
	public void setPageSize(int pageSize) {
		if(pageSize>0)
			this.pageSize = pageSize;
	}
	public int getRecordCount() {
		return recordCount;
	}
	public void setRecordCount(int recordCount) {
		if(recordCount>0){
			this.recordCount = recordCount;
		}
	}
	
	//设置总页数
	private void setTotalPageCountByRs(){
		if(this.recordCount%this.pageSize==0)
			this.totalPageCount=this.recordCount/this.pageSize;
		else if(this.recordCount%this.pageSize>0)
			this.totalPageCount=this.recordCount/this.pageSize+1;
		else 
			this.totalPageCount=0;
	}
	
}

4编写分页查询sql语句（等差数列）

SELECT * FROM news_detail;

--每页显示2条新闻信息，查看第1页新闻信息（新闻标题、作者、创建时间）的分页sql语句如下
--页码：currPageNo 1  每页显示的记录数：pageSize 此例=2
SELECT id,title,author,createdate FROM (SELECT id,title,author,createdate,ROWNUM rn FROM news_detail) a
WHERE a.rn>=1 AND a.rn<=2 

--每页显示2条新闻信息，查看第2页新闻信息（新闻标题、作者、创建时间）的分页sql语句如下
--页码：currPageNo 2  每页显示的记录数：pageSize 此例=2
SELECT id,title,author,createdate FROM (SELECT id,title,author,createdate,ROWNUM rn FROM news_detail) a
WHERE a.rn>=3  AND a.rn<=4  

--每页显示2条新闻信息，查看第3页新闻信息（新闻标题、作者、创建时间）的分页sql语句如下
--页码：currPageNo 3  每页显示的记录数：pageSize 此例=2
SELECT id,title,author,createdate FROM (SELECT id,title,author,createdate,ROWNUM rn FROM news_detail) a
WHERE a.rn>=5  AND a.rn<=6 

--起始记录数和结束记录数有什么特点？和页码与每页显示的记录有什么关系？
--页码：currPageNo   每页显示的记录数：pageSize 此例=2
SELECT id,title,author,createdate FROM (SELECT id,title,author,createdate,ROWNUM rn FROM news_detail) a
WHERE a.rn>=(currPageNo - 1) * pageSize + 1 AND a.rn<=currPageNo * pageSize

page类：

package com.pb.news.util;

public class Page {
	//总页数
	private int totalPageCount=1;
	//页面大小，即每页显示记录数
	private int pageSize=0;
	//记录总数
	private int recordCount=0;
	//当前页号
	private int currPageNo=1;
	
	
	
	public int getCurrPageNo() {
		if(totalPageCount==0)
			return 0;
		return currPageNo;
	}
	public void setCurrPageNo(int currPageNo) {
		if(this.currPageNo>0)
			this.currPageNo = currPageNo;
	}
	public int getTotalPageCount() {
		return totalPageCount;
	}
	public void setTotalPageCount(int totalPageCount) {
		this.totalPageCount = totalPageCount;
	}
	public int getPageSize() {
		return pageSize;
	}
	public void setPageSize(int pageSize) {
		if(pageSize>0)
			this.pageSize = pageSize;
	}
	public int getRecordCount() {
		return recordCount;
	}
	public void setRecordCount(int recordCount) {
		if(recordCount>0){
			this.recordCount = recordCount;
		}
	}
	
	//设置总页数
	private void setTotalPageCountByRs(){
		if(this.recordCount%this.pageSize==0)
			this.totalPageCount=this.recordCount/this.pageSize;
		else if(this.recordCount%this.pageSize>0)
			this.totalPageCount=this.recordCount/this.pageSize+1;
		else 
			this.totalPageCount=0;
	}
	/**
	 * 得到开始记录数
	 * */
	public int getStartRow(){
		return (currPageNo - 1) * pageSize + 1;
	}
	
	/**
	 * 得到结束记录数
	 * */
	public int getEndRow(){
		return currPageNo * pageSize;
	}
}

5实现分页查询

newsdao添加

	//分页获取新闻信息
	public List<News> getPageNewsList(int pageNo,int pageSize);//当前页码，显示几条记录

newsdaoimpl实现

	/**
	 * 分页获取新闻信息
	 * */
	public List<News> getPageNewsList(int pageNo, int pageSize) {
		List<News> newsList=new ArrayList<News>();
		String sql="SELECT id,title,author,createdate FROM (SELECT id,title,author,createdate,ROWNUM rn FROM news_detail) a WHERE a.rn>=? AND a.rn<=?";
		Page page=new Page();
		page.setCurrPageNo(pageNo);//设置当前页码
		page.setPageSize(pageSize);//每页显示记录数
		//计算sql语句的起始记录数以及结束记录数的行数
		int startRow=page.getStartRow();
		int endRow=page.getEndRow();
		Object[] params={startRow,endRow};
		ResultSet rs=this.executeSQL(sql, params);
		try {
			while(rs.next()){
				int id=rs.getInt("id");
				String title=rs.getString("title");
				String author=rs.getString("author");
				Date date=rs.getDate("createdate");
				News newInfo=new News();
				newInfo.setId(id);
				newInfo.setTitle(title);
				newInfo.setAuthor(author);
				newInfo.setCreateDate(new java.sql.Timestamp(date.getTime()));
			
				newsList.add(newInfo);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			this.closeResource();
		}
		return newsList;
	}

测试

//测试
	public static void main(String[] args) {
		NewsDaoImpl newsDao=new NewsDaoImpl();
		/*newsDao.add(3, 1, "快女选秀快讯", "快女比赛正进入白热化", "她、她、她，谁是你心中的快女冠军？",
				new Date());*/
		//newsDao.update(3, "快女选秀快讯速递");
		//newsDao.delete(3);
		/*List<News> newsList=newsDao.getNewsList();
		for(News news:newsList){
			System.out.println(news.getId()+"\t"+news.getTitle()+"\t"+news.getSummary()+"\t"+news.getContent()+"\t"+news.getAuthor()+"\t"+news.getCreateDate());
		}*/
		/*int totalCount=newsDao.getTotalCount();
		System.out.println("新闻总数量是："+totalCount);*/
		//第一页新闻信息（每页显示2条）
		//List<News> newsList=newsDao.getPageNewsList(1, 2);
		//第二页新闻信息（每页显示2条）
		//List<News> newsList=newsDao.getPageNewsList(2, 2);
		//第三页新闻信息（每页显示2条）
		List<News> newsList=newsDao.getPageNewsList(3, 2);
		for(News news:newsList){
			System.out.println(news.getTitle()+"\t"+news.getAuthor()+"\t"+news.getCreateDate());
		}
	}

newsService类添加

<span style="white-space:pre">	</span>//获取新闻总数量
<span style="white-space:pre">	</span>public int getTotalCount();

//分页获取新闻信息
	public List<News> getPageNewsList(int pageNo,int pageSize);

newsServiceImpl实现

<span style="white-space:pre">	</span>@Override
<span style="white-space:pre">	</span>public int getTotalCount() {
<span style="white-space:pre">		</span>// TODO Auto-generated method stub
<span style="white-space:pre">		</span>return newsDao.getTotalCount();
<span style="white-space:pre">	</span>}

	@Override
	public List<News> getPageNewsList(int pageNo, int pageSize) {
		// TODO Auto-generated method stub
		return newsDao.getPageNewsList(pageNo, pageSize);
	}

6使用CallableStatement执行存储过程

create or replace procedure getNewsCount(v_totalCount  out number) as
begin
  select count(*) into v_totalCount from news_detail;
end;

newsDaoImpl添加

	/**
	 * 获取新闻总数量（执行存储过程）
	 * */
	public int getTotalCountProc(){
		int totalCount=0;
		CallableStatement proc=null;
		String sql="{call getNewsCount(?)}";
		getConnection();
		try {
			proc=conn.prepareCall(sql);
			proc.registerOutParameter(1, Types.INTEGER);
			proc.execute();
			totalCount=proc.getInt(1);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return totalCount;
	}

	//测试
	public static void main(String[] args) {
		NewsDaoImpl newsDao=new NewsDaoImpl();
		int totalCount=newsDao.getTotalCountProc();
		System.out.println("新闻总数量是："+totalCount);
	}

10实现分页查询（下）
1分页显示新闻列表

修改newsDetailList

                <%
                	//获取当前页码
                	String currntPage=request.getParameter("pageIndex");
                	if(currntPage==null)
                		currntPage="1";
                	int pageIndex=Integer.parseInt(currntPage);
                	//获取新闻记录总数量
                	int totalCount=newsService.getTotalCount();
                	//每页显示记录数
                	int pageSize=2;
                	/*获取总页数*/
                	Page pages=new Page();
                	pages.setCurrPageNo(pageIndex);
                	pages.setPageSize(pageSize);
                	pages.setRecordCount(totalCount);
                	int totalPage=pages.getTotalPageCount();
                	
                	//控制首页和 末页
                	if(pageIndex<1)
                		pageIndex=1;
                	else if(pageIndex>totalPage)
                		pageIndex=totalPage;
                	
                	//每页显示的新闻列表
                	List<News> newsList=newsService.getPageNewsList(pageIndex, pageSize);
                	int i=0;
                	for(News news:newsList){
                		i++;
                %>
                <tbody>
                	<tr <%if(i%2!=0){%>class="admin-list-td-h2"<%} %>>
                		<td><a href='newsDetailView.jsp?id=<%=news.getId()%>'><%=news.getTitle() %></a></td>
                		<td><%=news.getAuthor()%></td>
                		<td><%=news.getCreateDate() %></td>
                		<td><a href='adminNewsCreate.jsp?id=2'>修改</a>
                			<a href="javascript:if(confirm('确认是否删除此新闻？')) location='adminNewsDel.jsp?id=2'">删除</a>
                		</td>
                	</tr> 
                </tbody>
                <%
                	}
                %>
            </table>
           <div class="page-bar">
			<ul class="page-num-ul clearfix">
				<li>共<%=totalCount %>条记录   <%=pageIndex %>/<%=totalPage %>页</li>
				<%
					if(pageIndex>1){
				%>
				<a href="newsDetailList.jsp?pageIndex=1">首页</a>
				<a href="newsDetailList.jsp?pageIndex=<%=pageIndex-1%>">上一页</a>
				<%
					}if(pageIndex<totalPage){
				%>
				<a href="newsDetailList.jsp?pageIndex=<%=pageIndex+1%>">下一页</a>
				<a href="newsDetailList.jsp?pageIndex=<%=totalPage%>">最后一页</a>
				<%
					}
				%>
				  
			</ul>
		 <span class="page-go-form"><label>跳转至</label>

2“GO”按钮跳转

（修改升级分页）

	function page_nav(frm,num){
		frm.pageIndex.value=num;
		frm.submit();
	}

<%
					if(pageIndex>1){
				%>
				<a href="javaScript:page_nav(document.forms[0],1)">首页</a>
				<a href="javaScript:page_nav(document.forms[0],<%=pageIndex-1%>)">上一页</a>
				<%
					}if(pageIndex<totalPage){
				%>
				<a href="javaScript:page_nav(document.forms[0],<%=pageIndex+1%>)">下一页</a>
				<a href="javaScript:page_nav(document.forms[0],<%=totalPage%>)">最后一页</a>
				<%
					}
				%>

GO跳转

	function jump_to(frm,pageno){
	 	var regexp=/^\d+$/;
		if(!regexp.test(pageno)){
			alert("请输入 正确的数字！");
			return false;
		}else{
			page_nav(frm,pageno);
		}  
		
	}

<span class="page-go-form"><label>跳转至</label>
	     <input type="text" name="inputPage" id="inputPage" class="page-key" />页
	     <button type="button" class="page-btn" onClick='jump_to(document.forms[0],document.getElementById("inputPage").value)'>GO</button>
		</span>