8、Mybatis分页

一、普通的Limit分页

MySQL中执行分页语句：

index：从什么位置开始分页   pageIndex:分页显示的数量
select * from mybatis.user limit index,pageIndex;

第一种、使用mybatis进行分页操作：（map操作）

　　IUserDao接口（定义业务方法）：

public interface IUserDao {
    /**分页查询*/
    List<User> getPageByLimit(Map<String,Integer> map);
}

　　UserMapper.xmlmapper（实现类）：

<!--命名空间：绑定UserDao相当于实现这个接口-->
<mapper namespace="com.zhixi.dao.IUserDao">

    <!--结果集映射:用于实体类跟数据库字段名称不一样的情况-->
    <resultMap id="userMap" type="user">
        <!--column:数据库中的字段 property:实体类中的属性-->
        <result column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="pwd" property="password"/>
    </resultMap>

    <!--分页-->
    <select id="getPageByLimit" resultMap="userMap" parameterType="map" >
        select * from mybatis.user limit #{index},#{pageIndex};
    </select>

</mapper>

　　测试类：

public class IUserDaoTest {

    @Test
    public void getLimitTest() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        IUserDao mapper = sqlSession.getMapper(IUserDao.class);

        Map<String, Integer> map = new HashMap<String, Integer>();
        //这里的index跟pageIndex要与mapper对应
        map.put("index", 0);
        map.put("pageIndex", 2);

        List<User> pageByLimit = mapper.getPageByLimit(map);
        for (User user : pageByLimit) {
            System.out.println(user);
        }
        sqlSession.close();
    }

　　结果：

 第二种、使用mybatis进行分页操作：（普通limit实现）

IUserDao接口（定义业务方法）：

public interface UserDao {
    /**
     * 分页查询的第二种方式
     */
    List<User> getLimit(@Param("id1") Integer id1, @Param("id2") Integer id2);
}

　　UserMapper.xmlmapper（实现类）：

<!--命名空间：绑定UserDao相当于实现这个接口-->
<mapper namespace="com.zhixi.dao.IUserDao">

    <!--结果集映射:用于实体类跟数据库字段名称不一样的情况-->
    <resultMap id="userMap" type="user">
        <!--column:数据库中的字段 property:实体类中的属性-->
        <result column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="pwd" property="password"/>
    </resultMap>

    <!--分页-->

　　<select id="getLimit" parameterType="int" resultMap="BaseResultMap">
 　　 select * from mybatis.user limit #{id1},#{id2};
　　</select>

</mapper>

　　测试类：

public class IUserDaoTest {

@Test
public void getLimit(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserDao mapper = sqlSession.getMapper(UserDao.class);
    List<User> limit = mapper.getLimit(0, 2);
    for (User user : limit) {
        System.out.println(user);
    }
    sqlSession.close();
}

}

 二、使用PageHelper插件

官方文档地址：https://pagehelper.github.io/

在SpringBoot中使用分页插件：https://zhuanlan.zhihu.com/p/344982068

Maven：

        <!--Mybatis分页插件-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.3.0</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.2</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

1、数据表

CREATE TABLE `user`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `createtime` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 100001 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;
INSERT INTO `mybatis`.`user`(`id`, `name`, `password`, `createtime`) VALUES (1, 'test:0', '123456', '2022-09-09 10:07:53');
INSERT INTO `mybatis`.`user`(`id`, `name`, `password`, `createtime`) VALUES (2, 'test:1', '123456', '2022-09-09 10:07:53');
INSERT INTO `mybatis`.`user`(`id`, `name`, `password`, `createtime`) VALUES (3, 'test:2', '123456', '2022-09-09 10:07:53');
INSERT INTO `mybatis`.`user`(`id`, `name`, `password`, `createtime`) VALUES (4, 'test:3', '123456', '2022-09-09 10:07:53');
INSERT INTO `mybatis`.`user`(`id`, `name`, `password`, `createtime`) VALUES (5, 'test:4', '123456', '2022-09-09 10:07:53');
INSERT INTO `mybatis`.`user`(`id`, `name`, `password`, `createtime`) VALUES (6, 'test:5', '123456', '2022-09-09 10:07:53');
INSERT INTO `mybatis`.`user`(`id`, `name`, `password`, `createtime`) VALUES (7, 'test:6', '123456', '2022-09-09 10:07:53');
INSERT INTO `mybatis`.`user`(`id`, `name`, `password`, `createtime`) VALUES (8, 'test:7', '123456', '2022-09-09 10:07:53');
INSERT INTO `mybatis`.`user`(`id`, `name`, `password`, `createtime`) VALUES (9, 'test:8', '123456', '2022-09-09 10:07:53');
INSERT INTO `mybatis`.`user`(`id`, `name`, `password`, `createtime`) VALUES (10, 'test:9', '123456', '2022-09-09 10:07:53');
INSERT INTO `mybatis`.`user`(`id`, `name`, `password`, `createtime`) VALUES (11, 'test:10', '123456', '2022-09-09 10:07:53');
INSERT INTO `mybatis`.`user`(`id`, `name`, `password`, `createtime`) VALUES (12, 'test:11', '123456', '2022-09-09 10:07:53');
INSERT INTO `mybatis`.`user`(`id`, `name`, `password`, `createtime`) VALUES (13, 'test:12', '123456', '2022-09-09 10:07:53');
INSERT INTO `mybatis`.`user`(`id`, `name`, `password`, `createtime`) VALUES (14, 'test:13', '123456', '2022-09-09 10:07:53');
INSERT INTO `mybatis`.`user`(`id`, `name`, `password`, `createtime`) VALUES (15, 'test:14', '123456', '2022-09-09 10:07:53');
INSERT INTO `mybatis`.`user`(`id`, `name`, `password`, `createtime`) VALUES (16, 'test:15', '123456', '2022-09-09 10:07:53');
INSERT INTO `mybatis`.`user`(`id`, `name`, `password`, `createtime`) VALUES (17, 'test:16', '123456', '2022-09-09 10:07:53');
INSERT INTO `mybatis`.`user`(`id`, `name`, `password`, `createtime`) VALUES (18, 'test:17', '123456', '2022-09-09 10:07:53');
INSERT INTO `mybatis`.`user`(`id`, `name`, `password`, `createtime`) VALUES (19, 'test:18', '123456', '2022-09-09 10:07:53');
INSERT INTO `mybatis`.`user`(`id`, `name`, `password`, `createtime`) VALUES (20, 'test:19', '123456', '2022-09-09 10:07:53');

2、导入依赖

<!--Mybatis分页插件-->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.3.0</version>
</dependency>

3、在SpringBoot中声明插件

#pagehelper分页插件配置
pagehelper.helperDialect=mysql
pagehelper.reasonable=true
pagehelper.supportMethodsArguments=true
pagehelper.params=count=countSql

4、业务代码

实体类：User

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import java.io.Serializable;
import java.time.LocalDateTime;
import lombok.Data;

/**
 * 
 * @TableName user
 */
@TableName(value ="user")
@Data
public class User implements Serializable {
    /**
     * 
     */
    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;

    /**
     * 
     */
    @TableField(value = "name")
    private String name;

    /**
     * 
     */
    @TableField(value = "password")
    private String password;

    /**
     * 
     */
    @TableField(value = "createtime")
    private LocalDateTime createtime;

    @TableField(exist = false)
    private static final long serialVersionUID = 1L;

    @Override
    public boolean equals(Object that) {
        if (this == that) {
            return true;
        }
        if (that == null) {
            return false;
        }
        if (getClass() != that.getClass()) {
            return false;
        }
        User other = (User) that;
        return (this.getId() == null ? other.getId() == null : this.getId().equals(other.getId()))
            && (this.getName() == null ? other.getName() == null : this.getName().equals(other.getName()))
            && (this.getPassword() == null ? other.getPassword() == null : this.getPassword().equals(other.getPassword()))
            && (this.getCreatetime() == null ? other.getCreatetime() == null : this.getCreatetime().equals(other.getCreatetime()));
    }

    @Override
    public int hashCode() {
        final int prime = 31;
        int result = 1;
        result = prime * result + ((getId() == null) ? 0 : getId().hashCode());
        result = prime * result + ((getName() == null) ? 0 : getName().hashCode());
        result = prime * result + ((getPassword() == null) ? 0 : getPassword().hashCode());
        result = prime * result + ((getCreatetime() == null) ? 0 : getCreatetime().hashCode());
        return result;
    }

    @Override
    public String toString() {
        StringBuilder sb = new StringBuilder();
        sb.append(getClass().getSimpleName());
        sb.append(" [");
        sb.append("Hash = ").append(hashCode());
        sb.append(", id=").append(id);
        sb.append(", name=").append(name);
        sb.append(", password=").append(password);
        sb.append(", createtime=").append(createtime);
        sb.append(", serialVersionUID=").append(serialVersionUID);
        sb.append("]");
        return sb.toString();
    }
}

Dao层：UserMapper

/**
* @author zhixi
* @description 针对表【user】的数据库操作Mapper
* @createDate 2022-09-09 09:23:14
* @Entity com.zhixi.pojo.User
*/
@Repository
public interface UserMapper extends BaseMapper<User> {
    List<User> selectAll();
}

Service层：UserService

import com.zhixi.pojo.User;
import com.baomidou.mybatisplus.extension.service.IService;

import java.util.List;

/**
 * @author zhixi
 * @description 针对表【user】的数据库操作Service
 * @createDate 2022-09-09 09:23:14
 */
public interface UserService extends IService<User> {
    /**
     * 测试分页插件(PageHelper)查询用户数据
     * @param pageNum 页码
     * @param pageSize  每页显示数量
     * @return 用户数据
     */
    List<User> selectAll(int pageNum, int pageSize);
}

Service层：UserServiceImpl　　

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.zhixi.mapper.UserMapper;
import com.zhixi.pojo.User;
import com.zhixi.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @author zhixi
 * @description 针对表【user】的数据库操作Service实现
 * @createDate 2022-09-09 09:23:14
 */
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User>
        implements UserService {

    @Autowired
    private UserMapper userMapper;

    @Override
    public List<User> selectAll(int pageNum, int pageSize) {
        /*执行分页，传入页码以及每页显示的数量*/
        PageHelper.startPage(pageNum, pageSize);
        List<User> users = userMapper.selectList(null);
        PageInfo<User> pageInfo = new PageInfo<>(users);
        return pageInfo.getList();
    }
}

Controller层：UserController　　

import com.zhixi.pojo.User;
import com.zhixi.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping("/selectAll/{pageNum}/{pageSize}")
    public List<User> selectAll(@PathVariable("pageNum") int pageNum, @PathVariable("pageSize") int pageSize) {
        return userService.selectAll(pageNum, pageSize);
    }
}

访问测试：