首页 > 代码库 > 简单分页查询(web基础学习笔记十三)
简单分页查询(web基础学习笔记十三)
一、建立资源文件和工具类
1.1 、database.properties
jdbc.driver_class=oracle.jdbc.driver.OracleDriverjdbc.connection.url=jdbc:oracle:thin:@localhost:1521:orcljdbc.connection.username=scottjdbc.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="http://www.mamicode.com/" />
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="http://www.mamicode.com/emp.jsp?pageIndex=1">首页</a></td><td><a href="http://www.mamicode.com/emp.jsp?pageIndex=">上一页</a></td><%}if(pageNo<totalPageCount){ //控制最后一页的显示%><td><a href="http://www.mamicode.com/emp.jsp?pageIndex=">下一页</a></td><td><a href="http://www.mamicode.com/emp.jsp?pageIndex=">最后一页</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方式
小弟没持明白，搞明白了再写
简单分页查询(web基础学习笔记十三)
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。