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='参数')

• 解决方法

实现方式如下：

   //1.根据订单ID查出用户ID
            Example example=new Example(TOrderOrder.class);
            Example.Criteria criteria = example.createCriteria();
            criteria.andEqualTo("id",orderId)
                     .andGreaterThan("mark",0)
                    .andEqualTo("type",1)
                    .andEqualTo("isReturn",0);
            Example.Criteria criteria2 = example.createCriteria();
            criteria.andEqualTo("id",orderId)
                    .andGreaterThan("mark",0)
                    .andEqualTo("type",2)
                    .andEqualTo("isReturn",0);
            example.or(criteria2);
            List<TOrderOrder> orderInfolist= tOrderOrderDao.getListByExample(example);

• 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>