oracle的分页语句怎么转换成mysql中的分页语句

[coyking]

/**分页查询User
* pageSize:表示一页显示的数据个数；page：表示第几页
*/
public List<User> findUserByPage(int pageSize, int page) {
String sql="select dbs.rn,dbs.* from (select rownum as rn,users.* from users) dbs where rn between "+(pageSize*(page-1)+1)+" and "+(pageSize*page);
List params=null;
return daoImpl.findUserByPage(sql, params);
}
public int getUserCount() {
String sql="select count(*) from users";
List params=null;
return daoImpl.getCount(sql, params);
}
/**
* 分页查询Bill
*
*/
public List<Bill> findBillByPage(int pageSize, int page) {
String sql="select dbs.rn,dbs.* from (select rownum as rn,bill.* from bill) dbs where rn between "+(pageSize*(page-1)+1)+" and "+(pageSize*page);
List params=null;
return daoImpl.findBillByPage(sql, params);
}
public int getBillCount() {
String sql="select count(*) from bill";
List params=null;
return daoImpl.getCount(sql, params);
}
//根据条件获得满足条件的bill的数目
public int getBillCount(String product_name,String ispay){
String sql=null;
List params=new ArrayList();
if(product_name==null && ispay==null){
sql="select count(*) from Bill";
}else if(product_name==null && ispay!=null){
sql="select count(*) from Bill where ispay=?";
params.add(ispay);
}else if(product_name!=null && ispay==null){
sql="select count(*) from Bill where product_name like '%"+product_name+"%'";
}else{
sql="select count(*) from Bill where product_name like '%"+product_name+"%' and ispay=?";
params.add(ispay);
}
return daoImpl.getCount(sql, params);
}
//根据条件进行分页
public List<Bill> findBillByPage(int pageSize, int page,String product_name,String ispay) {
String sql=null;
List params=new ArrayList();
if(product_name==null && ispay==null){
sql="select dbs.rn,dbs.* from (select rownum as rn,bill.* from bill) dbs where rn between "+(pageSize*(page-1)+1)+" and "+(pageSize*page);
}else if(product_name==null && ispay!=null){
sql="select dbs.rn,dbs.* from (select rownum as rn,bill.* from bill where ispay=?) dbs where rn between "+(pageSize*(page-1)+1)+" and "+(pageSize*page) ;
params.add(ispay);
}else if(product_name!=null && ispay==null){
sql="select dbs.rn,dbs.* from (select rownum as rn,bill.* from bill where product_name like '%"+product_name+"%') dbs where rn between "+(pageSize*(page-1)+1)+" and "+(pageSize*page);
}else{
sql="select dbs.rn,dbs.* from (select rownum as rn,bill.* from bill where product_name like '%"+product_name+"%' and ispay=?) dbs where rn between "+(pageSize*(page-1)+1)+" and "+(pageSize*page);
params.add(ispay);
}
return daoImpl.findBillByPage(sql, params);
}
/**
* 分页查询Supplier
*/
public List<Supplier> findSupplierByPage(int pageSize, int page) {
String sql="select dbs.rn,dbs.* from (select rownum as rn,supplier.* from supplier) dbs where rn between "+(pageSize*(page-1)+1)+" and "+(pageSize*page);
List params=null;
return daoImpl.findSupplierByPage(sql, params);
}
public int getSupplierCount() {
String sql="select count(*) from Supplier";
List params=null;
return daoImpl.getCount(sql, params);
}
//根据条件查询supplier的个数
public int getSupplierCount(String supplier_name,String supplier_des){
String sql=null;
List params=new ArrayList();
if(supplier_name==null && supplier_des==null){
sql="select count(*) from supplier";
}else if(supplier_name==null && supplier_des!=null){
sql="select count(*) from supplier where supplier_des like '%"+supplier_des+"%'";
}else if(supplier_name!=null && supplier_des==null){
sql="select count(*) from supplier where supplier_name like '%"+supplier_name+"%'";
}else{
sql="select count(*) from supplier where supplier_name like '%"+supplier_name+"%' and supplier_des like '%"+supplier_des+"%'";
}
return daoImpl.getCount(sql, params);
}
//根据条件对supplier的查询结果进行分页
//根据条件进行分页
public List<Supplier> findSupplierByPage(int pageSize, int page,String supplier_name,String supplier_des) {
String sql=null;
List params=new ArrayList();
if(supplier_name==null && supplier_des==null){
sql="select dbs.rn,dbs.* from (select rownum as rn,supplier.* from supplier) dbs where rn between "+(pageSize*(page-1)+1)+" and "+(pageSize*page);
}else if(supplier_name==null && supplier_des!=null){
sql="select dbs.rn,dbs.* from (select rownum as rn,supplier.* from supplier where supplier_des like '%"+supplier_des+"%') dbs where rn between "+(pageSize*(page-1)+1)+" and "+(pageSize*page) ;
}else if(supplier_name!=null && supplier_des==null){
sql="select dbs.rn,dbs.* from (select rownum as rn,supplier.* from supplier where supplier_name like '%"+supplier_name+"%') dbs where rn between "+(pageSize*(page-1)+1)+" and "+(pageSize*page);
}else{
sql="select dbs.rn,dbs.* from (select rownum as rn,supplier.* from supplier where supplier_name like '%"+supplier_name+"%' and supplier_des like '%"+supplier_des+"%') dbs where rn between "+(pageSize*(page-1)+1)+" and "+(pageSize*page);
}
return daoImpl.findSupplierByPage(sql, params);
}
//根据条件查询User的个数
public int getUserCount(String user_name){
String sql=null;
List params=new ArrayList();
if(user_name==null){
sql="select count(*) from users";
}else if(user_name!=null){
sql="select count(*) from users where name like '%"+user_name+"%'";
}
return daoImpl.getCount(sql, params);
}
//根据条件对User的查询结果进行分页
//根据条件进行分页
public List<User> findUserByPage(int pageSize, int page,String user_name) {
String sql=null;
List params=new ArrayList();
if(user_name==null){
sql="select dbs.rn,dbs.* from (select rownum as rn,users.* from users) dbs where rn between "+(pageSize*(page-1)+1)+" and "+(pageSize*page);
}else if(user_name!=null){
sql="select dbs.rn,dbs.* from (select rownum as rn,users.* from users where name like '%"+user_name+"%') dbs where rn between "+(pageSize*(page-1)+1)+" and "+(pageSize*page);
}
return daoImpl.findUserByPage(sql, params);
}
//根据bill_id查找账单
public Bill findBillById(String billId) {
String sql="select * from bill where bill_id=?";
List params=new ArrayList();
params.add(billId);
List<Bill> bills=daoImpl.findAllBill(sql,params);
if(bills.size()>0){
return bills.get(0);
}else{
return null;
}
}

//更新账单信息
public boolean updateBill(Bill bill) {
String sql="update bill set money=?,deal_company=?,product_num=?,product_name=?,product_des=?,ispay=?,supplier_name=? where bill_id=?";
List params=new ArrayList();
params.add(Integer.parseInt(bill.getMoney()));
params.add(bill.getDeal_company());
params.add(Integer.parseInt(bill.getProduct_num()));
params.add(bill.getProduct_name());
params.add(bill.getProduct_des());
params.add(bill.getIspay());
params.add(bill.getSupplier_name());
params.add(Integer.parseInt(bill.getBill_id()));
return daoImpl.update(sql, params);
}
//执行删除账单操作
public boolean deleteBill(String id) {
String sql="delete from bill where bill_id=?";
List params=new ArrayList();
params.add(Integer.parseInt(id));
return daoImpl.update(sql, params);
}

[MiceRice]

MySQL更简单，都不用这样去拼SQL，直接有关键字支持，limit，如：
select * from Member limit 10, 20;

[you_understand]

楼上正解。

[热烈的红颜]

Oralce :你可以通过rownum来分页
Mysql: 你可以通过limit来分页
MSSQL:通过rownum或者top分页
Sybase：不支持分页

[cjoy4856]

LZ使用的是Hibrnate对不, 这个问题很简单：
你的问题就是为了能使你的分页不同数据库中切换，一下是参考过程
1.获取Hibernate的方言（这个是没有问题的）
2.通过hibernate的方言反射出这个对象 这个对象实现了Dialect接口 直接为Dialect dialect=XXX(实例化得对象)
3.通过该接口获取对应的分页查询语句 getLimitString该方法是获取对应的查询语句。具体的可以看看Hibernate的源码。
4.要保证的你的查询语句是一个T-SQL
如果以上都保证了还有问题，给我发邮件。我提供对应的代码给你。

[花谢尊前不敢香]

package com.tools;

import java.util.ArrayList;
import java.util.List;

public class MyPagination {
	public List<Object> list=null;
	private int recordCount=0;
	private int pagesize=0;
	private int maxPage=0;

	public List getInitPage(List list,int Page,int pagesize){
		List<Object> newList=new ArrayList<Object>();
		this.list=list;
		recordCount=list.size();
		this.pagesize=pagesize;
		this.maxPage=getMaxPage();
		try{
		for(int i=(Page-1)*pagesize;i<=Page*pagesize-1;i++){
			try{
				if(i>=recordCount){break;}
			}catch(Exception e){}
			newList.add((Object)list.get(i));
		}
		}catch(Exception e){
			e.printStackTrace();
		}
		return newList;
	}

	public List<Object> getAppointPage(int Page){
		List<Object> newList=new ArrayList<Object>();
		try{
			for(int i=(Page-1)*pagesize;i<=Page*pagesize-1;i++){
				try{
					if(i>=recordCount){break;}
				}catch(Exception e){}
				newList.add((Object)list.get(i));
			}
			}catch(Exception e){
				e.printStackTrace();
			}
			return newList;
	}

	public int getMaxPage(){
		int maxPage=(recordCount%pagesize==0)?(recordCount/pagesize):(recordCount/pagesize+1);
		return maxPage;
	}

	public int getRecordSize(){
		return recordCount;
	}
	

	public int getPage(String str){		
		if(str==null){
			str="0";
		}
		int Page=Integer.parseInt(str);
		if(Page<1){
			Page=1;
		}else{
			if(((Page-1)*pagesize+1)>recordCount){
				Page=maxPage;
			}
		}
		return Page;
	}

	


	public String printCtrl(int Page){
		String strHtml="<table width='370'  border='0' cellspacing='0' cellpadding='0'><tr> <td height='24' align='right'>当前页数：["+Page+"/"+maxPage+"]  ";
		try{
		if(Page>1){
			strHtml=strHtml+"<a href='?&Page=1'>第一页</a>　";
			strHtml=strHtml+"  <a href='?Page="+(Page-1)+"'>上一页</a>";
		}
		if(Page<maxPage){
			strHtml=strHtml+"  <a href='?Page="+(Page+1)+"'>下一页</a>  　<a href='?Page="+maxPage+"'>最后一页 </a>";
		}
		strHtml=strHtml+"</td> </tr>	</table>";
		}catch(Exception e){
			e.printStackTrace();
			
		}
		return strHtml;
	}
	
	
	
}

Mysql的分页代码