分页算法:
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}]页 总共[${maxPage}]页
</body>
</html>