复习之MyBatis基础用法（三）——动态SQL

た入场券 2022-05-09 06:12 148阅读 0赞

## MyBatis 的强大特性之一便是它的动态 SQL 能力。 ##

如果你有使用 JDBC 或其他类似框架的经验，你就能体会到根据不同条件拼接 SQL 字符串有多么痛苦。拼接的时候要确保不能忘了必要的空格，还要注意省掉列名列表最后的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。

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

## if、where、set标签 ##

where 标签可以去掉第一个前and

<select id="selectUserBySexAndName" parameterType="User" resultType="User">
       			select * from e_user
       		<where>
       		
       		<if test="sex != null and sex != ''" >
       		sex=#{sex}
       		</if>
       		<if test="username !=null and username != ''" 
       		 >
       		 and username = #{username}
       		</if>
       		</where>
       	</select>

set拼接的修改字段会把最后的,删除

<update id="updateUser">
    		update smbms_user
    		<set>
    			
    			<if test="userName != null">
    				userName = #{userName},
    			</if>
    			<if test="userPassword != null">
    				userPassword = #{userPassword}
    			</if>
    		</set>
    		where id = #{id}
    	</update>

## trim标签 ##

*  prefixOverrides前缀重写删除前面的字符
 *  suffixOverrides后缀重写删除后面的字符

<select id="selectUserBySexAndName" parameterType="User" resultType="User">
       		<include refid="selectall"/> 
       		
       		<trim prefix="where" prefixOverrides="and | or" >
       
       		<if test="sex != null and sex != ''" >
       		and sex=#{sex}
       		</if>
       		<if test="username !=null and username != ''" 
       		 >
       		 and username = #{username}
       		</if>
       		
       		</trim>
       	</select>

## foreach标签 ##

*  三种数据类型list、数组、map  
    foreach的属性  
    collection 数据集  
    item 数据集中元素  
    open 开头  
    close 结尾  
    separator 分隔符

<select id="selectUserByIds" parameterType="QueryVo" resultType="User">
    	<include refid="selectall"/>
    	<where>
    		id in 
    		<foreach collection="idsList" item="id" separator="," open="(" close=")">
    			#{id}
    		</foreach>
    	</where>
    </select>
    
    <select id="selectUserByIdsA" parameterType="QueryVo" resultType="User">
    	<include refid="selectall"/>
    	<where>
    		id in 
    		<foreach collection="ids" item="id" separator="," open="(" close=")">
    			#{id}
    		</foreach>
    	</where>
    </select>
    <select id="selectUserByIdsB" parameterType="QueryVo" resultType="User">
    	<include refid="selectall"/>
    	<where>
    		id in 
    		
    		<foreach collection="roleList" item="map" separator="," open="(" close=")">
    			#{map}
    		</foreach>
    		add gender = #{gender}
    	</where>
    </select>

上述实体类

public class QueryVo implements Serializable {
    
    	private static final long serialVersionUID = 1L;
    	
    	private  User user;
    	
    	List<Integer> idsList;
    	
    	Integer[] ids;
    	
    	Map<String,Object> map;
    	//get、set方法省略

## choose、when标签 ##

*  choose when otherwise 类似于Java 的 switch 语句，choose 为 switch，when 为  
    case，otherwise 则为 default。

if标签是与(and)的关系，而 choose 是或(or)的关系

<select id="getUserListByChoose" resultType="User">
    		select * from smbms_user
    		<where >
    			<choose>
    				<when test="userName != null and userName != ''">
    					and userName like concat('%',#{userName},'%')
    				</when>
    				<when test="userPassword != null and userPassword != ''">
    					and userPassword = #{userPassword}
    				</when>
    				<otherwise>
    					and gender = #{gender}
    				</otherwise>
    			</choose>
    		</where>
    	</select>

## selectKey添加内容的主键值 ##

<insert id="addUser" parameterType="top.maniy.mybatisdemo.pojo.User">
       
       	<selectKey keyProperty="id" resultType="Integer" order="AFTER">
       		select LAST_INSERT_ID()
       	</selectKey>
       	insert into e_user (username,birthday,address,sex) 
       	values (#{username},#{birthday},#{address},#{sex})
       </insert>