Java学习之分页查询

Java学习之分页查询

一先做数据库的实体类

Userinfo.java

<span style="font-size:14px;">package com.zhu.books.jdbc;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class Pagation extends DBConnection {
	public int getTableRsCount(){
		int iCount=0;	//记录行数，然后返回
		try {
			getConnection();
			String sql="select  count(id) as icount from table1";
			pStmt=conn.prepareStatement(sql);
			rs=pStmt.executeQuery();
			if(rs.next()){
				iCount=rs.getInt("icount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return iCount;  
	}
	/**
	 * 查询信息
	 */
	public List<Userinfo> getPageListUserinfo(int pageNum,int pageSize){
		List<Userinfo> list=new ArrayList <Userinfo> ();
		try {
			getConnection();
			int begin=(pageNum-1)*pageSize;
			String sql="select * from table1 limit "+begin+","+pageSize;
			pStmt=conn.prepareStatement(sql);
			rs=pStmt.executeQuery();
			while(rs.next()){
				Userinfo user=new Userinfo();
				user.setId(rs.getInt("id"));
				user.setUname(rs.getString("uname"));
				user.setRealname(rs.getString("realname"));
				list.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
			return list;
	}
	
</span><span style="font-size:18px;"><strong>}
</strong></span>

二:建立数据库连接

DBConnection.java

package com.zhu.books.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class DBConnection {
	private static final String CLS = "com.mysql.jdbc.Driver";
	private static final String URL = "jdbc:mysql://localhost:3306/test";
	private static final String USER = "root";
	private static final String PWD = "122198";
	public static Connection conn = null;
	public static Statement stmt = null;
	public static PreparedStatement pStmt = null;
	public static ResultSet rs = null;
	/**
	 * 建立数据库连接
	 */
	public static void getConnection(){
		try{
			Class.forName(CLS);
			conn = DriverManager.getConnection(URL, USER, PWD);
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}
	/**
	 * 关闭连接
	 */
	public static void closeAll(){
		try{
			if(rs!=null){
				rs.close();
				rs=null;
			}
			if(stmt!=null){
				stmt.close();
				stmt=null;
			}
			if(pStmt!=null){
				pStmt.close();
				pStmt=null;
			}
			if(conn!=null){
				conn.close();
				conn=null;
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}

}

三实施分页
Pagation.java

package com.zhu.books.jdbc;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class Pagation extends DBConnection {
	public int getTableRsCount(){
		int iCount=0;	//记录行数，然后返回
		try {
			getConnection();
			String sql="select  count(id) as icount from table1";
			pStmt=conn.prepareStatement(sql);
			rs=pStmt.executeQuery();
			if(rs.next()){
				iCount=rs.getInt("icount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return iCount;  
	}
	/**
	 * 查询信息
	 */
	public List<Userinfo> getPageListUserinfo(int pageNum,int pageSize){
		List<Userinfo> list=new ArrayList <Userinfo> ();
		try {
			getConnection();
			int begin=(pageNum-1)*pageSize;
			String sql="select * from table1 limit "+begin+","+pageSize;
			pStmt=conn.prepareStatement(sql);
			rs=pStmt.executeQuery();
			while(rs.next()){
				Userinfo user=new Userinfo();
				user.setId(rs.getInt("id"));
				user.setUname(rs.getString("uname"));
				user.setRealname(rs.getString("realname"));
				list.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
			return list;
	}
	
}

四建立jsp查询页面
paging.jsp

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@page import="com.zhu.books.jdbc.Userinfo"%>
<%@page import="com.zhu.books.jdbc.Pagation"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>分页显示数据</title>
    
	<meta http-equiv="pragma" content="no-cache">
	<meta http-equiv="cache-control" content="no-cache">
	<meta http-equiv="expires" content="0">    
	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
	<meta http-equiv="description" content="This is my page">
	<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->

  </head>
  
  <body>
      	<table border="1" align="center">
      	<tr>
      	 <td>id</td>
      	  <td>用户名</td>
      	   <td>真实姓名	</td>
      	</tr>
      	<%
      	String strPagenum=request.getParameter("pageNum");
      	int pageNum=1;//当前列数
      	int pageSize=3;
      	int pageSum=0;
      	if(strPagenum!=null){
      	  pageNum=Integer.parseInt(strPagenum);
      	  
      	}
      	Pagation  pagation=new Pagation();
      	List<Userinfo> list=pagation.getPageListUserinfo(pageNum,pageSize);
      	 int  iCount=pagation.getTableRsCount();//记录总数
      	
      	 //计算总面积
      	 pageSum=iCount/pageSize;
      	 if(iCount%pageSize!=0){
      	   pageSum+=1;
      	 }
      	 if(list.size()>0){
      	 for(Userinfo  user: list){
      	    %>     	    
      	    <tr>
      	    <td> <%=user.getId()%></td>
      	     <td> <%=user.getUname()%></td>
      	      <td> <%=user.getRealname()%></td>
      	    </tr>
      <%
       }  
      	 }	
       %>
       <tr>
       <td colspan="3" align="center">
        <%
          out.print("第["+pageNum+"]页, 共["+pageSum+"]页");
          if(pageNum<2){
          
         
         %>
               上一页
         <%
       }else{
          %>
          <a href="../Books/books/paging.jsp?pageNum=<%=pageNum-1%>" > 上一页</a>
          <% 
          }
           %>
             
           <%
           if(pageNum>=pageSum)
           {
           %>
           上一页
           <%
           }else
           {
           
            %>
             <a href="../Books/books/paging.jsp?pageNum=<%=pageNum+1 %>" >下 一页</a> 
            <%
            
            } %>
       </td>
       
       </tr>	
      	
      	</table>
  </body>
<span style="color:#ff0000;"></html>
</span>

页面展示