Springboot 进行分页操作（通过原生sql语句进行）

小咪咪 2023-03-03 08:47 325阅读 0赞

## 为什么进行分页操作 ##

因为列表内容太多了，所以使用分页进行显示。

分页的核心就是计算每页多少记录和总页数以及第几页。

## 操作 ##

mysql的分页语句：  
select \* from student limit 页数\*页面大小,页面大小

#### 1、首先先写如何计算每页多少记录和第几页， ####

每页展示数据需要先进行设置：pageSize=4;
        获取总条数：sql语句在持久层（Dao）编写获取总条数方法

public int getCount() { 
            //获取总的条数
            String sql="select count(*) from users ";
            int count =jdbcTemplate.queryForObject(sql, Integer.TYPE);//得到数据库中所有的数据
            return count;
        }

#### 2、获取总页数 ####

两种情况：  
总页数 = 100/20 =数据总数/页面大小  
总页数 = 103/20 = 数据总数/页面大小+1  
逻辑处理—>  
总页数 = 数据总数%页面大小==0? 数据总数/页面大小:数据总数/页面大小+1

int maxPage=totalCount%pageSize==0?totalCount/pageSize:totalCount/pageSize+1;

#### 3、代码及目录结构 ####

![目录结构][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTEwNTUx_size_16_color_FFFFFF_t_70]  
数据库连接：在application.properties 配置文件中进行数据库连接，配置信息如下

spring.datasource.url=jdbc:mysql://localhost:3306/ssm?serverTimezone=GMT%2B8&userUnicode=true&characterEncoding=utf-8&useSSL=false
    spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
    spring.datasource.username=root
    spring.datasource.password=xxxx
    spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

同时需要在pom.xml文件进行一下依赖配置

<dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-thymeleaf</artifactId>
            </dependency>
    
            
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-jdbc</artifactId>
            </dependency>
    
            
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.15</version>
            </dependency>
    
            
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                <version>1.1.12</version>
            </dependency>

创建与数据库数据匹配的对象（在pojo层进行对象封装）

public class User { 
        private Integer Uid;
        private String username;
        private String password;
        private String sex;
    
        public Integer getUid() { 
            return Uid;
        }
    
        public void setUid(Integer uid) { 
            Uid = uid;
        }
    
        public String getusername() { 
            return username;
        }
    
        public void setusername(String uusername) { 
            username = uusername;
        }
    
        public String getpassword() { 
            return password;
        }
    
        public void setpassword(String upassword) { 
            password = upassword;
        }
    
        public String getsex() { 
            return sex;
        }
    
        public void setsex(String usex) { 
            sex = usex;
        }
        @Override
        public String toString() { 
            return "User{" +
                    "Uid=" + Uid +
                    ", username='" + username + '\'' +
                    ", password='" + password + '\'' +
                    ", sex='" + sex + '\'' +
                    '}';
        }
    }

在dao层（持久层）进行接口封装及实现  
UserDao.java

package com.flyfly.springbootjdbc.dao;
    
    import com.flyfly.springbootjdbc.pojo.User;
    
    import java.util.List;
    
    public interface UserDao { 
        //获取数据库数据条数
        int getCount();
        //获取分页数据
        List<User> selectUserPage(int pageNo,int pageSize);
    }

实现UserDao接口（UserDaoImpl.java）

package com.flyfly.springbootjdbc.dao.impl;
    
    import com.flyfly.springbootjdbc.dao.UserDao;
    import com.flyfly.springbootjdbc.pojo.User;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.jdbc.core.RowCallbackHandler;
    import org.springframework.jdbc.core.RowMapper;
    import org.springframework.stereotype.Repository;
    
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.List;
    
    @Repository
    public class UserDaoImpl implements UserDao { 
        @Autowired
        private JdbcTemplate jdbcTemplate;
    
        @Override
        public int getCount() { 
            //获取总的条数
            String sql="select count(*) from users ";
            int count =jdbcTemplate.queryForObject(sql, Integer.TYPE);//得到数据库中所有的数据
            return count;
        }
        /** * 分页查询 * @return */
        @Override
        public List<User> selectUserPage(int pageNo, int pageSize) { 
        	//分页查询
            String sql="select * from users order by u_id limit "+(pageNo-1)*pageSize+","+pageSize;
    		//封装，返回数据库数据
            return jdbcTemplate.query(sql, new RowMapper<User>() { 
                @Override
                public User mapRow(ResultSet rs, int rowNum) throws SQLException { 
                    User users = new User();
                    users.setUid(rs.getInt("u_id"));
                    users.setusername(rs.getString("username"));
                    users.setsex(rs.getString("sex"));
                    return users;
                }
            });
        }
    }

在service层进行接口编辑（UserService.java）

package com.flyfly.springbootjdbc.service;
    
    import com.flyfly.springbootjdbc.pojo.User;
    
    import java.util.List;
    
    public interface UserService { 
        //获取数据总数
        int getCount();
        //获取分页展示数据
        List<User> findUserPage(int pageNo,int pageSize);
    }

实现UserService接口方法（UserServiceImpl.java）

package com.flyfly.springbootjdbc.service.impl;
    
    import com.flyfly.springbootjdbc.dao.UserDao;
    import com.flyfly.springbootjdbc.pojo.User;
    import com.flyfly.springbootjdbc.service.UserService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Repository;
    import org.springframework.transaction.annotation.Transactional;
    
    import java.util.List;
    
    @Repository
    public class UserServiceImpl implements UserService { 
        @Autowired
        private UserDao userDao;
    
        //获取数据总条数
        @Override
        public int getCount() { 
            return this.userDao.getCount();
        }
    
        /** * 根据页数查询用户 * @param pageNo * @param pageSize * @return */
        @Override
        public List<User> findUserPage(int pageNo, int pageSize) { 
            return this.userDao.selectUserPage(pageNo,pageSize);
        }
    }

底层数据库查询和service层代码完成后可以对控制层进行编辑了  
页面跳转PageController

package com.flyfly.springbootjdbc.controller;
    
    import com.flyfly.springbootjdbc.pojo.User;
    import com.flyfly.springbootjdbc.service.UserService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Controller;
    import org.springframework.ui.Model;
    import org.springframework.web.bind.annotation.PathVariable;
    import org.springframework.web.bind.annotation.RequestMapping;
    
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    /* *页面跳转 */
    @Controller
    public class PageController { 
        @RequestMapping("/{page}")
        public String showPage(@PathVariable String page) { 
            return page;
        }
    }

UserController.java

package com.flyfly.springbootjdbc.controller;
    
    import com.flyfly.springbootjdbc.pojo.User;
    import com.flyfly.springbootjdbc.service.UserService;
    import com.flyfly.springbootjdbc.service.impl.UserServiceImpl;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Controller;
    import org.springframework.ui.Model;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.PostMapping;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestMethod;
    
    import javax.sql.DataSource;
    import java.util.List;
    
    @Controller
    @RequestMapping("/user")
    public class UserController { 
    
        @Autowired
        private UserService userService;
    
        /** * 分页查询 * @param pageNumber * @param model * @return */
        @GetMapping("/pageShowUser")
        public String page(String pageNumber, Model model){ 
    
            String spPage=pageNumber;
            //设置每页条数
            int pageSize=3;
            //页数，对页数进行判断设置
            int pageNo=0;
            if (spPage==null){ 
                pageNo=1;
            }else { 
                pageNo=Integer.valueOf(spPage);
                if (pageNo<1){ 
                    pageNo=1;
                }
            }
            //设置首页
            int minPage=1;
            //设置最大页数
            int totalCount=userService.getCount();    //获取总条数
            int maxPage=totalCount%pageSize==0?totalCount/pageSize:totalCount/pageSize+1;
            if (pageNo>maxPage){ 
                pageNo=maxPage;
            }
    
            try { 
                //分页查询
                List<User> list = userService.findUserPage(pageNo,pageSize);
                //把信息放入model转发到页面
                model.addAttribute("list", list);
                model.addAttribute("pageNo",pageNo);
                model.addAttribute("totalCount",totalCount);
                model.addAttribute("maxPage",maxPage);
                model.addAttribute("minPage",minPage);
            } catch (Exception e) { 
                e.printStackTrace();
                return "error";
            }
            return "pageShow";
        }
    }

控制层写好后就可以对数据在前端页面进行传输了  
前端展示这里使用Thymeleaf进行展示

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
            "http://www.w3.org/TR/html4/loose.dtd">
    <html xmlns:th="http://www.thymeleaf.org">
    <link rel="shortcut icon" href="../resources/favicon.ico" th:href="@{/static/favicon.ico}">
    <head>
        <title>显示全部用户</title>
    </head>
    <body>
    <table border="1" align="center">
        <tr>
            <th>用户ID</th>
            <th>用户姓名</th>
            <th>用户性别</th>
            <th>操作</th>
        </tr>
        <tr th:each="u:${list}">
            <td th:text="${u.getUid()}"></td>
            <td th:text="${u.getusername()}"></td>
            <td th:text="${u.getsex()}"></td>
            <td>
                <a th:href="@{/user/preUpdateUser(id=${u.getUid()})}">修改</a>
                <a th:href="@{/user/deleteUser(id=${u.getUid()})}">删除</a>
            </td>
        </tr>
        <tr align="center">
            <td colspan="4">
                <a th:href="@{'/user/pageShowUser?pageNumber='+${minPage}}">首页</a>
                <a th:href="@{'/user/pageShowUser?pageNumber='+${pageNo-1}}">上一页</a>
                <a th:href="@{'/user/pageShowUser?pageNumber='+${pageNo+1}}">下一页</a>
                <a th:href="@{'/user/pageShowUser?pageNumber='+${maxPage}}">尾页</a>  
            </td>
        </tr>
    </table>
    </body>
    </html>

效果展示(页面略丑）  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTEwNTUx_size_16_color_FFFFFF_t_70 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTEwNTUx_size_16_color_FFFFFF_t_70]: /images/20230208/04e7942ca08746b6a8c92649f576c944.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTEwNTUx_size_16_color_FFFFFF_t_70 1]: /images/20230208/c3153928459c411b88d2edadac416557.png