之前学习oracle时,写的一个分页语句,是对emp表做的分页,为了加深记忆以及方便以后的查询使用,现将语句记录如下:
public List<Emp> queryEmpAllOnRownum(int pageNum) {
String sql = "select em.* from " +
"(select rownum rn,e.* from (select * from emp order by deptno) e "+
"where rownum <= ?) em where em.rn>= ?";
//获取记录的总条数
int countRow = new EmpServiceImpl().queryCountEmp();//此处需要重新创建对象调用,否则会出现无效的列类型
int pages=(int)(Math.ceil(countRow*1.0/pageSize));//总页数
//如果当前页小于第一页,就为第一页
if(pageNum<1){
pageNum =1;
System.out.println("页数不能小于第一页");
}
//如果当前页大于最后一页,就为最后一页
if(pageNum>pages){
pageNum =pages;
System.out.println("页数不能大于最大页,最大页为:"+pages);
}
int start = (pageNum-1)*pageSize+1;
int end = pageNum!=pages?(start+pageSize-1):countRow;
String[] params ={end+"",start+""};
List<Emp> list = new ArrayList<Emp>();
try {
this.open(sql);
this.exeSelect(params);
while(rs.next()){
Integer empno = rs.getInt("empno");
String ename = rs.getString("ename");
String job = rs.getString("job");
Integer mgr = rs.getInt("mgr");
Date hiredate = rs.getDate("hiredate");
Double sal = rs.getDouble("sal");
Double comm = rs.getDouble("comm");
Integer deptno = rs.getInt("deptno");
Emp emp = new Emp(empno,ename,job,mgr,hiredate,sal,comm,deptno);
list.add(emp);
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
this.close();
}
return list;
}
//获取记录的总条数
public int queryCountEmp() {
String sql = "select count(*) from emp";
int i=0;
try {
this.open(sql);
rs = this.exeSelect(null);
while(rs.next()){
i = rs.getInt(1);
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
this.close();
}
return i;
}
比较简单的一个分页语句,有别的需求也可以通过这个语法变形去添加!