内容"/>
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>
更多推荐
mybatis提取公用的sql内容
发布评论