项目场景:
项目背景:学员查询需要关联查询学员历史数据,是一对多的关系,项目是boot框架
问题描述
正常操作:通过mapper.xml文件进行查询,学员表关联学员历史表进行查询,结果导致学员总数量对不上
旧有mapper
<resultMap type="com.domain.DxBaseStudentMessageInfo" id="DxBaseStudentMessageInfoResult">
<result property="id" column="id" />
<result property="name" column="name" />
<result property="sex" column="sex" />
<collection property="historyMessageInfos" javaType="java.util.List" ofType="com.domain.DxBaseStudentHistoryMessageInfo" >
<id property="historyId" column="history_id" />
<id property="studentId" column="student_id" />
<result property="studentNo" column="student_no" />
.......
</collection>
</resultMap>
原因分析:
mybatis 分页插件问题,具体原因 有大佬写的很详细,我不过多叙述
原创作者: 识时务者J
https://blog.csdn.net/qq_65377318/article/details/125408219
解决方案:
我改过之后的mapper
column=“id” id是查询结果字段,不是属性值,以#{}的方式传值给内嵌sql
<resultMap type="com.domain.DxBaseStudentMessageInfo" id="DxBaseStudentMessageInfoResult">
<result property="id" column="id" />
<result property="name" column="name" />
<result property="sex" column="sex" />
<collection property="historyMessageInfos" column="id" javaType="java.util.List" select="selectHistoryMessageInfoListById" />
</resultMap>
<select id="selectHistoryMessageInfoListById" resultMap="HistoryMessageInfoResult">
select sh.history_id,sh.student_id,sh.student_no
from dx_base_student_history_message_info sh
where sh.student_id = #{id}
</select>
别急 到这还没结束 !!!!!!!
后续因优化sql,又来修改这个查询,添加了一个查询条件【是否绑定账户】的这样一个条件【isBinderSysUser】,但是 这个字段不是学员表里新增的字段
select s.id, s.name,s.sex,s.nation,s.politicsstatus,s.education,s.student_status, s.identityno, s.phone, d.name deptname, d.id deptid, s.duties, s.rank, s.birthday, s.native_place, s.identityphoto, s.bjid, s.bjname, s.studentno
, s.reportedtime,s.classcommittee,
s.remark, s.create_by, s.create_time, s.update_by, s.update_time,IF(u.user_id IS NULL,'0','1') as isBinderSysUser
from dx_base_student_message_info s
left join dx_base_send_train_dept d on d.id = s.deptname
left join sys_user u on u.idnumber =s.id and u.user_role_type = '1' and u.del_flag = '0'
原先编码逻辑如下:
public TableDataInfo list(DxBaseStudentMessageInfo dxBaseStudentMessageInfo)
{
startPage();
List<DxBaseStudentMessageInfo> list = dxBaseStudentMessageInfoService.selectDxBaseStudentMessageInfoList(dxBaseStudentMessageInfo);
List<DxBaseStudentMessageInfo> res_list = new ArrayList<>();
if (null!=dxBaseStudentMessageInfo && StringUtils.hasText(dxBaseStudentMessageInfo.getIsBinderSysUser()))
{
res_list = list.stream().filter(e->dxBaseStudentMessageInfo.getIsBinderSysUser().equals(e.getIsBinderSysUser())).collect(Collectors.toList());
}else {
res_list.addAll(list);
}
return getDataTable(res_list);
}
敲重点!!!!!!!
我以为startPage() 开启分页,返回的getDataTable(res_list) ,就是正常的,结果确是total 总数为10条,但是实际上 学员总数应该为143条!
mybatis的分页插件,只对第一个sql生效,返回的total总数只对第一个sql返回的对象生效
后续直接在mapper 里修改sql-通过条件查询就可以了!
select s.* from (
select s.id, s.name,s.sex,s.nation,s.politicsstatus,s.education,s.student_status, s.identityno, s.phone, d.name deptname, d.id deptid, s.duties, s.rank, s.birthday, s.native_place, s.identityphoto, s.bjid, s.bjname, s.studentno
, s.reportedtime,s.classcommittee,
s.remark, s.create_by, s.create_time, s.update_by, s.update_time,IF(u.user_id IS NULL,'0','1') as isBinderSysUser
from dx_base_student_message_info s
left join dx_base_send_train_dept d on d.id = s.deptname
left join sys_user u on u.idnumber =s.id and u.user_role_type = '1' and u.del_flag = '0'
) s
startPage();
List<DxBaseStudentMessageInfo> list = dxBaseStudentMessageInfoService.selectDxBaseStudentMessageInfoList(dxBaseStudentMessageInfo);
return getDataTable(list);
做java开发的都知道 select * 会导致全表扫描 , 但是上述sql 不是直接访问原表,只是包裹一层,实际上还是查询的字段,不会导致全表扫描!