查询
<select id="selectUserByArray" resultMap="selectUserAssc">
SELECT su.userName,su.userRole,sr.roleName FROM smbms_user su
<where>
<if test="userName != null and userName != ''">
AND su.userName like concat('%',#{userName},'%')
</if>
<if test="userRole != null and userRole != ''">
AND su.userRole = #{userRole}
</if>
</where>
</select>
<select id="selectUserByList" resultType="User">
SELECT id,userCode,userName,userRole FROM smbms_user
where userRole IN
<foreach collection="lists" item="list" open="(" close=")" separator=",">
#{list}
</foreach>
</select>
修改
<update id="updateUserTest" parameterType="User">
UPDATE smbms_user
<set>
<if test="userCode != null">
userCode = #{userCode},
</if>
<if test="userName != null">
userName = #{userName},
</if>
<if test="userPassword != null">
userPassword = #{userPassword},
</if>
<if test="gender != null">
gender = #{gender},
</if>
<if test="phone != null">
phone = #{phone},
</if>
<if test="modifyDate != null">
modifyDate = now(),
</if>
</set>
where id = #{id}
</update>
<!--
因为如果没有修改列的话会发生异常,所以我们要求如果传入空值的话就给它赋值自己本身;
刚刚开始我是这样写的
userCode = (SELECT userCode FROM smbms_user where id = #{id})
完全没有毛病,但是发生了异常,去网上搜了一下在MySQl数据库中需要一个中间表转折就好了;
userCode = (SELECT userCode FROM (SELECT userCode FROM smbms_user where id = #{id}) AS one)
one 为给表起的别名
-->
<update id="updateUserByTrim" parameterType="Integer">
UPDATE smbms_user
<trim prefix="set" suffix="where id = #{id}" suffixOverrides=",">
<if test="userCode == null or userCode == ''">
userCode = (SELECT userCode FROM (SELECT userCode FROM smbms_user where id = #{id}) AS one),
</if>
<if test="userCode != null and userCode != ''">
userCode = #{userCode},
</if>
<if test="userName == null or userName == ''">
userName = (SELECT userName FROM (SELECT userName FROM smbms_user where id = #{id}) AS two),
</if>
<if test="userName != null and userName != ''">
userName = #{userName},
</if>
</trim>
</update>
还有choose标签相当于if-else,不过里面加了一个没有满足条件就执行的代码块,非常简单可以去网上搜一下,我要溜了,要下课了.继续去对spring发呆