<span role="heading" aria-level="2">sql 分页

sql 分页

全栈程序员-站长 • 2022年7月2日上午8:16 • 未分类 • 阅读 30

大家好，又见面了，我是你们的朋友全栈君。

//查询所有
    public List<products>getAll(int pageNo,int count){
        System.out.println(pageNo+";"+count);
        System.out.println((pageNo-1)*count+1+";"+pageNo*count);
        List<products> list=null;
        String sql="select * from " + 
                "(select ROW_NUMBER()over(order by id) row,* from products where 0=0 ) t" + 
                "    where  row between "+((pageNo-1)*count+1)+" and "+pageNo*count;
        
        System.out.println(sql);
        try {
            list=bs.getObjectList("com.web.Entity.products", sql);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

//获取总页数
//获取默认总页数
public int getPages(int count) {
int n=0;
String sql="select count(*) from products where 0=0";
n=bs.getPages(sql, count);
return n;
}

//条件查询
        public List<products>getAll2(int pageNo,int count,QueryProduct qd){
            List<products> list=null;
            String sql="select * from " + 
                    "(select ROW_NUMBER()over(order by id) row,* from products where 0=0";
            
            String sql2=" ) t where  row between "+((pageNo-1)*count+1)+" and "+pageNo*count;
            
            if(qd.getPdName()!=null&&qd.getPdName()!="")
                sql+=" and name like'%"+qd.getPdName()+"%'";
            if(qd.getPdType()!=null&&qd.getPdType()!="")
                sql+=" and typeCode like'%"+qd.getPdType()+"%'";
            if(qd.isState()!=null&&qd.isState()!="")
                if(!(qd.isState().equals("2")))
                    sql+=" and pdState="+qd.isState();
            System.out.println(sql+sql2);
            try {
                list=bs.getObjectList("com.web.Entity.products", sql+sql2);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return list;
        }

//获取条件查询总页数
    public int getPages2(int count,QueryProduct qd) {
        int n=0;
        String sql="select count(*) from products where 1=1";
        if(qd.getPdName()!=null&&qd.getPdName()!="")
            sql+=" and name like'%"+qd.getPdName()+"%'";
        if(qd.getPdType()!=null&&qd.getPdType()!="")
            sql+=" and typeCode like'%"+qd.getPdType()+"%'";
        if(qd.isState()!=null&&qd.isState()!="") {
            System.out.println(qd.isState());
            System.out.println(qd.isState()=="2");
            System.out.println(qd.isState().equals("2"));
            if(!(qd.isState().equals("2")))
                sql+=" and pdState="+qd.isState();
        }
            
        n=bs.getPages(sql, count);
        return n;
    }