mysql语句使用in条件查询,从前端获取数组
1、post请求体中的json对象
@Data
public class UserSearchParam {
String userName;
@NotNull
Integer pageNum;
@NotNull
Integer pageSize;
// 权限表ID
Integer[] roleIds;
}
2、控制层接收数据
@PostMapping("getUserListByRoles")
public Object getInerUserList(@Valid @RequestBody UserSearchParam params) {
List<UserInfoDetailVo> userList = userInfoDetailService.getUserListByRoleIds(params);
return userList;
}
3、业务层进行数据处理
将名字进行模糊查询修改,数组修改为map类型传给mapper接口
@Override
public List<UserInfoDetailVo> getUserListByRoleIds(UserSearchParam params) {
params.setPageNum((params.getPageNum() - 1) * params.getPageSize());
if (StringUtils.hasText(params.getUserName())) {
params.setUserName("%" + params.getUserName() + "%");
}
Integer[] roleIds = params.getRoleIds();
Map map = new Hashtable();
for (int i = 0; i < roleIds.length; i++) {
map.put(i,roleIds[i]);
}
return userInfoDetailMapper.selectUserListByRoleIdsPage(params, map);
}
4、Mapper定义接口
List<UserInfoDetailVo> selectUserListByRoleIdsPage(@Param("params") UserSearchParam params, @Param("roleIds") Map map);
5、mybatix对应的xml文件
<select id="selectUserListByRoleIdsPage" resultType="com.example.demo.vo.UserInfoDetailVo">
select
user.id as id,
user.user_name as userName,
user.user_phone as userPhone,
user.create_time as createTime,
user.auth_not as authNot,
user_role.role_id as roleId,
user_role.role_name as userType,
user.account_status as accountStatus,
user.img_url as imgUrl,
user.real_idcard as realIdcard,
user.dept_id as deptId,
dept.dept_name as deptName
from
(yuan.user left join yuan.dept on user.dept_id = dept.dept_id), yuan.user_role
where user.user_role=user_role.role_id
<if test="params != null and roleIds.size() > 0 ">
and user_role.role_id in
<foreach
collection="roleIds"
item="roleId"
open="("
separator=","
close=")">
#{roleId}
</foreach>
</if>
<if test="params != null and params.userName != ''">
and user.user_name like #{params.userName}
</if>
<if test="params != null and params.deptId != null">
and user.dept_id = #{params.deptId}
</if>
limit #{params.pageNum}, #{params.pageSize};
</select>