mybatis and,or复合查询操作
程序员文章站
2022-07-02 18:26:59
要查询的sql:select * from user where name = ? and (age=? or city=?);方法1:不使用example查询直接在userm...
要查询的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 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文件中使用()放在语句中执行情况
<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情况,那么所实现功能会有问题,因为多个关键词有%来连接,会有一个次序问题。具体效果见下图
以上这篇mybatis and,or复合查询操作就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持。
上一篇: 纯编码实现Access数据库的建立或压缩
下一篇: 解决myBatis中删除条件的拼接问题
推荐阅读
-
在mybatis和PostgreSQL Json字段作为查询条件的解决方案
-
C# LINQ学习笔记二:LINQ标准查询操作概述
-
C#操作SQLite数据库方法小结(创建,连接,插入,查询,删除等)
-
Python SQL查询并生成json文件操作示例
-
Python操作mongodb数据库进行模糊查询操作示例
-
MySQL入门(三) 数据库表的查询操作【重要】
-
Django视图之ORM数据库查询操作API的实例
-
php 数组操作(增加,删除,查询,排序)等函数说明第1/2页
-
cmd 命令行模式操作数据库 添加查询 修改 删除 ( 表 字段 数据)
-
用Mysql进行emp、dept、salgrade表的相关查询操作