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标签,这样会导致列表查询出来的数据真实性有问题,因为主表数据重复了,导致分页有问题,比如:
<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>
<collection property="zsbmStudentParents" ofType="com.iflytek.edu.hnezzhxy.model.ZsbmStudentParent">
<result column="pSid" property="sid"/>
<result column="pid" property="id"/>
<result column="pType" property="type"/>
<result column="pName" property="name"/>
<result column="dw" property="dw"/>
<result column="phone" property="phone"/>
<result column="pModifyTime" property="modifyTime"/>
</collection>
<collection property="bestScores" ofType="com.iflytek.edu.hnezzhxy.model.ZsbmAttach">
<result property="sid" column="cjSid"></result>
<result property="id" column="cjid"></result>
<result property="fileName" column="file_name"></result>
<result property="suffix" column="suffix"></result>
<result property="fileSize" column="file_size"></result>
<result property="contextId" column="context_id"></result>
<result property="fileUrl" column="file_url"></result>
<result property="type" column="type"></result>
</collection>
</resultMap>