Paging query

Paging query can be divided into front-end paging and back-end paging. Front-end paging refers to the back-end directly responds all the data of the database table to the front-end and is displayed by the front-end paging. If the amount of data created in this way is relatively large, the consumption of memory and network transmission is very large. Back-end paging means that the front end initiates paging requests and sends relevant paging data to the back end (such as pageNum, PageSize, etc.), and the back end returns the data after paging to the front end according to the relevant data of paging, so as to achieve paging. Generally, back-end paging is adopted.

MyBatis paging

MyBatis has physical paging and logical paging. Physical paging refers to fetching data directly from the database, such as using limit in Mysql. Logical paging, on the other hand, means taking all the data that meets the requirements from the database and then retrieving the paging data that we need from that data.

Physical pages

Use limit paging (physical paging)

MySQL provides the limit specified keyword can help us return to our queries among the first few lines or a few rows of data, so he can be applied to our page, use limit need one or two given parameters, the first parameter specifies the first return rows offsets, the second parameter specifies the return to the record for the largest number, For example, if you want to return four rows from the first page, if you want to return four rows from the second page, then you want to return four rows from the second page, then you want to return four rows from the second page, then you want to return four rows from the third page, then you want to return four rows from the third page. Limit (pagenum-1)*pageSize, pageSize If only one argument is specified, the maximum number of rows will be returned. For example, limit 10, which is the same as limit 0,10, which returns 10 rows from lines 1 through 10.

Use process

  • Table data

  • Entity class
@Data
public class User {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;
}
Copy the code
  • The SQL statement
<select id="listUser" resultMap="BaseResultMap"> select * from user <if test="pageNum! =null and pageSize! =null"> limit #{pageNum},#{pageSize} </if> </select>Copy the code
  • Mapper method
List<User> listUser(Integer pageNum,Integer pageSize);
Copy the code
  • Test, take out the first page of four data
@test void contextLoads() {List<User> users = userdao.listuser (0, 4); for (User user : users) { System.out.println(user.toString()); }}Copy the code
  • The query results

  • Test, take out the second page of four data
@test void contextLoads() {List<User> users = userdao.listuser (4, 4); for (User user : users) { System.out.println(user.toString()); }}Copy the code
  • The query results

Using the PageHelper paging plug-in (physical paging)

Use process

  • Introducing PageHelper dependencies in POM files
<dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper-spring-boot-starter</artifactId> The < version > 1.2.3 < / version > < / dependency >Copy the code
  • The SQL statement
<! --pageHelper--> <select id="listUser2" resultMap="BaseResultMap"> select * from user </select>Copy the code
  • Mapper method
List<User> listUser2();
Copy the code
  • To test, extract the first page of 4 rows of data, wherePageHelper.startPage()The pagination () method is used to turn on paging and limit queries by intercepting any SQL executed after the method.
@test void test2() {//pageNum =1; Int pageSize=4; PageHelper.startPage(pageNum,pageSize); List<User> users = userDao.listUser2(); PageInfo<User> page = new PageInfo<>(users); for (User user : page.getList()) { System.out.println(user.toString()); }}Copy the code
  • The query results

  • To test, extract 4 rows of data from the second page
@test void test2() {//pageNum = 1; Int pageSize=4; PageHelper.startPage(pageNum,pageSize); List<User> users = userDao.listUser2(); PageInfo<User> page = new PageInfo<>(users); for (User user : page.getList()) { System.out.println(user.toString()); }}Copy the code
  • The query results

MyBatis Plus paging

MyBatisPls paging is based on MyBatis physical paging, developers do not need to care about the specific operation, after configuring the plugin, write paging is the same as ordinary List query.

Using the step

  • SpringBoot introduces the MyBatis- Plus dependency
< the dependency > < groupId > com. Baomidou < / groupId > < artifactId > mybatis - plus - the boot - starter < / artifactId > < version > 3.3.2 rainfall distribution on 10-12 < / version >  </dependency>Copy the code
  • Configure the YML configuration file
Spring: # configuration data source datasource: driver - class - name: the mysql. Cj.. JDBC driver url: JDBC: mysql: / / localhost: 3306 / students? ServerTimezone = Asia/Shanghai username: root password: root Classpath *:/mapper/* mapper.xml # configure mybatis data return type alias type-aliases-package: com.example.mp.pojo configuration: Map-underscore: level: com.example.mp.mapper: debug server: port: 8081Copy the code
  • configurationPaginationInterceptorPage interceptor component
@Configuration public class MyBatisPlusConfig { @Bean public PaginationInterceptor paginationInterceptor(){ return new PaginationInterceptor(); }}Copy the code
  • Define the Mapper interface
public interface UserMapper extends BaseMapper<User> {
}
Copy the code
  • Test, get the first Page of data, 4 rows per Page (🚨 note: here in the PagecurrentRepresents the current page number, starting with page number 1,sizeData volume per page)

  • The query results

Custom paging

Above our Mapper interface method does not define any methods, we use MyBatisPlus provided paging method selectPage(Page Page,QueryWrapper QueryWrapper), below we use custom paging method.

  • Mapper interface method
public interface UserMapper extends BaseMapper<User> {

    IPage<User> slefPageUser(Page<User> page, User user);
}
Copy the code
  • The SQL statement
<select id="slefPageUser" resultMap="BaseResultMap"> select * from user <where> <if test="user! =null and user.username! =null"> and username=#{user.username} </if> <if test="user! =null and user.address! =null"> and address=#{user.address} </if> <if test="user! =null and user.birthday! =null"> and birthday=#{user.birthday} </if> <if test="user! =null and user.sex! =null"> and sex=#{user.sex} </if> </where> </select>Copy the code
  • Test, query the first page,usernameforLoves drinking milk tea ProgrammerThe User of
Page<User> page = new Page<>(1, 4); User user = new User(); User1. setUsername(" Programmer who likes milk tea "); IPage<User> userPage = userMapper.slefPageUser(page,user); List<User> users = userPage.getRecords(); for (User u : users) { System.out.println(u.toString()); }Copy the code
  • The query results

Logic paging

RowBounds paging (logical paging)

RowBounds paging is logical paging, which means that the SQL query actually queries all the data and then retrievable the data at the business level (paging the ResultSet). This method consumes memory and is not recommended for large data loads.

  • The RowBounds object has two properties,offsetandlimit.Offset indicates the number of starting rows, and limit indicates the number of required data rows, they combine to mean that starting at row offset+1, take the data from the limit row.

Use process

  • The SQL statement
<! --RowBounds--> <select id="listUser3" resultMap="BaseResultMap"> select * from user </select>Copy the code
  • Mapper method
List<User> listUser3(RowBounds rowbouns);
Copy the code
  • To test, take 4 rows of data from the first page
@Test void test3() { RowBounds rowBounds = new RowBounds(0, 4); List<User> users = userDao.listUser3(rowBounds); for (User user : users) { System.out.println(user.toString()); }}Copy the code
  • To test, take 4 rows of data on the second page, that is, lines 5 through 8
@Test void test3() { RowBounds rowBounds = new RowBounds(4, 4); List<User> users = userDao.listUser3(rowBounds); for (User user : users) { System.out.println(user.toString()); }}Copy the code

🏁 the above is a simple introduction to several kinds of pagination, you learn to “waste”? 🌚, if there is a mistake, please leave a comment, if you think this article is helpful to you, then move your little finger, click a like 👍, 😋 college