mybatis之动态sql

查询(where+if或choose)

假如有需求就是传入user根据属性动态拼接sql语句。

<select id="getTypeByIdAndUsers" resultMap="getTypeByIdAndUsers">
    select * from user
    <where>
    	<if test="id!=null">
    		id=#{id}
    	</if>
    	<if test="name!=null">
    		and name=#{name}
    	</if>
	</where>
</select>

<select id="getTypeByIdAndUsers" resultMap="getTypeByIdAndUsers">
    select * from user
    <where>
    	<choose>
			<when test="id!=null">
				id=#{id}
			</when>
			<when test="name!=null">
				id=#{id}
			</when>
			<otherwise>
				1=1
			</otherwise>
		</choose>
	</where>
</select>

更新(set)

<update id="getTypeByIdAndUsers" resultMap="getTypeByIdAndUsers">
    update tabel
    <set>
    	<if test="id!=null">
    		id=#{id},
    	</if>
    	<if test="name!=null">
    		name=#{name}
    	</if>
    </set>
    where id=#{id}
</update>

插入

批量插入foreach

<!-- public void addUser(@Param("users") List<User> users)-->
<update id="addUser">
    insert into user(id,name,password,type_id)
    values
    <foreach collection="users" item="user" separator=",">
		(#{user.id},#{user.name},#{user.password},#{user.type.typeId})
	</foreach>
</update>

<!-- public void addUser(@Param("users") List<User> users)-->
<update id="addUser">
    <foreach collection="users" item="user" separator=","
    open="insert into user(id,name,password,type_id) values"
    close="">
		(#{user.id},#{user.name},#{user.password},#{user.type.typeId})
	</foreach>
</update>

动态sql插入

<!-- 动态sql插入方法二   start -->
  <!-- 对应的插入字段的名字 -->
  <sql id="key">
   <trim suffixOverrides=",">
    <if test="userCode!=null and userCode!=''">
     userCode,
    </if>
    <if test="userName!=null and userName!=''">
     userName,
    </if>
    <if test="userPassword!=null and userPassword!=''">
     userPassword,
    </if>
    <if test="gender!=null and gender!=''">
     gender,
    </if>
    <if test="address!=null and address!=''">
     address,
    </if>
    <if test="phone!=null and phone!=''">
     phone,
    </if>
   </trim>
  </sql>
  
  <!-- 对应的插入字段的值 -->
  <sql id="values">
   <trim suffixOverrides=",">
    <if test="userCode!=null and userCode!=''">
     #{userCode},
    </if>
    <if test="userName!=null and userName!=''">
     #{userName},
    </if>
    <if test="userPassword!=null and userPassword!=''">
     #{userPassword},
    </if>
    <if test="gender!=null and gender!=''">
     #{gender},
    </if>
    <if test="address!=null and address!=''">
     #{address},
    </if>
    <if test="phone!=null and phone!=''">
     #{phone},
    </if>
   </trim>
  </sql>
  <insert id="addUser2" parameterType="user">
   insert into smbms_user(<include refid="key"/>) 
    values(<include refid="values"/>)
  </insert>

重复字段的抽取(sql和include标签)

在配置文件中有许多重复字段怎么办呢？用sql标签抽取重复字段。

<select id="selectUser">
    select id,name,password from user
</select>

等价于：

<select id="selectUser">
    select <include refid="selectColumn"></include> from user
</select>
<sql id="selectColumn">
	id,name,password
</sql>