Day09-13.Mybatis-动态SQL-sql&include
1.问题分析
如果说在一个映射配置文件中存在大量重复性的SQL片段,后期如果要修改就会很麻烦,代码复用性较差。
<select id="list" resultType="com.itheima.pojo.Emp">
select id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time from emp
where
<if test="name != null">
name like concat('%',#{name},'%')
</if>
<if test="gender != null">
and gender = #{gender}
</if>
<if test="begin != null and end != null">
and entrydate between #{begin} and #{end}
</if>
order by update_time desc
</select>
<select id="getById" resultType="com.itheima.pojo.Emp">
select id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time from emp
where id = #{id}
</select>
抽取SQL片段:
<sql id="commonSelect">
select id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time from emp
</sql>
//简化源代码:
<select id="list" resultType="com.itheima.pojo.Emp">
<include refid="commonSelect"/>
where
<if test="name != null">
name like concat('%',#{name},'%')
</if>
<if test="gender != null">
and gender = #{gender}
</if>
<if test="begin != null and end != null">
and entrydate between #{begin} and #{end}
</if>
order by update_time desc
</select>
<select id="getById" resultType="com.itheima.pojo.Emp">
<include refid="commonSelect"/>
where id = #{id}
</select>
2.小结:
1.XML映射文件
1.映射配置文件名与Mapper接口名一致,且放在相同的包下(同包同名)
2.映射配置文件的namespace属性与Mapper接口的全类名一致
3.XML映射文件中sql语句的id与Mapper接口中的方法名一致。
2.动态SQL
1.<if>
2.<where>
3.<set>
4.<foreach>
5.<sql>
6.<include>