Mybatis 动态 sql 可以在 Xml 映射文件内,以标签的形式编写动态 sql执行原理是根据表达式的值 ,完成逻辑判断, 并动态拼接 sql 的功能。
常见的动态SQL标签:
-
if
<select id="selectByStudentSelective" resultMap="BaseResultMap" parameterType="com.homejim.mybatis.entity.Student">
select
<include refid="Base_Column_List" />
from student
where 1=1
<if test="name != null and name !=''">
and name like concat('%', #{name}, '%')
</if>
<if test="sex != null">
and sex=#{sex}
</if>
</select>
-
choose
choose when otherwise 标签可以帮我们实现 if else 的逻辑。一个 choose 标签至少有一个 when, 最多一个otherwise。
<select id="selectByIdOrName" resultMap="BaseResultMap" parameterType="com.homejim.mybatis.entity.Student">
select
<include refid="Base_Column_List" />
from student
where 1=1
<choose>
<when test="studentId != null">
and student_id=#{studentId}
</when>
<when test="name != null and name != ''">
and name=#{name}
</when>
<otherwise>
and 1=2
</otherwise>
</choose>
</select>
-
foreach
遍历集合或者Map,执行一些批量操作
<insert id="insertList">
insert into student(name, phone, email, sex, locked)
values
<foreach collection="list" item="student" separator=",">
(
#{student.name}, #{student.phone},#{student.email},
#{student.sex},#{student.locked}
)
</foreach>
</insert>
-
where和if搭配
<select id="selectByStudentSelectiveWhereTag" resultMap="BaseResultMap" parameterType="com.homejim.mybatis.entity.Student">
select
<include refid="Base_Column_List" />
from student
<where>
<if test="name != null and name !=''">
and name like concat('%', #{name}, '%')
</if>
<if test="sex != null">
and sex=#{sex}
</if>
</where>
</select>