简单分页查询(web基础学习笔记十三)

9/6/2015来源:Java教程人气:5232

简单分页查询(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="<%=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 %>条记录&nbsp;&nbsp; <%=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方式

小弟没持明白,搞明白了再写