• java后端分页的多种操作你必须要知道


    为什么要使用分页?我们服务器的数据jvm内存有限制,数据不能全部返回,必须采用分页或者拆分的方式进行分页这样不仅仅提高搜索和查询的效率,也能够减少前后端的带宽传输。

    1,原生的jdbc分页

    原生的分页通过获取jdbc连接然后通过sql预处理传入分页参数获取分页参数,其实也是用了limit 函数分页

    2,集合分页。

    集合分页常用的几种方式:

    截取数据     

    List subList(int fromIndex, int toIndex);

    下标获取数据:list.get(i);获取起始位置下标去读取数据

                                    

    java8函数分页:

    1. list.stream().skip((i - 1) * sheetSize).limit(sheetSize). collect(Collectors.toList());
    2. List newList2 = newList.stream().skip(( 1-1)* 2).limit(2).collect(Collectors.toList());
    3. List newList3 = newList.stream().skip(( 2 -1)*2).limit(2).collect(Collectors.toList());
    4. List newList4= newList.stream().skip(( 3 -1)*2).limit(2).collect(Collectors.toList());

    3,pagehelper分页

    第一种:springboot引入包的方式:


        com.github.pagehelper
        pagehelper-spring-boot-starter
        ${pagehelper.version}


    第二种:直接引入包的方式:

    
          com.github.pagehelper
          pagehelper
          5.2.0
     

    配置文件:

    1. #pagehelper分页助手
    2. pagehelper:
    3. helperDialect: mysql
    4. reasonable: false
    5. supportMethodsArguments: true
    6. params: count=countSql
    1. PageHelper.startPage(pageNum,pageSize);
    2. List list = studentService.findAllStudent();
    3. PageInfo pageInfo = PageInfo.of(list);

     

    4,mybateisplus 内置分页。

    1. @ApiOperation(value = "vin查询")
    2. @PostMapping("/chagreVinSearch")
    3. public ResultMsg chagreVinSearch(@RequestBody ChagerVinDto chagerVinDto) {
    4. return chagreVinService.chagreVinSearch(chagerVinDto);
    5. }
    1. import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
    2. import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
    3. import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
    4. import com.baomidou.mybatisplus.core.metadata.IPage;
    5. import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
    6. import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
    7. import lombok.extern.slf4j.Slf4j;
    8. import org.apache.commons.lang3.StringUtils;
    9. import org.springframework.beans.factory.annotation.Autowired;
    10. import org.springframework.stereotype.Service;
    11. import org.springframework.web.multipart.MultipartFile;
    12. import java.sql.SQLException;
    13. import java.sql.Wrapper;
    14. import java.time.LocalDateTime;
    15. import java.util.List;
    16. import java.util.stream.Collectors;
    17. /**
    18. * @author liuhan
    19. * @version 1.0
    20. * @description: TODO
    21. * @date 2022/7/27 10:34
    22. */
    23. @Service
    24. @Slf4j
    25. public class ChagreVinServiceImpl extends ServiceImpl implements ChagreVinService {
    26. @Autowired
    27. TcChargerFilterMapper tcChargerFilterMapper;
    28. @Override
    29. public ResultMsg chagreVinSearch(ChagerVinDto chagerVinDto) {
    30. //分页参数
    31. Page page = Page.of(chagerVinDto.getPageNum(),chagerVinDto.getPageSize());
    32. //queryWrapper组装查询where条件
    33. LambdaQueryWrapper queryWrapper = new LambdaQueryWrapper<>();
    34. if(StringUtils.isNotBlank(chagerVinDto.getPlateNo())){
    35. queryWrapper.like(TcChargerFilterVO::getPlateNo,chagerVinDto.getPlateNo());
    36. }
    37. if(StringUtils.isNotBlank(chagerVinDto.getVin())){
    38. queryWrapper.eq(TcChargerFilterVO::getVin,chagerVinDto.getVin());
    39. }
    40. IPage tcChargerFilterVOIPage = tcChargerFilterMapper.selectPage(page,queryWrapper);
    41. ChagerVinDto resultDto = new ChagerVinDto();
    42. resultDto.setResult(tcChargerFilterVOIPage.getRecords());
    43. resultDto.setTotal(tcChargerFilterVOIPage.getTotal());
    44. return ResultMsg.ok(resultDto);
    45. }
    46. }
    1. import com.baomidou.mybatisplus.core.mapper.BaseMapper;
    2. import org.apache.ibatis.annotations.Mapper;
    3. import java.util.List;
    4. @Mapper
    5. public interface TcChargerFilterMapper extends BaseMapper {
    6. }
    1. import java.io.Serializable;
    2. import java.time.LocalDateTime;
    3. import java.util.Date;
    4. import com.alibaba.excel.annotation.ExcelProperty;
    5. import com.baomidou.mybatisplus.annotation.TableName;
    6. import lombok.Data;
    7. /**
    8. * tc_charger_filter
    9. * @author
    10. */
    11. @Data
    12. @TableName("tc_charger_filter")
    13. public class TcChargerFilterVO extends BaseEntity implements Serializable {
    14. /**
    15. * 主键id
    16. */
    17. private Integer id;
    18. /**
    19. * vin码
    20. */
    21. @ExcelProperty(value = {"vin"},order = 1)
    22. private String vin;
    23. /**
    24. * 车牌号
    25. */
    26. @ExcelProperty(value = {"车牌号"},order = 2)
    27. private String plateNo;
    28. /**
    29. * 创建人
    30. */
    31. private String createBy;
    32. /**
    33. * 创建时间
    34. */
    35. private LocalDateTime createTime;
    36. /**
    37. * 更新人
    38. */
    39. private String updateBy;
    40. /**
    41. * 更新时间
    42. */
    43. private LocalDateTime updateTime;
    44. }

    1. import org.springframework.web.multipart.MultipartFile;
    2. /**
    3. *

    4. *
    5. * @author yangchang
    6. * @since 2022-09-05
    7. */
    8. public interface ChagreVinService {
    9. ResultMsg chagreVinSearch(ChagerVinDto chagerVinDto);
    10. }

    5,SpringDataJPA 分页

    1. resultPage = template.queryForPage(searchQuery, ManualEngDirEntity.class, searchResultMapper);
    2. // 获取集合数据
    3. content = resultPage.getContent();
    4. MapperFactory mapperFactory = new DefaultMapperFactory.Builder().build();
    5. List directoryDtoList = mapperFactory.getMapperFacade().mapAsList(content, DirectoryDto.class);
    1. @Query("select u from User u")
    2. Page findALL(Pageable pageable);

    6,Spring JdbcTemplate 分页

    1. public Result getTrafficEventinfoList(RafficEventinfoDto rafficEventinfoDto) {
    2. Object[] args = new Object[1];
    3. Integer page = rafficEventinfoDto.getPageNum();
    4. Integer pageSize = rafficEventinfoDto.getPageSize();
    5. StringBuffer sbdata = new StringBuffer();
    6. StringBuffer sbCount = new StringBuffer();
    7. String sqlCount = "select count(1) from TRAFFIC_EVENTINFO where";
    8. sbdata.append("select ee.* from (select e.*,rownum rowno from (select * from TRAFFIC_EVENTINFO where ");
    9. sbCount.append(sqlCount);
    10. if (rafficEventinfoDto.getPlateNo() != null) {
    11. sbdata.append(" PLATENO=? ");
    12. sbCount.append(" PLATENO=? ");
    13. args[0] = rafficEventinfoDto.getPlateNo();
    14. }
    15. sbdata.append(" ) e ) ee where ee.rowno >=" + (((page <= 0 ? 1 : page) - 1) * pageSize + 1) + " and ee.rowno <=" + (page <= 0 ? 1 : page) * pageSize);
    16. Integer total = (Integer)this.jdbc.queryForObject(sbCount.toString(), args, Integer.class);
    17. if (total == 0) {
    18. return Result.ok((Object)null);
    19. } else {
    20. List list = this.jdbc.query(sbdata.toString(), args, new TrafficEventinfoResultRowMapper());
    21. PageInfo pageInfo = new PageInfo(list);
    22. RafficEventinfoDto resultDto = new RafficEventinfoDto();
    23. resultDto.setTotal(total);
    24. resultDto.setResultList(pageInfo.getList());
    25. return Result.ok(resultDto);
    26. }
    27. }

    mapper层:

    1. import org.slf4j.Logger;
    2. import org.slf4j.LoggerFactory;
    3. import org.springframework.jdbc.core.RowMapper;
    4. import java.lang.reflect.Field;
    5. import java.lang.reflect.InvocationTargetException;
    6. import java.sql.ResultSet;
    7. import java.sql.SQLException;
    8. /**
    9. * @ClassName: TrafficEventinfoResultRowMapper
    10. * @Description: TrafficEventinfoResult
    11. * @author: yangchang
    12. * @date: 2021/12/23
    13. */
    14. public class TrafficEventinfoResultRowMapper extends BaseSqlColumn implements RowMapper{
    15. public TrafficEventinfoResult mapRow(ResultSet rs,int rowNum) throws SQLException {
    16. try {
    17. TrafficEventinfoResult suspicionResult = new TrafficEventinfoResult();
    18. TrafficEventinfoResult result =mapEntityProperties(rs,rowNum,suspicionResult);
    19. return result;
    20. }catch (Exception e){
    21. e.printStackTrace();
    22. }
    23. return null;
    24. }
    25. }

    base转换:

    1. import org.slf4j.Logger;
    2. import org.slf4j.LoggerFactory;
    3. import java.lang.reflect.Field;
    4. import java.lang.reflect.InvocationTargetException;
    5. import java.sql.ResultSet;
    6. import java.sql.SQLException;
    7. /**
    8. *

    9. *
    10. * @author xpwi
    11. * @since 2021-12-24
    12. */
    13. public class BaseSqlColumn {
    14. private static final Logger log = LoggerFactory.getLogger(BaseSqlColumn.class);
    15. /**
    16. * 对象参数实现
    17. * @param rs
    18. * @param rowNum
    19. * @param object
    20. * @param
    21. * @return
    22. * @throws SQLException
    23. */
    24. protected T mapEntityProperties(ResultSet rs, int rowNum, T object) throws SQLException {
    25. T t = (T) object;
    26. Field[] fields= object.getClass().getDeclaredFields();
    27. try {
    28. for (int i = 0; i < fields.length; i++) {
    29. String name = fields[i].getName();//获取属性名
    30. Object o = null;
    31. // 通过字段名称获取该字段的值(实体字段名称必须与数据库字段名称一致才可以)
    32. if ("serialVersionUID".equals(name)) {
    33. continue;
    34. }
    35. if (isExistColumn(rs, name)) {
    36. o = rs.getObject(name);
    37. }
    38. if (o == null) {
    39. continue;
    40. }
    41. // 使用BeanUtils通过字段名将value设置到实体中  
    42. try {
    43. MyBeanUtils.setProperty(t, name, o);
    44. } catch (IllegalAccessException e) {
    45. e.printStackTrace();
    46. log.error("类型转换错误"+e);
    47. } catch (InvocationTargetException e) {
    48. e.printStackTrace();
    49. log.error("类型转换错误"+e);
    50. }
    51. }
    52. return t;
    53. } catch (Exception e) {
    54. log.error("其他异常"+e);
    55. }
    56. return null;
    57. }
    58. /**
    59. * 判断查询结果集中是否存在某列
    60. *
    61. * @param rs 查询结果集
    62. * @param columnName 列名
    63. * @return true 存在; false 不存在
    64. */
    65. protected boolean isExistColumn(ResultSet rs, String columnName) {
    66. try {
    67. if (rs.findColumn(columnName) > 0) {
    68. return true;
    69. }
    70. } catch (SQLException e) {
    71. return false;
    72. }
    73. return false;
    74. }
    75. }

    当然还有很多种分页,第三方工具分页,其实底层都是走的是limit分页,比如我们的pagehepler,mybateisplus ,还有mybateis 分页,jpa都是一致的。

  • 相关阅读:
    Linux 部署Neo4j 并且创建自定义Service
    java实现幂等性校验
    搞透 IOC,Spring IOC 看这篇就够了!
    pytorch自定义图像分类项目的训练+OpenCV部署实战案例
    交通 | python网络爬虫:“多线程并行 + 多线程异步协程
    重新思考边缘负载均衡
    JAVA【设计模式】命令模式
    数据结构——二叉树
    用C语言随机读写二进制文件
    数据结构 | 单链表专题【详解】
  • 原文地址:https://blog.csdn.net/qq_39751120/article/details/126752950