session,sql分页

package com.model.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import com.model.vo.JobsVO; import com.model.vo.PageVO; public class JobsDAO { private Statement st; private PreparedStatement pst; private ResultSet rs; public List<JobsVO> selectJobs(Connection conn) throws SQLException{ List<JobsVO> list = new ArrayList<JobsVO>(); String selectSQL = "select * from jobs"; try { st = conn.createStatement(); rs = st.executeQuery(selectSQL); while(rs.next()){ JobsVO jobsVO = new JobsVO(); jobsVO.setJobId(rs.getInt("job_id")); jobsVO.setJobDesc(rs.getString("job_desc")); jobsVO.setMinLvl(rs.getInt("min_lvl")); jobsVO.setMaxLvl(rs.getInt("max_lvl")); list.add(jobsVO); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ this.free(); } return list; } public List selectJobsForPage(Connection conn, PageVO pageVO) throws SQLException{ List<JobsVO> list = new ArrayList<JobsVO>(); int firstNumber = 0; int secondNumber = 0; if(pageVO.getCurrentPage() >= pageVO.getMaxPage()){ firstNumber = pageVO.getMaxCount() % pageVO.getPerPage(); }else{ firstNumber = pageVO.getPerPage(); } secondNumber = pageVO.getPerPage() * pageVO.getCurrentPage(); String selectSQL = "select * from (select top " + firstNumber + " * from (select top " + secondNumber + " * from jobs order by job_id) t1 order by t1.job_id desc) t2 order by t2.job_id"; try { st = conn.createStatement(); rs = st.executeQuery(selectSQL); while(rs.next()){ JobsVO jobsVO = new JobsVO(); jobsVO.setJobId(rs.getInt("job_id")); jobsVO.setJobDesc(rs.getString("job_desc")); jobsVO.setMinLvl(rs.getInt("min_lvl")); jobsVO.setMaxLvl(rs.getInt("max_lvl")); list.add(jobsVO); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ this.free(); } return list; } public int getMaxCount(Connection conn) throws SQLException{ int maxCount = 0; String selectSQL = "select count(*) as counts from jobs"; try { st = conn.createStatement(); rs = st.executeQuery(selectSQL); while(rs.next()){ maxCount = rs.getInt("counts"); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ this.free(); } return maxCount; } private void free(){ try { if(rs != null){ rs.close(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { if(pst != null){ pst.close(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { if(st != null){ st.close(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }