数据库Oracle分页显示技术

package com.lovo.dao.impl; import java.io.ByteArrayOutputStream; import java.io.FileInputStream; import java.io.OutputStream; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import com.lovo.dao.InterEmp; import com.lovo.util.DBUtil; import com.lovo.util.XMLUtil; import com.lovo.vo.EmpVo; public class EmpDao implements InterEmp { public static final String SQL_SEARCH_ADVANCE = "searchAdvance"; public static final String SQL_SEARCH_ADVANCE_CUT_PAGE = "searchAdvanceCutPage"; public static final String SQL_COUNT_LINE = "countLine"; public static final String SQL_GET_ALL_JOB = "getAllJob"; public List<EmpVo> getAllJob() throws Exception { DBUtil db = new DBUtil(); Connection conn = db.getConnection(); String sql = XMLUtil.getSQL(SQL_GET_ALL_JOB); Statement sta = conn.createStatement(); ResultSet rs = sta.executeQuery(sql); List<EmpVo> list = new ArrayList<EmpVo>(); while (rs.next()) { EmpVo emp = new EmpVo(); emp.setJob(rs.getString("JOB")); list.add(emp); } db.close(rs, sta, conn); return list; } public int count(String sql) throws Exception { DBUtil db = new DBUtil(); Connection conn = db.getConnection(); Statement sta = conn.createStatement(); ResultSet rs = sta.executeQuery(sql); rs.next(); int row = rs.getInt(1); db.close(rs, sta, conn); return row; } public OutputStream searchAdvance(String sql) throws Exception { DBUtil db = new DBUtil(); Connection conn = db.getConnection(); Statement sta = conn.createStatement(); ResultSet rs = sta.executeQuery(sql); OutputStream out = writeToArray(rs); // List<EmpVo> list = new ArrayList<EmpVo>(); // while (rs.next()) { // EmpVo vo = new EmpVo(); // vo.setDeptno(rs.getInt("deptno")); // vo.setEname(rs.getString("ename")); // vo.setJob(rs.getString("job")); // vo.setMgr(rs.getInt("mgr")); // vo.setComm(rs.getFloat("comm")); // vo.setDname(rs.getString("dname")); // vo.setEmpno(rs.getInt("empno")); // vo.setHiredate(rs.getDate("hiredate")); // vo.setLoc(rs.getString("loc")); // vo.setSal(rs.getFloat("sal")); // list.add(vo); // } db.close(rs, sta, conn); return out; } public OutputStream writeToArray(ResultSet rs) throws Exception { ByteArrayOutputStream out = new ByteArrayOutputStream(); FileInputStream in = new FileInputStream("E:/lovo/emp_search_constitute/WebRoot/WEB-INF/template.xls"); // 工作表(excel文件本身) HSSFWorkbook wb = new HSSFWorkbook(in); // 建立电子表 sheet HSSFSheet sheet = wb.getSheet("Sheet1"); // 在第一行填充数据 ResultSetMetaData msmd = rs.getMetaData(); int count = msmd.getColumnCount(); HSSFRow titleRow = sheet.createRow(1); for(int i = 0;i<count;i++){ titleRow.createCell((short)i).setCellValue(msmd.getColumnName(i+1)); } int rowNum = 2; while(rs.next()){ HSSFRow row = sheet.createRow(rowNum); row.createCell((short)0).setCellValue(rowNum-1); for(int i = 0;i<count;i++){ System.out.println(row); Object value = rs.getObject(i+1); String valueStr = ""; if(value != null){ valueStr = value.toString(); } System.out.println(value); row.createCell((short)i).setCellValue(valueStr); } rowNum++; } wb.write(out); rs.close(); return out; } public List<EmpVo> searchAdvanceCutPage(String sql, int uprow, int downrow) throws Exception { DBUtil db = new DBUtil(); Connection conn = db.getConnection(); PreparedStatement pre = conn.prepareStatement(sql); pre.setInt(1, uprow); pre.setInt(2, downrow); ResultSet rs = pre.executeQuery(); List<EmpVo> list = new ArrayList<EmpVo>(); while (rs.next()) { EmpVo vo = new EmpVo(); vo.setDeptno(rs.getInt("deptno")); vo.setEname(rs.getString("ename")); vo.setJob(rs.getString("job")); vo.setMgr(rs.getInt("mgr")); vo.setComm(rs.getFloat("comm")); vo.setDname(rs.getString("dname")); vo.setEmpno(rs.getInt("empno")); vo.setHiredate(rs.getDate("hiredate")); vo.setLoc(rs.getString("loc")); vo.setSal(rs.getFloat("sal")); list.add(vo); } db.close(rs, pre, conn); return list; } }