java mysql分页工具类翻页_java实现分页工具类(JDBC)

1 public classPageModelTest{2 public static voidmain(String[] args){3 int pageNo=1;4 int pageSize=10;5 findUserList(pageNo,pageSize);6 }7 /**

8 * 分页查询9 *@parampageNo 第几页10 *@parampageSize 每页多少条记录11 *@returnPageModel12 */

13 public PageModel findUserList(int pageNo,intpageSize){14 PageModel pageModel = null;15 Connection conn = null;16 PreparedStatement ps = null;17 ResultSet rs = null;18 //各数据库的分页语句不一样19

20 /*oracle实现分页,三层嵌套，这里10应该为pageNo*pageSize ，0为(pageNo-1)*pageSize21 String sql="22 select column1,column2,column3,column4,column5 from23 (select rownum rn,column1,column2,column3,column4,column5 from24 (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 personList = new ArrayList();37 while(rs.next()){38 Person person=newPerson();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();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 returnpageModel;75 }76 /**

77 * 得到总记录数，私有方法，外部无法访问，本类中使用78 *@paramconn79 *@return

80 */

81 private intgetTotalRecords(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 block

109 e.printStackTrace();110 }111 }112 returncount;113 }

114 }