简单分页查询(web基础学习笔记十三)
一、建立资源文件和工具类
1.1 、database.properties
jdbc.driver_class=oracle.jdbc.driver.OracleDriver jdbc.connection.url=jdbc:oracle:thin:@localhost:1521:orcl jdbc.connection.username=scott jdbc.connection.password=tiger
1.2、建立包：com.pb.emp.untily
ConfigManager类
package com.pb.emp.untily; import java.io.IOException; import java.io.InputStream; import java.util.Properties; public class ConfigManager { private static ConfigManager configManager; private static Properties properties; private ConfigManager(){ String configfile="database.properties"; properties=new Properties(); InputStream in=ConfigManager.class.getClassLoader().getResourceAsStream(configfile); try { properties.load(in); in.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } public static ConfigManager getInstance(){ if(configManager==null){ configManager=new ConfigManager(); } return configManager; } public static String getString(String key){ return properties.getProperty(key); } }
1.3、建立员工实体类
package com.pb.emp.entity; import java.util.Date; /** * 员工实体类 * @author 森林森 * */ public class Emp { private int empno; private String ename; private String job; private int mgr; private Date hiredate; private double sal; private double comm; private int deptno; //getter和setter方法 public int getEmpno() { return empno; } public void setEmpno(int empno) { this.empno = empno; } public String getEname() { return ename; } public void setEname(String ename) { this.ename = ename; } public String getJob() { return job; } public void setJob(String job) { this.job = job; } public int getMgr() { return mgr; } public void setMgr(int mgr) { this.mgr = mgr; } public Date getHiredate() { return hiredate; } public void setHiredate(Date hiredate) { this.hiredate = hiredate; } public double getSal() { return sal; } public void setSal(double sal) { this.sal = sal; } public double getComm() { return comm; } public void setComm(double comm) { this.comm = comm; } public int getDeptno() { return deptno; } public void setDeptno(int deptno) { this.deptno = deptno; } }
二、建立BaseDao基类
2.1、建立基类
package com.pb.emp.dao; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import com.pb.emp.untily.ConfigManager; public class BaseDao { protected Connection conn; protected PreparedStatement ps; protected ResultSet rs; //建立连接 public boolean getConnection(){ String driver=ConfigManager.getInstance().getString("jdbc.driver_class"); String url=ConfigManager.getInstance().getString("jdbc.connection.url"); String username=ConfigManager.getInstance().getString("jdbc.connection.username"); String password=ConfigManager.getInstance().getString("jdbc.connection.password"); try { Class.forName(driver); conn=DriverManager.getConnection(url,username, password); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); return false; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); return false; } return true; } //增加，修改，删除 public int executeUpdate(String sql, Object[] params){ getConnection(); int updateRow=0; try { ps=conn.prepareStatement(sql); //填充占位符 for(int i=0;i<params.length;i++){ ps.setObject(i+1, params[i]); } updateRow = ps.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return updateRow; } // //查询 public ResultSet executeSQL(String sql, Object[] params){ getConnection(); try { ps=conn.prepareStatement(sql); //填充占位符 for(int i=0;i<params.length;i++){ ps.setObject(i+1, params[i]); } rs = ps.executeQuery(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return rs; } // 关闭资源 public boolean closeResource() { if(rs!=null){ try { rs.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); return false; } } if(ps!=null){ try { ps.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); return false; } } if(conn!=null){ try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); return false; } } return true; } }
2.2、建立分页类
package com.pb.emp.untily; public class Page { private int currPageNo=1; //当前页号 private int pageSize=0; //每页显示的记录数 private int recordCount=0; //总记录数 private int totalPageCount=1; //总页数 public int getCurrPageNo() { //条中不能加this if(totalPageCount==0) return 0; return currPageNo; } public void setCurrPageNo(int currPageNo) { if(currPageNo>0) this.currPageNo = currPageNo; } public void setPageSize(int pageSize) { //条中不能加this if(pageSize>0) this.pageSize = pageSize; } public int getPageSize() { return pageSize; } public int getTotalPageCount() { return totalPageCount; } public void setTotalPageCount(int totalPageCount) { this.totalPageCount = totalPageCount; } public int getRecordCount() { return recordCount; } public void setRecordCount(int recordCount) { //条中不能加this if(recordCount>0) this.recordCount = recordCount; //调用总页数设置方法为totalPageCount赋值 //this.settotalPageCountByRs(); } //设置总页数 public int settotalPageCountByRs(){ if(this.recordCount%this.pageSize==0){ this.totalPageCount=this.recordCount/this.pageSize; }else if(this.recordCount%this.pageSize>0){ this.totalPageCount=this.recordCount/this.pageSize+1; }else{ this.totalPageCount=0; } return this.totalPageCount; } //开始记录数 public int getStartRow(){ return (currPageNo-1) * pageSize+1; } //结束记录数 public int getEndRow(){ return currPageNo * pageSize; } public static void main(String[] args) { Page p=new Page(); p.setCurrPageNo(2); System.out.println(p.getCurrPageNo()); p.setPageSize(3); System.out.println(p.getPageSize()); int i=p.getStartRow(); System.out.println("启始记录"+i); int j=p.getEndRow(); System.out.println("结束记录"+j); } }
三、建立分页查询的实现类
3.1、实现类
package com.pb.emp.dao.empl; import java.sql.SQLException; import java.util.ArrayList; import java.util.Date; import java.util.List; import com.pb.emp.dao.BaseDao; import com.pb.emp.dao.EmpDao; import com.pb.emp.entity.Emp; import com.pb.emp.untily.Page; public class EmpDaoImpl extends BaseDao implements EmpDao { /** * 获取新闻总数量 * */ public int getTotalCount() { int totalCount=0; String sql="select count(*) from emp"; Object[] params={}; rs=this.executeSQL(sql, params); try { while(rs.next()){ totalCount=rs.getInt(1); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ this.closeResource(); } return totalCount; } /** * 分页获取新闻信息 * */ public List<Emp> getEmpByPage(int pageNo, int pageSize) { // 建立集合存放查询结果 List<Emp> emplist = new ArrayList<Emp>(); String sql = "SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno FROM(SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno,ROWNUM rn FROM emp) e WHERE e.rn BETWEEN ? AND ?"; Page pages=new Page(); //设置当前页和页显示显示记录数 pages.setCurrPageNo(pageNo); //设置当前页码 pages.setPageSize(pageSize); //每页显示记录数 //计算开始和结束记录数 int startRow = pages.getStartRow(); int endRow = pages.getEndRow(); //填充占位符? Object [] params={startRow,endRow}; //调用类BaseDao的查询方法并接收结果 rs=this.executeSQL(sql, params); try { while(rs.next()){ int empno=rs.getInt("empno"); String ename=rs.getString("ename"); String job=rs.getString("job"); int mgr=rs.getInt("mgr"); Date hiredate=rs.getDate("hiredate"); double sal=rs.getDouble("sal"); double comm=rs.getDouble("comm"); int deptno=rs.getInt("deptno"); //声明Emp对象 Emp emp=new Emp(); //将得到的值添加到对象中 emp.setEmpno(empno); emp.setEname(ename); emp.setJob(job); emp.setMgr(mgr); emp.setHiredate(hiredate); emp.setSal(sal); emp.setComm(comm); emp.setDeptno(deptno); //将对象添加到集合 emplist.add(emp); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ this.closeResource(); } return emplist; } public static void main(String[] args) { EmpDaoImpl e=new EmpDaoImpl(); System.out.println(e.getTotalCount()); List<Emp> emplist=e.getEmpByPage(2, 3); System.out.println("=====员工信息===="); for (Emp emp : emplist) { System.out.println(emp.getSal()+"\t"+emp.getComm()+"\t"+emp.getDeptno() ); } } }
3.2、分离出接口
package com.pb.emp.dao; import java.util.List; import com.pb.emp.entity.Emp; public interface EmpDao { /** * 获取新闻总数量 * */ public int getTotalCount(); public List<Emp> getEmpByPage(int pageNo, int pageSize); }
3.3、分享出service接口和实现类
package com.pb.service; import java.util.List; import com.pb.emp.entity.Emp; public interface Service { public List<Emp> getEmpByPage(int pageNo, int pageSize); public int getTotalCount(); }
package com.pb.service.impl; import java.util.List; import com.pb.emp.dao.EmpDao; import com.pb.emp.entity.Emp; import com.pb.service.Service; public class ServiceImpl implements Service { private EmpDao empDao; /** * 一定要加setter和getter方法，不然无法调用 * @森林森 */ public EmpDao getEmpDao() { return empDao; } public void setEmpDao(EmpDao empDao) { this.empDao = empDao; } @Override public List<Emp> getEmpByPage(int pageNo, int pageSize) { return empDao.getEmpByPage(pageNo, pageSize); } @Override public int getTotalCount() { // TODO Auto-generated method stub return empDao.getTotalCount(); } }
四、建立页面
4.1导入service类
建立common.jsp页面
<jsp:useBean id="empService" class="com.pb.service.impl.ServiceImpl" scope="page"></jsp:useBean> <jsp:useBean id="empDao" class="com.pb.emp.dao.empl.EmpDaoImpl" scope="page"></jsp:useBean> <jsp:setProperty property="empDao" name="empService" value="<%=empDao%>" />
4.2 实现页面get方式
<%@page import="com.pb.emp.untily.Page"%> <%@page import="com.pb.emp.entity.Emp"%> <%@page import="java.util.List"%> <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@include file="../common/common.jsp" %> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>Insert title here</title> <script type="text/javascript"> function go(){ var goid=document.getElementById("goid").value; var regexp=/^\d+$/; if(!regexp.test(goid)){ alert("请输入数字"); }else{ window.location="emp.jsp?pageIndex="+goid; } } </script> </head> <body> <table border="1" width="80%" align="center"> <tr> <td>员工编号</td> <td>员工姓名</td> <td>职位</td> <td>入职日期</td> <td>工资</td> <td>奖金</td> <td>部门编号</td> </tr> <% request.setCharacterEncoding("utf-8"); int pageSize=5; String currPageno=request.getParameter("pageIndex"); if(currPageno==null){ currPageno="1"; } //当前页 int pageNo=Integer.parseInt(currPageno); //总记录娄 int recordCount=empService.getTotalCount(); //每页显示记录数 Page pages=new Page(); //当前页 pages.setCurrPageNo(pageNo); //页面显示多少条记录 pages.setPageSize(pageSize); //总记录数 pages.setRecordCount(recordCount); //总页数 int totalPageCount=pages.settotalPageCountByRs(); //对首面和最后一面设置不重小于1和大于最后一页 if(pageNo<1){ pageNo=1; }else if (pageNo>totalPageCount){ pageNo=totalPageCount; } List<Emp> emplist=empService.getEmpByPage(pageNo, pageSize); for(Emp emp:emplist){ %> <tr> <td><%=emp.getEmpno() %></td> <td><%=emp.getEname() %></td> <td><%=emp.getJob() %></td> <td><%=emp.getHiredate() %></td> <td><%=emp.getSal() %></td> <td><%=emp.getComm() %></td> <td><%=emp.getDeptno() %></td> </tr> <% } %> <tr> <td>共<%=recordCount %>条记录 <%=pageNo %>/<%=totalPageCount %>页</td> <% //控制首页的显示 if(pageNo>1){ %> <td><a href="emp.jsp?pageIndex=1">首页</a></td> <td><a href="emp.jsp?pageIndex=<%=pageNo-1%>">上一页</a></td> <% } if(pageNo<totalPageCount){ //控制最后一页的显示 %> <td><a href="emp.jsp?pageIndex=<%=pageNo+1%>">下一页</a></td> <td><a href="emp.jsp?pageIndex=<%=totalPageCount%>">最后一页</a></td> <% } %> <td><input type="text" id="goid" name="goid"> </td> <td><a href="javascript:go();">GO</a></td> </tr> </table> </body> </html>
4.3 实现页面Post方式
小弟没持明白，搞明白了再写
