Mybatis And,Or复合查询操作

要查询的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得以按照语句的顺序执行。

如下图所示:

F4EABD6C-1F98-E110-774A-DEB8D26FA009.jpeg

java测试代码

D48B261D-198A-8220-CD57-21FD7CAF2CA0.jpeg

@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文件中没有使用()放在语句中执行情况

601AFA8B-4CAF-9A9D-E360-E19B51EF6B71.jpeg

<select id="querySpeciesesListByMutilCondition" 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文件中使用()放在语句中执行情况

B2DE27A6-6D05-8F1B-C5B9-9A9A1A620794.jpeg

<select id="querySpeciesesListByMutilCondition" 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情况,那么所实现功能会有问题,因为多个关键词有%来连接,会有一个次序问题。具体效果见下图

599CECF4-F0F0-EBD7-A7A1-6EF885E7967C.jpeg

收藏 (0)
评论列表
正在载入评论列表...
我是有底线的