Hello everyone, meet again, I'm your friend Quanzhanjun.
In mybatis, it is very convenient to use RowBounds for paging. You can complete the paging function without writing limit in the sql statement. However, since it intercepts data on the basis of all the results of the sql query, it is not suitable for sql with a large amount of data. It is more suitable for use in queries that return fewer data results.
The core is at the mapper interface layer, pass in the RowBounds(int offset, int limit) object when passing parameters to complete the paging
Note: Since the maximum integer allowed by java is 2147483647, the maximum integer that can be used by limit is also 2147483647. Taking out a large amount of data at one time may cause memory overflow, so use it with caution in big data query occasions
The mapper interface layer code is as follows
copyList<Book> selectBookByName(Map<String, Object> map, RowBounds rowBounds);
call as follows
copyList<Book> list = bookMapper.selectBookByName(map, new RowBounds(0, 5));
Description: new RowBounds(0, 5), that is, the first page, each page takes 5 pieces of data
Test example
database data

mapper interface layer
copy@Mapper public interface BookMapper { //adding data int insert(Book book); //fuzzy query List<Book> selectBookByName(Map<String, Object> map, RowBounds rowBounds); }
mapper.xml file
copy<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > <mapper namespace="com.demo.mapper.BookMapper"> <resultMap id="BaseResultMap" type="com.demo.bean.Book"> <id column="id" property="id" jdbcType="VARCHAR" /> <result column="book_name" property="bookName" jdbcType="VARCHAR" /> <result column="book_author" property="bookAuthor" jdbcType="VARCHAR" /> <result column="create_date" property="createDate" jdbcType="VARCHAR" /> <result column="update_date" property="updateDate" jdbcType="VARCHAR" /> </resultMap> <sql id="Base_Column_List"> book_name as bookName, book_author as bookAuthor, create_date as createDate, update_date as updateDate </sql> <insert id="insert" useGeneratedKeys="true" keyProperty="id" parameterType="com.demo.bean.Book"> insert into book(book_name, book_author, create_date, update_date) values(#{bookName}, #{bookAuthor}, #{createDate}, #{updateDate}) </insert> <select id="selectBookByName" resultMap="BaseResultMap"> <bind name="pattern_bookName" value="'%' + bookName + '%'" /> <bind name="pattern_bookAuthor" value="'%' + bookAuthor + '%'" /> select * from book where 1 = 1 <if test="bookName != null and bookName !=''"> and book_name LIKE #{pattern_bookName} </if> <if test="bookAuthor != null and bookAuthor !=''"> and book_author LIKE #{pattern_bookAuthor} </if> </select> </mapper>
test code
copy@RunWith(SpringRunner.class) @SpringBootTest public class SpringbootJspApplicationTests { @Autowired private BookMapper bookMapper; @Test public void contextLoads() { Book book = new Book(); book.setBookName("Sui and Tang Dynasties"); book.setBookAuthor("Chu Renhuo"); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); book.setCreateDate(sdf.format(new Date())); book.setUpdateDate(sdf.format(new Date())); bookMapper.insert(book); System.out.println("primary key returned: "+book.getId()); } @Test public void query() { Map<String, Object> map = new HashMap<String, Object>(); map.put("bookName", ""); map.put("bookAuthor", ""); List<Book> list = bookMapper.selectBookByName(map, new RowBounds(0, 5)); for(Book b : list) { System.out.println(b.getBookName()); } } }
Run query to query the first page, 5 pieces of data, the effect is as follows

Mybatis provides a simple logical paging class using RowBounds (physical paging, of course, is that we specify limit and offset values in the sql statement). In some query interfaces provided by DefaultSqlSession, we can see that RowBounds is used as a parameter for paging , the following interface:
copypublic <E> List<E> selectList(String statement, Object parameter, RowBounds rowBounds)
The RowBounds source code is as follows:
- public class RowBounds {
- /* The default offset is 0**/
- public static final int NO_ROW_OFFSET = 0;
- /* The default Limit is the maximum value of int, so it uses logical paging **/
- public static final int NO_ROW_LIMIT = Integer.MAX_VALUE;
- public static final RowBounds DEFAULT = new RowBounds();
- private int offset;
- private int limit;
- public RowBounds() {
- this.offset = NO_ROW_OFFSET;
- this.limit = NO_ROW_LIMIT;
- }
- public RowBounds(int offset, int limit) {
- this.offset = offset;
- this.limit = limit;
- }
- public int getOffset() {
- return offset;
- }
- public int getLimit() {
- return limit;
- }
- }
The implementation principle of logical paging:
In DefaultResultSetHandler, logical paging will query all the results, and then obtain the final result according to the offset and limit values provided in RowBounds. DefaultResultSetHandler is implemented as follows:
- private void handleRowValuesForSimpleResultMap(ResultSetWrapper rsw, ResultMap resultMap, ResultHandler<?> resultHandler, RowBounds rowBounds, ResultMapping parentMapping)
- throws SQLException {
- DefaultResultContext<Object> resultContext = new DefaultResultContext<Object>();
- //Skip the offset value set by RowBounds
- skipRows(rsw.getResultSet(), rowBounds);
- / / Determine whether the data is less than the limit, if it is less than the limit, it will continue to loop the value
- 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());
- }
- }
- private boolean shouldProcessMoreRows(ResultContext<?> context, RowBounds rowBounds) throws SQLException {
- / / Determine whether the data is less than the limit, less than return true
- return !context.isStopped() && context.getResultCount() < rowBounds.getLimit();
- }
- //Skip unnecessary rows, it should be the limit and offset set by rowbounds
- 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 {
- //Skip the offset bar data set in RowBounds
- for (int i = 0; i < rowBounds.getOffset(); i++) {
- rs.next();
- }
- }
- }
Summary: The logical paging of Mybatis is relatively simple. In short, it takes out all the data that meets the conditions, then discards the previous offset data, and then takes the limit of the remaining data.
Copyright statement: The content of this article is contributed by Internet users, and the opinions of this article only represent the author himself. This site only provides information storage space services, does not own ownership, and does not assume relevant legal responsibilities. If you find any content suspected of infringing/violating laws and regulations on this site, please send an email to report. Once verified, this site will be deleted immediately.
Publisher: Full-stack programmer, please indicate the source: https://javaforall.cn/192715.html Original link: https://javaforall.cn