JavaWeb查询分页的例子,查询条件存储

package com.etcxm.myteam.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import com.etcxm.myteam.bean.PageBean; import com.etcxm.myteam.exception.DaoException; import com.etcxm.myteam.pojo.Emp; import com.etcxm.myteam.util.JdbcUtils; import com.etcxm.myteam.vo.EmpVO; /** * * @author chenyx * */ public class EmpDao { public void insert(Emp emp) { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); String sql = "insert into emp(empno,ename,job,hiredate,sal,comm) values (seq_emp.nextval,?,?,?,?,?) "; ps = conn.prepareStatement(sql); ps.setString(1, emp.getEname()); ps.setString(2, emp.getJob()); ps.setDate(3, emp.getHiredate()); ps.setFloat(4, emp.getSal()); ps.setFloat(5, emp.getComm()); ps.executeUpdate(); } catch (SQLException e) { throw new DaoException(e.getMessage(), e); } finally { JdbcUtils.free(rs, ps, conn); } } public void update(Emp emp) { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); String sql = "update emp set ename=?,job=?,hiredate=?,sal=?,comm=? where empno=? "; ps = conn.prepareStatement(sql); ps.setString(1, emp.getEname()); ps.setString(2, emp.getJob()); ps.setDate(3, emp.getHiredate()); ps.setFloat(4, emp.getSal()); ps.setFloat(5, emp.getComm()); ps.setLong(6, emp.getEmpno()); ps.executeUpdate(); } catch (SQLException e) { throw new DaoException(e.getMessage(), e); } finally { JdbcUtils.free(rs, ps, conn); } } public void delete(String[] array) { String empnos = ""; if (array != null) { for (String s : array) { if (empnos.equals("")) empnos = s; else empnos += "," + s; } } Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); String sql = "delete from emp where empno in(" + empnos + ")"; System.out.println(sql); ps = conn.prepareStatement(sql); ps.executeUpdate(); } catch (SQLException e) { throw new DaoException(e.getMessage(), e); } finally { JdbcUtils.free(rs, ps, conn); } } public List<Emp> findAll() { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; List<Emp> empList = new ArrayList<Emp>(); try { conn = JdbcUtils.getConnection(); String sql = "select * from emp"; ps = conn.prepareStatement(sql); rs = ps.executeQuery(); while (rs.next()) { Emp emp = mappingEmp(rs); empList.add(emp); } } catch (SQLException e) { throw new DaoException(e.getMessage(), e); } finally { JdbcUtils.free(rs, ps, conn); } return empList; } public PageBean findPage(int pageNo, int pageSize) { PageBean page = new PageBean(pageNo, pageSize); Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; int totalCount = 0; List<Emp> empList = new ArrayList<Emp>(); try { conn = JdbcUtils.getConnection(); String countSql = "select count(*) from emp"; ps = conn.prepareStatement(countSql); rs = ps.executeQuery(); if (rs.next()) { totalCount = rs.getInt(1); } page.setTotalCount(totalCount);// 总记录数 String listSql = "select * from (" + "select t.*,rownum rn from (select * from emp) t " + "where rownum <= " + page.getLimit() + ") " + "where rn >= " + page.getStart(); ps = conn.prepareStatement(listSql); rs = ps.executeQuery(); while (rs.next()) { Emp emp = mappingEmp(rs); empList.add(emp); } page.setList(empList);// 查询结果集 } catch (SQLException e) { throw new DaoException(e.getMessage(), e); } finally { JdbcUtils.free(rs, ps, conn); } return page; } public PageBean findPage(EmpVO vo, int pageNo, int pageSize) { PageBean page = new PageBean(pageNo, pageSize); Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; int totalCount = 0; List<Emp> empList = new ArrayList<Emp>(); try { // -------------------构造where语句，当查询条件不为空----------------- String whereSql = " where 1=1 "; List<Object> values = new ArrayList<Object>(); if (vo != null) { if (vo.getEname() != null && !vo.getEname().trim().equals("")) { whereSql += " and ename like ?"; values.add("%" + vo.getEname() + "%"); } if (vo.getJob() != null && !vo.getJob().trim().equals("")) { whereSql += " and job = ?"; values.add(vo.getJob()); } if (vo.getBeginDate() != null) { whereSql += " and hiredate >= ?"; values.add(vo.getBeginDate()); } if (vo.getEndDate() != null) { whereSql += " and hiredate <= ?"; values.add(vo.getEndDate()); } } // ----------------------------------------------------------------- conn = JdbcUtils.getConnection(); String countSql = "select count(*) from emp"; System.out.println("SQL_COUNT: " + countSql + whereSql); ps = conn.prepareStatement(countSql + whereSql); for (int i = 0; i < values.size(); i++) { ps.setObject(i + 1, values.get(i)); } rs = ps.executeQuery(); if (rs.next()) { totalCount = rs.getInt(1); } page.setTotalCount(totalCount);// 总记录数 String listSql = "select * from (" + "select t.*,rownum rn from (select * from emp " + whereSql + ") t " + "where rownum <= " + page.getLimit() + ") " + "where rn >= " + page.getStart(); System.out.println("SQL_LIST: " + listSql); ps = conn.prepareStatement(listSql); for (int i = 0; i < values.size(); i++) { ps.setObject(i + 1, values.get(i)); } rs = ps.executeQuery(); while (rs.next()) { Emp emp = mappingEmp(rs); empList.add(emp); } page.setList(empList);// 查询结果集 } catch (SQLException e) { throw new DaoException(e.getMessage(), e); } finally { JdbcUtils.free(rs, ps, conn); } return page; } public Emp getEmp(long empno) { Emp emp = null; Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); String sql = "select * from emp where empno = ? "; ps = conn.prepareStatement(sql); ps.setLong(1, empno); rs = ps.executeQuery(); if (rs.next()) { emp = mappingEmp(rs); } } catch (SQLException e) { throw new DaoException(e.getMessage(), e); } finally { JdbcUtils.free(rs, ps, conn); } return emp; } private Emp mappingEmp(ResultSet rs) throws SQLException { Emp emp = new Emp(); emp.setEmpno(rs.getLong("empno")); emp.setEname(rs.getString("ename")); emp.setJob(rs.getString("job")); emp.setHiredate(rs.getDate("hiredate")); emp.setSal(rs.getFloat("sal")); emp.setComm(rs.getFloat("comm")); return emp; } }