三重循环
select mbid00,mbmc00, llurl0,cfwz00,mbsltp,sycs00,lrsj00 from (
select mbid00,mbmc00, llurl0,cfwz00,mbsltp,sycs00,lrsj00,rownum as con from (
select * from JZ_MBXXB0 where 1=1 and lbid00=
'201'
order by lrsj00)
where rownum<=
10
) where con>=
1
在hibernate + Mysql 方式
/**
* 分页查询
*/
@SuppressWarnings("unchecked")
public List doSearch(PageDao pageDAO, final int pageSize,
final String name00) throws Exception {
List list = new ArrayList();
StringBuffer sql = new StringBuffer(
"from Book b where b.name00 LIKE '%");
sql.append(name00);
sql.append("%'");
final String typeSql = new String(sql);
// 设置每页显示条数
pageDAO.setPagesize(pageSize);
int pagesize = pageDAO.getPagesize();
// 获取总记录数
List count = (List)hibernateTemplate.execute(
new HibernateCallback(){
public Object doInHibernate(Session session)
throws HibernateException, SQLException{
Query q = session.createQuery(typeSql);
//q.setFirstResult(0);
//q.setMaxResults(2);
List ss =q.list();
return q.list(); //没查到数据也不会返回null, 只能用isEmpty
}
}
);
pageDAO.getRsCountForSQL(count.size());
// 获取总页数
pageDAO.GetPageCount();
// 获取当前页
final int currpage = pageDAO.GetCurrentPageForRequest();
// 设置工具条类型
String pool = pageDAO.pagetool(PageDao.Text);
//System.out.println(pageDAO.getParamUrl());
List<BookDTO> dt = new ArrayList<BookDTO>();
List rs = (List)hibernateTemplate.execute(
new HibernateCallback(){
public Object doInHibernate(Session session)
throws HibernateException, SQLException{
Query q = session.createQuery(typeSql);
q.setFirstResult((currpage - 1) * pageSize);
q.setMaxResults(pageSize);//currpage *
List ss =q.list();
return q.list(); //没查到数据也不会返回null, 只能用isEmpty
}
}
);
for (Iterator it = rs.iterator(); it.hasNext();) {
Object obj = it.next();
BookDTO dto = new BookDTO();
BeanUtils.copyProperties(obj,dto);
dt.add(dto);
}
//session.setAttribute("pagedto", pageDAO);
list.add(0,dt);
list.add(1,pool);
return list;
}
hibernate + Oracle 方式 效率较差
public List queryMbList(final PageDao pageDAO, final int pageSize,
String lbid) {
List list = new ArrayList();
StringBuffer sql = new StringBuffer();
StringBuffer hql = new StringBuffer();
sql = new StringBuffer(
"select * from JZ_MBXXB0 where 1=1 and lbid00='");
sql.append(lbid);
sql.append("'");
}
// 设置每页显示条数
pageDAO.setPagesize(pageSize);
@SuppressWarnings("unused")
int pagesize = pageDAO.getPagesize();
// 获取总记录数
List count = persistence.executeSQLQuery(sql.toString());
pageDAO.getRsCountForSQL(count.size());
// 获取总页数
pageDAO.GetPageCount();
// 获取当前页
int currpage = pageDAO.GetCurrentPageForRequest();
// 设置工具条类型
String pool = pageDAO.pagetool(PageDao.Text);
hql = new StringBuffer(
"select mbid00,mbmc00, llurl0,cfwz00,mbsltp,sycs00,lrsj00 from ( ");
hql.append("select mbid00,mbmc00, llurl0,cfwz00,mbsltp,sycs00,lrsj00,rownum as con from (");
if(lbid.equals("200")){
hql.append("select * from JZ_MBXXB0 where 1=1");
}else{
hql.append("select * from JZ_MBXXB0 where 1=1 and lbid00='");
hql.append(lbid);
hql.append("'");
}
hql.append("order by sycs00 desc,lrsj00 desc)"); //按条件排序
hql.append(" where rownum<=");
hql.append(currpage * pageSize); //显示到第几条
hql.append(") where con>="); //从第几行开始显示
hql.append((currpage - 1) * pageSize + 1);
List<Mbxxb0DTO> dt = new ArrayList<Mbxxb0DTO>();
List rs = persistence.executeSQLQuery(hql.toString());
for (Iterator it = rs.iterator(); it.hasNext();) {
Object obj = it.next();
Mbxxb0DTO dto = new Mbxxb0DTO();
BeanHelper.copyProperties(obj,dto);
dt.add(dto);
}
list.add(0,dt);
list.add(1,pool);
return list;
}