MyBatis 提供了强大的动态 SQL 功能,允许你基于变量值构建不同的 SQL 查询。以下是几种主要的实现方式:
<if>
标签进行条件判断<select id="findUsers" resultType="User">
SELECT * FROM users
WHERE 1=1
<if test="name != null">
AND name = #{name}
</if>
<if test="age != null">
AND age = #{age}
</if>
</select>
<choose>
, <when>
, <otherwise>
实现多条件选择<select id="findActiveUsers" resultType="User">
SELECT * FROM users
WHERE status = 'ACTIVE'
<choose>
<when test="role == 'admin'">
AND admin = 1
</when>
<when test="role == 'manager'">
AND manager = 1
</when>
<otherwise>
AND guest = 1
</otherwise>
</choose>
</select>
<where>
标签智能处理 WHERE 子句<select id="findUsers" resultType="User">
SELECT * FROM users
<where>
<if test="name != null">
AND name = #{name}
</if>
<if test="email != null">
AND email = #{email}
</if>
</where>
</select>
<set>
标签动态更新字段<update id="updateUser">
UPDATE users
<set>
<if test="name != null">name = #{name},</if>
<if test="email != null">email = #{email},</if>
<if test="age != null">age = #{age},</if>
</set>
WHERE id = #{id}
</update>
<foreach>
标签处理集合参数<select id="findUsersByIds" resultType="User">
SELECT * FROM users
WHERE id IN
<foreach item="id" collection="ids" open="(" separator="," close=")">
#{id}
</foreach>
</select>
<bind>
标签创建变量<select id="findUsersByName" resultType="User">
<bind name="pattern" value="'%' + name + '%'" />
SELECT * FROM users
WHERE name LIKE #{pattern}
</select>
在动态 SQL 中可以使用 OGNL 表达式进行更复杂的判断:
<if test="@java.util.Objects@equals(status, 'active')">
AND active = 1
</if>
通过以上方法,你可以灵活地根据变量值构建不同的 SQL 语句,满足各种业务场景的需求。