1, Bicycles
Sometimes we may use our own business code to find out a List, and then use sublist for manual paging.
Manual paging can make it clear how to use the subList method of List, but this is very desirable. If the return value is too large, the memory is easy to be ruthlessly burst.
import dao.TestMapper;
import entity.TestEntity;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
import java.util.*;
/**
* @author Discover more interesting official account: Muzi's day and night programming
* A code farmer who lives at the bottom of the Internet and does addition, deletion, modification and inspection, is not familiar with the affectation of the world
*/
public class TestMain03 {
public static void main(String[] args) throws Exception {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
try (SqlSession session = sqlSessionFactory.openSession()) {
// Get Mapper through sesson. This Mapper will program the proxy Mapper of Mybatis
TestMapper mapper = session.getMapper(TestMapper.class);
// Paging parameters passed in by business
Integer pageNum = 1; // Current page (generally, if you interact with the front end, please accommodate them. The number of pages starts from 1)
Integer pageSize = 10; // Size per page
// Sometimes a third party or other interface may be called to query all data, and only all data is available
// I also want to display in pages, but people don't provide you with a paging interface. This may be used at this time
List<TestEntity> list = mapper.select();
List<TestEntity> res = new ArrayList<>();
if (list != null) {
// Params:
//fromIndex – low endpoint of the subList
//toIndex – high endpoint of the subList
// If yes: (fromindex < 0 | toindex > size | fromindex > toindex) throw exception IndexOutOfBoundsException –
int size = list.size();
// Prevent fromindex < 0
if (pageNum <= 0) {
throw new Exception("pageNum Must be an integer greater than or equal to 1");
}
if (pageSize <= 0) {
throw new Exception("pageSize Must be an integer greater than or equal to 1");
}
int totalPage = (int) Math.ceil(Double.valueOf(size)/Double.valueOf(pageSize));
// The number of pages exceeds the maximum number of pages (the front end is not trusted)
if (pageNum > pageSize) {
res = new ArrayList<>();
} else {
Integer fromIndex = (pageNum -1 ) * pageSize;
Integer toIndex =pageNum * pageSize;
// Prevent toindex > size
toIndex = toIndex > list.size() ? list.size() : toIndex;
res = list.subList(fromIndex, toIndex);
}
}
for (int i = 0; i < res.size(); i++) {
System.out.println(res.get(i));
}
}
}
}
2, Motorcycles
Mybatis comes with RowBounds
After reading the source code, we know that it is actually a logical paging, which will also find out all the data. Then, an internal operation is carried out, first filter out the number of offset size, then continue to read the number of limit size, and finally return.
// If there are multiple parameters, add this parameter at the end
@Select("select * from test")
List<TestEntity> selectRowBounds(RowBounds rd);
import dao.TestMapper;
import entity.TestEntity;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
import java.util.List;
/**
* @author Discover more interesting official account: Muzi's day and night programming
* A code farmer who lives at the bottom of the Internet and does addition, deletion, modification and inspection, is not familiar with the affectation of the world
*/
public class TestMain04 {
public static void main(String[] args) throws Exception {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
try (SqlSession session = sqlSessionFactory.openSession()) {
// Get Mapper through sesson. This Mapper will program the proxy Mapper of Mybatis
TestMapper mapper = session.getMapper(TestMapper.class);
// The offset offset starts from 0, and the limit checks how many items in total
Integer pageNum =1; // Current page (generally, if you interact with the front end, please accommodate them. The number of pages starts from 1)
Integer pageSize = 10; // Size per page
// If the paging parameter is wrong, it will not be judged first. Judge according to the business
//
RowBounds rd = new RowBounds((pageNum-1)*pageSize,pageSize);
List<TestEntity> res = mapper.selectRowBounds(rd);
for (int i = 0; i < res.size(); i++) {
System.out.println(res.get(i));
}
}
}
}
The source code implementation is simple, and the understanding is as follows:
// Cross offset records
private void skipRows(ResultSet rs, RowBounds rowBounds) throws SQLException {
if (rs.getType() != ResultSet.TYPE_FORWARD_ONLY) {
if (rowBounds.getOffset() != RowBounds.NO_ROW_OFFSET) {
rs.absolute(rowBounds.getOffset());
}
} else {
// That is, rs.next(); Those who have written native sql know that this is
for (int i = 0; i < rowBounds.getOffset(); i++) {
rs.next();
}
}
}
//
private void handleRowValuesForSimpleResultMap(ResultSetWrapper rsw, ResultMap resultMap, ResultHandler<?> resultHandler, RowBounds rowBounds, ResultMapping parentMapping)
throws SQLException {
DefaultResultContext<Object> resultContext = new DefaultResultContext<Object>();
// Skip offset record
skipRows(rsw.getResultSet(), rowBounds);
// Read limit records from the remaining results
// Every time a record is read, it is given to resultContext++
while (shouldProcessMoreRows(resultContext, rowBounds)
&& rsw.getResultSet().next()) {
ResultMap discriminatedResultMap = resolveDiscriminatedResultMap(rsw.getResultSet(), resultMap, null);
Object rowValue = getRowValue(rsw, discriminatedResultMap);
storeObject(resultHandler, resultContext, rowValue, parentMapping, rsw.getResultSet());
}
}
// Here is to compare whether the number of reads is less than the limit. If it is less than the limit, it will be read circularly
private boolean shouldProcessMoreRows(ResultContext<?> context, RowBounds rowBounds) throws SQLException {
return !context.isStopped() && context.getResultCount() < rowBounds.getLimit();
}
To put it bluntly, it is also processed through code logic
3, Manual transmission car
Physical paging, that is, the client will not be found. It has been divided on the server,
@Select("select * from test limit #{offset} , #{pageSize}")
List<TestEntity> selectLimit(@Param("offset") int offset, @Param("pageSize") Integer pageSize);
import dao.TestMapper;
import entity.TestEntity;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
import java.util.List;
/**
* @author Discover more interesting official account: Muzi's day and night programming
* A code farmer who lives at the bottom of the Internet and does addition, deletion, modification and inspection, is not familiar with the affectation of the world
*/
public class TestMain05 {
public static void main(String[] args) throws Exception {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
try (SqlSession session = sqlSessionFactory.openSession()) {
// Get Mapper through sesson. This Mapper will program the proxy Mapper of Mybatis
TestMapper mapper = session.getMapper(TestMapper.class);
// The offset offset starts from 0, and the limit checks how many items in total
Integer pageNum =1; // Current page (generally, if you interact with the front end, please accommodate them. The number of pages starts from 1)
Integer pageSize = 10; // Size per page
// If the paging parameter is wrong, it will not be judged first. Judge according to the business
List<TestEntity> res = mapper.selectLimit((pageNum-1)*pageSize, pageSize);
for (int i = 0; i < res.size(); i++) {
System.out.println(res.get(i));
}
}
}
}
Why is it a car? Because it consumes the least system memory and the least network. He will filter out unnecessary rows on the server.
4, Automatic transmission car
Someone said that it's boring to spell limit yourself. Can you be a little more intelligent? Of course, I can give it as long as you want.
Custom parameter base class PageQo:
package entity;
/**
* @author Discover more interesting official account: Muzi's day and night programming
* A code farmer who lives at the bottom of the Internet and does addition, deletion, modification and inspection, is not familiar with the affectation of the world
* @create 2021-09-21 17:37
*/
public class PageQo {
// Current number of pages: 1
Integer pageNum;
// Size per page
Integer pageSize;
public PageQo() {
}
public PageQo(Integer pageNum, Integer pageSize) {
this.pageNum = pageNum;
this.pageSize = pageSize;
}
public Integer getPageNum() {
return pageNum;
}
public void setPageNum(Integer pageNum) {
this.pageNum = pageNum;
}
public Integer getPageSize() {
return pageSize;
}
public void setPageSize(Integer pageSize) {
this.pageSize = pageSize;
}
}
Custom interceptor MyPageInterceptor:
package interceptor;
import entity.PageInfo;
import entity.PageQo;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.session.Configuration;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.Properties;
/**
* @author Discover more interesting official account: Muzi's day and night programming
* A code farmer who lives at the bottom of the Internet and does addition, deletion, modification and inspection, is not familiar with the affectation of the world
*/
@Intercepts({
@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})
})
public class MyPageInterceptor implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
System.out.println("Execute interceptor code");
// Get the parameter. It is specified here that paging will be performed only if the parameter is a subclass of PageQo, and other paging will not be performed
StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
//Gets the wrapper class of StatementHandler
MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
BoundSql boundSql = statementHandler.getBoundSql();
Object param = boundSql.getParameterObject();
// Only when the parameter is a subclass of PageQo can paging be performed
if(param instanceof PageQo) {
// Forced conversion is mainly for paging parameters
PageQo pageQo = (PageQo) param;
//Get original SQL statement
String originalSql = (String) metaObject.getValue("delegate.boundSql.sql");
System.out.println("primary sql:"+originalSql);
String sql = originalSql.trim() + " limit " + (pageQo.getPageNum()-1)* pageQo.getPageSize() + ", " + pageQo.getPageSize();
System.out.println("paging sql:"+sql);
metaObject.setValue("delegate.boundSql.sql", sql);
}
// Paging logic is not performed by default
return invocation.proceed();
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target,this);
}
@Override
public void setProperties(Properties properties) {
}
}
Configuration file configuration interceptor mybatis-config.xml:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<setting name="logImpl" value="LOG4J"/>
</settings>
<!--Scan package path-->
<typeAliases>
<package name="entity"/>
</typeAliases>
<!--custom interceptor -->
<plugins>
<plugin interceptor="interceptor.MyPageInterceptor"></plugin>
</plugins>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<!--scanning-->
<mappers>
<mapper class="dao.TestMapper"/>
</mappers>
</configuration>
Test:
import dao.TestMapper;
import entity.PageQo;
import entity.TestEntity;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
import java.util.List;
/**
* @author Discover more interesting official account: Muzi's day and night programming
* A code farmer who lives at the bottom of the Internet and does addition, deletion, modification and inspection, is not familiar with the affectation of the world
*/
public class TestMain06 {
public static void main(String[] args) throws Exception {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
try (SqlSession session = sqlSessionFactory.openSession()) {
// Get Mapper through sesson. This Mapper will program the proxy Mapper of Mybatis
TestMapper mapper = session.getMapper(TestMapper.class);
// The offset offset starts from 0, and the limit checks how many items in total
Integer pageNum =1; // Current page (generally, if you interact with the front end, please accommodate them. The number of pages starts from 1)
Integer pageSize = 10; // Size per page
// If the paging parameter is wrong, it will not be judged first. Judge according to the business
List<TestEntity> res = mapper.selectPage(new PageQo(pageNum, pageSize));
for (int i = 0; i < res.size(); i++) {
System.out.println(res.get(i));
}
}
}
}
Output:
Mybatis interceptor is similar to our usual web project interceptors in that it is a mechanism that can be specially extended. Those who have studied the source code of SpringBoot know that in fact, it also has a similar mechanism called PostProcessor, which can customize their own business implementation
You can see that the original SQL has been modified, so this is also a server-side paging, physical paging.
5, Self driving car
PageHelper
He is an expert from the Internet. He has customized the Interceptor, which can be directly introduced and used
When we have the ability, we must provide people with more open source things (blogging is also a way of contribution)
Please pay attention to:
PageHelper.startPage() ; This must be followed by Mapper's calling method,
Introducing pom.xml
<!--pagehelper-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<!--520 It's nice to say to me: I love you-->
<version>5.2.0</version>
</dependency>
Usage 1:
Like the custom Interceptor, mybatis-config.xml is introduced
<!--custom interceptor -->
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor">
</plugin>
</plugins>
import com.github.pagehelper.PageHelper;
import dao.TestMapper;
import entity.TestEntity;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
import java.util.List;
/**
* @author Discover more interesting official account: Muzi's day and night programming
* A code farmer who lives at the bottom of the Internet and does addition, deletion, modification and inspection, is not familiar with the affectation of the world
*/
public class TestMain07 {
public static void main(String[] args) throws Exception {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
try (SqlSession session = sqlSessionFactory.openSession()) {
// Get Mapper through sesson. This Mapper will program the proxy Mapper of Mybatis
TestMapper mapper = session.getMapper(TestMapper.class);
// The offset offset starts from 0, and the limit checks how many items in total
Integer pageNum =1; // Current page (generally, if you interact with the front end, please accommodate them. The number of pages starts from 1)
Integer pageSize = 10; // Size per page
// 1. Most commonly used
// https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/en/HowToUse.md
PageHelper.startPage(pageNum, pageSize);
List<TestEntity> res = mapper.select();
for (int i = 0; i < res.size(); i++) {
System.out.println(res.get(i));
}
}
}
}
Mode of use 2:
Custom pageNum and pageSize parameter names
mybatis-config.xml needs to be modified
<!--custom interceptor -->
<plugins>
<!-- com.github.pagehelper by PageHelper Package name of class -->
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<!-- Use the following method to configure parameters. All parameters will be described later -->
<property name="supportMethodsArguments" value="true"/>
<!--custom key There's nothing to do with a special value. Generally, we use it pageSize pageNum-->
<property name="params" value="pageNum=myPageNum;pageSize=myPageSize;"/>
</plugin>
</plugins>
Mapper
@Select("select * from test")
List<TestEntity> select01(@Param("myPageNum") Integer pageNum, @Param("myPageSize") Integer pageSize);
@Select("select * from test")
List<TestEntity> select02(Map<String, Integer> params);
Test
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import dao.TestMapper;
import entity.TestEntity;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import javax.servlet.ServletRequest;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletRequestWrapper;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @author Discover more interesting official account: Muzi's day and night programming
* A code farmer who lives at the bottom of the Internet and does addition, deletion, modification and inspection, is not familiar with the affectation of the world
*/
public class TestMain08 {
public static void main(String[] args) throws Exception {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
try (SqlSession session = sqlSessionFactory.openSession()) {
// Get Mapper through sesson. This Mapper will program the proxy Mapper of Mybatis
TestMapper mapper = session.getMapper(TestMapper.class);
// The offset offset starts from 0, and the limit checks how many items in total
Integer pageNum =1; // Current page (generally, if you interact with the front end, please accommodate them. The number of pages starts from 1)
Integer pageSize = 10; // Size per page
List<TestEntity> all = mapper.select();
System.out.println("Total number of pieces:"+all.size());
// 1. Parameter mode
// https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/en/HowToUse.md
List<TestEntity> res = mapper.select01(pageNum, pageSize);
System.out.println("Number of pages 01:"+res.size());
// 2. Support ServletRequest, map and POJO objects,
// request is difficult to obtain. Here is an example of Map
Map<String, Integer> params = new HashMap<>();
params.put("myPageNum", pageNum);
params.put("myPageSize", pageSize);
List<TestEntity> res02 = mapper.select02(params);
System.out.println("Number of pages 02:"+res02.size());
// You can see that the return type here is received with a list, but this list is not that list
// In fact, he returned a com.github.pagehelper.Page, which inherited ArrayList
// In addition to the data we see on this page, I think there are many advanced things
// We can directly convert it to the PageInfo provided by him
PageInfo pageInfo = new PageInfo(res02);
System.out.println("Current number of pages pageNum:"+pageInfo.getPageNum());
System.out.println("Page size pageSize:"+pageInfo.getPageSize());
System.out.println("Previous page prePage:"+pageInfo.getPrePage());
System.out.println("How many pages altogether:"+pageInfo.getPages());
System.out.println("Number of current pages:"+pageInfo.getSize());
System.out.println("Total number:"+pageInfo.getTotal());
System.out.println("Number of record start lines:"+pageInfo.getStartRow());
System.out.println("Record end lines:"+pageInfo.getEndRow());
System.out.println("------------Gorgeous dividing line-----------------");
// It's ok to use Page instead
Page page = (Page)res02;
System.out.println("Current number of pages pageNum:"+page.getPageNum());
System.out.println("Page size pageSize:"+page.getPageSize());
System.out.println("How many pages altogether:"+page.getPages());
System.out.println("Number of current pages:"+page.size());
System.out.println("Total number:"+page.getTotal());
System.out.println("Number of record start lines:"+page.getStartRow());
System.out.println("Record end lines:"+page.getEndRow());
}
}
}
Other usage:
It can also specify whether to use the count statement to count the total number of entries. My colleagues have encountered before. Because the count consumes a lot of performance, it is only counted once at the first query. If the front end of the query brings the count again, the count query will not be carried out here
6, Nagging
Some people think that PageHelper is very magical. Why does the method below know the page when startPage is called, and it can also get the page parameters.
Here, a practical knowledge point is called ThreadLocal
startPage actually puts the parameters in ThreadLocal. When you go to the Interceptor, you will find the corresponding parameters in ThreadLocal.
This knowledge point will be written in the future to briefly talk about the use of ThreadLocal. This knowledge point will also be used when the project needs to dynamically switch data sources.
There's nothing to do with the official account: Muzi's day and night programming.