Mapper.xml动态sql

if

<select id="selByCon" parameterType="People" resultType="People">
	select * from people where 1=1 
	<!-- OGNL 表达式,直接写 key 或对象的属性.不需要添加任何特殊字符号 -->
	<if test=" name != null and name!='' ">
		and name = #{name}
	</if>
	<if test=" age != null and age!=0 ">
		and age = #{age}
	</if>
</select>

where

<select id="selByCon2" parameterType="People" resultType="People">
	select * from people 
	<where> 
		<!-- OGNL 表达式,直接写 key 或对象的属性.不需要添加任何特殊字符号 -->
		<if test=" name != null and name!='' ">
			and name = #{name}
		</if>
		<if test=" age != null and age!=0 ">
			and age = #{age}
		</if>
		</where>
</select>

choose-when-otherwise

只会执行一个

<select id="selByCon3" parameterType="People" resultType="People">
		select * from people 
		<where> 
			<choose>
	        <when test=" name != null and name!='' ">
	            name = #{name}
	        </when>
	        <when test=" age != null and age!=0 ">
	            age = #{age}
	        </when>
			</choose>
		</where>
</select>

Set

<update id="upd" parameterType="People">
	update people 
         <set>
             id = #{id},
             <if test=" name != null and name!='' ">
	             name = #{name},
	        </if>
	        <if test=" age != null and age!=0 ">
	             age = #{age},
	        </if>
         </set>
           where  id = #{id}
</update>

trim

prefix 在前面添加内容
prefixOverrides 去掉前面内容
suffix 在后面添加内容
suffixOverrieds 去掉后面内容

<update id="upd2" parameterType="People">
         update people 
         <trim prefix="set" suffixOverrides=",">
              name = #{name},
         </trim>
         where id = #{id}
</update>

bind

给参数重新赋值
应用在模糊查询或者在原内容前或后添加内容

<select id="select001" parameterType="People" resultType="People">
       <bind name="name2"  value="'%'+name+'%'"></bind>
        SELECT * FROM people WHERE NAME LIKE #{name2};
</select>

foreach

循环

<select id="selectIn" parameterType="list" resultType="People">
	SELECT * FROM people WHERE age IN 
	<foreach collection="list" item="ages" open="(" close=")" separator=",">
		#{ages}
	</foreach>
</select>

<insert id="batchAdd" parameterType="list">
	insert into people values
		<trim suffixOverrides=",">
			<foreach collection="list" item="people">
				(default,#{people.name},#{people.age}),
			</foreach>
		</trim>
</insert>

sql和include

某些 SQL 片段如果希望重复利用,可以使用sql定义这个片段（复用）

<sql id="psql">
    id,name,age
</sql>
<select id="select001" parameterType="People" resultType="People">
    <bind name="name2"  value="'%'+name+'%'"></bind>
    SELECT <include refid="psql"/> FROM people WHERE NAME LIKE #{name2};
</select>