/** * wrapper.ne 不等于 * 查询 user_name 不等于 【阿达】的数据 * Execute SQL：SELECT user_id,dept_id,login_name,user_name,user_type,email,phonenumber,sex,avatar,status,deleted,project_id,login_ip,login_date,version FROM sys_user WHERE deleted='1' AND (user_name <> '阿达') */ @Test voidselectWrapperNt(){ QueryWrapperwrapper=newQueryWrapper(); wrapper.ne("user_name", "阿达"); System.out.println(mapper.selectList(wrapper)); }
模糊查询
wrapper.like 模糊查询
1 2 3 4 5 6 7 8 9 10 11
/** * wrapper.like 模糊查询 * 查询 user_name 中包含了【测试】的数据 * Execute SQL：SELECT user_id,dept_id,login_name,user_name,user_type,email,phonenumber,sex,avatar,status,deleted,project_id,login_ip,login_date,version FROM sys_user WHERE deleted='1' AND (user_name LIKE '%测试%') */ @Test voidselectWrapperLike() { QueryWrapperwrapper=newQueryWrapper(); wrapper.like("user_name", "测试"); System.out.println(mapper.selectList(wrapper)); }
左模糊查询
wrapper.like 左模糊查询
1 2 3 4 5 6 7 8 9 10 11
/** * wrapper.like 左模糊查询 * 查询 user_name 中以【测试】结尾的数据 * Execute SQL：SELECT user_id,dept_id,login_name,user_name,user_type,email,phonenumber,sex,avatar,status,deleted,project_id,login_ip,login_date,version FROM sys_user WHERE deleted='1' AND (user_name LIKE '%测试') */ @Test voidselectWrapperLikeLeft() { QueryWrapperwrapper=newQueryWrapper(); wrapper.likeLeft("user_name", "测试"); System.out.println(mapper.selectList(wrapper)); }
右模糊查询
wrapper.likeRight 右模糊查询
1 2 3 4 5 6 7 8 9 10 11
/** * wrapper.likeRight 右模糊查询 * 查询 user_name 中以【测试】开头的数据 * Execute SQL：SELECT user_id,dept_id,login_name,user_name,user_type,email,phonenumber,sex,avatar,status,deleted,project_id,login_ip,login_date,version FROM sys_user WHERE deleted='1' AND (user_name LIKE '测试%') */ @Test voidselectWrapperLikeRight() { QueryWrapperwrapper=newQueryWrapper(); wrapper.likeRight("user_name", "测试"); System.out.println(mapper.selectList(wrapper)); }
联合查询
wrapper.inSql 联合查询
1 2 3 4 5 6 7 8 9 10 11 12
/** * wrapper.inSql 联合查询 * 查询 user_id > 10004,status = 1 * Execute SQL：SELECT user_id,dept_id,login_name,user_name,user_type,email,phonenumber,sex,avatar,status,deleted,project_id,login_ip,login_date,version FROM sys_user WHERE deleted='1' AND (user_id IN (select user_id from sys_user where user_id > 10004) AND status IN (select status from sys_user where status = 1)) */ @Test voidselectWrapperInsql() { QueryWrapperwrapper=newQueryWrapper(); wrapper.inSql("user_id", "select user_id from sys_user where user_id > 10004"); wrapper.inSql("status", "select status from sys_user where status = 1"); System.out.println(mapper.selectList(wrapper)); }
升序
wrapper.orderByAsc 升序排列
1 2 3 4 5 6 7 8 9 10 11
/** * wrapper.orderByAsc 升序排列 * 查询数据并根据 user_id 升序排列 * Execute SQL：SELECT user_id,dept_id,login_name,user_name,user_type,email,phonenumber,sex,avatar,status,deleted,project_id,login_ip,login_date,version FROM sys_user WHERE deleted='1' ORDER BY user_id ASC */ @Test voidselectWrapperOrderByAsc() { QueryWrapperwrapper=newQueryWrapper(); wrapper.orderByAsc("user_id"); mapper.selectList(wrapper).forEach(System.out::println); }
降序
wrapper.orderByDesc 降序排列
1 2 3 4 5 6 7 8 9 10 11
/** * wrapper.orderByDesc 降序排列 * 查询数据并根据 user_id 降序排列 * Execute SQL：SELECT user_id,dept_id,login_name,user_name,user_type,email,phonenumber,sex,avatar,status,deleted,project_id,login_ip,login_date,version FROM sys_user WHERE deleted='1' ORDER BY user_id DESC */ @Test voidselectWrapperOrderByDesc() { QueryWrapperwrapper=newQueryWrapper(); wrapper.orderByDesc("user_id"); mapper.selectList(wrapper).forEach(System.out::println); }
降序+过滤
wrapper.orderByDesc 降序排列
wrapper.having 排序后过滤
1 2 3 4 5 6 7 8 9 10 11 12 13 14
/** * wrapper.orderByDesc 降序排列 * wrapper.having 排序后过滤 * 查询数据并根据 user_id 降序排列，只保留 status = 1 的数据（StatusEnums.启用 枚举中映射的为 1） * Execute SQL：SELECT user_id,dept_id,login_name,user_name,user_type,email,phonenumber,sex,avatar,status,deleted,project_id,login_ip,login_date,version FROM sys_user WHERE deleted='1' HAVING status ORDER BY user_id DESC */
@Component publicinterfaceSysUserMapperextendsBaseMapper<SysUser> { /** * * @param user_id 用户表的 id 主键 * @param status 是否禁用标识，用的枚举类 * @return 查询结果列表封装到 UserVO 中 */ //@Select("SELECT u.login_name,u.user_id,u.user_name,d.dept_name FROM sys_dept d,sys_user u WHERE u.dept_id=d.dept_id and u.user_id=#{user_id}") @Select("SELECT u.login_name,u.user_id,u.user_name,d.dept_name FROM sys_dept d,sys_user u WHERE u.dept_id=d.dept_id AND u.user_id=#{user_id} AND u.status=#{status}") List<UserVO> selectUserVoById(Integer user_id, StatusEnums status); }
调用 selectUserVoById
1 2 3 4 5 6 7 8 9 10
/** * selectUserVoById 自定义 sql * 需要传递两个参数，一个是用户ID，一个是启用状态（这里直接用的枚举中的） * Execute SQL：SELECT u.login_name,u.user_id,u.user_name,d.dept_name FROM sys_dept d,sys_user u WHERE u.dept_id=d.dept_id AND u.user_id=10003 AND u.status=1 */ @Test voidselectUserVoById(){ List<UserVO> userVOS = mapper.selectUserVoById(10003, StatusEnums.ENABLE); userVOS.forEach(System.out::println); }
