From d3a3ad5cbd15a54d41e98ef12344fa9ff88ce635 Mon Sep 17 00:00:00 2001
From: 疯狂的狮子li <15040126243@163.com>
Date: 星期五, 04 三月 2022 11:45:38 +0800
Subject: [PATCH] update 简化查询用户功能
---
ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysUserServiceImpl.java | 48 ++++++++++++++-
ruoyi-system/src/main/resources/mapper/system/SysUserMapper.xml | 59 +------------------
ruoyi-system/src/main/java/com/ruoyi/system/mapper/SysUserMapper.java | 16 +++--
ruoyi-system/src/main/java/com/ruoyi/system/mapper/SysUserRoleMapper.java | 2
ruoyi-common/src/main/java/com/ruoyi/common/constant/UserConstants.java | 5 +
ruoyi-system/src/main/resources/mapper/system/SysUserRoleMapper.xml | 5 +
6 files changed, 69 insertions(+), 66 deletions(-)
diff --git a/ruoyi-common/src/main/java/com/ruoyi/common/constant/UserConstants.java b/ruoyi-common/src/main/java/com/ruoyi/common/constant/UserConstants.java
index bbdc688..9f800be 100644
--- a/ruoyi-common/src/main/java/com/ruoyi/common/constant/UserConstants.java
+++ b/ruoyi-common/src/main/java/com/ruoyi/common/constant/UserConstants.java
@@ -23,6 +23,11 @@
String EXCEPTION = "1";
/**
+ * 鐢ㄦ埛姝ｅ父鐘舵��
+ */
+ String USER_NORMAL = "0";
+
+ /**
* 鐢ㄦ埛灏佺鐘舵��
*/
String USER_DISABLE = "1";
diff --git a/ruoyi-system/src/main/java/com/ruoyi/system/mapper/SysUserMapper.java b/ruoyi-system/src/main/java/com/ruoyi/system/mapper/SysUserMapper.java
index 74b2943..46e48fe 100644
--- a/ruoyi-system/src/main/java/com/ruoyi/system/mapper/SysUserMapper.java
+++ b/ruoyi-system/src/main/java/com/ruoyi/system/mapper/SysUserMapper.java
@@ -1,5 +1,7 @@
package com.ruoyi.system.mapper;
+import com.baomidou.mybatisplus.core.conditions.Wrapper;
+import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ruoyi.common.annotation.DataColumn;
import com.ruoyi.common.annotation.DataPermission;
@@ -20,43 +22,43 @@
@DataColumn(key = "deptName", value = "d.dept_id"),
@DataColumn(key = "userName", value = "u.user_id")
})
- Page<SysUser> selectPageUserList(@Param("page") Page<SysUser> page, @Param("user") SysUser user);
+ Page<SysUser> selectPageUserList(@Param("page") Page<SysUser> page, @Param(Constants.WRAPPER) Wrapper<SysUser> queryWrapper);
/**
* 鏍规嵁鏉′欢鍒嗛〉鏌ヨ鐢ㄦ埛鍒楄〃
*
- * @param sysUser 鐢ㄦ埛淇℃伅
+ * @param queryWrapper 鏌ヨ鏉′欢
* @return 鐢ㄦ埛淇℃伅闆嗗悎淇℃伅
*/
@DataPermission({
@DataColumn(key = "deptName", value = "d.dept_id"),
@DataColumn(key = "userName", value = "u.user_id")
})
- List<SysUser> selectUserList(SysUser sysUser);
+ List<SysUser> selectUserList(@Param(Constants.WRAPPER) Wrapper<SysUser> queryWrapper);
/**
* 鏍规嵁鏉′欢鍒嗛〉鏌ヨ宸查厤鐢ㄦ埛瑙掕壊鍒楄〃
*
- * @param user 鐢ㄦ埛淇℃伅
+ * @param queryWrapper 鏌ヨ鏉′欢
* @return 鐢ㄦ埛淇℃伅闆嗗悎淇℃伅
*/
@DataPermission({
@DataColumn(key = "deptName", value = "d.dept_id"),
@DataColumn(key = "userName", value = "u.user_id")
})
- Page<SysUser> selectAllocatedList(@Param("page") Page<SysUser> page, @Param("user") SysUser user);
+ Page<SysUser> selectAllocatedList(@Param("page") Page<SysUser> page, @Param(Constants.WRAPPER) Wrapper<SysUser> queryWrapper);
/**
* 鏍规嵁鏉′欢鍒嗛〉鏌ヨ鏈垎閰嶇敤鎴疯鑹插垪琛�
*
- * @param user 鐢ㄦ埛淇℃伅
+ * @param queryWrapper 鏌ヨ鏉′欢
* @return 鐢ㄦ埛淇℃伅闆嗗悎淇℃伅
*/
@DataPermission({
@DataColumn(key = "deptName", value = "d.dept_id"),
@DataColumn(key = "userName", value = "u.user_id")
})
- Page<SysUser> selectUnallocatedList(@Param("page") Page<SysUser> page, @Param("user") SysUser user);
+ Page<SysUser> selectUnallocatedList(@Param("page") Page<SysUser> page, @Param(Constants.WRAPPER) Wrapper<SysUser> queryWrapper);
/**
* 閫氳繃鐢ㄦ埛鍚嶆煡璇㈢敤鎴�
diff --git a/ruoyi-system/src/main/java/com/ruoyi/system/mapper/SysUserRoleMapper.java b/ruoyi-system/src/main/java/com/ruoyi/system/mapper/SysUserRoleMapper.java
index f9dd167..ce11d0f 100644
--- a/ruoyi-system/src/main/java/com/ruoyi/system/mapper/SysUserRoleMapper.java
+++ b/ruoyi-system/src/main/java/com/ruoyi/system/mapper/SysUserRoleMapper.java
@@ -10,4 +10,6 @@
*/
public interface SysUserRoleMapper extends BaseMapperPlus<SysUserRoleMapper, SysUserRole, SysUserRole> {
+ Long selectUserIdByRoleId(Long roleId);
+
}
diff --git a/ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysUserServiceImpl.java b/ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysUserServiceImpl.java
index 6ec437b..8fca17b 100644
--- a/ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysUserServiceImpl.java
+++ b/ruoyi-system/src/main/java/com/ruoyi/system/service/impl/SysUserServiceImpl.java
@@ -2,11 +2,15 @@
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ObjectUtil;
+import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
+import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
+import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ruoyi.common.constant.UserConstants;
import com.ruoyi.common.core.domain.PageQuery;
+import com.ruoyi.common.core.domain.entity.SysDept;
import com.ruoyi.common.core.domain.entity.SysRole;
import com.ruoyi.common.core.domain.entity.SysUser;
import com.ruoyi.common.core.page.TableDataInfo;
@@ -26,6 +30,7 @@
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
+import java.util.Map;
import java.util.stream.Collectors;
/**
@@ -39,6 +44,7 @@
public class SysUserServiceImpl implements ISysUserService {
private final SysUserMapper baseMapper;
+ private final SysDeptMapper deptMapper;
private final SysRoleMapper roleMapper;
private final SysPostMapper postMapper;
private final SysUserRoleMapper userRoleMapper;
@@ -46,7 +52,7 @@
@Override
public TableDataInfo<SysUser> selectPageUserList(SysUser user, PageQuery pageQuery) {
- Page<SysUser> page = baseMapper.selectPageUserList(pageQuery.build(), user);
+ Page<SysUser> page = baseMapper.selectPageUserList(pageQuery.build(), this.buildQueryWrapper(user));
return TableDataInfo.build(page);
}
@@ -58,7 +64,28 @@
*/
@Override
public List<SysUser> selectUserList(SysUser user) {
- return baseMapper.selectUserList(user);
+ return baseMapper.selectUserList(this.buildQueryWrapper(user));
+ }
+
+ private Wrapper<SysUser> buildQueryWrapper(SysUser user) {
+ Map<String, Object> params = user.getParams();
+ QueryWrapper<SysUser> wrapper = Wrappers.query();
+ wrapper.eq("u.del_flag", UserConstants.USER_NORMAL)
+ .eq(ObjectUtil.isNotNull(user.getUserId()), "u.user_id", user.getUserId())
+ .like(StringUtils.isNotBlank(user.getUserName()), "u.user_name", user.getUserName())
+ .eq(StringUtils.isNotBlank(user.getStatus()), "u.status", user.getStatus())
+ .like(StringUtils.isNotBlank(user.getPhonenumber()), "u.phonenumber", user.getPhonenumber())
+ .between(params.get("beginTime") != null && params.get("endTime") != null,
+ "u.create_time", params.get("beginTime"), params.get("endTime"))
+ .and(ObjectUtil.isNotNull(user.getDeptId()), w -> {
+ List<SysDept> deptList = deptMapper.selectList(new LambdaQueryWrapper<SysDept>()
+ .select(SysDept::getDeptId)
+ .apply("find_in_set({0},ancestors)", user.getDeptId()));
+ w.eq("u.dept_id", user.getDeptId())
+ .or()
+ .in("u.dept_id", deptList.stream().map(SysDept::getDeptId).collect(Collectors.toList()));
+ });
+ return wrapper;
}
/**
@@ -69,7 +96,13 @@
*/
@Override
public TableDataInfo<SysUser> selectAllocatedList(SysUser user, PageQuery pageQuery) {
- Page<SysUser> page = baseMapper.selectAllocatedList(pageQuery.build(), user);
+ QueryWrapper<SysUser> wrapper = Wrappers.query();
+ wrapper.eq("u.del_flag", UserConstants.USER_NORMAL)
+ .eq(ObjectUtil.isNotNull(user.getRoleId()), "r.role_id", user.getRoleId())
+ .like(StringUtils.isNotBlank(user.getUserName()), "u.user_name", user.getUserName())
+ .eq(StringUtils.isNotBlank(user.getStatus()), "u.status", user.getStatus())
+ .like(StringUtils.isNotBlank(user.getPhonenumber()), "u.phonenumber", user.getPhonenumber());
+ Page<SysUser> page = baseMapper.selectAllocatedList(pageQuery.build(), wrapper);
return TableDataInfo.build(page);
}
@@ -81,7 +114,14 @@
*/
@Override
public TableDataInfo<SysUser> selectUnallocatedList(SysUser user, PageQuery pageQuery) {
- Page<SysUser> page = baseMapper.selectUnallocatedList(pageQuery.build(), user);
+ Long userId = userRoleMapper.selectUserIdByRoleId(user.getRoleId());
+ QueryWrapper<SysUser> wrapper = Wrappers.query();
+ wrapper.eq("u.del_flag", UserConstants.USER_NORMAL)
+ .and(w -> w.ne("r.role_id", user.getRoleId()).or().isNull("r.role_id"))
+ .notIn("u.user_id", userId)
+ .like(StringUtils.isNotBlank(user.getUserName()), "u.user_name", user.getUserName())
+ .like(StringUtils.isNotBlank(user.getPhonenumber()), "u.phonenumber", user.getPhonenumber());
+ Page<SysUser> page = baseMapper.selectUnallocatedList(pageQuery.build(), wrapper);
return TableDataInfo.build(page);
}
diff --git a/ruoyi-system/src/main/resources/mapper/system/SysUserMapper.xml b/ruoyi-system/src/main/resources/mapper/system/SysUserMapper.xml
index ef968ad..c76abda 100644
--- a/ruoyi-system/src/main/resources/mapper/system/SysUserMapper.xml
+++ b/ruoyi-system/src/main/resources/mapper/system/SysUserMapper.xml
@@ -87,26 +87,7 @@
u.status, u.del_flag, u.login_ip, u.login_date, u.create_by, u.create_time, u.remark, 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="user.userId != null and user.userId != 0">
- AND u.user_id = #{user.userId}
- </if>
- <if test="user.userName != null and user.userName != ''">
- AND u.user_name like concat('%', #{user.userName}, '%')
- </if>
- <if test="user.status != null and user.status != ''">
- AND u.status = #{user.status}
- </if>
- <if test="user.phonenumber != null and user.phonenumber != ''">
- AND u.phonenumber like concat('%', #{user.phonenumber}, '%')
- </if>
- <if test="user.params.beginTime != null and user.params.endTime != null">
- AND u.create_time between #{user.params.beginTime} and #{user.params.endTime}
- </if>
- <if test="user.deptId != null and user.deptId != 0">
- AND (u.dept_id = #{user.deptId} OR u.dept_id IN ( SELECT t.dept_id FROM sys_dept t WHERE find_in_set(#{user.deptId},
- ancestors) ))
- </if>
+ ${ew.getCustomSqlSegment}
</select>
<select id="selectUserList" parameterType="SysUser" resultMap="SysUserResult">
@@ -114,26 +95,7 @@
u.status, u.del_flag, u.login_ip, u.login_date, u.create_by, u.create_time, u.remark, 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="userId != null and userId != 0">
- 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="params.beginTime != null and params.endTime != null">
- AND u.create_time between #{params.beginTime} and #{params.endTime}
- </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>
+ ${ew.getCustomSqlSegment}
</select>
<select id="selectAllocatedList" parameterType="SysUser" resultMap="SysUserResult">
@@ -142,13 +104,7 @@
left join sys_dept d on u.dept_id = d.dept_id
left join sys_user_role ur on u.user_id = ur.user_id
left join sys_role r on r.role_id = ur.role_id
- where u.del_flag = '0' and r.role_id = #{user.roleId}
- <if test="user.userName != null and user.userName != ''">
- AND u.user_name like concat('%', #{user.userName}, '%')
- </if>
- <if test="user.phonenumber != null and user.phonenumber != ''">
- AND u.phonenumber like concat('%', #{user.phonenumber}, '%')
- </if>
+ ${ew.getCustomSqlSegment}
</select>
<select id="selectUnallocatedList" parameterType="SysUser" resultMap="SysUserResult">
@@ -157,14 +113,7 @@
left join sys_dept d on u.dept_id = d.dept_id
left join sys_user_role ur on u.user_id = ur.user_id
left join sys_role r on r.role_id = ur.role_id
- where u.del_flag = '0' and (r.role_id != #{user.roleId} or r.role_id IS NULL)
- and u.user_id not in (select u.user_id from sys_user u inner join sys_user_role ur on u.user_id = ur.user_id and ur.role_id = #{user.roleId})
- <if test="user.userName != null and user.userName != ''">
- AND u.user_name like concat('%', #{user.userName}, '%')
- </if>
- <if test="user.phonenumber != null and user.phonenumber != ''">
- AND u.phonenumber like concat('%', #{user.phonenumber}, '%')
- </if>
+ ${ew.getCustomSqlSegment}
</select>
<select id="selectUserByUserName" parameterType="String" resultMap="SysUserResult">
diff --git a/ruoyi-system/src/main/resources/mapper/system/SysUserRoleMapper.xml b/ruoyi-system/src/main/resources/mapper/system/SysUserRoleMapper.xml
index 3066f47..2c3d182 100644
--- a/ruoyi-system/src/main/resources/mapper/system/SysUserRoleMapper.xml
+++ b/ruoyi-system/src/main/resources/mapper/system/SysUserRoleMapper.xml
@@ -8,5 +8,10 @@
<result property="userId" column="user_id"/>
<result property="roleId" column="role_id"/>
</resultMap>
+ <select id="selectUserIdByRoleId" resultType="Long">
+ select u.user_id from sys_user u
+ inner join sys_user_role ur
+ on u.user_id = ur.user_id and ur.role_id = #{roleId}
+ </select>
</mapper>
--
Gitblit v1.9.3