springboot sql不用limit语句实现分页查询，很方便！！！

1、涉及内容：

①springboot

②java分页查询

③bootstrap前端框架 表单

④FreeMarker的<#if><#else>、<#list>标签使用

1.1建表：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

 1.2、我的springboot目录：

 

1.3、maven依赖：

对于springboot框架 以及FreeMarker模板等

 

 <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <!--
        <version>2.0.2.RELEASE</version>
         -->
        <version>1.5.6.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.7</java.version>
    </properties>

<dependencies>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
        <exclusions>
            <exclusion>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-logging</artifactId>
            </exclusion>
        </exclusions>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
        <exclusions>
            <exclusion>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-logging</artifactId>
            </exclusion>
        </exclusions>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-legacy</artifactId>
        <version>1.1.0.RELEASE</version>
    </dependency>
    <!--
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-thymeleaf</artifactId>
    </dependency>
    -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-freemarker</artifactId>
    </dependency>

    <!-- Spring Boot log4j依赖 -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-log4j</artifactId>
        <version>1.3.8.RELEASE</version>
    </dependency>

    <dependency>
        <groupId>c3p0</groupId>
        <artifactId>c3p0</artifactId>
        <version>0.9.0</version>
    </dependency>

    <dependency>
        <groupId>commons-dbutils</groupId>
        <artifactId>commons-dbutils</artifactId>
        <version>1.7</version>
    </dependency>

    <dependency>
        <groupId>com.sinocontact</groupId>
        <artifactId>sinocontact-dbutils</artifactId>
        <version>0.0.1</version>
    </dependency>

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.21</version>
    </dependency>

    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>fastjson</artifactId>
        <version>1.2.15</version>
    </dependency>


    <dependency>
        <groupId>com.aliyun.oss</groupId>
        <artifactId>aliyun-sdk-oss</artifactId>
        <version>2.8.2</version>
    </dependency>


    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-lang3</artifactId>
        <version>3.5</version>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-redis</artifactId>
    </dependency>

    <dependency>
        <groupId>org.springframework.session</groupId>
        <artifactId>spring-session-data-redis</artifactId>
    </dependency>
    <!-- https://mvnrepository.com/artifact/javax.mail/mail -->
    <dependency>
        <groupId>javax.mail</groupId>
        <artifactId>mail</artifactId>
        <version>1.4.7</version>
    </dependency>


    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.17</version>
    </dependency>

    <!--barcode4j-->
    <dependency>
        <groupId>net.sf.barcode4j</groupId>
        <artifactId>barcode4j-light</artifactId>
        <version>2.0</version>
    </dependency>

    <dependency>
        <!-- jsoup HTML parser library @ https://jsoup.org/ -->
        <groupId>org.jsoup</groupId>
        <artifactId>jsoup</artifactId>
        <version>1.11.3</version>
    </dependency>

    <dependency>
        <groupId>com.sinocontact</groupId>
        <artifactId>aspectjweaver</artifactId>
        <version>1.9.1</version>
    </dependency>


    <dependency>
        <groupId>com.sinocontact</groupId>
        <artifactId>aspose-words</artifactId>
        <version>14.9.0</version>
    </dependency>


</dependencies>


    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

 2、实现效果：

3、具体实现：

 

3.1、User实体类：

package com.lemon.pojo;

public class User {

    private Integer id;
    private String username;
    private String password;

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
}

3.2、UserDao

springboot可以整合mybatis、hobernate等，用于数据库查询（熟练那个就用哪个）

package com.lemon.dao;

import com.lemon.pojo.User;
import com.lemon.utils.Page;
import com.lemon.utils.PageUtils;
import com.sinocontact.dbutils.DBControl;
import org.apache.log4j.Logger;
import org.omg.PortableInterceptor.INACTIVE;


import java.util.List;

public class UserDao {

    private static final Logger logger = Logger.getLogger(UserDao.class);

    //查询所有用户
    public Page queryAllUser(Integer pageNo){

        String sql = "select * from user";
        try {

            List<User> userList = DBControl.getMainDbOperator().queryBeanList(sql, User.class);

            Page page = PageUtils.doPage(userList, pageNo);

            return page;
        }catch (Exception e){
            logger.error("查询user错误：",e);
        }

        return null;
    }

}

 3.3、userService：

package com.lemon.service;

import com.lemon.dao.UserDao;
import com.lemon.pojo.User;
import com.lemon.utils.Page;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserService {
    private static final Logger logger = Logger.getLogger(UserService.class);

    private UserDao userDao = new UserDao();

    //查询所有用户
    public Page queryAllUser(String pageNo){

        Integer realPageNo = getRealPageNo(pageNo);

        Page page = userDao.queryAllUser(realPageNo);




        return page;
    }

    /**
     * 真实页码
     * @author lemon
     * @since 2019/10/28 0028
     */
    private Integer getRealPageNo(String pageNo) {
        Integer realPageNo = 1;

        try {
            if (!StringUtils.isEmpty(pageNo)) {
                realPageNo = Integer.parseInt(pageNo);
            }
        }catch (Exception e){
            logger.error("真实页码转换错误",e);
        }

        return realPageNo;
    }

}

 3.4、Page.java

分页实体类

package com.lemon.utils;

import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;



/**
 * 分页实体类
 * 页码从第一页开始。
 * @author lemon
 * @since 2019/10/28 0028
 */
public class Page {
    private static final Logger logger = Logger.getLogger(Page.class);

    //返回的一页数据
    private List list = new ArrayList();

    //符合条件的总记录数
    private Integer totalElements = 0;

    //符合条件的总页数
    private Integer totalPages = 0;

    //一页多少条记录
    private Integer pageSize = 3;

    //当前页码
    private Integer currentPageNo = 1;

    //上一页的页码（如果为0，表示不同显示或不可点击）
    private Integer prevPageNo = 0;

    //下一页的页码（如果为0，表示不用显示或不可点击）
    private Integer nextPageNo = 0;

    //可点击的页码列表（其中页码为零表示不可点击）
    private List<Integer> pageNoList = new ArrayList<>();

    @Override
    public String toString() {
        return "Page{" +
                "list=" + list +
                ", Page一页数据输出：totalElement=" + totalElements +
                ", totalPages=" + totalPages +
                ", pageSize=" + pageSize +
                ", currentPageNo=" + currentPageNo +
                ", prevPageNo=" + prevPageNo +
                ", nextPageNo=" + nextPageNo +
                ", pageNoList=" + pageNoList +
                '}';
    }

    public Integer getTotalElements() {
        return totalElements;
    }

    public void setTotalElements(Integer totalElements) {
        this.totalElements = totalElements;
    }

    public static Logger getLogger() {
        return logger;
    }

    public List getList() {
        return list;
    }

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


    public Integer getTotalPages() {
        return totalPages;
    }

    public void setTotalPages(Integer totalPages) {
        this.totalPages = totalPages;
    }

    public Integer getPageSize() {
        return pageSize;
    }

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

    public Integer getCurrentPageNo() {
        return currentPageNo;
    }

    public void setCurrentPageNo(Integer currentPageNo) {
        this.currentPageNo = currentPageNo;
    }

    public Integer getPrevPageNo() {
        return prevPageNo;
    }

    public void setPrevPageNo(Integer prevPageNo) {
        this.prevPageNo = prevPageNo;
    }

    public Integer getNextPageNo() {
        return nextPageNo;
    }

    public void setNextPageNo(Integer nextPageNo) {
        this.nextPageNo = nextPageNo;
    }

    public List<Integer> getPageNoList() {
        return pageNoList;
    }

    public void setPageNoList(List<Integer> pageNoList) {
        this.pageNoList = pageNoList;
    }
}

 3.5、PageUtils.java

分页工具类

package com.lemon.utils;

import org.apache.log4j.Logger;

import java.util.ArrayList;
import java.util.List;
/**
 * 分页工具类
 * @author: lemon
 * @since: 2019/10/29 0029
 */
public class PageUtils {

    private static final Logger logger = Logger.getLogger(PageUtils.class);

    //一页最多显示多少条记录
    private static final Integer pageSize = 3;

    //页码最多显示多少个
    private static final Integer maxCountPageNo = 3;

    /**
     * 进行指定页数据获取
     * @author lemon
     * @since 2019/10/28 0028
     */
    public static Page doPage(List elementList, Integer pageNo){
        Page page = new Page();

        //设置一页多少条记录
        page.setPageSize(pageSize);
        //数据为空 则返回Page默认设置
        if(elementList == null || elementList.size() == 0){
            return page;
        }
        //设置总的页码数
        Integer totalPages = getTotalPageNo(elementList);
        page.setTotalPages(totalPages);

        //设置总的记录数
        page.setTotalElements(elementList.size());

        //设置当前的页码
        Integer realPageNo = getCurrentPageNo(pageNo,totalPages);
        page.setCurrentPageNo(pageNo);

        //设置上一页的页码
        Integer prevPageNo = getPrevPageNo(pageNo);
        page.setPrevPageNo(prevPageNo);

        //设置下一页的页码
        Integer nextPageNo = getNextPageNo(pageNo,totalPages);
        page.setNextPageNo(nextPageNo);

        //得到可点击的list
        List<Integer> pageNoList = getPageNoList(pageNo, totalPages);
        page.setPageNoList(pageNoList);

        //得到指定页的一页数据
        List currentPageElementList = getCurrentPageElementList(elementList, pageNo);
        page.setList(currentPageElementList);

        return page;
    }




    /**
     * 获得总页码数
     * @author lemon
     * @since 2019/10/28 0028
     */
    private static Integer getTotalPageNo(List elementList) {
        //计算总页数
        int totalPages = elementList.size()/pageSize;

        if((elementList.size()/pageSize) != 0){
            totalPages = totalPages + 1;
        }

        return totalPages;
    }

    /**
     * 得到当前正确页码
     * @author lemon
     * @since 2019/10/28 0028
     */
    private static Integer getCurrentPageNo(Integer pageNo, Integer totalPages) {

        if(pageNo < 1){ // 页码小于1 则设为第一页
            pageNo = 1;
        }
        if(pageNo > totalPages){ // 页码大于最大页 则设为最后一页
            pageNo = totalPages;
        }

        return pageNo;
    }

    /**
     * 得到当前页面的上一页
     * @author lemon
     * @since 2019/10/28 0028
     */
    private static Integer getPrevPageNo(Integer pagaNo){
        Integer pervPageNo = pagaNo - 1;

        if(pervPageNo < 0){
            pervPageNo = 0;
        }

        return pervPageNo;
    }

    /**
     * 得到当前页面的上一页
     * @author lemon
     * @since 2019/10/28 0028
     */
    private static Integer getNextPageNo(Integer pagaNo, Integer totalPage){
        Integer nextPageNo = pagaNo + 1;

        if(nextPageNo < 2){
            nextPageNo = 2;
        }

        if(nextPageNo > totalPage){ //超过总页码，设置为0 ， 表示没有下一页
            nextPageNo = 0;
        }
        return nextPageNo;
        }

    /**
     * 得到可点击页码
     * @param pageNo 当前页码
     * @param totalPages 总的页码
     * @author lemon
     * @since 2019/10/28 0028
     */
    private static List<Integer> getPageNoList(Integer pageNo, Integer totalPages) {
        List<Integer> pageNoList = new ArrayList<>();

        //只有一页的情况
        if(totalPages < 2){
            pageNoList.add(1);
            return pageNoList;
        }

        //总页码数小于最大显示页码数
        if(totalPages < maxCountPageNo){
            for (int i = 1; i <= totalPages; i++) {
                pageNoList.add(i);
            }
            return pageNoList;
        }

        //当前页数小于等于最大显示页数
        if(pageNo <= maxCountPageNo){
            for (int i = 1; i <= pageSize; i++) {
                pageNoList.add(i);
            }
            return pageNoList;
        }

        //当前页数大于最大显示页数
        if(pageNo > maxCountPageNo){
            for (int i = 0; i < 3; i++) {
                pageNoList.add(pageNo - pageSize + i + 1);

                if (i == pageSize){
                    break;
                }
            }
            return pageNoList;
        }

        return pageNoList;
    }


    /**
     * 得到指定页的一页数据
     * @param elementList
     * @param pageNo
     * @return List
     * @author lemon
     * @since 2019/10/28 0028
     */
    private static List getCurrentPageElementList(List elementList, Integer pageNo) {
        ArrayList onePageElementList = new ArrayList();

        int startIndex = (pageNo - 1) * pageSize;
        for(int i = startIndex; i < elementList.size(); i++){//从指定页的记录数开始取，如果不满一页的数据，则全部取得
            Object ob = elementList.get(i);
            onePageElementList.add(ob);
            if(onePageElementList.size() >= pageSize) {//如果取慢一页数据 则不再取
                break;
            }
        }
        return onePageElementList;
    }




}

 3.6、list.html

页面显示（这里使用FreeMarker语句 if list等）

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>Bootstrap 实例 - 基本的表格</title>
    <link rel="stylesheet" href="https://cdn.staticfile.org/twitter-bootstrap/3.3.7/css/bootstrap.min.css">
    <script src="https://cdn.staticfile.org/jquery/2.1.1/jquery.min.js"></script>
    <script src="https://cdn.staticfile.org/twitter-bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body>

<div class="container .col-xs-6">
    <table class="table table-hover">
        <thead>
        <tr class="info">
            <th class="col-md-2">ID</th>
            <th class="col-md-3">用户名</th>
            <th class="col-md-4">密码</th>
        </tr>
        </thead>
        <tbody>
        <#list page.list as user>
        <tr>
            <td>${user.id}</td>
            <td>${user.username}</td>
            <td>${user.password}</td>
        </tr>
        </#list>
        </tbody>
    </table>

    <viv >
        <div class="col-md-offset-9">
            <#if page.totalPages gt 1 >
            <div>
                <ul class="pagination">


                    <#if page.prevPageNo gt 0>
                    <li><a href="/list?pageNo=${page.prevPageNo}">&laquo;</a></li>
                    <#else>
                    <li class="disabled"><a href="#">&laquo;</a></li>
                     </#if>


                <#list page.pageNoList as pageNo>
                     <#if page.currentPageNo == pageNo>
                <li class="active"><a href="/list?pageNo=${pageNo}">${pageNo}</a></li>
                <#else>
                <li><a href="/list?pageNo=${pageNo}">${pageNo}</a></li>
            </#if>
            </#list>




            <#if page.nextPageNo gt 0>
                <li><a href="/list?pageNo=${page.nextPageNo}">&raquo;</a></li>
                <#else>
                 <li class="disabled"><a href="#">&raquo;</a></li>
                </#if>
                </ul>
            </div>
            </#if>

            <div>
                每页${page.pageSize}条 共${page.totalElements}条 共${page.totalPages}页
            </div>
        </div>
    </viv>
</div>
</body>
</html>