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

最新推荐文章于 2024-07-29 14:22:26 发布

小杨互联网

最新推荐文章于 2024-07-29 14:22:26 发布

阅读量3k

点赞数 1

分类专栏： java服务文章标签： java 分页 java分页方式 java几种分页 mysql分页

本文链接：https://blog.csdn.net/qq_39751120/article/details/126752950

版权

java服务专栏收录该内容

18 篇文章 1 订阅

订阅专栏

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

1，原生的jdbc分页

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

2，集合分页。

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

截取数据

List<E> subList(int fromIndex, int toIndex);

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

java8函数分页：

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

3，pagehelper分页

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

<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>${pagehelper.version}</version>
</dependency>

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

<dependency>
      <groupId>com.github.pagehelper</groupId>
      <artifactId>pagehelper</artifactId>
      <version>5.2.0</version>
 </dependency>

配置文件：

#pagehelper分页助手
pagehelper:
  helperDialect: mysql
  reasonable: false
  supportMethodsArguments: true
  params: count=countSql

     PageHelper.startPage(pageNum,pageSize);
        List<Student> list = studentService.findAllStudent();
        PageInfo pageInfo = PageInfo.of(list);

4，mybateisplus 内置分页。

    @ApiOperation(value = "vin查询")
    @PostMapping("/chagreVinSearch")
    public ResultMsg chagreVinSearch(@RequestBody ChagerVinDto chagerVinDto) {
        return  chagreVinService.chagreVinSearch(chagerVinDto);
    }



import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;


import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.sql.SQLException;
import java.sql.Wrapper;
import java.time.LocalDateTime;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @author liuhan
 * @version 1.0
 * @description: TODO
 * @date 2022/7/27 10:34
 */
@Service
@Slf4j
public class ChagreVinServiceImpl extends ServiceImpl<TcChargerFilterMapper, TcChargerFilterVO> implements ChagreVinService {

    @Autowired
    TcChargerFilterMapper tcChargerFilterMapper;

    @Override
    public ResultMsg chagreVinSearch(ChagerVinDto chagerVinDto) {

        //分页参数
        Page<TcChargerFilterVO> page = Page.of(chagerVinDto.getPageNum(),chagerVinDto.getPageSize());
        //queryWrapper组装查询where条件
        LambdaQueryWrapper<TcChargerFilterVO> queryWrapper = new LambdaQueryWrapper<>();
        if(StringUtils.isNotBlank(chagerVinDto.getPlateNo())){
            queryWrapper.like(TcChargerFilterVO::getPlateNo,chagerVinDto.getPlateNo());
        }
        if(StringUtils.isNotBlank(chagerVinDto.getVin())){
            queryWrapper.eq(TcChargerFilterVO::getVin,chagerVinDto.getVin());
        }
        IPage<TcChargerFilterVO>  tcChargerFilterVOIPage = tcChargerFilterMapper.selectPage(page,queryWrapper);
        ChagerVinDto resultDto = new ChagerVinDto();
        resultDto.setResult(tcChargerFilterVOIPage.getRecords());
        resultDto.setTotal(tcChargerFilterVOIPage.getTotal());
        return  ResultMsg.ok(resultDto);
    }


}


import com.baomidou.mybatisplus.core.mapper.BaseMapper;

import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface TcChargerFilterMapper extends BaseMapper<TcChargerFilterVO> {




}


import java.io.Serializable;
import java.time.LocalDateTime;
import java.util.Date;

import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

/**
 * tc_charger_filter
 * @author
 */
@Data
@TableName("tc_charger_filter")
public class TcChargerFilterVO extends BaseEntity implements Serializable {
    /**
     * 主键id
     */
    private Integer id;

    /**
     * vin码
     */
    @ExcelProperty(value = {"vin"},order = 1)
    private String vin;

    /**
     * 车牌号
     */
    @ExcelProperty(value = {"车牌号"},order = 2)
    private String plateNo;




    /**
     * 创建人
     */
    private String createBy;

    /**
     * 创建时间
     */
    private LocalDateTime createTime;

    /**
     * 更新人
     */
    private String updateBy;

    /**
     * 更新时间
     */
    private LocalDateTime updateTime;




}


import org.springframework.web.multipart.MultipartFile;

/**
 * <p>
 *
 * @author yangchang
 * @since 2022-09-05
 */
public interface ChagreVinService {

    ResultMsg chagreVinSearch(ChagerVinDto chagerVinDto);

}

5，SpringDataJPA 分页

        resultPage = template.queryForPage(searchQuery, ManualEngDirEntity.class, searchResultMapper);
            // 获取集合数据
         content = resultPage.getContent();
        
        MapperFactory mapperFactory = new DefaultMapperFactory.Builder().build();
        List<DirectoryDto> directoryDtoList = mapperFactory.getMapperFacade().mapAsList(content, DirectoryDto.class);

@Query("select u from User u")
Page<User> findALL(Pageable pageable);

6，Spring JdbcTemplate 分页

 public Result<RafficEventinfoDto> getTrafficEventinfoList(RafficEventinfoDto rafficEventinfoDto) {
        Object[] args = new Object[1];
        Integer page = rafficEventinfoDto.getPageNum();
        Integer pageSize = rafficEventinfoDto.getPageSize();
        StringBuffer sbdata = new StringBuffer();
        StringBuffer sbCount = new StringBuffer();
        String sqlCount = "select count(1) from TRAFFIC_EVENTINFO where";
        sbdata.append("select ee.* from (select e.*,rownum rowno from (select * from TRAFFIC_EVENTINFO  where ");
        sbCount.append(sqlCount);
        if (rafficEventinfoDto.getPlateNo() != null) {
            sbdata.append(" PLATENO=?  ");
            sbCount.append(" PLATENO=?  ");
            args[0] = rafficEventinfoDto.getPlateNo();
        }

        sbdata.append(" ) e ) ee where ee.rowno >=" + (((page <= 0 ? 1 : page) - 1) * pageSize + 1) + " and ee.rowno <=" + (page <= 0 ? 1 : page) * pageSize);
        Integer total = (Integer)this.jdbc.queryForObject(sbCount.toString(), args, Integer.class);
        if (total == 0) {
            return Result.ok((Object)null);
        } else {
            List<TrafficEventinfoResult> list = this.jdbc.query(sbdata.toString(), args, new TrafficEventinfoResultRowMapper());
            PageInfo pageInfo = new PageInfo(list);
            RafficEventinfoDto resultDto = new RafficEventinfoDto();
            resultDto.setTotal(total);
            resultDto.setResultList(pageInfo.getList());
            return Result.ok(resultDto);
        }
    }

mapper层：


import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.RowMapper;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @ClassName:  TrafficEventinfoResultRowMapper
 * @Description: TrafficEventinfoResult
 * @author:  yangchang
 * @date:   2021/12/23
 */

public class TrafficEventinfoResultRowMapper extends BaseSqlColumn implements RowMapper<TrafficEventinfoResult>{

	public TrafficEventinfoResult mapRow(ResultSet rs,int rowNum) throws SQLException {
		try {
			TrafficEventinfoResult suspicionResult = new TrafficEventinfoResult();
			TrafficEventinfoResult result =mapEntityProperties(rs,rowNum,suspicionResult);
			return result;
		}catch (Exception e){
			e.printStackTrace();
		}
		return null;

	}


}

base转换：



import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * <p>
 *
 * @author xpwi
 * @since 2021-12-24
 */
public class BaseSqlColumn {
    private static final Logger log = LoggerFactory.getLogger(BaseSqlColumn.class);

    /**
     * 对象参数实现
     * @param rs
     * @param rowNum
     * @param object
     * @param <T>
     * @return
     * @throws SQLException
     */
    protected <T> T mapEntityProperties(ResultSet rs, int rowNum, T object) throws SQLException {
        T t =  (T) object;
        Field[]  fields= object.getClass().getDeclaredFields();
        try {
            for (int i = 0; i < fields.length; i++) {
                String name = fields[i].getName();//获取属性名
                Object o = null;
                // 通过字段名称获取该字段的值（实体字段名称必须与数据库字段名称一致才可以）
                if ("serialVersionUID".equals(name)) {
                    continue;
                }
                if (isExistColumn(rs, name)) {
                    o = rs.getObject(name);
                }
                if (o == null) {
                    continue;
                }
                // 使用BeanUtils通过字段名将value设置到实体中  
                try {
                    MyBeanUtils.setProperty(t, name, o);
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                    log.error("类型转换错误"+e);
                } catch (InvocationTargetException e) {
                    e.printStackTrace();
                  log.error("类型转换错误"+e);
                }

            }
            return t;
        } catch (Exception e) {
            log.error("其他异常"+e);
        }
        return null;
    }


    /**
     * 判断查询结果集中是否存在某列
     *
     * @param rs         查询结果集
     * @param columnName 列名
     * @return true 存在; false 不存在
     */
    protected boolean isExistColumn(ResultSet rs, String columnName) {
        try {
            if (rs.findColumn(columnName) > 0) {
                return true;
            }
        } catch (SQLException e) {
            return false;
        }

        return false;
    }
}

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