Oracle与mysql分页语句

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/u010222318/article/details/24377977

Oracle分页:


String sqlcount = "select count(*) from Dictinfo where type =?";
String sql = "SELECT * FROM(SELECT A.*, ROWNUM RN FROM (SELECT * FROM Dictinfo where type =? ORDER BY ID DESC) A WHERE ROWNUM <= ?)WHERE RN >= ?";

mysql分页:

select count(*) from table [查询条件] 
select * from table [查询条件] order by id limit ?,?  



展开阅读全文

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

04-28

/**分页查询Userrn * pageSize:表示一页显示的数据个数;page:表示第几页rn */rn public List findUserByPage(int pageSize, int page) rn 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);rn List params=null;rn return daoImpl.findUserByPage(sql, params);rn rn public int getUserCount() rn String sql="select count(*) from users";rn List params=null;rn return daoImpl.getCount(sql, params);rn rn /**rn * 分页查询Billrn * rn */rn public List findBillByPage(int pageSize, int page) rn 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);rn List params=null;rn return daoImpl.findBillByPage(sql, params);rn rn public int getBillCount() rn String sql="select count(*) from bill";rn List params=null;rn return daoImpl.getCount(sql, params);rn rn //根据条件获得满足条件的bill的数目rn public int getBillCount(String product_name,String ispay)rn String sql=null;rn List params=new ArrayList();rn if(product_name==null && ispay==null)rn sql="select count(*) from Bill";rn else if(product_name==null && ispay!=null)rn sql="select count(*) from Bill where ispay=?";rn params.add(ispay);rn else if(product_name!=null && ispay==null)rn sql="select count(*) from Bill where product_name like '%"+product_name+"%'";rn elsern sql="select count(*) from Bill where product_name like '%"+product_name+"%' and ispay=?";rn params.add(ispay);rn rn return daoImpl.getCount(sql, params);rn rn //根据条件进行分页rn public List findBillByPage(int pageSize, int page,String product_name,String ispay) rn String sql=null;rn List params=new ArrayList();rn if(product_name==null && ispay==null)rn sql="select dbs.rn,dbs.* from (select rownum as rn,bill.* from bill) dbs where rn between "+(pageSize*(page-1)+1)+" and "+(pageSize*page);rn else if(product_name==null && ispay!=null)rn 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) ;rn params.add(ispay);rn else if(product_name!=null && ispay==null)rn 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);rn elsern 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); rn params.add(ispay);rn rn return daoImpl.findBillByPage(sql, params);rn rn /**rn * 分页查询Supplierrn */rn public List findSupplierByPage(int pageSize, int page) rn 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);rn List params=null;rn return daoImpl.findSupplierByPage(sql, params);rn rn public int getSupplierCount() rn String sql="select count(*) from Supplier";rn List params=null;rn return daoImpl.getCount(sql, params);rn rn //根据条件查询supplier的个数rn public int getSupplierCount(String supplier_name,String supplier_des)rn String sql=null;rn List params=new ArrayList();rn if(supplier_name==null && supplier_des==null)rn sql="select count(*) from supplier";rn else if(supplier_name==null && supplier_des!=null)rn sql="select count(*) from supplier where supplier_des like '%"+supplier_des+"%'";rn else if(supplier_name!=null && supplier_des==null)rn sql="select count(*) from supplier where supplier_name like '%"+supplier_name+"%'";rn elsern sql="select count(*) from supplier where supplier_name like '%"+supplier_name+"%' and supplier_des like '%"+supplier_des+"%'";rn rn return daoImpl.getCount(sql, params);rn rn //根据条件对supplier的查询结果进行分页rn //根据条件进行分页rn public List findSupplierByPage(int pageSize, int page,String supplier_name,String supplier_des) rn String sql=null;rn List params=new ArrayList();rn if(supplier_name==null && supplier_des==null)rn sql="select dbs.rn,dbs.* from (select rownum as rn,supplier.* from supplier) dbs where rn between "+(pageSize*(page-1)+1)+" and "+(pageSize*page);rn else if(supplier_name==null && supplier_des!=null)rn 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) ;rn else if(supplier_name!=null && supplier_des==null)rn 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);rn elsern 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); rn rn return daoImpl.findSupplierByPage(sql, params);rn rn //根据条件查询User的个数rn public int getUserCount(String user_name)rn String sql=null;rn List params=new ArrayList();rn if(user_name==null)rn sql="select count(*) from users";rn else if(user_name!=null)rn sql="select count(*) from users where name like '%"+user_name+"%'";rn rn return daoImpl.getCount(sql, params);rn rn //根据条件对User的查询结果进行分页rn //根据条件进行分页rn public List findUserByPage(int pageSize, int page,String user_name) rn String sql=null;rn List params=new ArrayList();rn if(user_name==null)rn sql="select dbs.rn,dbs.* from (select rownum as rn,users.* from users) dbs where rn between "+(pageSize*(page-1)+1)+" and "+(pageSize*page);rn else if(user_name!=null)rn 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);rn rn return daoImpl.findUserByPage(sql, params);rn rn //根据bill_id查找账单rn public Bill findBillById(String billId) rn String sql="select * from bill where bill_id=?";rn List params=new ArrayList();rn params.add(billId);rn List bills=daoImpl.findAllBill(sql,params);rn if(bills.size()>0)rn return bills.get(0);rn elsern return null;rn rn rn rn //更新账单信息rn public boolean updateBill(Bill bill) rn String sql="update bill set money=?,deal_company=?,product_num=?,product_name=?,product_des=?,ispay=?,supplier_name=? where bill_id=?";rn List params=new ArrayList();rn params.add(Integer.parseInt(bill.getMoney()));rn params.add(bill.getDeal_company());rn params.add(Integer.parseInt(bill.getProduct_num()));rn params.add(bill.getProduct_name());rn params.add(bill.getProduct_des());rn params.add(bill.getIspay());rn params.add(bill.getSupplier_name());rn params.add(Integer.parseInt(bill.getBill_id()));rn return daoImpl.update(sql, params);rn rn //执行删除账单操作rn public boolean deleteBill(String id) rn String sql="delete from bill where bill_id=?";rn List params=new ArrayList();rn params.add(Integer.parseInt(id));rn return daoImpl.update(sql, params);rn rn rn rnrn 论坛

没有更多推荐了,返回首页