Example查询SQL之or查询and查询

Example查询SQL之or查询and查询

问题描述解决方法注意事项

问题描述

通过Example实现如下SQL语句功能

SELECT T.* FROM DAA_ORGINFO T WHERE T.DAA_ZTNO='参数' AND T.ID = '参数' OR T.DAA_ID_SJ='参数'

因为Example‘没有直接写or的方法，所以需要通过and来构造改SQL语句，首先我们分析一下，进行改造如下：

SELECT T.* FROM DAA_ORGINFO T WHERE (T.DAA_ZTNO='参数' AND T.ID = '参数') OR (T.DAA_ZTNO='参数' AND T.DAA_ID_SJ='参数')

解决方法

实现方式如下：

Example example = new Example(); example.setOrderByClause("CREATE_DATE DESC,ID"); Example.Criteria criteria = example.createCriteria(); criteria.andEqualTo(DaaOrginfo.class,"DAA_ZTNO","1"); criteria.andEqualTo(DaaOrginfo.class,"ID",daaId); Example.Criteria criteria2 = example.createCriteria(); criteria2.andEqualTo(DaaOrginfo.class,"DAA_ZTNO","1"); criteria2.andEqualTo(DaaOrginfo.class,"DAA_ID_SJ",daaId); example.or(criteria2);

xml中的SQL写法，因项目问题，example重新构造，可参考如下：

<select id="getDataGridByExample" resultType="com.ctg.pbis.partymanage.daaorginfo_x.entity.DaaOrginfo"> SELECT T.*, GET_NAME_BYDYID(T.DAB_ID_LXR) DAB_ID_LXR_NAME, GET_NAME_BYDYID(T.DAB_ID_ZZSJ) DAB_ID_ZZSJ_NAME FROM DAA_ORGINFO T WHERE 1=1 <if test="example != null"> <trim suffix="" suffixOverrides="and"> <trim suffix="" suffixOverrides="()"> AND <trim prefix="(" suffix=")"> <foreach collection="example.oredCriteria" item="criteria" separator="or"> <if test="criteria.valid"> <trim prefix="(" suffix=")" prefixOverrides="and"> <foreach collection="criteria.criteria" item="criterion"> <choose> <when test="criterion.noValue"> and ${criterion.condition} </when> <when test="criterion.singleValue"> and ${criterion.condition} #{criterion.value} </when> <when test="criterion.betweenValue"> and ${criterion.condition} #{criterion.value} and #{criterion.secondValue} </when> <when test="criterion.listValue"> and ${criterion.condition} <foreach collection="criterion.value" item="listItem" open="(" close=")" separator=","> #{listItem} </foreach> </when> </choose> </foreach> </trim> </if> </foreach> </trim> </trim> </trim> </if> <if test="example.orderByClause != null"> order by ${example.orderByClause} </if> </select>

注意事项

如SQL出现参数异常，请在where后面添加1 = 1，因为exampl的封装中首先加入了and关键字，这回导致SQL报错。

完。

如果你看到了这里，觉得文章写得不错就给个赞,关注公众号，可订阅更多干货？如果你觉得那里值得改进的，请给我留言，一定会认真查询，修正不足，谢谢！