在 MyBatis 中,你可以通过多种方式根据变量值动态执行不同的 SQL 语句。以下是几种常用的方法:
<if>
标签<select id="findUsers" parameterType="map" 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" parameterType="map" resultType="User">
SELECT * FROM users
WHERE status = 'ACTIVE'
<choose>
<when test="name != null">
AND name like #{name}
</when>
<when test="email != null">
AND email = #{email}
</when>
<otherwise>
AND is_vip = 1
</otherwise>
</choose>
</select>
<where>
标签自动处理 WHERE 条件<select id="findUsers" parameterType="map" resultType="User">
SELECT * FROM users
<where>
<if test="name != null">
AND name = #{name}
</if>
<if test="age != null">
AND age = #{age}
</if>
</where>
</select>
<set>
标签动态更新<update id="updateUser" parameterType="User">
UPDATE users
<set>
<if test="name != null">name=#{name},</if>
<if test="age != null">age=#{age},</if>
<if test="email != null">email=#{email},</if>
</set>
WHERE id=#{id}
</update>
<foreach>
标签处理集合<select id="findUsersByIds" parameterType="list" resultType="User">
SELECT * FROM users
WHERE id IN
<foreach item="id" collection="list" open="(" separator="," close=")">
#{id}
</foreach>
</select>
<script>
标签在注解中使用动态 SQL@Update("<script>" +
"UPDATE users " +
"<set>" +
" <if test='name != null'>name=#{name},</if>" +
" <if test='age != null'>age=#{age},</if>" +
"</set>" +
"WHERE id=#{id}" +
"</script>")
void updateUser(User user);
public class UserSqlProvider {
public String findUsers(Map<String, Object> params) {
return new SQL() {{
SELECT("*");
FROM("users");
if (params.get("name") != null) {
WHERE("name = #{name}");
}
if (params.get("age") != null) {
WHERE("age = #{age}");
}
}}.toString();
}
}
// 在Mapper接口中使用
@SelectProvider(type = UserSqlProvider.class, method = "findUsers")
List<User> findUsers(Map<String, Object> params);
<if>
标签<choose>
标签<set>
标签避免多余的逗号<where>
标签处理动态 WHERE 条件<foreach>
标签处理集合参数这些动态 SQL 功能可以灵活组合使用,以满足各种复杂的业务需求。