MyBatis--动态语句，对一关联

//接口类

package cn.smbms.dao.user;


import java.util.List;


import org.apache.ibatis.annotations.Param;


import cn.smbms.pojo.User;


public interface UserDao {
List<User> getUserList(@Param("userName") String userName,@Param("userRole") Integer userRole);
int updateUser(User user);

public List<User> getUserByRoleId_foreach_list(List<Integer> roleList);

//根据userRole查询
List<User> getUser(@Param("userRole") Integer userRole);


}

//"userMapper.xml" :

<mapper namespace="cn.smbms.dao.user.UserDao">

<!--   演示如何灵活处理where and|or : -->

<select id="getUserList" resultType="User">
select * from smbms_user 
<trim prefix="where" prefixOverrides="and | or">
<if test="userName !=null and userName !=''">
and userName like concat('%',#{userName},'%')

</if>
<if test="userRole !=null">
and userRole=#{userRole}
</if>


</trim>

</select> 

<!-- 修改用户信息 

suffixOverrides: 忽略最后一个","

suffix: 连接符

-->
<update id="updateUser" parameterType="User">
update smbms_user 
<trim prefix="set" suffixOverrides="," suffix="where id=#{id}">
<!--  <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="birthday != null">birthday=#{birthday},</if> 
<!-- <if test="phone != null">phone=#{phone},</if>  -->
<if test="address != null">address=#{address},</if>
<if test="creationDate != null">creationDate=#{creationDate},</if>
<!-- <if test="modifyBy != null">modifyBy=#{modifyBy},</if>
<if test="modifyDate != null">modifyDate=#{modifyDate},</if> -->
</trim>
</update> 

<!-- 根据用户角色列表，获取该角色列表下用户列表信息-foreach_list -->

<resultMap type="User" id="userMapByRole">
<id property="id" column="id" />
<result property="userCode" column="userCode" />
<result property="userName" column="userName" />
</resultMap>


<select id="getUserByRoleId_foreach_list" resultMap="userMapByRole">
select * from smbms_user where userRole in
<foreach collection="list" item="roleList" open="(" separator=","
close=")">
#{roleList}
</foreach>
</select>

<!-- 一对一关联 -->
<resultMap type="User" id="getRoleList">
<id property="id" column="id"/>
<result property="userCode" column="userCode"/>
<result property="userName" column="userName"/>
<association property="role" javaType="Role">
<id property="id" column="rid"/>
<result property="roleCode" column="roleCode"/>
<result property="roleName" column="roleName"/>
</association>

</resultMap>
<!-- 一对一关联 -->
<select id="getUser" resultMap="getRoleList" parameterType="int">
SELECT * ,r.id as rid,r.roleCode,r.roleName FROM smbms_user u,smbms_role r 
WHERE u.userRole=r.id AND u.userRole=2

</select>

</mapper>