SpringBoot+Mybatis实现分页查询

客官°小女子只卖身不卖艺 2021-12-24 00:53 431阅读 0赞

### 文章目录 ###

*   *  前言
     *  1.引入依赖
     *  2.Mapper中接口
     *  3.修改XML文件
     *  4.controller层调用接口
     *  5.测试
     *  总结

## 前言 ##

分页查询是在web开发中常用的一种技术，当某个页面查询返回的数据量较大时，为了提高性能和用户体验不能将所有数据一次性返回给过前端，这时候就需要用到分页查询了

PageHelper是一款开源的Mybatis第三方物理分页插件，spring boot项目中集成PageHelper插件非常简单，下面将为大家详细介绍；

插件地址：[https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/zh/HowToUse.md][https_github.com_pagehelper_Mybatis-PageHelper_blob_master_wikis_zh_HowToUse.md]

## 1.引入依赖 ##

在上一篇文章[Mybatis 实现基本的增删改查 (基于Mybatis-generator插件方式)][Mybatis _ _Mybatis-generator]的基础上,在pom.xml中添加如下依赖：

<dependency>
                <groupId>com.github.pagehelper</groupId>
                <artifactId>pagehelper-spring-boot-starter</artifactId>
                <version>1.2.3</version>
            </dependency>

## 2.Mapper中接口 ##

在EmployeeMapper.java中新增findByPaging接口，接口返回类型为Page

public interface EmployeeMapper { 
        int deleteByPrimaryKey(Long id);
    
        int insert(Employee record);
    
        int insertSelective(Employee record);
    
        Employee selectByPrimaryKey(Long id);
    
        int updateByPrimaryKeySelective(Employee record);
    
        int updateByPrimaryKey(Employee record);
    
        Page<Employee> findByPaging(Map param);
    }

## 3.修改XML文件 ##

在EmployeeMapper.xml中添加上面接口对应Sql查询语句，可以看到使用插件的方式查询时，这里入参的类型为com.github.pagehelper.Page

<select id="findByPaging" resultMap="BaseResultMap" parameterType="map">
        select
        *
        from employee
        where 1=1
        <if test="age != null">
          and age = #{age}
        </if>
      </select>

## 4.controller层调用接口 ##

EmployeeController.java中新增findBypaging方法

@ApiOperation(value = "分页查询")
        @GetMapping("findBypaging")
        public ResultMsg findByPaging(Integer age,Integer pageNum, Integer pageSize){ 
            PageHelper.startPage(pageNum,pageSize);
            Map param = new HashMap();
            param.put("age",age);
            Page<Employee> data = employeeMapper.findByPaging(param);
            JSONObject result = new JSONObject();
            result.put("employees",data);
            result.put("pages",data.getPages());
            result.put("total",data.getTotal());
            return ResultMsg.getMsg(result);
        }

**ps:这里分页查询参数的传递方式和普通的查询是一样的，map的方式添加就可以了**

## 5.测试 ##

编写一个测试用例，向数据库中批量插入200个员工数据

@RunWith(SpringRunner.class)
    @SpringBootTest
    public class HrefApplicationTests { 
        @Autowired
        private EmployeeMapper employeeMapper;
        @Autowired
        private IdWorker idWorker;
        public static String getRandomStr(int length) { 
            String base = "abcdefghijklmnopqrstuvwxyz0123456789";
            int randomNum;
            char randomChar;
            Random random = new Random();
            // StringBuffer类型的可以append增加字符
            StringBuffer str = new StringBuffer();
            for (int i = 0; i < length; i++) { 
                // 可生成[0,n)之间的整数，获得随机位置
                randomNum = random.nextInt(base.length());
                // 获得随机位置对应的字符
                randomChar = base.charAt(randomNum);
                // 组成一个随机字符串
                str.append(randomChar);
            }
            return str.toString();
        }
        Employee createRadomEmployee()
        { 
            Employee employee = new Employee();
            employee.setAddress("北新街" + getRandomStr(5));
            employee.setAge("22");
            employee.setGender(new Short("1"));
            employee.setCreateTime(new Date());
            employee.setName(getRandomStr(10));
            employee.setId(idWorker.nextId());
            return employee;
        }
        @Test
        public void insertEmployees() { 
            for(int i=0;i<402;i++)
            { 
                employeeMapper.insert(createRadomEmployee());
            }
        }
    }

执行后，看到测试数据已经建好

![format_png][]

打开swagger，输入pageNum和pageSize,点击Try it out

![format_png 1][]

返回结果如下：可以看到总数据为402，总页数为134,一共返回了3条数据

{ 
      "data": { 
        "total": "402",
        "pages": 134,
        "employees": [
          { 
            "address": "北新街4wf91",
            "age": "22",
            "createTime": 1558951333000,
            "deptId": 0,
            "gender": 0,
            "id": "317724116919799808",
            "name": "69wcpvii46"
          },
          { 
            "address": "北新街8voe8",
            "age": "22",
            "createTime": 1558951333000,
            "deptId": 0,
            "gender": 0,
            "id": "317724117007880192",
            "name": "9sicl9xer4"
          },
          { 
            "address": "北新街tbq90",
            "age": "22",
            "createTime": 1558951333000,
            "deptId": 0,
            "gender": 0,
            "id": "317724117309870080",
            "name": "u9zxm84sqo"
          }
        ]
      },
      "result": "SUCCESS",
      "resultCode": 200,
      "resultMsg": ""
    }

## 总结 ##

可以看到在spring boot中使用pageHealper插件进行分页查询很简单，包括如下3步:

1.  导入插件依赖或jar包
2.  在Mapper中添加接口，返回类型为Page<实体类型>，本例为Page
3.  在xml中添加查询语句，入参的类型为com.github.pagehelper.Page

[项目源码][Link 1]

[https_github.com_pagehelper_Mybatis-PageHelper_blob_master_wikis_zh_HowToUse.md]: https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/zh/HowToUse.md
[Mybatis _ _Mybatis-generator]: https://blog.csdn.net/bdqx_007/article/details/93634370
[format_png]: /images/20211223/507cf6a8202241bc9a6bb91d9cc47ad5.png
[format_png 1]: /images/20211223/0707fdcfa857446f908e8e46270b7188.png
[Link 1]: https://github.com/wanggang-git/Mybatis_demo