提取公用字段
<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.orderFee
FROM
user_test u left join order_id_test o on u.id=o.id
where 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>
FROM
user_test u left join order_id_test o on u.id=o.id
where 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>
