Java 手写一个SQL分页

我会带着你远行 2022-05-18 01:37 260阅读 0赞

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

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

## 测试环境 ##

1.  Spring Boot：1.5.9
2.  PageInfo类
3.  JDBC工具类（[传送门][Link 1]）

## 开始实践 ##

### 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需要`Get`和`Set`方法  
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`类，赋值`currentPage`和`pageSize`，在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;
        }

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

![这里写图片描述][70]

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

[Link 1]: https://blog.csdn.net/m0_37701381/article/details/80154846
[70]: /images/20220518/085b20a1953949809004f240ceeb8fc6.png