<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.dao.UserMapper">
<!-- user的resultMap,当数据库字段信息和对象的属性不一样时，需要通过resultMap来映射 -->
<!--association,property,对应的是实体类对象的名称；resultMap对应的是另一个表的映射 -->
<resultMap type="Users" id="userList">
<result property="id" column="id"/>
<result property="userCode" column="userCode"/>
<result property="userName" column="userName"/>
<result property="userPassword" column="userPassword"/>
<result property="gender" column="gender"/>
<result property="birthday" column="birthday"/>
<result property="phone" column="phone"/>
<result property="address" column="address"/>
<result property="userrole" column="userrole"/>
<result property="createBy" column="createBy"/>
<result property="creationDate" column="creationDate"/>
<result property="modifyBy" column="modifyBy"/>
<result property="modifyDate" column="modifyDate"/>
<association property="role" resultMap="roleList"/>
</resultMap>
<!-- Role的resultMap -->
<resultMap type="Role" id="roleList">
<result property="id" column="id"/>
<result property="roleCode" column="roleCode"/>
<result property="roleName" column="roleName"/>
<result property="createdBy" column="createdBy"/>
<result property="creationDate" column="creationDate"/>
<result property="modifyBy" column="modifyBy"/>
<result property="modifyDate" column="modifyDate"/>
</resultMap>
<!-- 查询用户表的记录数 -->
<select id="count" resultType="int">
select count(*) from user
</select>
<!-- 查询所有的用户信息 -->
<select id="getUserList" resultType="Users">
select * from user
</select>
<!-- 根据用户名进行模糊查询 -->
<select id="getUsersByName" resultMap="userList" parameterType="string">
select * from user where userName like concat ('%',#{userName},'%')
</select>
<!-- 查询用户列表 -->
<select id="getUserListByUser" resultMap="userList" parameterType="Users">
select * from user where userName like concat('%',#{userName},'%') and userRole = #{userrole}
</select>
<!-- 查询用户列表，参数是Map集合 -->
<select id="getUsersListByMap" resultMap="userList" parameterType="Map">
select * from user where userName like concat ('%',#{userName},'%') and userRole = #{userrole}
</select>
<!-- 查询用户列表，连接查询-->
<select id="getUserListAndRole" resultMap="userList" parameterType="Users">
select * from user u join role r on u.userRole = r.id
where u.userName like concat ('%',#{userName},'%') and u.userrole = #{userrole}
</select>
<!-- 增加用户,字段名必须都写上 -->
<insert id="saveUser" parameterType="Users">
insert into user (userCode,userName,userPassword,gender,birthday,phone,
address,userRole,createdBy,creationDate,modifyBy,modifyDate)
values(#{userCode},#{userName},#{userPassword},#{gender},#{birthday},#{phone},#{address},#{userrole},#{createBy},#{creationDate},#{modifyBy},#{modifyDate})
</insert>
<!-- 根据id修改信息 -->
<update id="updateUser" parameterType="Users">
update user set userCode=#{userCode},userName=#{userName},userPassword=#{userPassword},gender=#{gender},
birthday=#{birthday},phone=#{phone},address=#{address},userRole=#{userrole},createdBy=#{createBy},
creationDate=#{creationDate},modifyBy=#{modifyBy},modifyDate=#{modifyDate}
where id=#{id}
</update>
<!-- 根据编号进行删除数据 -->
<delete id="delUser" parameterType="int">
delete from user where id=#{id}
</delete>
<!-- 根据id查询用户列表 -->
<select id="getUsersById" parameterType="int" resultMap="userList">
select * from user where id = #{id}
</select>
<!-- 根据角色id查询用户列表 -->
<select id="getUsersByRoleId" parameterType="int" resultMap="userList">
select * from user u,role r where u.userrole = #{userrole} and r.id = u.userrole
</select>
<!-- 根据用户名和角色编号查询用户信息 -->
<select id="getUsersListByUserNameAndRole_if" resultMap="userList">
select * from user u, role r where u.userrole=r.id
<if test="userrole!=null">
and userrole = #{userrole}
</if>
<if test="userName!=null">
and userName like concat('%',#{userName},'%')
</if>
</select>
<!-- 动态根据用户名和角色id查询用户列表，where and|or -->
<select id="getUsersListByUserNameAndRole_ifAndwhere" resultMap="userList">
select * from user
<where>
<if test="userrole!=null and userrole!=''">
and userrole = #{userrole}
</if>
<if test="userName!=null and userName!=''">
and userName like concat('%',#{userName},'%')
</if>
</where>
</select>
<!-- 动态修改用户信息表if+set -->
<update id="updateUser_ifAndSet" parameterType="Users">
update 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="birthday!=null">birthday=#{birthday},</if>
<if test="phone!=null">phone=#{phone},</if>
<if test="address!=null">address=#{address},</if>
<if test="userrole!=null">userrole=#{userrole},</if>
<if test="createBy!=null">createdBy=#{createBy},</if>
<if test="creationDate!=null">creationDate=#{creationDate},</if>
<if test="modifyBy!=null">modifyBy=#{modifyBy},</if>
<if test="modifyDate!=null">modifyDate=#{modifyDate},</if>
</set>
where id=#{id}
</update>
<!-- 动态根据用户名和角色id查询用户列表，使用trim进行查询用户信息，where and|or -->
<!-- prefix:前缀，通过自动识别是否有返回值，在trim包含的内容上加上前缀 -->
<!-- suffix:后缀，在trim包含的内容的上加上后缀 -->
<!-- prefixOverrides:对于trim包含的内容的首部进行指定内容 -->
<!-- suffixOverrides：地狱与trim包含的内容的首尾部进行指定内容的忽略 -->
<select id="getUsersListByUserNameAndRole_ifAndwhere_trim" resultMap="userList">
select * from user
<trim prefix="where" prefixOverrides="and | or">
<if test="userrole!=null and userrole!=''">
and userrole = #{userrole}
</if>
<if test="userName!=null and userName!=''">
and userName like concat('%',#{userName},'%')
</if>
</trim>
</select>
<!-- 动态修改用户信息表if+trim -->
<update id="updateUser_ifAndTrim" parameterType="Users">
update 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="userrole!=null">userrole=#{userrole},</if>
<if test="createBy!=null">createdBy=#{createBy},</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_array -->
<!-- item：循环体中的具体对象。支持属性的点路径访问，如item.age,item.info.details。 具体说明：在list和数组中是其中的对象，在map中是value。该参数为必选。 -->
<!-- collection：要做foreach的对象，作为入参时，List<?>对象默认用list代替作为键，数组对象有array代替作为键，Map对象没有默认的键。
当然在作为入参时可以使用@Param("keyName")来设置键，设置keyName后，list,array将会失效。 除了入参这种情况外，还有一种作为参数对象的某个字段的时候。举个例子：
如果User有属性List ids。入参是User对象，那么这个collection = "ids"
如果User有属性Ids ids;其中Ids是个对象，Ids有个属性List id;入参是User对象，那么collection = "ids.id"
上面只是举例，具体collection等于什么，就看你想对那个元素做循环。
该参数为必选。 -->
<!-- separator：元素之间的分隔符，例如在in()的时候，separator=","会自动在元素中间用“,“隔开，避免手动输入逗号导致sql错误，如in(1,2,)这样。该参数可选。 -->
<!-- open：foreach代码的开始符号，一般是(和close=")"合用。常用在in(),values()时。该参数可选。 -->
<!-- close：foreach代码的关闭符号，一般是)和open="("合用。常用在in(),values()时。该参数可选。 -->
<!-- index：在list和数组中,index是元素的序号，在map中，index是元素的key，该参数可选。 -->
<select id="getUsersByRoleId_foreach_array" resultMap="userList">
select * from user where userrole in
<foreach collection="array" item="roleids" open="(" separator="," close=")">
#{roleids}
</foreach>
</select>
<!-- 根据用户角色列表，获取该角色刘表下用户列表信息foreach_list -->
<select id="getUsersByRoleId_foreach_list" resultMap="userList">
select * from user where userrole in
<foreach collection="list" item="roleids" open="(" separator="," close=")">
#{roleids}
</foreach>
</select>
<!-- 根据用户角色列表和性别（多参数），获取该角色刘表下用户列表信息foreach_map -->
<select id="getUsersByRoleId_foreach_many_map" resultMap="userList">
select * from user where gender = #{gender} and userrole in
<foreach collection="roleids" item="roleMap" open="(" separator="," close=")">
#{roleMap}
</foreach>
</select>
<!-- 根据用户角色列表（单参数），获取该角色刘表下用户列表信息foreach_map -->
<select id="getUsersByRoleId_foreach_one_map" resultMap="userList">
select * from user where userrole in
<foreach collection="rKey" item="roleMap" open="(" separator="," close=")">
#{roleMap}
</foreach>
</select>
<!-- 查询用户列表，使用choose -->
<select id="getUsersList_choose" resultMap="userList">
select * from user where 1=1
<choose>
<when test="userName!=null and userName!=''">
and userName like concat('%',#{userName},'%')
</when>
<when test="userrole!=null">
and userrole =#{userrole}
</when>
<when test="userCode!=null and userCode!=''">
and userCode =#{userCode}
</when>
<otherwise>
and YEAR(creationDate) = YEAR(#{creationDate})
</otherwise>
</choose>
</select>
<!-- 分页显示用户信息 -->
<select id="getUserList_page" resultMap="userList">
select * from user limit #{from},#{pageSize}
</select>
</mapper>