mybatis提取公用的sql内容

内容"/>

mybatis提取公用的sql内容

提取公用字段

<resultMap type="mptest.mybatistest.entity.GoodVo" id="userMap" ><result column="id" property="id" /><result column="name" property="name" /><collection property="orderList" ofType="mptest.mybatistest.entity.Order"><result column="orderId" property="orderId" /><result column="orderFee" property="orderFee" /></collection>
</resultMap><sql id="field" >u.id,u.name
</sql><select id="getGoodsList" resultMap="userMap" >select<include refid="field"></include> ,o.orderId,o.orderFeeFROMuser_test u left join  order_id_test  o on u.id=o.idwhere u.id = 1
</select>

表在使用的时候可能起别名，有别名的情况：

<resultMap type="mptest.mybatistest.entity.GoodVo" id="userMap"><result column="id" property="id"/><result column="name" property="name"/><collection property="orderList" ofType="mptest.mybatistest.entity.Order"><result column="orderId" property="orderId"/><result column="orderFee" property="orderFee"/></collection>
</resultMap><sql id="field">u.id,u.name
</sql><sql id="field1">${alias}.orderId,${alias}.orderFee
</sql><select id="getGoodsList" resultMap="userMap">select<include refid="field"></include>,<include refid="field1"><property name="alias" value="o"/></include>FROMuser_test u left join order_id_test o on u.id=o.idwhere u.id = 1
</select>

提取<where><if>共用代码

mybatis项目dao层中很多sql语句都会拥有某些相同的查询条件，以<where><if test=""></if></where>的形式拼接在sql语句后，一个两个的sql语句感觉不到什么，但是如果查询语句特别多，但是查询的条件总是类似的，那就可以考虑把<where><if>这部分代码抽取出来，封装一下，然后需要条件搜索的sql语句直接引用就可以了。

先来看下没有抽取代码之前的条件sql语句

第一条

<select id = "getUserEmailByProvinceAndOrderType" resultType="String">select DISTINCT(wo_responsibility) from t_view_workorder<where><if test="province != '全国' and province != null">wo_province = #{province}</if><if test="orderType != '全部' and orderType != null">and wo_type = #{orderType}</if><if test="email != ''">and wo_responsibility = #{email}</if></where>
</select>

第二条 

<select id = "getUndoneDelayOrderByProvinceAndOrderTypeAndUserEmail" resultType="com.chinamobile.sias.workorder.po.Workorder">select * from t_view_workorder<where><if test="province != '全国' and province != null">wo_province = #{province}</if><if test="orderType != '全部' and orderType != null">and wo_type = #{orderType}</if><if test="email != ''">and wo_responsibility = #{email}</if><if test="true"> and (wo_complete_time is null or wo_complete_time='') and (select curdate()) >= wo_regulations_time</if> 　　　　</where> 
</select>

以上是两条sql语句，可以看出，两个sql语句中有某些查询条件是相同的

<if test="province != '全国' and province != null">wo_province = #{province}
</if>
<if test="orderType != '全部' and orderType != null">and wo_type = #{orderType}
</if>
<if test="email != ''">and wo_responsibility = #{email}
</if>

此时我们就可以对此段判断条件进行提取。如下：

<sql id="common_where_if"><if test="province != '全国' and province != null">wo_province = #{province}</if><if test="orderType != '全部' and orderType != null">and wo_type = #{orderType}</if><if test="email != ''">and wo_responsibility = #{email}</if>
</sql>

此时把<where>标签下相同的判断条件提去了出来，id自己取，这里定为 common_where_if.

那么如何使用这段代码呢，如下：

<include refid="common_where_if"/>

格式如下：

<select id = "getUserEmailByProvinceAndOrderType" resultType="String">select DISTINCT(wo_responsibility) from t_view_workorder<where><include refid="common_where_if"/></where></select>