要查询的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 arraylist = new arraylist();
arraylist.add("长喙蚤");
arraylist.add("尤氏");
list 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 * from t_json_species
and englishname like concat('%',#{englishname},'%') or samename like concat('%',#{englishname},'%')
and host like concat('%',#{host},'%')
and position like concat('%',#{position},'%')
and location like concat('%',#{location},'%')
and phylumname = #{phylumname}
and classname = #{classname}
and ordername = #{ordername}
and familyname = #{familyname}
and jenusname = #{jenusname}
and name like concat('%',#{name},'%') or samename like concat('%',#{name},'%')
mapper文件中使用()放在语句中执行情况
select * from t_json_species
and englishname like concat('%',#{englishname},'%') or samename like concat('%',#{englishname},'%')
and host like concat('%',#{host},'%')
and position like concat('%',#{position},'%')
and location like concat('%',#{location},'%')
and phylumname = #{phylumname}
and classname = #{classname}
and ordername = #{ordername}
and familyname = #{familyname}
and jenusname = #{jenusname}
and (name like concat('%',#{name},'%') or samename like concat('%',#{name},'%'))
补充:
如果这里使用多个%来解决上述的含有多个or和and情况,那么所实现功能会有问题,因为多个关键词有%来连接,会有一个次序问题。具体效果见下图
以上这篇mybatis and,or复合查询操作就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持萬仟网。
希望与广大网友互动??
点此进行留言吧!