where b.row_num between 1 and 10

结果发现由于该语句会先生成rownum 后执行order by 子句,因而排序结果根本不对,后来在GOOGLE上搜到一篇文章,原来多套一层select 就能很好的解决该问题,特此记录,语句如下:

select * from

(select a.*,rownum row_num from

(select * from mytable t order by t.id desc) a

) b where b.row_num between 1 and 10 

2.分页排序

主要思想：采用PageUtil类和用基于rownum的排序分页技术得到每页的内容。

（1）使用上一文章的pageutil类

（2）在查询类里得到结果

public List showList(int start,int end)
{
List<Oplist> listShow = new ArrayList<Oplist>();
DB_OPER db=new DB_OPER();
String s=select * from(select a.*,rownum row_num from(select * from mytable t order by t.id desc) a) b where b.row_num between 1 and 10”;

 Result result=db.executeQuery(s);

......//将result转为list

return listShow;

}

//得到总行数 

public int AllCount()
{
DB_OPER db=new DB_OPER();
Result result=db.executeQuery("select count(*) as count from a");
Map row = result.getRows()[0];
int size=Integer.parseInt(row.get("count").toString());
return size;
}

（3）在jsp页面查询

<%listOper db=new listOper();
int size=0;
size=db.AllCount();//得到总数
//System.out.println(size);
String pageStr = request.getParameter("page");
int currentPage = 1;
if (pageStr != null)
currentPage = Integer.parseInt(pageStr);
PageUtil pUtil = new PageUtil(15, size, currentPage);
currentPage = pUtil.getCurrentPage();
System.out.println("start:"+pUtil.getFromIndex());
System.out.println("end:"+pUtil.getToIndex());
List result=db.showList(pUtil.getFromIndex()+1, pUtil.getToIndex());

%>

（3）在jsp页面显示结果

<%

for (int i = 0; i <result.size() ; i++) {
Oplist model = (Oplist) result.get(i);
out.print("<TR class=‘alter‘><TD>"+(i+1+pUtil.getFromIndex())+"</TD>");
out.print("<TD>"+model.getLcbh()+"</>");
out.print("<TD>"+model.getFwsx()+"</TD>");

%>

jsp+oracle 排序分页+Pageutil类

标签：