SpringCloud+MyBatis分页处理(前后端分离)

网友投稿 683 2023-07-18

SpringCloud+MyBatis分页处理(前后端分离)

SpringCloud+MyBatis分页处理(前后端分离)

分页处理,这是做javaWeb项目中常见的场景。

背景:

1.系统架构:SpringCloud分布式

2.持久层:MyBatis

3.前端:前后分离vue.js/bootstrap等.

后台提供restful api 接口,前端访问后端接口展示数据

2种方式提供分页处理方案:

一、直接MyBatis数据库进行分页

controller接口

@ApiImplicitParams({

@ApiImplicitParam(name = "categoryId", value = "支付渠道大类Id", required = false, dataType = "Long", paramType = "query"),

@ApiImplicitParam(name = "payChannelId", value = "支付渠道ID", required = false, dataType = "Long", paramType = "query"),

@ApiImplicitParam(name = "bankCode", value = "银行名称code", required = false, dataType = "String", paramType = "query"),

@ApiImplicitParam(name = "startTime", value = "创建时间起始(示例:2018-09-05)", required = false, dataType = "String", paramType = "query", length = 10),

@ApiImplicitParam(name = "endTime", value = "创建时间截止(示例:2018-09-05)", required = false, dataType = "String", paramType = "query", length = 10),

@ApiImplicitParam(name = "pageNum", value = "查询开始页", required = true, dataType = "int", paramType = "query"),

@ApiImplicitParam(name = "pageSize", value = "查询的页面大小不需要分页则把此值填大一点", required = true, dataType = "int", paramType = "query")})

public PageResult queryPayChannel(

@RequestParam(value = "categoryId", required = false) Long categoryId,

@RequestParam(value = "payChannelId", required = false) Long payChannelId,

@RequestParam(value = "bankCode", required = false) String bankCode,

@RequestParam(value = "startTime", required = false) String startTime,

@RequestParam(value = "endTime", required = false) String endTime,

@RequestParam(value = "pageNum", required = true) IntOENfpufHOeger pageNum,

@RequestParam(value = "pageSize", required = true) Integer pageSize) {

PageResult response = new PageResult();

try {

int endRowNo = pageNum * pageSize;

int beginRowNo = (pageNum - 1) * pageSize + 1;

return channelBaseDataService.queryBaseData(categoryId,payChannelId,bankCode,startTime,endTime,beginRowNo,endRowNo);

} catch (Exception e) {

response = new PageResult();

if (e instanceof AppException) {

response.setCode(((AppException) e).getErrorCode());

response.setResult(((AppException) e).getErrorMsg());

} else {

response.setCode(CumReturnCode.SYSTEM_EXCEPTION.code);

response.setResult(CumReturnCode.SYSTEM_EXCEPTION.message);

}

return response;

}

}

Swagger-ui接口界面如下:

pageNum 和 pageSize 用于前端分页的参数,pageNum:表示页码第几页,pageSize:表示每页展示数据数量。

所有查询条件参数,在mapper-xml里进行处理,再利用数据库oracle本身的rownum行值进行分页。

xml代码

select

ID, PAY_CHANNEL_MAIN_ID, PAY_CHANNEL_ID,

BANK_CODE, CREATE_TIME, UPDATE_TIME,UPDATOR

from (

select A.*, rownum RN

from (

select ID, PAY_CHANNEL_MAIN_ID, PAY_CHANNEL_ID,

BANK_CODE, CREATE_TIME, UPDATE_TIME,UPDATOR

from CUM_PAY_CHANNEL_BASE

CREATE_TIME <![CDATA[ >= ]]> #{beginCreateTime,jdbcType=TIMESTAMP}

AND CREATE_TIME <![CDATA[ <= ]]> #{endCreateTime,jdbcType=TIMESTAMP}

AND PAY_CHANNEL_MAIN_ID = #{categoryId,jdbcType=DECIMAL}

AND PAY_CHANNEL_ID = #{payChannelId,jdbcType=DECIMAL}

AND BANK_CODE = #{bankCode,jdbcType=VARCHAR}

order by CREATE_TIME desc

) A

where rownum <= #{endRowNo,jdbcType=DECIMAL}

)

where RN >= #{beginRowNo,jdbcType=DECIMAL}

优点:直观、方便、易排查问题。 缺点:访问数据库过于频繁,未利用到mybatis本身的缓存优势。

二、Java+缓存分页

这种方法对于前端而言是没变化,无感的。

只是在后端处理稍作变动,思路:

1.先把所有数据记录查询出来

2.数据库实体再次封装为查询实体

3.纯java代码进行分页

controller接口不变

service业务处理——>数据库crud操作变动

把所有数据库记录查询处理

select

ID, PAY_CHANNEL_MAIN_ID, PAY_CHANNEL_ID,

BANK_CODE, CREATE_TIME, UPDATE_TIME,UPDATOR

from CUM_PAY_CHANNEL_BASE

CREATE_TIME <![CDATA[ >= ]]> #{beginCreateTime,jdbcType=TIMESTAMP}

AND CREATE_TIME <![CDATA[ <= ]]> #{endCreateTime,jdbcType=TIMESTAMP}

AND PAY_CHANNEL_MAIN_ID = #{categoryId,jdbcType=DECIMAL}

AND PAY_CHANNEL_ID = #{payChannelId,jdbcType=DECIMAL}

AND BANK_CODE = #{bankCode,jdbcType=VARCHAR}

order by CREATE_TIME desc

再按照pageNum和pageSize进行分页处理

//以分页形式输出给前端

List resultList = new ArrayList<>();

if(groupRespList.size() >= endRowNo){

for(int i=(beginRowNo-1); i< endRowNo; i++){

resultList.add(groupRespList.get(i));

}

}else{

for(int i=(beginRowNo-1); i< groupRespList.size(); i++){

resultList.add(groupRespList.get(i));

}

}

优点:利用了mybatis缓存机制,分页查询快速,减少数据库访问次数。

缺点:当数据量大的时候,100W+;在没有条件查询数据库的时候,耗时久

前端分页后的界面展示如下:

版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。

上一篇:intellij idea创建第一个动态web项目的步骤方法
下一篇:你不知道的 IDEA Debug调试小技巧(小结)
相关文章

 发表评论

暂时没有评论,来抢沙发吧~