<?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>