Mybatis - 3 动态sql

动态sql

• if
• choose (when, otherwise)
• trim (where, set)
• foreach

2.1 where自动删首and

2.2 set自动删尾逗号，

2.3 choose-when相当于java中的switch-case，otherwise不是必要的

2.4 使用select时需要指定resultType或者resultMap

2.5 trim可以代替where和set使用

2.6 foreach多用于in后

2.7 include可以提高代码复用但是降低代码可读性

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.bjsxt.mapper.StudentMapper">
	<select id="selectByChoose" resultType="Student">
		select id,name,age,score from student 
		<where>
			<choose>
				<when test="name != null and name !=''">
					and name like '%' #{name} '%'
				</when>
				<when test="age > 0">
					and age >#{age}
				</when>
				<otherwise>
					and 1=1
				</otherwise>
			</choose>
				 
		</where>
	</select>
	
	<update id="updateBySet">
		update student 
		<set>
			<if test="name != null and name !=''">
				name=#{name},
			</if>
			<if test="age > 0">
				age=#{age},
			</if>
			<if test="score > 0">
				score=#{score}
			</if>
		</set> 
			where id=#{id}
		
	</update>
	
	<select id="selectByTrim" resultType="Student">
		select id,name,age,score from student 
		<trim prefix="where" prefixOverrides="AND |OR">
			<if test="name != null and name !=''">
				and name like '%' #{name} '%'
			</if>
			<if test="age > 0">
				and age>#{age}
			</if>
			
		</trim>
		
	</select>
	
	<update id="updateByTrim">
		update student 
		<trim prefix="set" suffixOverrides=",">
			<if test="name != null and name !=''">
				name=#{name},
			</if>
			<if test="age > 0">
				age=#{age},
			</if>
			<if test="score > 0">
				score=#{score}
			</if>
		</trim>
			where id=#{id}
		
	</update>
	
	<select id="forEachArray" resultType="Student">
		<!-- select id,name,age,score from student where id in(1,2) -->
		select id,name,age,score from student 
		<if test="array.length >0">
			where id in 
			<foreach collection="array" item="myid" open="(" close=")" separator=",">
				#{myid}
			</foreach>
		</if>
	</select>
	
	<select id="forEachList" resultType="Student">
		<!-- select id,name,age,score from student where id in(1,2) -->
		select id,name,age,score from student 
		<if test="list.size >0">
			where id in 
			<foreach collection="list" item="myid" open="(" close=")" separator=",">
				#{myid}
			</foreach>
		</if>
	</select>
	
	<select id="forEachList1" resultType="Student">
		<!-- select id,name,age,score from student where id in(1,2) -->
		<include refid="selectAll"/> from student 
		<if test="list.size >0">
			where id in 
			<foreach collection="list" item="stu" open="(" close=")" separator=",">
				#{stu.id}
			</foreach>
		</if>
	</select>
	
	<sql id="selectAll">
		select id,name,age,score
	</sql>
</mapper>

转载于:https://my.oschina.net/nan99/blog/1517924