分页算法:

maxPageSize=count/pageSize(最大页数=数据行数/每页行数)

currentPage=1:当前页最小为1(初始为1)

首页:currentPage=1

上一页:currentPage-1(下一页:当前页减1)

下一页:currentPage+1(下一页:当前页加1)

末页:currentPage=maxPageSize(当前页为最大页数，即为末页)

分页sql,mssqlserver和mysql的sql语句需要两个参数，pageSize每页大小，及currentPage当前页编号

mssqlserver:

SELECTTOP"+paegSize+"*

FROMtable_name

WHERE(IDNOTIN

(SELECTTOP"+(currentPage-1)*pageSize+"id

FROMtable_name

ORDERBYIDdesc))

ORDERBYIDdesc

ID为表主键,用作排序

desc为降序查询,默认为升序查询

table_name为表名

mysqlsql

select*fromtable_nameorderbyIDdesclimit"+pageSize*(current-1)+","+pageSize+"

table_name表示表名:

ID:表的主键，用作排序
desc:降序，默认为esc升序

pageSize*(current-1):表示查询的起始位置

pageSize:表示显示的行数

xk数据库student表设计

CREATETABLE`student`(

`stuno`varchar(8)NOTNULL,

`classno`varchar(8)DEFAULTNULL,

`stuname`varchar(10)DEFAULTNULL,

`pwd`varchar(8)DEFAULTNULL,

PRIMARYKEY(`stuno`)

)ENGINE=InnoDBDEFAULTCHARSET=utf8;

数据库连接:DataBaseConnection.java

packagecom.model.command;

importjava.sql.*;

publicclassDataBaseConnection{

privateConnectionconn=null;

//使用mysql

privateStringDBDRIVER="com.mysql.jdbc.Driver";

privateStringDBURL="jdbc:mysql://localhost:3306/xk";

privateStringDBUSER="root";

privateStringDBPASSWORD="root";

/**

*连接mssqlserver2008

privateStringDBDRIVER="com.microsoft.sqlserver.jdbc.SQLServerDriver";

privateStringDBURL="jdbc:sqlserver://127.0.0.1:1433;DatabaseName=xk";

privateStringDBUSER="sa";

privateStringDBPASSWORD="yixiaoqun";

*/

publicDataBaseConnection()

{

try

{

Class.forName(DBDRIVER);

this.conn=DriverManager.getConnection(DBURL,DBUSER,DBPASSWORD);

}

catch(Exceptione)

{

e.printStackTrace();

}

}

publicConnectiongetConnection()

{

returnthis.conn;

}

publicvoidclose()

{

try

{

this.conn.close();

}

catch(Exceptione)

{

e.printStackTrace();

}

}

}

Sutdent表字段封装:Student.java

packagecom.model.Student.entitie;

publicclassStudent{

privateStringstuno;

privateStringclassno;

privateStringstuname;

privateStringpwd;

publicStringgetStuno(){

returnstuno;

}

publicvoidsetStuno(Stringstuno){

this.stuno=stuno;

}

publicStringgetClassno(){

returnclassno;

}

publicvoidsetClassno(Stringclassno){

this.classno=classno;

}

publicStringgetStuname(){

returnstuname;

}

publicvoidsetStuname(Stringstuname){

this.stuname=stuname;

}

publicStringgetPwd(){

returnpwd;

}

publicvoidsetPwd(Stringpwd){

this.pwd=pwd;

}

}

SutdentDao接口:StudnetDao.java定义操作Stuent的方法

packagecom.model.dao;

importcom.model.Student.entitie.*;

importjava.util.*;

publicinterfaceStudentDao{

publicList<Student>findAllByPage(intcurrenPage,intpageSize);

publicintMaxCount(intpageSize);

}

接口实现类:StudentDaoImpl.java具体实现对Student的操作方法

packagecom.model.dao;

importjava.util.ArrayList;

importjava.util.List;

importcom.model.Student.entitie.Student;

importcom.model.command.*;

importjava.sql.*;

publicclassStudentDaoImplimplementsStudentDao{

DataBaseConnectiondbc=null;

PreparedStatementpstmt=null;

ResultSetrs=null;

Stringsql=null;

/**

*@paramcurrenPage

*当前页，最小为1(初始值为1

*@parampageSize

*每页大小，每页显示的记录数

*@paramlimit?,?

*第一个?表示从第几行开始(用(currenPage-1)*pageSize),第二个?表示每页显示的记录数(用pageSize)

*/

publicList<Student>findAllByPage(intcurrentPage,intpageSize){

//TODOAuto-generatedmethodstub

List<Student>all=newArrayList();

StringBuffersql=newStringBuffer();

dbc=newDataBaseConnection();

/**

//mssql语句,未排序

//sql="selecttop"+pageSize+"stuno,classno,stuname,pwdfromstudentwherestunonotin(selecttop"+(currentPage-1)*pageSize+"stunofromStudent)";

//排序mssql

//sql1="SELECTTOP"+pageSize+"stuno,classno,stuname,pwdFROMstudentWHERE(stunoNOTIN(SELECTTOP"+(currentPage-1)*pageSize+"stunoFROMstudentORDERBYStuNodesc))ORDERBYStuNodesc";

//同上，使用StringBuffer

sql.append("SELECTTOP"+pageSize+"stuno,classno,stuname,pwdFROMstudentWHERE");

sql.append("(stunoNOTIN(SELECTTOP"+(currentPage-1)*pageSize+"stuno");

sql.append("FROMstudentORDERBYStuNodesc))ORDERBYStuNodesc");

*/

try

{

sql.append("selectstuno,classno,stuname,pwdfromstudentorderbystunodesclimit?,?");

pstmt=dbc.getConnection().prepareStatement(sql.toString());

pstmt.setInt(1,(currentPage-1)*pageSize);

pstmt.setInt(2,pageSize);

rs=pstmt.executeQuery();

Studentstudent=newStudent();

while(rs.next())

{

student.setStuno(rs.getString(1));

student.setStuname(rs.getString(2));

student.setClassno(rs.getString(3));

student.setPwd(rs.getString(4));

all.add(student);

}

System.out.println("分页sql执行成功");

rs.close();

pstmt.close();

}catch(Exceptione)

{

System.out.println("分页sql执行失败");

e.printStackTrace();

}finally

{

dbc.close();

}

returnall;

}

publicintMaxCount(intpageSize){

//TODOAuto-generatedmethodstub

intcount=0,maxPageSize=0;

dbc=newDataBaseConnection();

sql="selectcount(*)fromStudent";

try

{

rs=dbc.getConnection().prepareStatement(sql).executeQuery();

if(rs.next())

{

count=rs.getInt(1);

}

if(count%pageSize==0)

{

maxPageSize=count/pageSize;

}

else

{

maxPageSize=count/pageSize+1;

}

}catch(Exceptione)

{

System.out.println("获取记录数失败!");

e.printStackTrace();

}

System.out.println("获取记录数为!"+count);

returnmaxPageSize;

}

}

StudentFactory.java(应用工厂模式)

packagecom.model.dao.factory;

importcom.model.dao.StudentDao;

importcom.model.dao.StudentDaoImpl;

publicclassStudentFactory{

privatestaticStudentFactorystudentFactory=null;

privateStudentFactory(){

}

publicstaticStudentFactorygetInstance()

{

/**

*单例模式

*实例数量只设置一个，有利于节约内存

*/

if(studentFactory==null)

{

studentFactory=newStudentFactory();

}

returnstudentFactory;

}

publicstaticStudentDaogetStudnetDaoInstance(){

returnnewStudentDaoImpl();

}

}

分页查询servlet:findByPageServlet.java

packagecom.controller.web.student;

importjava.io.IOException;

importjava.io.PrintWriter;

importjavax.servlet.ServletException;

importjavax.servlet.http.HttpServlet;

importjavax.servlet.http.HttpServletRequest;

importjavax.servlet.http.HttpServletResponse;

importjava.util.*;

importjava.sql.*;

importcom.model.dao.factory.StudentFactory;

importcom.model.dao.*;

importcom.model.Student.entitie.*;

publicclassfindByPageServletextendsHttpServlet{

publicvoiddoGet(HttpServletRequestrequest,HttpServletResponseresponse)

throwsServletException,IOException{

response.setContentType("text/html");

doPost(request,response);

}

publicvoiddoPost(HttpServletRequestrequest,HttpServletResponseresponse)

throwsServletException,IOException{

StringcurrentPages=request.getParameter("currentPage");

intcurrentPage=1;

intpageSize=10;//每页大小为10行数据

if(currentPages!=null){

currentPage=Integer.parseInt(currentPages);

}

StudentDaostudentdao=StudentFactory.getStudnetDaoInstance();

intmaxPage=studentdao.MaxCount(10);

List<Student>all=studentdao.findAllByPage(currentPage,pageSize);

System.out.println("findAllByPage分页成功!");

request.setAttribute("all",all);

request.setAttribute("currentPage",currentPage);

request.setAttribute("maxPage",maxPage);

request.getRequestDispatcher("index.jsp").forward(request,response);

}

}

web.xml配置

<?xmlversion="1.0"encoding="UTF-8"?>

<web-appversion="2.5"

xmlns="http://java.sun.com/xml/ns/javaee"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://java.sun.com/xml/ns/javaee

http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">

<display-name></display-name>

<servlet>

<servlet-name>findByPageServlet</servlet-name>

<servlet-class>com.controller.web.student.findByPageServlet</servlet-class>

</servlet>

<servlet-mapping>

<servlet-name>findByPageServlet</servlet-name>

<url-pattern>/findByPageServlet.do</url-pattern>

</servlet-mapping>

<welcome-file-list>

<welcome-file>findByPageServlet.do</welcome-file>

</welcome-file-list>

</web-app>

index.jsp

<%@pagelanguage="java"import="java.util.*"pageEncoding="UTF-8"%>

<%@tagliburi="http://java.sun.com/jsp/jstl/core"prefix="c"%><!--引入jstl标签库-->

<!DOCTYPEHTMLPUBLIC"-//W3C//DTDHTML4.01Transitional//EN">

<html>

<head>

</head>

<body>

<tableborder="1"width="50%"align="center">

<tr>

<th>

学生编号

</th>

<th>

班级编号

</th>

<th>

学生姓名

</th>

<th>

学生密码

</th>

</tr>

<c:forEachitems="${all}"var="s">

<tr>

<td>

<c:outvalue="${s.stuno}"></c:out>

</td>

<td>

<c:outvalue="${s.classno}"></c:out>

</td>

<td>

<c:outvalue="${s.stuname}"></c:out>

</td>

<td>

<c:outvalue="${s.pwd}"></c:out>

</td>

</tr>

</c:forEach>

</table>

<center>

<c:choose>

<c:whentest="${currentPage>1}">

<a

href="<%=request.getContextPath()%>/findByPageServlet.do?currentPage=1">首页</a>

<a

href="<%=request.getContextPath()%>/findByPageServlet.do?currentPage=${currentPage-1}">上一页</a>

</c:when>

<c:otherwise>

首页上一页

</c:otherwise>

</c:choose>

<c:choose>

<c:whentest="${currentPage<maxPage}">

<a

href="<%=request.getContextPath()%>/findByPageServlet.do?currentPage=${currentPage+1}">下一页</a>

<a

href="<%=request.getContextPath()%>/findByPageServlet.do?currentPage=${maxPage}">末页</a>

</c:when>

<c:otherwise>

下一页末页

</c:otherwise>

</c:choose>

当前第[${currentPage}]页&nbsp;&nbsp;总共[${maxPage}]页

</body>

</html>