常用分页方法总结

记录工作中的一些分页方法

1.内存分页

内存分页即在程序运行中对数据进行分页，一般是先从数据库查出来再分页。
缺点是内存消耗大，优点是方法简单
这里给出我自己常用的Java流操作分页和C#的LINQ分页

Java流操作分页

public class Test1 {
	
	public static List<Student> InitData(){
		
		List list = new ArrayList<Student>();
		list.add(new Student(1, "黄建雄", "南城"));
		list.add(new Student(2, "KKK", "qw"));
		list.add(new Student(3, "李承骐", "江苏"));
		list.add(new Student(1, "QQQ", "广东"));
		
		return list;
	}
	
	
	public static void main(String[] args) {
		
		List<Student> list = InitData();
		int pageIndex = 2,pageSize = 3;
		
		List<Student> pageOne =  list.stream().skip(--pageIndex*pageSize).limit(pageSize).collect(Collectors.toList());
		
		pageOne.forEach(item->{System.out.println(item);});
	}
	
	public static class Student{
		private int Id;
		private String Name;
		private String Address;
		@Override
		public String toString() {
			return "Student [Id=" + Id + ", Name=" + Name + ", Address=" + Address + "]";
		}
		public Student(int id, String name, String address) {
			super();
			Id = id;
			Name = name;
			Address = address;
		}
		public int getId() {
			return Id;
		}
		public void setId(int id) {
			Id = id;
		}
		public String getName() {
			return Name;
		}
		public void setName(String name) {
			Name = name;
		}
		public String getAddress() {
			return Address;
		}
		public void setAddress(String address) {
			Address = address;
		}
	}

}

简单来说可以总结为

 List newlist =  list.stream().skip(--pageIndex*pageSize).limit(pageSize).collect(Collectors.toList());

这里的pageIndex为页号，pageSize为一页的条数，当然你必须提供总的记录数Count,不然无法知道有几页。

LINQ则更加简单了

	public static void main(string[] args){
			List<SimpleModel> list = new List<SimpleModel>()
            {
                new SimpleModel(){id = 1,name = "ss1",address = "q1"},
                new SimpleModel(){id = 2,name = "ss2",address = "q2"},
                new SimpleModel(){id = 3,name = "ss3",address = "q3"},
                new SimpleModel(){id = 4,name = "ss4",address = "q4"},
                new SimpleModel(){id = 5,name = "ss5",address = "q5"}
            };
            int PageIndex = 2, PageSize = 2;

            List<SimpleModel> afterPage = list.Skip(--PageIndex*PageSize).Take(PageSize).ToList();

            afterPage.ForEach(item => { Console.WriteLine(item); });


        }
        internal class SimpleModel
        {
            public int id { get; set; }

            public string name { get; set; }

            public string address { get; set; }

            public override string ToString()
            {
                return "[id:"+this.id+" name: "+this.name+" address: "+this.address+"]";
            }
        }

2.Sql分页

Sql分页是在sql语句中进行分页，一次只从数据库中查出若干条记录

优点：内存开销小，缺点：不同数据库分页sql各不相同

Oracle:

oracle可以使用自带的rownum

select * from (
    select t.*,rownum rn from XXX t where 1=1 and  rownum <= pageIndex*pageSize
) where rn >= （pageIndex-1）*pageSzie + 1

当然，也可以通过开窗函数来完成，效果也是一样的

MySql:

通过自带的limit关键字可以非常方便的分页

Select * from XXX [Where xxx] [order by xxx] limit （pageIndex-1）*pageSize,pageSize

SqlServer:

一般来说使用top语句，可以如此

select top pageSize *
from R_ASSET_DETAIL_T
where id not in
(
 --40是这么计算出来的：10*(5-1)
 select top (pageIndex-1)*pageSize id from R_ASSET_DETAIL_T order by ID
)
order by id

当然还有另一种方法，OFFSET 和FETCH,但是这种方法需要高版本的SqlServer
https://docs.microsoft.com/zh-cn/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-ver16