第四课动态SQL-优快云博客

本文链接：https://blog.youkuaiyun.com/code34/article/details/127007504

本文详细介绍了动态SQL在配置SQL语句时的使用，包括if、foreach、choose、trim和where等标签的用法，展示了如何根据条件动态生成SQL片段，避免多余的逻辑运算符，并在多条件组合查询时保持SQL的简洁性。动态SQL在处理复杂查询和批量操作时能提供更高的灵活性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

动态SQL

配置SQL语句时，可以添加一些动态元素，类似于JSTL。

if

根据条件添加SQL语句的部分

<if test="value != null and value.length > 0">
  where name like '%${value}%'
</if>

foreach

<delete id="batchDelete">
  delete from dept
  where id in 
  <!-- 遍历数组，生成SQL  (1, 2, 3) -->
  <foreach collection="ids" item="id"
  	open="(" close=")" separator=",">
  	#{id}
  </foreach>
</delete>

choose

<choose>
   <when test="条件"></when>
   <when test="条件"></when>
   ...
   <otherwise></otherwise>
</choose>

trim

set

去掉update中多余的逗号,

<update id="update">
	update dept 
	<set>
		<if test="name != null">name = #{name}, </if>
		<if test="room != null">room = #{room}, </if>
		<if test="phone != null">phone = #{phone}, </if>
		<if test="email != null">email = #{email} </if>
	</set>
	where id = #{id}
</update>

where

<!-- 多条件组合查询 -->
<select id="select" resultType="com.situ.entity.Dept">
	select * from dept
	<where> 
		<if test="name != null and name.length > 0"> name like '%${name}%'</if>
		<if test="room != null and room.length > 0"> and room like '%${room}%'</if>
		<if test="phone != null and phone.length > 0"> and phone like '%${phone}%'</if>
		<if test="email != null and email.length > 0"> and email like '%${email}%' </if>
	</where>
</select>