Paging can be divided into logical paging and physical paging.

Logical paging is that when our program displays the data of each page,

First, query 1000 pieces of data in the table, and then select 100 pieces of data to display according to the "page number" of the current page.

Physical paging is that the program first judges the number to which of the 1000 items should be selected,

Then, according to the information given by the program, the database queries out 100 items required by the program and returns them to our program.

2.MyBatis physical paging

Implement logical paging:

The paging implemented by MyBatis using rowboundaries is logical paging,

That is, first query all the data records, and then truncate the records according to offset and limit.

Physical paging

In order to realize physical paging at the database level without changing the original MyBatis function logic,

You can write a plugin to intercept the statement handler of MyBatis Executor and rewrite SQL to execute the query.

3. Development steps

Step 1: function description

Use the integration of MyBatis and Spring MVC to complete paging, and complete such a simple function, that is, specify a user (ID=1),

Query all orders associated with this user and display them in pages (using MySQL database)

Step 2: create a project

          Configuration.xml

           applicationContext.xml

Step 3: database table structure and data record

Step 4: instance object

Step 5: configuration file

            applicationContext.xml

           Configuration.xml

           UserMaper.xml 

Step 6: test execution and output results

Tool class: PagePlugin java，Page.java, PageHelper.java, where PagePlugin is a plug-in for MyBatis paging.

        PagePlugin:

@Intercepts( { @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class }) })
	public class PagePlugin implements Interceptor {

		private static String dialect = "";
		private static String pageSqlId = "";

		@SuppressWarnings("unchecked")
		public Object intercept(Invocation ivk) throws Throwable {

			if (ivk.getTarget() instanceof RoutingStatementHandler) {
				RoutingStatementHandler statementHandler = (RoutingStatementHandler) ivk
						.getTarget();
				BaseStatementHandler delegate = (BaseStatementHandler) ReflectHelper
						.getValueByFieldName(statementHandler, "delegate");
				MappedStatement mappedStatement = (MappedStatement) ReflectHelper
						.getValueByFieldName(delegate, "mappedStatement");

				if (mappedStatement.getId().matches(pageSqlId)) {
					BoundSql boundSql = delegate.getBoundSql();
					Object parameterObject = boundSql.getParameterObject();
					if (parameterObject == null) {
						throw new NullYiibaierException("parameterObject error");
					} else {
						Connection connection = (Connection) ivk.getArgs()[0];
						String sql = boundSql.getSql();
						String countSql = "select count(0) from (" + sql
								+ ") myCount";
						System.out.println("total sql sentence:" + countSql);
						PreparedStatement countStmt = connection
								.prepareStatement(countSql);
						BoundSql countBS = new BoundSql(mappedStatement
								.getConfiguration(), countSql, boundSql
								.getParameterMappings(), parameterObject);
						setParameters(countStmt, mappedStatement, countBS,
								parameterObject);
						ResultSet rs = countStmt.executeQuery();
						int count = 0;
						if (rs.next()) {
							count = rs.getInt(1);
						}
						rs.close();
						countStmt.close();

						Page page = null;
						if (parameterObject instanceof Page) {
							page = (Page) parameterObject;
							page.setTotalResult(count);
						} else if (parameterObject instanceof Map) {
							Map<String, Object> map = (Map<String, Object>) parameterObject;
							page = (Page) map.get("page");
							if (page == null)
								page = new Page();
							page.setTotalResult(count);
						} else {
							Field pageField = ReflectHelper.getFieldByFieldName(
									parameterObject, "page");
							if (pageField != null) {
								page = (Page) ReflectHelper.getValueByFieldName(
										parameterObject, "page");
								if (page == null)
									page = new Page();
								page.setTotalResult(count);
								ReflectHelper.setValueByFieldName(parameterObject,
										"page", page);
							} else {
								throw new NoSuchFieldException(parameterObject
										.getClass().getName());
							}
						}
						String pageSql = generatePageSql(sql, page);
						System.out.println("page sql:" + pageSql);
						ReflectHelper.setValueByFieldName(boundSql, "sql", pageSql);
					}
				}
			}
			return ivk.proceed();
		}

		private void setParameters(PreparedStatement ps,
				MappedStatement mappedStatement, BoundSql boundSql,
				Object parameterObject) throws SQLException {
			ErrorContext.instance().activity("setting parameters").object(
					mappedStatement.getParameterMap().getId());
			List<ParameterMapping> parameterMappings = boundSql
					.getParameterMappings();
			if (parameterMappings != null) {
				Configuration configuration = mappedStatement.getConfiguration();
				TypeHandlerRegistry typeHandlerRegistry = configuration
						.getTypeHandlerRegistry();
				MetaObject metaObject = parameterObject == null ? null
						: configuration.newMetaObject(parameterObject);
				for (int i = 0; i < parameterMappings.size(); i++) {
					ParameterMapping parameterMapping = parameterMappings.get(i);
					if (parameterMapping.getMode() != ParameterMode.OUT) {
						Object value;
						String propertyName = parameterMapping.getProperty();
						PropertyTokenizer prop = new PropertyTokenizer(propertyName);
						if (parameterObject == null) {
							value = null;
						} else if (typeHandlerRegistry
								.hasTypeHandler(parameterObject.getClass())) {
							value = parameterObject;
						} else if (boundSql.hasAdditionalParameter(propertyName)) {
							value = boundSql.getAdditionalParameter(propertyName);
						} else if (propertyName
								.startsWith(ForEachSqlNode.ITEM_PREFIX)
								&& boundSql.hasAdditionalParameter(prop.getName())) {
							value = boundSql.getAdditionalParameter(prop.getName());
							if (value != null) {
								value = configuration.newMetaObject(value)
										.getValue(
												propertyName.substring(prop
														.getName().length()));
							}
						} else {
							value = metaObject == null ? null : metaObject
									.getValue(propertyName);
						}
						TypeHandler typeHandler = parameterMapping.getTypeHandler();
						if (typeHandler == null) {
							throw new ExecutorException(
									"There was no TypeHandler found for parameter "
											+ propertyName + " of statement "
											+ mappedStatement.getId());
						}
						typeHandler.setParameter(ps, i + 1, value, parameterMapping
								.getJdbcType());
					}
				}
			}
		}

		private String generatePageSql(String sql, Page page) {
			if (page != null && (dialect != null || !dialect.equals(""))) {
				StringBuffer pageSql = new StringBuffer();
				if ("mysql".equals(dialect)) {
					pageSql.append(sql);
					pageSql.append(" limit " + page.getCurrentResult() + ","
							+ page.getShowCount());
				} else if ("oracle".equals(dialect)) {
					pageSql
							.append("select * from (select tmp_tb.*,ROWNUM row_id from (");
					pageSql.append(sql);
					pageSql.append(")  tmp_tb where ROWNUM<=");
					pageSql.append(page.getCurrentResult() + page.getShowCount());
					pageSql.append(") where row_id>");
					pageSql.append(page.getCurrentResult());
				}
				return pageSql.toString();
			} else {
				return sql;
			}
		}

		public Object plugin(Object arg0) {
			// TODO Auto-generated method stub
			return Plugin.wrap(arg0, this);
		}

		public void setProperties(Properties p) {
			dialect = p.getProperty("dialect");
			if (dialect == null || dialect.equals("")) {
				try {
					throw new PropertyException("dialect property is not found!");
				} catch (PropertyException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			pageSqlId = p.getProperty("pageSqlId");
			if (dialect == null || dialect.equals("")) {
				try {
					throw new PropertyException("pageSqlId property is not found!");
				} catch (PropertyException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}

	}

Results:

Learning source: https://www.yiibai.com/mybatis/mybatis_pagination.html#article-start