mybatis的${}和#{}的用法剖析

在实际项目用到的，搞了半天，决定总结下。

1. #{}的用法。

 

<select id="findAppUserById" parameterType="map" resultType="au">
		select * from sys_app_user
		where user_id = #{id}
	</select>

	<select id="getAppUser" parameterType="map" resultType="au">
		select * from sys_app_user where  type=#{type}
		 <if test="id != 'null' and  id !='' and id != null">
			and ascriptionId = #{id} 
		</if>
		<if test="school_info_id != null and school_info_id != ''">
			and school_info_id = #{school_info_id}
		</if>
		<if test="name != 'null' and name !='' and name != null">
			and  username like concat(concat('%', #{name}),'%')
		</if>
		<if test="list != null">
			and user_id not in
			<foreach collection="list" item="user_id" index="index" open="("
				separator="," close=")">
				#{user_id}
				</foreach>
		</if>
		limit #{start},#{pageCount}
</select>open="("
				separator="," close=")">
				#{user_id}
				</foreach>
		</if>
		limit #{start},#{pageCount}
</select>

 ==>Preparing: select * from sys_app_user where type=? and ascriptionId = ? and school_info_id = ? and user_id not in ( ? , ? , ? , ? ) limit ?,? 

==>Parameters: 2(Integer), 6bf6007c71c54d15b8b2e03181efa2fc(String), 70bcfcf6dc3a47a79f68c1c12caef54e(String), 85c58bf8-7b7c-11e8-b249-f832e46fa70c(String), 928c6616-7b77-11e8-b249-f832e46fa70c(String), f707a232-7b7a-11e8-b249-f832e46fa70c(String), 7c515b1c-7b7a-11e8-b249-f832e46fa70c(String), 0(Integer), 10(Integer)

 

2.${}的用法举例：

<select id="getAppUser" parameterType="map" resultType="au">
		select * from sys_app_user where  type=#{type}
		 <if test="id != 'null' and  id !='' and id != null">
			and ascriptionId = #{id} 
		</if>
		<if test="school_info_id != null and school_info_id != ''">
			and school_info_id = #{school_info_id}
		</if>
		<if test="name != 'null' and name !='' and name != null">
			and  username like concat(concat('%', #{name}),'%')
		</if>
		<if test="list != null">
			and user_id not in
			<foreach collection="list" item="user_id" index="index" open="('"
				separator="','" close="')">${user_id}</foreach>
		</if>
		limit #{start},#{pageCount}
</select><foreach collection="list" item="user_id" index="index" open="('"
				separator="','" close="')">${user_id}</foreach>
		</if>
		limit #{start},#{pageCount}
</select>

打印的日志：

 ==>Preparing: select * from sys_app_user where type=? and ascriptionId = ? and school_info_id = ? and user_id not in  ( ' 85c58bf8-7b7c-11e8-b249-f832e46fa70c ' , ' 928c6616-7b77-11e8-b249-f832e46fa70c ' , ' f707a232-7b7a-11e8-b249-f832e46fa70c ' , ' 7c515b1c-7b7a-11e8-b249-f832e46fa70c ' ) limit ?,? 

==>Parameters: 2(Integer), 6bf6007c71c54d15b8b2e03181efa2fc(String), 70bcfcf6dc3a47a79f68c1c12caef54e(String), 0(Integer), 10(Integer)

3.实际，类似的用法还有一个

<select id="userlistPage" parameterType="page" resultType="pd">
        select u.user_id,
        u.username,
        u.password,
        u.name,
	u.ascriptionId,        
        u.status,
        u.email,
        u.phone,
        u.number,
        u.gender,
        u.id_type,
        u.id_number,
        u.role_ids,
        u.school_info_id,
        u.role_id
        from
        <include refid="tableName"></include>
        u
        left join base_ascription a on u.ascriptionId=a.id
        <where>
            <if test="pd.sessionUser!=null and pd.sessionUser.school_info_id!=null">
                and u.school_info_id=#{pd.sessionUser.school_info_id}
            </if>
            <if test="pd.ascriptionId !=null ">
                and u.ascriptionId in
                <foreach collection="pd.ascriptionId" item="item" open="('" 
                separator="','" close="')">${item}</foreach>
            </if>
            <if test="pd.type!=null ">
                and u.type=#{pd.type}
            </if>
            <if test="pd.keywords != null and pd.keywords!= ''"><!-- 关键词检索 -->
                and (
                u.username like concat(concat('%', #{pd.keywords}),'%')
                or
                u.name like concat(concat('%', #{pd.keywords}),'%')
                )
            </if>
        </where>
        order by u.user_id
    </select>item="item" open="('" 
                separator="','" close="')">${item}</foreach>
            </if>
            <if test="pd.type!=null ">
                and u.type=#{pd.type}
            </if>
            <if test="pd.keywords != null and pd.keywords!= ''"><!-- 关键词检索 -->
                and (
                u.username like concat(concat('%', #{pd.keywords}),'%')
                or
                u.name like concat(concat('%', #{pd.keywords}),'%')
                )
            </if>
        </where>
        order by u.user_id
    </select>

总结：第一种是最正宗的用法，其实大多数情况都是第一种，第二种和第三种，其实是一样的用法，只是里面有一个参数的不同，关键是${}原样输入，和参数无关。并且注意空格问题，第二三种${}的前后没有空格，是紧挨着的！不这样会出问题的哦！