Mybatis RowBounds paging principle "recommended collection"

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

List<Book> selectBookByName(Map<String, Object> map, RowBounds rowBounds);
copy

call as follows

List<Book> list = bookMapper.selectBookByName(map, new RowBounds(0, 5));
copy

Description: new RowBounds(0, 5), that is, the first page, each page takes 5 pieces of data

Test example

database data

mapper interface layer

@Mapper

public interface BookMapper {


//adding data

int insert(Book book);


//fuzzy query

List<Book> selectBookByName(Map<String, Object> map, RowBounds rowBounds);


}
copy

mapper.xml file

<?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>
copy

test code

@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());

}

}


}
copy

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:

 public <E> List<E> selectList(String statement, Object parameter, RowBounds rowBounds)
copy

The RowBounds source code is as follows:

  1. public class RowBounds {
  3. /* The default offset is 0**/
  4. public static final int NO_ROW_OFFSET = 0;
  6. /* The default Limit is the maximum value of int, so it uses logical paging **/
  7. public static final int NO_ROW_LIMIT = Integer.MAX_VALUE;
  8. public static final RowBounds DEFAULT = new RowBounds();
  10. private int offset;
  11. private int limit;
  13. public RowBounds() {
  14. this.offset = NO_ROW_OFFSET;
  15. this.limit = NO_ROW_LIMIT;
  16. }
  18. public RowBounds(int offset, int limit) {
  19. this.offset = offset;
  20. this.limit = limit;
  21. }
  23. public int getOffset() {
  24. return offset;
  25. }
  27. public int getLimit() {
  28. return limit;
  29. }
  31. }

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:

  1. private void handleRowValuesForSimpleResultMap(ResultSetWrapper rsw, ResultMap resultMap, ResultHandler<?> resultHandler, RowBounds rowBounds, ResultMapping parentMapping)
  2. throws SQLException {
  3. DefaultResultContext<Object> resultContext = new DefaultResultContext<Object>();
  4. //Skip the offset value set by RowBounds
  5. skipRows(rsw.getResultSet(), rowBounds);
  6. / / Determine whether the data is less than the limit, if it is less than the limit, it will continue to loop the value
  7. while (shouldProcessMoreRows(resultContext, rowBounds) && rsw.getResultSet().next()) {
  8. ResultMap discriminatedResultMap = resolveDiscriminatedResultMap(rsw.getResultSet(), resultMap, null);
  9. Object rowValue = getRowValue(rsw, discriminatedResultMap);
  10. storeObject(resultHandler, resultContext, rowValue, parentMapping, rsw.getResultSet());
  11. }
  12. }
  13. private boolean shouldProcessMoreRows(ResultContext<?> context, RowBounds rowBounds) throws SQLException {
  14. / / Determine whether the data is less than the limit, less than return true
  15. return !context.isStopped() && context.getResultCount() < rowBounds.getLimit();
  16. }
  17. //Skip unnecessary rows, it should be the limit and offset set by rowbounds
  18. private void skipRows(ResultSet rs, RowBounds rowBounds) throws SQLException {
  19. if (rs.getType() != ResultSet.TYPE_FORWARD_ONLY) {
  20. if (rowBounds.getOffset() != RowBounds.NO_ROW_OFFSET) {
  21. rs.absolute(rowBounds.getOffset());
  22. }
  23. } else {
  24. //Skip the offset bar data set in RowBounds
  25. for (int i = 0; i < rowBounds.getOffset(); i++) {
  26. rs.next();
  27. }
  28. }
  29. }

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

Tags: node.js SQL Android http

Posted by Jebs on Fri, 30 Sep 2022 17:11:08 +0930

Popular Tags

©2023 Programs Wiki