Java 手写一个SQL分页

Java手写一个类似PageHelper的分页SQL

目前分页插件众所周知的莫过于和mybatis完美融合的PageHelper了,简单两行代码就实现了sql分页,配合PageInfo类,将数据总数量,页数页码以及分页结果集等前端需要的信息都配齐了;
但是!!!
一个项目不能一直用到mybatis的,总有用到JDBC的时候,PageHelper不对手写的JDBC代码分页,那这个时候只好动动手自己做一个类似的了。

测试环境

  1. Spring Boot:1.5.9
  2. PageInfo类
  3. JDBC工具类(传送门

开始实践

1. 创建SpringBoot项目

如何创建一个SpringBoot项目这里就不做演示,IDEA中可以直接创建,只要记得在Pom文件中引入依赖

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

2. 创建PageInfo实体类

想一下需要什么属性,后台跟前端交互分页数据,前端用分页插件需要数据的结果集(List),需要总记录数(totalNum),需要当前页数(currentPage),需要每页显示多少数据(pageSize),需要总页数(totalPages),上一页(previousPage),下一页(nextPage)

好,知道了之后定一个类,取名PageInfo,将以上说的属性定义好,注意几个地方:
1. List、totalNum、currentPage、pageSize需要GetSet方法
2. totalPages、previousPage、nextPage不需要Set方法,只需要Get方法,因为这三个属性的值是通过计算出来的

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

public class PageInfo<T> implements Serializable{
    private List<T> list;    //保存页面数据
    private int totalNum;    //查询到的总记录数
    private int currentPage;       //用户当前看的页数
    private int pageSize;        //每页多少条显示数据
    private int totalPages;        //总页数
    private int previousPage;    //上一页
    private int nextPage;        //下一页

    public static PageInfo startPage(int currentPage, int pageSize){
        return new PageInfo(currentPage, pageSize);
    }

    public PageInfo(int currentPage, int pageSize) {
        this.currentPage = currentPage;
        this.pageSize = pageSize;
    }

    public void setList(List list) {
        this.list = list;
    }

    public void setTotalNum(int totalNum) {
        this.totalNum = totalNum;
    }

    public void setCurrentPage(int currentPage) {
        this.currentPage = currentPage;
    }

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

    public List getList() {
        return list;
    }

    public int getTotalNum() {
        return totalNum;
    }

    public int getCurrentPage() {
        return currentPage;
    }

    public int getPageSize() {
        return pageSize;
    }

    public int getTotalPages() {
        if (this.totalNum % this.pageSize == 0) {
            this.totalPages = this.totalNum / this.pageSize;
        } else {
            this.totalPages = this.totalNum / this.pageSize + 1;
        }
        return this.totalPages;
    }

    public int getPreviousPage() {
        if (currentPage == 1) {
            previousPage = 1;
        } else {
            previousPage = currentPage - 1;
        }
        return previousPage;
    }

    public int getNextPage() {
        if (currentPage == totalPages) {
            nextPage = totalPages;
        } else {
            nextPage = currentPage + 1;
        }
        return nextPage;
    }

    @Override
    public String toString() {
        return "PageInfo{" +
                "list=" + list +
                ", totalNum=" + totalNum +
                ", currentPage=" + currentPage +
                ", pageSize=" + pageSize +
                ", totalPages=" + totalPages +
                ", previousPage=" + previousPage +
                ", nextPage=" + nextPage +
                '}';
    }
}

3. 写SQL语句

要准备一个JDBC工具类,博主自己写了一个自己用的,不嫌弃可以直接拿,在博客中有一篇文章是有的,这里我就用自己工具类里的一个方法getTableComment做个例子

理一下思路,要拿到page页数和size一页数据数量,肯定是传进来的,那要创建刚写好的PageInfo类,赋值currentPagepageSize,在SQL中要写上LIMIT ?,?,利用JDBC中PreparedStatement接口类的setInt方法,将参数放进分页条件中,成功分页后将数据取出装入PageInfo的List中,再写一个count(*)SQL,不带分页的查出总记录数,装入totalNum里,这个时候PageInfo类中前面四个属性的值都有了,后面的三个属性会自行的去运算,再将PageInfo这个类返回给前端

public static PageInfo getTableComment(DbConfig dbConfig, Integer page, Integer size) {
        //连接数据库
        Connection connection = getConnection(dbConfig);
        List<Map<String, Object>> tableComment = new ArrayList<>();
        //赋值currentPage和pageSize
        PageInfo pageInfo = PageInfo.startPage(page, size);
        StringBuilder builder = new StringBuilder();
        builder.append("SELECT ")
                .append("a.data_type jdbcType,")
                .append("b.TABLE_NAME, b.TABLE_COMMENT")
                .append(" FROM information_schema.COLUMNS a ")
                .append("LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS p ON a.table_schema = p.table_schema ")
                .append("LEFT JOIN INFORMATION_SCHEMA.TABLES b ON a.table_schema = b.TABLE_SCHEMA ")
                .append("AND b.TABLE_NAME = a.TABLE_NAME ")
                .append("AND a.table_name = p.TABLE_NAME AND a.COLUMN_NAME = p.COLUMN_NAME ")
                .append("AND p.constraint_name = 'PRIMARY' ")
                .append("WHERE b.table_schema = ").append("'" + dbConfig.getDataBase() + "'")
                .append(" AND p.COLUMN_NAME IS NULL = 'true'").append(" ORDER BY b.TABLE_NAME ")
                .append("LIMIT ?,?");
        try {
            PreparedStatement statement = connection.prepareStatement(builder.toString());
            statement.setQueryTimeout(60000);
            //通过分页公式计算下一页在哪一行开始
            //如1,10,1-1 = 0,那第一条开始显示下面十条,第一页
            //如2,10,(2-1)乘以10 = 10,那就是第十条开始显示下面十条,第二页
            statement.setInt(1, (page -1) * size);
            statement.setInt(2, size);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                String jdbcType = resultSet.getString("jdbcType").toUpperCase();
                if (jdbcType.equals("VARCHAR")){
                    jdbcType = "String";
                }else {
                    jdbcType = "Integer";
                }
                Map<String, Object> map = new HashMap<>(16);
                map.put("tableName", resultSet.getString("TABLE_NAME"));
                map.put("comment", resultSet.getString("TABLE_COMMENT"));
                map.put("jdbcType", jdbcType);
                tableComment.add(map);
            }
            pageInfo.setList(tableComment);

            StringBuilder count = new StringBuilder();
            count.append("SELECT ")
                    .append("COUNT(*)")
                    .append(" FROM information_schema.COLUMNS a ")
                    .append("LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS p ON a.table_schema = p.table_schema ")
                    .append("LEFT JOIN INFORMATION_SCHEMA.TABLES b ON a.table_schema = b.TABLE_SCHEMA ")
                    .append("AND b.TABLE_NAME = a.TABLE_NAME ")
                    .append("AND a.table_name = p.TABLE_NAME AND a.COLUMN_NAME = p.COLUMN_NAME ")
                    .append("AND p.constraint_name = 'PRIMARY' ")
                    .append("WHERE b.table_schema = ").append("'" + dbConfig.getDataBase() + "'")
                    .append(" AND P.COLUMN_NAME IS NULL = 'true'");
            statement = connection.prepareStatement(count.toString());
            resultSet = statement.executeQuery();
            if (resultSet.next()){
                pageInfo.setTotalNum(resultSet.getInt(1));
            }
            logger.info("查询成功" + tableComment);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                closeConnection(connection);
                connection = null;
            }
        }
        return pageInfo;
    }

4. 写一个接口做测试

新建一个Controller,写一个接口调用JDBC方法,将PageInfo类返回前端

    @GetMapping("/tableComment")
    public PageInfo tableComment(DbConfig dbConfig, 
                               @RequestParam(defaultValue = "1",required = false) Integer page,
                               @RequestParam(defaultValue = "10",required = false) Integer size){
        PageInfo pageInfo = DataBaseUtil.getTableComment(dbConfig, page, size);
        return pageInfo;
    }

启动项目,调用一下这个接口,查看一下返回的数据对不对,这里我默认一页十条

这里写图片描述

好,数据返回的没问题,该有的数据都拿到了,手写一个SQL分页的大致思路就是这样。

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 书香水墨 设计师:CSDN官方博客 返回首页