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

川长思鸟来 2023-06-11 05:23 11阅读 0赞

# 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目录： ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xlbW9uU25t_size_16_color_FFFFFF_t_70][]

## 1.3、maven依赖： ##

> **对于springboot框架 以及FreeMarker模板等**

<parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            
            <version>1.5.6.RELEASE</version>
            <relativePath/> 
        </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-freemarker</artifactId>
        </dependency>
    
        
        <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>
        
        <dependency>
            <groupId>javax.mail</groupId>
            <artifactId>mail</artifactId>
            <version>1.4.7</version>
        </dependency>
    
    
        
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
    
        
        <dependency>
            <groupId>net.sf.barcode4j</groupId>
            <artifactId>barcode4j-light</artifactId>
            <version>2.0</version>
        </dependency>
    
        <dependency>
            
            <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、实现效果： #

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xlbW9uU25t_size_16_color_FFFFFF_t_70 1][]

# 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}">«</a></li>
                        <#else>
                        <li class="disabled"><a href="#">«</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}">»</a></li>
                    <#else>
                     <li class="disabled"><a href="#">»</a></li>
                    </#if>
                    </ul>
                </div>
                </#if>
    
                <div>
                    每页${page.pageSize}条 共${page.totalElements}条 共${page.totalPages}页
                </div>
            </div>
        </viv>
    </div>
    </body>
    </html>

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xlbW9uU25t_size_16_color_FFFFFF_t_70]: /images/20230531/a8d83aa093504995a8a2d9e94ede7902.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xlbW9uU25t_size_16_color_FFFFFF_t_70 1]: /images/20230531/d2a2da5e935d451dac290f5d39c9944d.png