mybatis-中级篇-UserMapper.xml

?xml version="1.0" encoding="UTF-8" ?>
<!-- 引入dtd -->
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">




<!-- namespace就是接口的包名加类名 -->
<mapper namespace="cn.dao.UserMapper">








<!-- 查个数 -->
<!-- id是一个方法，id是唯一的 -->
<!-- resaultType是返回值类型 -->
<select id="count" resultType="int">
<!-- 写SQL语句 -->
select count(1) from user
</select>



<!-- =============================================== -->

<!-- 增加 -->
<!-- parameterType:增加的时候传进来的是User对象 -->
<insert id="add" parameterType="User">
insert into user(userCode, userName, userPassword)
values (#{userCode},#{userName},#{userPassword})
</insert>

<!-- =============================================== -->

<!-- 修改 -->
<!-- 注意：如果属性很多而用户只修改一种一个或几个属性的时候
就不用每个属性都set了 -->
<update id="update" parameterType="User">
<!-- 方法1 -->
<!-- update user
<set>
<if test="userCode != null and userCode != ''">
userCode=#{userCode},
</if>
<if test="userName != null and userName != ''">
userName=#{userName},
</if>
<if test="userPassword != null and userPassword != ''">
userPassword=#{userPassword}
</if>
</set>
where id = #{id} -->

<!-- 方法2 -->

update user
<trim prefix = "set" suffixOverrides = ",">
<if test="userCode != null and userCode != ''">
userCode=#{userCode},
</if>
<if test="userName != null and userName != ''">
userName=#{userName},
</if>
<if test="userPassword != null and userPassword != ''">
userPassword=#{userPassword}
</if>
</trim>
where id = #{id}


</update>

<!-- =============================================== -->
<!-- 删除 -->
<delete id="delete" parameterType="User">
delete from user where id=#{id}
</delete>

<!-- =============================================== -->
<!-- 查询-->
<select id="getUserList" resultType="User">
select * from user
</select>
<!-- =============================================== -->
<!-- 根据roleId获取数据列表 -->

<!-- 自己建一个结果集 -->
<!-- id除了是唯一标识，还 被其他 犯法调用时用 -->
<!-- 把查询出来的结果集映射到User对象里 -->
<!-- 为了提高效率，用不着表里的内容都查一遍，所以不用resultType而用resultMap -->
<!-- 当列名和属性名不一致是我们用resultMap，比如说oracle的时候 -->
<!-- resultType的结果也是一个Map，key是列名，values是所对应的值 -->
<!-- <resultMap type="User" id="userMap">
<result property="id" column="id"/>
<result property="userCode" column="userCode"/>
<result property="userName" column="userName"/>
<result property="roleName" column="roleName"/>
</resultMap> -->

<!-- <select id="getUserListByRoleId" parameterType="Role" resultMap="userMap">
select u.*,r.roleName as roleName from user u,role r where u.roleId = r.id and u.roleId = #{id}
  </select> -->
 
  <!-- =============================================== -->
  <!-- 根据roleId获取数据列表 association多对一-->
  <resultMap type="User" id="userMap">
<result property="id" column="id"/>
<result property="userCode" column="userCode"/>
<result property="userName" column="userName"/>
<!-- 属性是JavaBean的时候 -->
<!-- 第一种写法 -->
    <!-- <association property="role" javaType="Role">
    <result property="id" column="r_id"/>
    <result property="roleCode" column="roleCode"/>
    <result property="roleName" column="roleName"/>
    </association> -->
    <!-- 第二种写法 -->
    <association property="role" javaType="Role" resultMap="roleMap"></association>
</resultMap>
<resultMap type="Role" id="roleMap">
<result property="id" column="r_id"/>
    <result property="roleCode" column="roleCode"/>
    <result property="roleName" column="roleName"/>
</resultMap>


<select id="getUserListByRoleId" parameterType="Role" resultMap="userMap">
select u.*,r.id as r_id,r.roleCode,r.roleName as roleName 
from user u,role r 
where u.roleId = r.id 
and u.roleId = #{id}
  </select>


<!-- 动态SQL查询 -->
<resultMap type="User" id="userMap2">
<result property="id" column="id"/>
<result property="userCode" column="userCode"/>
<result property="userName" column="userName"/>
<result property="roleName" column="roleName"/>
</resultMap>
<select id="serchUserList" parameterType="User" resultMap="userMap2">
select u.*,r.roleName as roleName 
from user u,role r 
where u.roleId = r.id 
<if test = "roleId != null">
and u.roleId = #{roleId}
</if>
<!-- 防止SQL注入 -->
<if test = "userName != null">
and u.userName like concat ('%',#{userName},'%') 
</if>
<if test = "userCode != null">
and u.userCode like concat ('%',#{userCode},'%') 
</if>
  </select>

<!-- =============================================== -->
  <!-- 获取指定用户的地址列表 collection一对多-->
  <resultMap type="User" id="serchMap">
  <id property="id" column="userId"/>
  <collection property="addressList" ofType="Address">
  <id property="id" column="a_id"/>
  <result property="postCode" column="postCode"/>
  <result property="addressContent" column="addressContent"/>
  </collection>
  </resultMap>
<select id="getAddressListByUserId" parameterType="User" resultMap="serchMap">
SELECT *,a.id AS a_id, a.addressContent, a.`userId` FROM USER u, address a WHERE u.id = a.userId AND u.id = #{id}
</select>

<!-- =============================================== -->
  <!-- 用foreach in-->
  <resultMap type="User" id="userMapDep">
<result property="id" column="id"/>
<result property="userCode" column="userCode"/>
<result property="userName" column="userName"/>
<result property="depName" column="depName"/>
</resultMap>
<select id="getUserByDepId" parameterType="User" resultMap="userMapDep">
select * from user where depId in
<foreach collection = "array" item = "depIds" open = "(" separator = "," close = ")">
#{depIds}
</foreach>
</select>
<select id="getUserByDepId2" parameterType="User" resultMap="userMapDep">
select * from user where depId in
<foreach collection = "list" item = "depIds" open = "(" separator = "," close = ")">
#{depIds}
</foreach>
</select>
 
</mapper>