java实现分页工具类(JDBC)
分页的实现在日常的开发中会经常遇到，编写一个分页工具类，以后可以直接引用了，呵呵。。。
1.分页工具类，封装分页信息
1 package com.student.util; 2 import java.util.List; 3 4 import com.student.entity.Person; 5 6 /** 7 * 封装分页信息 8 * @author Administrator 9 *10 * @param <Person>11 */12 public class PageModel<E> {13 14 //结果集15 private List<E> list;16 17 //查询记录数18 private int totalRecords;19 20 //第几页21 private int pageNo;22 23 //每页多少条记录24 private int pageSize;25 26 //总页数27 public int getTotalPages(){28 return (totalRecords + pageSize -1)/pageSize;29 }30 31 //首页32 public int getTopPage(){33 return 1;34 }35 36 //上一页37 public int getPreviousPage(){38 if(pageNo<=1){39 return 1;40 }41 return pageNo-1;42 }43 //下一页44 public int getNextPage(){45 if(pageNo>=getBottomPage()){46 return getBottomPage();47 }48 return pageNo+1;49 }50 51 //尾页52 public int getBottomPage(){53 return getTotalPages();54 }55 56 public List<E> getList() {57 return list;58 }59 public void setList(List<E> list) {60 this.list = list;61 }62 public int getTotalRecords() {63 return totalRecords;64 }65 public void setTotalRecords(int totalRecords) {66 this.totalRecords = totalRecords;67 }68 public int getPageNo() {69 return pageNo;70 }71 public void setPageNo(int pageNo) {72 this.pageNo = pageNo;73 }74 public int getPageSize() {75 return pageSize;76 }77 public void setPageSize(int pageSize) {78 this.pageSize = pageSize;79 }80 81 }2.测试类
1 public class PageModelTest{ 2 public static void main(String[] args){ 3 int pageNo=1; 4 int pageSize=10; 5 findUserList(pageNo,pageSize); 6 } 7 /** 8 * 分页查询 9 * @param pageNo 第几页 10 * @param pageSize 每页多少条记录 11 * @return PageModel<E> 12 */ 13 public PageModel<Person> findUserList(int pageNo,int pageSize){ 14 PageModel<Person> pageModel = null; 15 Connection conn = null; 16 PreparedStatement ps = null; 17 ResultSet rs = null; 18 //各数据库的分页语句不一样 19 20 /*oracle实现分页,三层嵌套，这里10应该为pageNo*pageSize ，0为(pageNo-1)*pageSize 21 String sql=" 22 select column1,column2,column3,column4,column5 from 23 (select rownum rn,column1,column2,column3,column4,column5 from 24 (select column1,column2,column3,column4,column5 from table_name order by column desc) 25 where rownum<=10) 26 where rn>0"; 27 */ 28 //mysql实现分页 29 String sql="select * from person order by id desc limit ?,? "; 30 conn=DBUtil.getUtil().getConnection(); 31 try { 32 ps=conn.prepareStatement(sql); 33 ps.setInt(1, (pageNo-1) * pageSize); 34 ps.setInt(2, pageSize); 35 rs=ps.executeQuery(); 36 List<Person> personList = new ArrayList<Person>(); 37 while(rs.next()){ 38 Person person=new Person(); 39 person.setName(rs.getString("stu_name")); 40 person.setPassword(rs.getString("stu_psw")); 41 person.setNumber(rs.getString("stu_number")); 42 person.setBirthday(rs.getDate("stu_birth")); 43 person.setSex(rs.getInt("stu_sex")); 44 person.setPolity(rs.getInt("stu_polity")); 45 person.setBrief(rs.getString("stu_brief")); 46 person.setType(rs.getInt("type")); 47 person.setState(rs.getInt("state")); 48 personList.add(person); 49 } 50 pageModel = new PageModel<Person>(); 51 pageModel.setList(personList); 52 pageModel.setTotalRecords(getTotalRecords(conn)); 53 pageModel.setPageSize(pageSize); 54 pageModel.setPageNo(pageNo); 55 } catch (SQLException e) { 56 // TODO Auto-generated catch block 57 e.printStackTrace(); 58 }finally{ 59 try { 60 if(rs!=null){ 61 rs.close(); 62 } 63 if(ps!=null){ 64 ps.close(); 65 } 66 if(conn!=null){ 67 conn.close(); 68 } 69 } catch (SQLException e) { 70 // TODO Auto-generated catch block 71 e.printStackTrace(); 72 } 73 } 74 return pageModel; 75 } 76 /** 77 * 得到总记录数，私有方法，外部无法访问，本类中使用 78 * @param conn 79 * @return 80 */ 81 private int getTotalRecords(Connection conn){ 82 PreparedStatement ps = null; 83 ResultSet rs = null; 84 85 String sql="select count(*) from person"; 86 87 conn=DBUtil.getUtil().getConnection(); 88 int count=0; 89 try { 90 91 ps=conn.prepareStatement(sql); 92 rs=ps.executeQuery(); 93 while(rs.next()){ 94 //此时根据sql语句查出的只有一列，否则不建议用int标识字段 95 count = rs.getInt(1); 96 } 97 } catch (SQLException e) { 98 e.printStackTrace(); 99 }finally{100 try {101 if(rs!=null){102 rs.close();103 }104 if(ps!=null){105 ps.close();106 }107 } catch (SQLException e) {108 // TODO Auto-generated catch block109 e.printStackTrace();110 }111 }112 return count;113 }114 }TAG:
