springboot 2.3 整合mybatis分页插件，以及注意事项案例

技术2022-07-15 70

1.添加依赖

<dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper-spring-boot-starter</artifactId> <version>1.2.5</version> </dependency>

2.控制层代码的方法

@RestController @Validated @Api(tags = "我的报名相关接口") @RequestMapping("/entroll") public class EntrollController { @RequestMapping("/getMyEntrollZsbmStudentsList") public ResultVO<MyEntrollVo> getMyEntrollZsbmStudentsList(HttpServletRequest request,@NotNull(message ="currentPage不能为空！") @RequestParam(value="currentPage") Integer currentPage, @NotNull(message ="pageSize不能为空！") @RequestParam(value="pageSize") Integer pageSize,@RequestParam(value="name",required = false) String name){ Object object = request.getSession().getAttribute(Constants.SESSION_USER_Attribute); if(object!=null){ ZsbmUser user=(ZsbmUser)object; return enterService.getMyEntrollZsbmStudentsList(currentPage,pageSize,name,user); } return new ResponseResultUtil().success(ResponseCodeEnum.UNLOGIN_ERROR.getCode(), ResponseCodeEnum.UNLOGIN_ERROR.getMessage(),null,true); } }

3.业务层方法代码

@Service public class EnterService { /** 我的报名模块接口注入 **/ @Autowired private ZsbmStudentDao zsbmStudentDao; /** * @description 我的报名列表查询 * @param currentPage 当前页 * @param pageSize 当前页的大小 * @param name 姓名 * @return ResultVO */ public ResultVO<MyEntrollVo> getMyEntrollZsbmStudentsList(Integer currentPage, Integer pageSize,String name,ZsbmUser user) { //给mybatis插件设置起始页要注意此处的位置【放在查询数据代码的前后都是不一样的】 PageHelper.startPage(currentPage, pageSize); String uid = user.getUid(); String roleEname =user.getRoleEname(); Boolean isAdmin=false; //如果是管理员用户 if(Constants.ROLE_ADMIN.equals(roleEname)){ isAdmin=true; } List<MyEntrollVo> zsbmStudents = zsbmStudentDao.getZsbmStudentList(uid,name,isAdmin); PageInfo<MyEntrollVo> pageInfo = new PageInfo<MyEntrollVo>(zsbmStudents); Page page=new Page(currentPage,true,pageSize,pageInfo.getSize()); if(pageInfo!=null&&pageInfo.getList().size()>0){ for(int i=0;i<=pageInfo.getList().size()-1;i++){ String sid=pageInfo.getList().get(i).getSid(); List<ZsbmStudentParent> zsbmStudentParentList = zsbmStudentDao.getZsbmStudentParentsBySid(sid); if(zsbmStudentParentList!=null&&zsbmStudentParentList.size()>0){ pageInfo.getList().get(i).setZsbmStudentParents(zsbmStudentParentList); } List<ZsbmAttach> zsbmAttachList = zsbmStudentDao.getBestSoreBySid(sid); if(zsbmAttachList!=null&&zsbmAttachList.size()>0){ pageInfo.getList().get(i).setBestScores(zsbmAttachList); } } return new ResponseResultUtil().success(new PageVO(pageInfo.getList(), page), true); } return new ResponseResultUtil().success(true); } }

4.接口代码

package com.iflytek.edu.hnezzhxy.dao; import com.iflytek.edu.hnezzhxy.model.ZsbmAttach; import com.iflytek.edu.hnezzhxy.model.ZsbmStudent; import com.iflytek.edu.hnezzhxy.model.ZsbmStudentParent; import com.iflytek.edu.hnezzhxy.vo.MyEntrollVo; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Param; import java.util.List; @Mapper public interface ZsbmStudentDao { /** 查询学生信息列表 **/ List<MyEntrollVo> getZsbmStudentList(@Param("uid") String uid,@Param("name") String name,@Param("isAdmin")Boolean isAdmin);zsbmStudentParent); /** 通过sid查询自己亲人信息 **/ List<ZsbmStudentParent> getZsbmStudentParentsBySid(@Param("sid") String sid); /** 获取成绩照片信息 **/ List<ZsbmAttach> getBestSoreBySid(@Param("sid") String sid); }

5.xml文件

<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > <mapper namespace="com.iflytek.edu.hnezzhxy.dao.ZsbmStudentDao" > <sql id="Base_Column_List" > a.id,a.sid,a.name,a.sex,a.birth,a.nation_code,a.graduate_school,a.stu_phone_num,a.ID_card_num ,a.admission_no,a.create_time,a.modify_time,a.del_flag,a.status,a.examine_time,examine_uid,a.examine_reason </sql> <resultMap id="baseResultMap" type="com.iflytek.edu.hnezzhxy.vo.MyEntrollVo"> <id property="id" column="id"></id> <id property="sid" column="sid"></id> <result property="name" column="name"></result> <result property="sex" column="sex"></result> <result property="birth" column="birth"></result> <result property="nationCode" column="nation_code"></result> <result property="graduateSchool" column="graduate_school"></result> <result property="stuPhoneNum" column="stu_phone_num"></result> <result property="idcardNum" column="ID_card_num"></result> <result property="admissionNo" column="admission_no"></result> <result property="createTime" column="create_time"></result> <result property="modifyTime" column="modify_time"></result> <result property="delFlag" column="del_flag"></result> <result property="modifyTime" column="modify_time"></result> <result property="delFlag" column="del_flag"></result> <result property="status" column="status"></result> <result property="examineTime" column="examine_time"></result> <result property="examineUid" column="examine_uid"></result> <result property="examineReason" column="examine_reason"></result> <result property="nationName" column="nationName"></result> <association property="zsbmAttach" javaType="com.iflytek.edu.hnezzhxy.model.ZsbmAttach"> <result property="sid" column="txSid"></result> <result property="id" column="txid"></result> <result property="fileName" column="txName"></result> <result property="suffix" column="txSuffix"></result> <result property="fileSize" column="txSize"></result> <result property="contextId" column="txContextid"></result> <result property="fileUrl" column="txUrl"></result> <result property="type" column="txType"></result> </association> </resultMap> <select id="getZsbmStudentList" resultMap="baseResultMap"> select <include refid="Base_Column_List" /> ,c.name nationName ,e.id txid,e.file_name txName,e.suffix txSuffix,e.file_size txSize,e.context_id txContextid,e.file_url txUrl,e.type txType from zsbm_student a left join zsbm_nation c on a.nation_code=c.code left join zsbm_attach e on a.sid=e.sid and e.type=0 and e.del_flag=0 where a.del_flag=0 <if test="isAdmin==false"> and a.creator_id=#{uid} </if> <if test="name!=null and name!='' "> and a.name like concat('%','${name}','%') </if> order by a.create_time desc </select> <select id="getZsbmStudentByID" resultMap="baseResultMap"> select <include refid="Base_Column_List" /> ,c.name nationName ,e.id txid,e.file_name txName,e.suffix txSuffix,e.file_size txSize,e.context_id txContextid,e.file_url txUrl,e.type txType from zsbm_student a left join zsbm_nation c on a.nation_code=c.code left join zsbm_attach e on a.sid=e.sid and e.type=0 and e.del_flag=0 <where> <if test="sid != null and sid != ''"> AND a.sid = #{sid} </if> </where> </select> <select id="getZsbmStudentParentsBySid" resultType="com.iflytek.edu.hnezzhxy.model.ZsbmStudentParent"> select * from zsbm_student_parent where sid=#{sid} </select> <select id="getBestSoreBySid" resultType="com.iflytek.edu.hnezzhxy.model.ZsbmAttach"> select * from zsbm_attach where sid=#{sid} and type=1 and del_flag=0 </select> <select id="selectIdcardNumIsExist" resultType="Integer"> select count(1) from zsbm_student where ID_card_num=#{idcardNum} <if test="sid!=null and sid!='' "> and sid != #{sid} </if> </select> <insert id="addMyEntrollZsbmStudent" parameterType="com.iflytek.edu.hnezzhxy.model.ZsbmStudent" useGeneratedKeys="true" keyProperty="id"> insert into zsbm_student (sid,name,sex,birth,nation_code, graduate_school,stu_phone_num,ID_card_num, admission_no,creator_id,create_time,modify_time,del_flag ) values (#{sid,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR},#{sex,jdbcType=TINYINT}, #{birth,jdbcType=DATE},#{nationCode,jdbcType=VARCHAR},#{graduateSchool,jdbcType=VARCHAR}, #{stuPhoneNum,jdbcType=VARCHAR},#{idcardNum,jdbcType=VARCHAR},#{admissionNo,jdbcType=VARCHAR},#{creatorId,jdbcType=VARCHAR}, #{createTime,jdbcType=TIMESTAMP}, #{modifyTime,jdbcType=TIMESTAMP},#{delFlag,jdbcType=TINYINT} ) </insert> <insert id="batchAddMyEntrollZsbmStudentParent" useGeneratedKeys="true" keyProperty="id" parameterType="java.util.List"> insert into zsbm_student_parent (sid,type,name,dw,phone,modify_time) values <foreach collection="list" item="item" index="index" separator=","> (#{item.sid,jdbcType=VARCHAR},#{item.type,jdbcType=TINYINT},#{item.name,jdbcType=VARCHAR}, #{item.dw,jdbcType=VARCHAR},#{item.phone,jdbcType=VARCHAR},#{item.modifyTime,jdbcType=TIMESTAMP}) </foreach> </insert> <insert id="batchAddMyEntrollBestScore" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id"> insert into zsbm_attach (sid,file_name,suffix,file_size,context_id,file_url,type) values <foreach collection="list" item="item" index="index" separator=","> (#{item.sid,jdbcType=VARCHAR},#{item.fileName,jdbcType=VARCHAR},#{item.suffix,jdbcType=VARCHAR}, #{item.fileSize,jdbcType=VARCHAR},#{item.contextId,jdbcType=VARCHAR},#{item.fileUrl,jdbcType=VARCHAR} ,#{item.type,jdbcType=TINYINT} ) </foreach> </insert> <insert id="addMyEntrollZsbmAttach" parameterType="com.iflytek.edu.hnezzhxy.model.ZsbmAttach" useGeneratedKeys="true" keyProperty="id"> insert into zsbm_attach(sid,file_name,suffix,file_size,context_id, file_url,type ) values (#{sid,jdbcType=VARCHAR},#{fileName,jdbcType=VARCHAR},#{suffix,jdbcType=VARCHAR}, #{fileSize,jdbcType=VARCHAR},#{contextId,jdbcType=VARCHAR},#{fileUrl,jdbcType=VARCHAR},#{type,jdbcType=TINYINT} ) </insert> <update id="updateMyEntrollZsbmStudent" parameterType="com.iflytek.edu.hnezzhxy.model.ZsbmStudent"> update zsbm_student <set> <if test="name != null and name!='' "> name = #{name,jdbcType=VARCHAR}, </if> <if test="sex != null"> sex = #{sex,jdbcType=TINYINT}, </if> <if test="birth != null "> birth = #{birth,jdbcType=DATE}, </if> <if test="nationCode != null and nationCode !='' "> nation_code = #{nationCode,jdbcType=VARCHAR}, </if> <if test="graduateSchool != null and graduateSchool !='' "> graduate_school = #{graduateSchool,jdbcType=VARCHAR}, </if> <if test="stuPhoneNum != null and stuPhoneNum !='' "> stu_phone_num = #{stuPhoneNum,jdbcType=VARCHAR}, </if> <if test="idcardNum != null and idcardNum !='' "> ID_card_num = #{idcardNum,jdbcType=VARCHAR}, </if> <if test="admissionNo != null and admissionNo !='' "> admission_no = #{admissionNo,jdbcType=VARCHAR}, </if> <if test="modifyTime != null "> modify_time = #{modifyTime,jdbcType=TIMESTAMP} </if> </set> where sid = #{sid,jdbcType=VARCHAR} </update> <update id="batchUpdateMyEntrollZsbmStudentParent" parameterType="java.util.List"> <foreach collection="list" item="item" index="index" separator=";"> update zsbm_student_parent <set> <if test="item.type != null "> type = #{item.type,jdbcType=TINYINT}, </if> <if test="item.name != null and item.name!='' "> name = #{item.name,jdbcType=VARCHAR}, </if> <if test="item.dw != null and item.dw !='' "> dw = #{item.dw,jdbcType=VARCHAR}, </if> <if test="item.phone != null and item.phone !='' "> phone = #{item.phone,jdbcType=VARCHAR}, </if> <if test="item.modifyTime != null "> modify_time = #{item.modifyTime,jdbcType=TIMESTAMP} </if> </set> where id = #{item.id,jdbcType=INTEGER} </foreach> </update> <update id="batchUpdateMyEntrollBestScore" parameterType="java.util.List"> <foreach collection="list" item="item" index="index" separator=";"> update zsbm_attach <set> <if test="item.fileName != null and item.fileName !='' "> file_name = #{item.fileName,jdbcType=VARCHAR}, </if> <if test="item.suffix != null and item.suffix !='' "> suffix = #{item.suffix,jdbcType=VARCHAR}, </if> <if test="item.fileSize != null and item.fileSize !='' "> file_size = #{item.fileSize,jdbcType=VARCHAR}, </if> <if test="item.contextId != null and item.contextId !='' "> context_id = #{item.contextId,jdbcType=VARCHAR}, </if> <if test="item.fileUrl != null and item.fileUrl !='' "> file_url = #{item.fileUrl,jdbcType=VARCHAR}, </if> <if test="item.delFlag != null"> del_flag = #{item.delFlag,jdbcType=TINYINT}, </if> <if test="item.type != null"> type = #{item.type,jdbcType=TINYINT}, </if> </set> where id = #{item.id,jdbcType=INTEGER} </foreach> </update> <update id="updateMyEntrollZsbmAttach" parameterType="com.iflytek.edu.hnezzhxy.model.ZsbmAttach"> update zsbm_attach <set> <if test="fileName != null and fileName !='' "> file_name = #{fileName,jdbcType=VARCHAR}, </if> <if test="suffix != null and suffix !='' "> suffix = #{suffix,jdbcType=VARCHAR}, </if> <if test="fileSize != null and fileSize !='' "> file_size = #{fileSize,jdbcType=VARCHAR}, </if> <if test="contextId != null and contextId !='' "> context_id = #{contextId,jdbcType=VARCHAR}, </if> <if test="fileUrl != null and fileUrl !='' "> file_url = #{fileUrl,jdbcType=VARCHAR}, </if> <if test="delFlag != null"> del_flag = #{delFlag,jdbcType=TINYINT}, </if> </set> where id = #{id,jdbcType=INTEGER} </update> <update id="updateZsbmAttachDelFlagByCondition"> update zsbm_attach set del_flag=1 where sid=#{sid} and type=0 and del_flag=0 </update> </mapper>

5.使用此mybatis分页插件需要注意的地方不要在xml中使用关联查询1对多的collection标签，这样会导致列表查询出来的数据真实性有问题，因为主表数据重复了，导致分页有问题，比如：

Processed: 0.021, SQL: 9