06 分页

PageInfo

package com.aistart.tech.common;

import java.io.Serializable;
import java.util.List;

public class PageInfo<T> implements Serializable {
    private Integer pageNum;//当前页

    private Integer pageSize;//每页显示条数

    private Integer pageTotal;//总的页数

    private Integer rows;//总的条数

    private List<T> dataList;

    public Integer getPageNum() {
        return pageNum;
    }

    public void setPageNum(Integer pageNum) {
        this.pageNum = pageNum;
    }

    public Integer getPageSize() {
        return pageSize;
    }

    public void setPageSize(Integer pageSize) {
        this.pageSize = pageSize;
    }

    public Integer getPageTotal() {
        return pageTotal;
    }

    public void setPageTotal(Integer pageTotal) {
        this.pageTotal = pageTotal;
    }

    public Integer getRows() {
        return rows;
    }

    public void setRows(Integer rows) {
        this.rows = rows;
    }

    public List<T> getDataList() {
        return dataList;
    }

    public void setDataList(List<T> dataList) {
        this.dataList = dataList;
    }

    public PageInfo(Integer pageNum, Integer pageSize, Integer pageTotal, Integer rows, List<T> dataList) {
        this.pageNum = pageNum;
        this.pageSize = pageSize;
        this.pageTotal = pageTotal;
        this.rows = rows;
        this.dataList = dataList;
    }

    public PageInfo() {
    }

    @Override
    public String toString() {
        final StringBuilder sb = new StringBuilder("PageInfo{");
        sb.append("pageNum=").append(pageNum);
        sb.append(", pageSize=").append(pageSize);
        sb.append(", pageTotal=").append(pageTotal);
        sb.append(", rows=").append(rows);
        sb.append(", dataList=").append(dataList);
        sb.append('}');
        return sb.toString();
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80

创建一个List<String>集合
创建一个连接
根据连接创建一个操作对象，执行sql，sql语句有两个占位符，分别代表之前页所有数量，每页数量
操作对象返回结果集
将数据存到list中
关闭

public List<String> getEmpName(int pageSize, int pageNum) throws Exception {
        List<String> list = new ArrayList<>();

        Connection connection = JDBCUtil.getConnection();

        PreparedStatement preparedStatement = connection.prepareStatement("select first_name from employees limit ?,?;");

        preparedStatement.setInt(1,pageSize*(pageNum-1));
        preparedStatement.setInt(2,pageSize);

        ResultSet resultSet = preparedStatement.executeQuery();

        while (resultSet.next()){
            list.add(resultSet.getString(1));
        }

        JDBCUtil.closeAll(connection,preparedStatement,resultSet);

        return list;


    }




@Test
    public void empLimitTest() throws Exception {
         List<String> empName = iEmpDao.getEmpName(3, 2);

         PageInfo<String> stringPageInfo = new PageInfo<>();

         stringPageInfo.setDataList(empName);
         stringPageInfo.setPageNum(2);
         stringPageInfo.setPageSize(3);
         stringPageInfo.setRows(107);
         stringPageInfo.setPageTotal(107/3);

         for (String s : empName) {
             System.out.print(s+" ");
         }

         System.out.println(stringPageInfo);
     }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

创建PageInfo对象
PageInfo设置每页数量和第几页
创建连接
根据连接创建操作对象，执行sql
返回结果集

public PageInfo<String> findNameAllByPage(int pageSize, int pageNum) {

        PageInfo<String> stringPageInfo = new PageInfo<>();

        stringPageInfo.setPageSize(pageSize);
        stringPageInfo.setPageNum(pageNum);
        List<String> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtil.getConnection();
            preparedStatement = connection.prepareStatement("select count(*) from employees;");
            ResultSet resultSet = preparedStatement.executeQuery();

            if(resultSet.next()){
                //获取总条目数
                int rows = resultSet.getInt(1);

                //赋值共有多少行
                stringPageInfo.setRows(rows);

                //赋值共有多少页,取整
                stringPageInfo.setPageTotal(rows/pageSize);

            }

            /*
            * 跟上面一样获取一个list<string>
            * */
            stringPageInfo.setDataList(getEmpName(pageSize,pageNum));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }


        return stringPageInfo;
    }


@Test
    public void limitTest(){
         PageInfo<String> nameAllByPage = iEmpDao.findNameAllByPage(4, 5);

         System.out.println(nameAllByPage);
     }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48