要查詢的sql:

select * from user where name = ? and (age=? or city=?)；

方法1：不使用Example查詢

直接在usermapper.xml中修改sql

方法2：使用Example查詢

sql可轉換成

select * from user where (name = ? and age=?) or (name=? and city=?)；

然后使用Example查詢

UserExample example=new UserExample();example.or().orAgeLike('%'+searchParam+'%').andNameEqualTo(userName);example.or().orCityLike('%'+searchParam+'%').andNameEqualTo(userName);

補充知識：MySQL/Mybatis多個AND和OR混用注意事項

mysql中AND的優先級高于OR，所以在查詢時，會優先執行AND條件，除非使用（）來將一個AND和OR括起來，這樣才能使得OR得以按照語句的順序執行。

如下圖所示：

java測試代碼

@Test public void TestMutil(){ Species species = new Species(); ArrayList<String> arrayList = new ArrayList<String>(); arrayList.add('長喙蚤'); arrayList.add('尤氏'); List<Species> querySpeciesesListByMutilCondition = this.speciesMapper.querySpeciesesListByMutilCondition(arrayList, species.getEnglishName(), species.getHost(), species.getPosition(), species.getLocation(), species.getPhylum(), species.getClassName(), species.getOrder(), species.getFamily(), species.getJenus()); for (Species s : querySpeciesesListByMutilCondition) { System.out.println(s); } System.out.println(querySpeciesesListByMutilCondition.size()); }

Mapper文件中沒有使用（）放在語句中執行情況

<select resultType='Species'> SELECT * FROM t_json_species <where> <if test='englisName != null and englisName != ’’'>AND englishName like CONCAT(’%’,#{englishName},’%’) OR sameName like CONCAT(’%’,#{englishName},’%’)</if> <if test='host != null and host != ’’'>AND host like CONCAT(’%’,#{host},’%’)</if> <if test='position != null and position != ’’'>AND position like CONCAT(’%’,#{position},’%’)</if> <if test='location != null and location != ’’'>AND location like CONCAT(’%’,#{location},’%’)</if> <if test='phylumName != null and phylumName != ’’'>AND phylumName = #{phylumName}</if> <if test='className != null and className != ’’'>AND className = #{className}</if> <if test='orderName != null and orderName != ’’'>AND orderName = #{orderName}</if> <if test='familyName != null and familyName != ’’'>AND familyName = #{familyName}</if> <if test='jenusName != null and jenusName != ’’'>AND jenusName = #{jenusName}</if> <if test='nameList != null and nameList != ’’'> <foreach collection='nameList' item='name' >AND name like CONCAT(’%’,#{name},’%’) OR sameName like CONCAT(’%’,#{name},’%’)</foreach> </if> </where> </select>

Mapper文件中使用（）放在語句中執行情況

<select resultType='Species'> SELECT * FROM t_json_species <where> <if test='englisName != null and englisName != ’’'>AND englishName like CONCAT(’%’,#{englishName},’%’) OR sameName like CONCAT(’%’,#{englishName},’%’)</if> <if test='host != null and host != ’’'>AND host like CONCAT(’%’,#{host},’%’)</if> <if test='position != null and position != ’’'>AND position like CONCAT(’%’,#{position},’%’)</if> <if test='location != null and location != ’’'>AND location like CONCAT(’%’,#{location},’%’)</if> <if test='phylumName != null and phylumName != ’’'>AND phylumName = #{phylumName}</if> <if test='className != null and className != ’’'>AND className = #{className}</if> <if test='orderName != null and orderName != ’’'>AND orderName = #{orderName}</if> <if test='familyName != null and familyName != ’’'>AND familyName = #{familyName}</if> <if test='jenusName != null and jenusName != ’’'>AND jenusName = #{jenusName}</if> <if test='nameList != null and nameList != ’’'> <foreach collection='nameList' item='name' >AND (name like CONCAT(’%’,#{name},’%’) OR sameName like CONCAT(’%’,#{name},’%’))</foreach> </if> </where> </select>

補充：

如果這里使用多個%來解決上述的含有多個OR和AND情況，那么所實現功能會有問題，因為多個關鍵詞有%來連接，會有一個次序問題。具體效果見下圖

以上這篇mybatis and,or復合查詢操作就是小編分享給大家的全部內容了，希望能給大家一個參考，也希望大家多多支持好吧啦網。