Springboot — Table operation 02

It is not recommended to use mybatis pagination query or PageHelper plug-in. It is more convenient to use MySql query statement.

Based on the above table operation, add paging operation, improve the core code. CRUD and file import and export.

process

The backend development

1. Define entity classes that are uniformly returned by paging queries and return data as lists. Generics are recommended.

(RespPageEntity.java)

@Data
public class RespPageEntity {
    privateList<? > data;private Long total;
}
Copy the code

2. Define the control layer method. To avoid null values, give default values in the request parameters.

(UserController. Java)

/** ** query by page number and size *@paramPage Indicates the current page. The default value is 1 *@paramSize Number of lines displayed on a page. The default value is 5 *@returnPage information entity */
@GetMapping("/page/")
public RespPageEntity getAllUserByPage(@RequestParam(defaultValue = "1") Integer page, @RequestParam(defaultValue = "5") Integer size) {
    return userService.getAllUserByPage(page, size);
}
Copy the code

Transactional Transactional method (Transactional) ¶ The Transactional method has two steps: 1, fetch the total data, 2, fetch the query page information.

(UserService.java)

@Transactional
public RespPageEntity getAllUserByPage(Integer page, Integer size) {
    RespPageEntity pageEntity = new RespPageEntity();
    // Starts from 0 by default
    if(page ! =null&& size ! =null) {
        page = (page-1)*size;
    }
    // Get the current user information
    List<User> users =  userMapper.getAllUserByPage(page, size);
    pageEntity.setData(users);
    // Obtain the total number of current users
    Long total = userMapper.getTotal();
    pageEntity.setTotal(total);
    return pageEntity;
}
Copy the code

4. SQL statements in mapping files.

(UserMapper.xml)

<select id="getAllUserByPage" resultMap="BaseResultMap">
  select
    *
  FROM
    userinfo
  limit #{page}, #{size}
</select>
<select id="getTotal" resultType="java.lang.Long">
  select count(*) from userinfo;
</select>
Copy the code

5, the postman test, such as address http://127.0.0.1:8081/user/page/ to return to the default value or http://127.0.0.1:8081/user/page/? page=1&size=5

The front-end perfect

Add paginated div (user.vue) to template temporary component

<div style="display: flex; justify-content: center; margin-top: 10px"> <el-pagination background @size-change="sizeChange" @current-change="currentChange" :current-page="currentPage" :page-size="pageSize" layout="sizes, prev, pager, next, jumper, ->, total, slot" :total="total"> </el-pagination> </div>Copy the code

2. Initial value

pageSize:5,
currentPage:1,
total:0,
Copy the code

3. The number of pages displayed and the current page number

sizeChange(size) {
  this.pageSize=size;
  this.initUser();
},
currentChange(page) {
  this.currentPage=page;
  this.initUser();
},
Copy the code

4. Modify the initialization page method

initUser() { this.getRequest("/user/page/? page="+this.currentPage+"&size="+this.pageSize).then(resp => { if (resp) { this.userinfo=resp.data; this.total=resp.total; }})}Copy the code

Effect picture after operation