优化分页查询

在数据量足够大，数据上百页的时候，使用 Mybatis 的PageHelper实现分页，效果还是挺拉胯的，可以尝试使用下面的方式来进行优化

1、controller 层代码

import com.github.pagehelper.PageInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping("/users")
    public PageInfo<User> getUsers(@RequestParam(value = "pageNo", defaultValue = "1") Integer pageNo,
                                   @RequestParam(value = "pageSize", defaultValue = "10") Integer pageSize) {
                                   
        return userService.getUsersByPageNo(pageNo, pageSize);
    }
}

2、service 层代码

@Service
public class UserService {

	@Autowired
    private UserMapper userMapper;

    public PageInfo<User> getUsersByPageNo(int pageNo, int pageSize) {
        // 计算last_id，这里假设id是自增的，并且每个用户的id是唯一的
        int lastId = (pageNo - 1) * pageSize + 1;
        List<User> users = userMapper.selectUsersByLastId(lastId, pageSize);
        PageInfo<User> pageInfo = new PageInfo<>(users);
        pageInfo.setTotal(userMapper.countAll());
        pageInfo.setPageSize(pageSize);
        pageInfo.setPageNum(pageNo);
        return pageInfo;
    }
}

3、mapper层代码

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface UserMapper {

    @Select("SELECT * FROM user WHERE id > #{lastId} ORDER BY id ASC LIMIT #{pageSize}")
    List<User> selectUsersByLastId(@Param("lastId") int lastId, @Param("pageSize") int pageSize);

    @Select("SELECT COUNT(*) FROM user")
    int countAll();
}

这样一定程度上加快响应

习惯向左，感觉至上