原先也很倾向纯 ORM ，但是现在反而越来越觉得复杂 sql 还是原生的更直观，可以参考这个[根据条件分页查询用户列表](
https://gitee.com/mybatis-flex/mybatis-flex/issues/I90V7G#note_25272825_link)，我贴过来：```
/**
* 根据条件分页查询用户列表
*
* @
param searchReq 用户信息
*
* @
return 用户信息集合信息
*/
@
Override @
DataScope(deptAlias = SysDept.TABLE_ALIAS, userAlias = SysUser.TABLE_ALIAS)
public Page<SysUser> selectUserList(PageAdapter<SysUser> pageAdapter, final SysUser searchReq) {
final QueryWrapper queryWrapper = queryChain()
.select(SYS_USER.DEFAULT_COLUMNS, SYS_DEPT.DEPT_NAME, SYS_DEPT.LEADER)
.from(
SYS_USER.as(SysUser.TABLE_ALIAS))
.leftJoin(SYS_DEPT).as(SysDept.TABLE_ALIAS).on(SYS_USER.DEPT_ID.eq(SYS_DEPT.DEPT_ID))
.eq(SysUser::getDelFlag, UserConstants.NORMAL) // 是否删除: 否
.eq(SysUser::getUserId, searchReq.getUserId(), IdUtils.isIdValid(searchReq.getUserId())) // 用户 ID
.like(SysUser::getUserName, searchReq.getUserName(), StrUtil.isNotBlank(searchReq.getUserName())) // 用户名称
.eq(SysUser::getStatus, searchReq.getStatus(), StrUtil.isNotBlank(searchReq.getStatus())) // 用户状态
.like(SysUser::getPhonenumber, searchReq.getPhonenumber(), StrUtil.isNotBlank(searchReq.getPhonenumber())) // 手机号码
.ge(SysUser::getCreateTime, searchReq.getParams().getBeginTime(), Objects.nonNull(searchReq.getParams().getBeginTime())) // 创建时间开始
.le(SysUser::getCreateTime, searchReq.getParams().getEndTime(), Objects.nonNull(searchReq.getParams().getEndTime())) // 创建时间结束
.and(qw -> qw.eq(SysUser::getDeptId, searchReq.getDeptId()) // 部门过滤
.or(
SYS_USER.DEPT_ID.in(QueryWrapper.create().select(SYS_DEPT.DEPT_ID)
.from(SYS_DEPT)
.where(QueryMethods.findInSet(QueryMethods.number(searchReq.getDeptId()), SYS_DEPT.ANCESTORS).gt(0)))),
IdUtils.isIdValid(searchReq.getDeptId()))
// 数据权限
.and(qw -> qw.where(searchReq.getParams().getDataScope()), searchReq.getParams().hasDataScopeFilter());
return getMapper().paginate(pageAdapter.getPage(), queryWrapper);
}
```
如果用 xml 差不多是这样：
```
<select id="selectUserList" parameterType="SysUser" resultMap="SysUserResult">
select u.user_id, u.dept_id, u.nick_name, u.user_name, u.email, u.avatar, u.phonenumber, u.password,
u.sex,u.status, u.del_flag, u.login_ip, u.login_date, u.create_by, u.create_time,
u.remark,
u.show_tag,
d.dept_name, d.leader
from sys_user u
left join sys_dept d on u.dept_id = d.dept_id
where u.del_flag = '0'
<if test="searchValue != null and searchValue != ''">
AND (
u.user_name like concat('%', #{searchValue}, '%')
OR u.nick_name like concat('%', #{searchValue}, '%')
)
</if>
<if test="userId != null ">
AND u.user_id = #{userId}
</if>
<if test="userName != null and userName != ''">
AND u.user_name like concat('%', #{userName}, '%')
</if>
<if test="status != null and status != ''">
AND u.status = #{status}
</if>
<if test="phonenumber != null and phonenumber != ''">
AND u.phonenumber like concat('%', #{phonenumber}, '%')
</if>
<if test="beginTime != null and beginTime != ''"><!-- 开始时间检索 -->
AND date_format(u.create_time,'%y%m%d') >= date_format(#{beginTime},'%y%m%d')
</if>
<if test="endTime != null and endTime != ''"><!-- 结束时间检索 -->
AND date_format(u.create_time,'%y%m%d') <= date_format(#{endTime},'%y%m%d')
</if>
<if test="deptId != null and deptId != 0">
AND (u.dept_id = #{deptId} OR u.dept_id IN ( SELECT t.dept_id FROM sys_dept t WHERE FIND_IN_SET
(#{deptId},ancestors) ))
</if>
<!-- 数据范围过滤 -->
${params.dataScope}
</select>
```
个人感觉 xml 方式反而更加直观看出查询的字段和检索条件。
Select Language