Mybatis示例and含or拼接及Mybatis-Plus包装类QueryWrapper实现--＞and ( ... or ... or...) 或or ( ... and ... and ...)

Mybatis示例and条件含or拼接及Mybatis-Plus包装类实现--＞and ( ... or ... or...) 或or ( ... and ... and ...)或and ( ... or ... in...)

需求需要进行对数据逻辑返回，创建者和审核者对数据有查看逻辑，其他人则查询不到相关数据。

第一种

and ( ... or ... or...) 

queryWrapper.and(wrapper -> wrapper.like("nick_name", "张三") .or().like("user_name", "张三");//查询昵称等于张三的或者登录姓名等于张三的全部数据

第二种：

or ( ... and ... and ...) 

wrapper.or(wrapper -> wrapper.ne("age", 12).ne("age",20);

//年纪既不等于18也不等于20

wrapper不限于.eq() 、.gt() 等方法。

sql很简单,mapper中的方法如下：

@Select("select * from table " + 
"where id =  #{id} and (creator = #{userName} or reviewer = #{userName} "))
Object selectByIdAndLogicPrivilige(long id, String userName);

或者使用Mybatis-Plus包装类进行数据查询

QueryWrapper<ObjectEntity> objectWrapper = new QueryWrapper<>();
objectWrapper.eq("id", id)
                .and(wrapper -> wrapper.eq("creator",userName).or().eq("reviewer", userName));
 
log.info("包装器返回数据： {}", objectDao.selectOne(objectWrapper));

 and ( ... or ... in...)

SELECT
	* 
FROM
	t_object 
WHERE
	( id = ? AND ( userName = ? OR reviewer = ? OR state IN ( ?,? ) ) )

 and ( ... or ... like...)

SELECT
	* 
FROM
	t_thing_model_review 
WHERE
	reviewer_number = ? 
	AND result IN ( - 1, 0, 1, 2 ) 
	AND type = ? 
	AND (
	NAME LIKE concat( '%',?, '%' ) 
	OR creator_name LIKE concat( '%',?, '%' ) 
	OR creator_number LIKE concat( '%',?, '%' ) 
	)

select * from (select
        *
        from t_object_model
         WHERE creator_number = ?
            or state in (1,20) 
        ) tempTable
         WHERE 1 = 1            
                AND category_id = ?                       
                AND (INSTR(name,?)
                OR INSTR(creator_name,?)
                OR INSTR(creator_number,?))

mapper，模糊匹配如果写的方式如下，需要注意单个字符为'%'和'_'的情况，否则会全匹配，控制层参数需要进行相关拦截！

AND (name like concat('%',#{req.keyword},'%')
 OR creator_name like concat('%',#{req.keyword},'%') 
 OR creator_number like concat('%',#{req.keyword},'%'))