Version: Next

分页

思考：问什么要分页？

  • 减少数据的处理量

Limit分页

SELECT * FROM USER LIMIT startIndex, pageSize
SELECT * FROM USER LIMIT 0, 2
# 查询[0, 3) 记录0, 1, 2
SELECT * FROM USER LIMIT 3

使用Mybatis实现分页，核心就是Sql

  1. 接口

    List<User> getUserByLimit(Map<String, Integer> map);

  2. Mapper.xml

    <select id="getUserByLimit" parameterType="map" resultType="com.bsx.pojo.User">
    SELECT * FROM USER LIMIT #{startIndex}, #{pageSize}
    </select>

  3. 测试类

    @Test
    public void testGetUserByLimit() throws IOException {
    String resource = "mybatis-config.xml";
    InputStream resourceAsStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    HashMap<String, Integer> hashMap = new HashMap<String, Integer>();
    hashMap.put("startIndex", 0);
    hashMap.put("pageSize", 2);
    List<User> userByLimit = mapper.getUserByLimit(hashMap);
    for (User user : userByLimit) {
    System.out.println(user);
    }
    } catch (Exception e) {
    e.printStackTrace();
    } finally {
    sqlSession.close();
    }
    }

RowBounds分页（了解）

  • 接口

    List<User> getUserByRowBounds();

  • mapper.xml

    <!-- rowBounds分页-->
    <select id="getUserByRowBounds" resultType="com.bsx.pojo.User">
    SELECT * FROM USER
    </select>

  • 测试

    @Test
    public void testGetUserByRowBounds() throws IOException {
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
    //通过RowBounds实现
    RowBounds rowBounds = new RowBounds(1, 2);
    //通过Java代码层面实现分页
    List<User> users = sqlSession.selectList("com.bsx.dao.UserMapper" +
    ".getUserByRowBounds", null, rowBounds);
    for (User user : users) {
    System.out.println(user);
    }
    } catch (Exception e) {
    e.printStackTrace();
    } finally {
    sqlSession.close();
    }
    }

分页插件MybatisPageHelper

https://blog.csdn.net/eson_15/article/details/52270046

  • layui分页

https://blog.csdn.net/ardo_pass/article/details/78694717

坐标

<!-- mybatis分页插件 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.4</version>
</dependency>

配置方式1：在Mybatis配置文件中配置

<!-- 配置分页插件 -->
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<!-- 设置数据库类型 Oracle,Mysql,MariaDB,SQLite,Hsqldb,PostgreSQL六种数据库-->
<property name="helperDialect" value="mysql"/>
</plugin>
</plugins>

配置方式2：Spring配置文件

2. Using in Spring application.xml
config org.mybatis.spring.SqlSessionFactoryBean as following:
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<!-- other configuration -->
<property name="plugins">
<array>
<bean class="com.github.pagehelper.PageInterceptor">
<property name="properties">
<!-- config params as the following -->
<value>
param1=value1
</value>
</property>
</bean>
</array>
</property>
</bean>

使用

public class DocServiceImpl implements IDocService {
@Autowired
private DocMapper docMapper;
@Override
public PageInfo<Doc> selectDocByPage1(int currentPage, int pageSize) {
// 使用PageHelper类即可分页
PageHelper.startPage(currentPage, pageSize);
// PageHelper.startPage方法的下一次数据库查询操作会被分页
List<Doc> list = docMapper.selectByPageAndSelections();
// 用PageInfo构造方法接收返回的查询结果，可以得到这次查询的分页信息
PageInfo<Doc> pageInfo = new PageInfo<>(list);
// 例如获取本次分页查询，数据库返回的总记录数
long total = pageInfo.getTotal(); // 从分页信息对象中查询出数据库记录总数
return pageInfo;
}
}
  • 前端js
// 定义展示表格和分页栏的方法
var currentPage = 1; // 默认当前页
var limitSize = 15; // 每页显示条数
function show(currentPage, limitSize) {
$.ajax({
type: "post",
dataType: "json",
async: true,
beforeSend: () => {
layer.load = load;
},
complete: () => {
layer.close(load);
},
data: {
'aac002': aac002,
'aac003': aac003,
'akb020_in': akb020_in,
'aae030': aae030,
'aae031': aae031,
'currentPage': currentPage,
'limitSize': limitSize
},
url: "<%=basePath%>vocationalcontroller/queryLocalMedicalInformation.json",
success: function (data) {
layer.close(load)
// 得到的响应
console.log(data);
// 总结果数
var total = data.total;
// 得到的响应数据
var resultList = data.list;
// console.log(resultList);
// 页面序号
var index = 0;
// 处理实付标志转义
var mioFlag = "";
// 处理证件类型转义
var cardType = "";
// 最终填充表格体的html内容
var html = "";
for (var i = 0; i < resultList.length; i++) {
// 处理序号
index = i + 1;
html += "<tr>" +
"<td>" + index + "</td>" +
"<td>" + resultList[i].AAC003 + "</td>" +
"<td>" + resultList[i].AAC002 + "</td>" +
"<td>" + resultList[i].AKB021 + "</td>" +
"<td>" + resultList[i].YKC193 + "</td>" +
"<td>" + resultList[i].AAE030 + "</td>" +
"<td>" + resultList[i].AAE031 + "</td>" +
"<td>" + resultList[i].AAA103 + "</td>" +
+"</tr>"
}
//往id为cotent的表格中加数据
$("#id-tbody").html(html);
//分页
layui.use('laypage', function () {
var laypage = layui.laypage;
//执行一个laypage实例
//自定义样式
laypage.render({
elem: 'id-pageSeparate'
, count: total // 总条数
, limit: limitSize // 每页条数
, curr: currentPage // 当前页
, theme: '#90EE90' // 颜色
, jump: function (obj, first) {
if (!first) { // 如果不是分页的第一页才执行
// alert(obj.curr); // 当前被点击的页码
show(obj.curr, limitSize); // 传入被点击的页码和每页的大小
}
}
});
});
}
});
}
// 调用
show(currentPage, limitSize);
  • 前端html
<!-- 分页框 -->
<div class="mt-1" style="text-align: center;">
<div id="id-pageSeparate"></div>
</div>