实现分页
工具类：
package com.bjsxt.util;
import java.util.List;
/**
* 分页的三个基本属性
* 1.每页几条记录size 可以有默认值5
* 2.当前页号 index 可以有默认值1
* 3.记录总数totalCount：不可能有默认值，需要查询数据库获取真正的记录总数
*
* 4.一共多少页 ：totalPageCount=totalCount/size+1
* 5 30 31 32 33 34 35
* 5.上一页 index-1 当前页1，上一页1
* 6.下一页 index+1 当前页是最后一页 下一页：还是最后一页
*
* 扩展
* 分页Bean还可以放要查询的数据 protected List<T> list;
* 分页Bean还可以放页码列表 [1] 2 3 4 5 private int[] numbers;
*
* @author Administrator
*
* @param <T>
*/
public class PageBean<T> {
private int size = 5;//每页显示记录 //
private int index = 1;// 当前页号
private int totalCount = 0;// 记录总数 ok
private int totalPageCount = 1;// 总页数 ok
private int[] numbers;//展示页数集合 //ok
protected List<T> list;//要显示到页面的数据集
/**
* 得到开始记录
* @return
*/
public int getStartRow() {
return (index - 1) * size;
}
/**
* 得到结束记录
* @return
*/
public int getEndRow() {
return index * size;
}
/**
* @return Returns the size.
*/
public int getSize() {
return size;
}
/**
* @param size
* The size to set.
*/
public void setSize(int size) {
if (size > 0) {
this.size = size;
}
}
/**
* @return Returns the currentPageNo.
*/
public int getIndex() {
if (totalPageCount == 0) {
return 0;
}
return index;
}
/**
* @param currentPageNo
* The currentPageNo to set.
*/
public void setIndex(int index) {
if (index > 0) {
this.index = index;
}
}
/**
* @return Returns the totalCount.
*/
public int getTotalCount() {
return totalCount;
}
/**
* @param totalCount
* The totalCount to set.
*/
public void setTotalCount(int totalCount) {
if (totalCount >= 0) {
this.totalCount = totalCount;
setTotalPageCountByRs();//根据总记录数计算总页�?
}
}
public int getTotalPageCount() {
return this.totalPageCount;
}
/**
* 根据总记录数计算总页�?
* 5
* 20 4
* 23 5
*/
private void setTotalPageCountByRs() {
if (this.size > 0 && this.totalCount > 0 && this.totalCount % this.size == 0) {
this.totalPageCount = this.totalCount / this.size;
} else if (this.size > 0 && this.totalCount > 0 && this.totalCount % this.size > 0) {
this.totalPageCount = (this.totalCount / this.size) + 1;
} else {
this.totalPageCount = 0;
}
setNumbers(totalPageCount);//获取展示页数集合
}
public int[] getNumbers() {
return numbers;
}
/**
* 设置显示页数集合
*
* 默认显示10个页码
* 41 42 43 44 [45 ] 46 47 48 49 50
*
*
* [1] 2 3 4 5 6 7 8 9 10
*
* 41 42 43 44 45 46 47 [48] 49 50
* @param totalPageCount
*/
public void setNumbers(int totalPageCount) {
if(totalPageCount>0){
//!.当前数组的长度
int[] numbers = new int[totalPageCount>10?10:totalPageCount];//页面要显示的页数集合
int k =0;
//
//1.数组长度<10 1 2 3 4 .... 7
//2.数组长度>=10
// 当前页<=6 1 2 3 4 10
// 当前页>=总页数-5 ......12 13 14 15
// 其他 5 6 7 8 9 当前页(10) 10 11 12 13
for(int i = 0;i < totalPageCount;i++){
//保证当前页为集合的中�?
if((i>=index- (numbers.length/2+1) || i >= totalPageCount-numbers.length) && k<numbers.length){
numbers[k] = i+1;
k++;
}else if(k>=numbers.length){
break;
}
}
this.numbers = numbers;
}
}
public void setNumbers(int[] numbers) {
this.numbers = numbers;
}
public List<T> getList() {
return list;
}
public void setList(List<T> list) {
this.list = list;
}
/*
public static int getTotalPageCount(int iTotalRecordCount, int iPageSize) {
if (iPageSize == 0) {
return 0;
} else {
return (iTotalRecordCount % iPageSize) == 0 ? (iTotalRecordCount / iPageSize) : (iTotalRecordCount / iPageSize) + 1;
}
}*/
}
package com.bjsxt.util;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
/**
* JDBC锟侥癸拷锟斤拷锟斤拷
*
* @author Administrator
*
*/
public class DBUtil {
/**
* *
* @return
*/
public static Connection getConnection() {
Connection conn = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
/**
*
* @param rs
* @param stmt
* @param conn
*/
public static void closeAll(ResultSet rs, Statement stmt, Connection conn) {
try {
if (rs != null) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (stmt != null) {
stmt.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
*
* @param sql
* @param prams
* @return
* @throws SQLException
*/
public static int executeUpdate(String sql, Object[] prams) throws SQLException {
Connection conn = null;
PreparedStatement pstmt = null;
conn = getConnection();
int n = 0;
try {
pstmt = conn.prepareStatement(sql);
for (int i = 0; i < prams.length; i++) {
pstmt.setObject(i + 1, prams[i]);
}
n = pstmt.executeUpdate();
} catch (SQLException e) {
System.out.print("");
throw e;
}
return n;
}
}
Servlet：
package com.bjsxt.servlet;
import java.io.IOException;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.bjsxt.entity.Student;
import com.bjsxt.service.StudentService;
import com.bjsxt.service.impl.StudentServiceImpl;
import com.bjsxt.util.PageBean;
public class ShowAllServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
// 1.1 接收从页面传入的当前页码index
String sindex = request.getParameter("index"); // null ""
int index = 1;// 默认当前页码数是1
try {
index = Integer.parseInt(sindex);//"5"
} catch (NumberFormatException e) {
System.out.print("");
}
// 1.2 接收从页面传入的每页的记录数
String ssize = request.getParameter("size"); // null ""
int size = 5;// 默认当前页码数是1
try {
size = Integer.parseInt(ssize);//"5"
} catch (NumberFormatException e) {
System.out.print("");
}
//1.3查询条件：接收学生姓名
String name = request.getParameter("name");
//1.4查询条件：接收最低分数
String sminScore = request.getParameter("minScore"); //null "" "abc"
if(sminScore ==null){
sminScore = "";
}
double minScore =0;
try{
minScore = Double.parseDouble(sminScore);
}catch(NumberFormatException e){
System.out.print("");
}
// 2
PageBean<Student> pageBean = new PageBean<Student>();
pageBean.setIndex(index);
pageBean.setSize(size);
StudentService stuService = new StudentServiceImpl();
//List<Student> stuList = stuBiz.findAll();
//stuService.findStu(pageBean);//不需要返回stuList，因为经过业务层处理，所有的数据都在PageBean中
stuService.findStu(pageBean,name,minScore);
request.setAttribute("pageBean", pageBean);// !!!!!!!
request.setAttribute("name", name);
request.setAttribute("minScore", sminScore);
// 3com.bjsxt
request.getRequestDispatcher("/jsp/showAll.jsp").forward(request,
response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
this.doGet(request, response);
}
}
重要的Service层：
package com.bjsxt.service.impl;
import java.util.List;
import com.bjsxt.dao.StudentDao;
import com.bjsxt.dao.impl.StudentDaoImpl;
import com.bjsxt.entity.Student;
import com.bjsxt.service.StudentService;
import com.bjsxt.util.PageBean;
public class StudentServiceImpl implements StudentService {
private StudentDao stuDao = new StudentDaoImpl();
public List<Student> findAll() {
return this.stuDao.findAll();
}
public void findStu(PageBean<Student> pageBean) {
//查询数据库表获取记录总数
//int totalCount = this.stuDao.findAll().size();//????
int totalCount = this.stuDao.findCount();
System.out.println("count="+totalCount);
//使用记录总数计算PageBean中的其他属性(totalCount,totalPageCount,numbers)，就差list属性
pageBean.setTotalCount(totalCount);
//调用DAO层获取指定页的学生数据，并放入pageBean的list属性
/*
*每页size = 5条记录
* 第几页 起始记录号>= 结束记录号<= <
* 1 0 4 5
* 2 5 9 10
* 3 10 14 15
*
* index (index-1)*size index*size
*
*/
//int start = (pageBean.getIndex()-1)*pageBean.getSize();
//int end= pageBean.getIndex()*pageBean.getSize();
int start = pageBean.getStartRow();
int end = pageBean.getEndRow();
List<Student> list = this.stuDao.findStu(start,end);
pageBean.setList(list);
}
public void findStu(PageBean<Student> pageBean, String name, double minScore) {
//查询数据库表获取符合查询条件的记录总数
int totalCount = this.stuDao.findCount(name,minScore);
System.out.println("count="+totalCount);
//使用记录总数计算PageBean中的其他属性(totalCount,totalPageCount,numbers)，就差list属性
pageBean.setTotalCount(totalCount);
//调用DAO层获取指定页的学生数据，并放入pageBean的list属性
int start = pageBean.getStartRow();
int end = pageBean.getEndRow();
List<Student> list = this.stuDao.findStu(start,end,name,minScore);
pageBean.setList(list);
}
}
Dao层:
package com.bjsxt.dao.impl;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.bjsxt.dao.StudentDao;
import com.bjsxt.entity.Student;
import com.bjsxt.util.DBUtil;
public class StudentDaoImpl implements StudentDao {
public List<Student> findAll() {
Connection conn =DBUtil.getConnection();
Statement stmt =null;
ResultSet rs =null;
List <Student> stuList = new ArrayList<Student>();
try {
stmt =conn.createStatement();
rs = stmt.executeQuery("select * from student");
while(rs.next()){
Student stu = new Student();
stu.setId(rs.getInt("id"));
stu.setName(rs.getString("name"));
stu.setAge(rs.getInt("age"));
stu.setScore(rs.getDouble("score"));
stuList.add(stu);
}
} catch (SQLException e) {
System.out.print("");
}finally{
DBUtil.closeAll(rs, stmt, conn);
}
return stuList;
}
public List<Student> findStu(int start, int end) {
Connection conn =DBUtil.getConnection();
Statement stmt =null;
ResultSet rs =null;
List <Student> stuList = new ArrayList<Student>();
try {
stmt =conn.createStatement();
String sql = "select * from (select rownum rn,stu2.* "
+ "from (select stu.* from student stu order by score desc ) stu2 "
+ "where rownum <="+end+" ) "
+ "where rn >"+start;
rs = stmt.executeQuery(sql);
while(rs.next()){
Student stu = new Student();
stu.setId(rs.getInt("id"));
stu.setName(rs.getString("name"));
stu.setAge(rs.getInt("age"));
stu.setScore(rs.getDouble("score"));
stuList.add(stu);
}
} catch (SQLException e) {
System.out.print("");
}finally{
DBUtil.closeAll(rs, stmt, conn);
}
return stuList;
}
public int findCount() {
Connection conn =DBUtil.getConnection();
Statement stmt =null;
ResultSet rs =null;
List <Student> stuList = new ArrayList<Student>();
int count = 0;
try {
stmt =conn.createStatement();
rs = stmt.executeQuery("select count(*) from student");
rs.next();
count = rs.getInt(1);
} catch (SQLException e) {
System.out.print("");
}finally{
DBUtil.closeAll(rs, stmt, conn);
}
return count;
}
public int findCount(String name, double minScore) {
Connection conn =DBUtil.getConnection();
Statement stmt =null;
ResultSet rs =null;
List <Student> stuList = new ArrayList<Student>();
int count = 0;
try {
StringBuilder sql = new StringBuilder("select count(*) from student where 1=1 ");
if(name != null && !"".equals(name)){
sql.append(" and name like '%"+name+"%'");
}
if(minScore >0){
sql.append(" and score >= "+minScore);
}
stmt =conn.createStatement();
rs = stmt.executeQuery(sql.toString());
rs.next();
count = rs.getInt(1);
} catch (SQLException e) {
System.out.print("");
}finally{
DBUtil.closeAll(rs, stmt, conn);
}
return count;
}
public List<Student> findStu(int start, int end, String name,
double minScore) {
Connection conn =DBUtil.getConnection();
Statement stmt =null;
ResultSet rs =null;
List <Student> stuList = new ArrayList<Student>();
try {
stmt =conn.createStatement();
StringBuilder sql = new StringBuilder("select stu.* from student stu where 1=1 ");
if(name != null && !"".equals(name)){
sql.append(" and name like '%"+name+"%'");
}
if(minScore >0){
sql.append(" and score >= "+minScore);
}
sql.append(" order by score desc");
String sql2 = "select * from (select rownum rn,stu2.* "
+ "from ("+sql.toString()+") stu2 "
+ "where rownum <="+end+" ) "
+ "where rn >"+start;
rs = stmt.executeQuery(sql2);
while(rs.next()){
Student stu = new Student();
stu.setId(rs.getInt("id"));
stu.setName(rs.getString("name"));
stu.setAge(rs.getInt("age"));
stu.setScore(rs.getDouble("score"));
stuList.add(stu);
}
} catch (SQLException e) {
System.out.print("");
}finally{
DBUtil.closeAll(rs, stmt, conn);
}
return stuList;
}
}
浙公网安备 33010602011771号