Mybatis小笔记

本文旨在记录我在idea中使用mybatis时的经验总结

基本规范：

1. Base_Column

   <sql id="Base_Column">
     ID,USER_NAME,PASSWORD,CREATE_TIME,UPDATE_TIME,HEAD_PORTRAIT,STATUS
   </sql>

2. Base_Where

   <sql id="Base_Where">
     <where>
         <if test="id != null and id != ''">
             AND ID=#{id}
         </if>
         <if test="userName != null and userName != ''">
             AND USER_NAME like concat('%',#{userName},'%')
         </if>
         <if test="password != null and password != ''">
             AND PASSWORD=md5(#{password})
         </if>
         <if test="status != null">
             AND STATUS=#{status}
         </if>
     </where>
   </sql>

3. Update_Column

   <sql id="Update_Column">
     <set>
         <if test="userName != null and userName!= ''">USER_NAME =#{userName},</if>
         <if test="password != null and password!= ''">PASSWORD=md5(#{password}),</if>
         <if test="headPortrait != null and headPortrait != ''">HEAD_PORTRAIT=#{headPortrait},</if>
         <if test="updateTime != null">UPDATE_TIME=#{updateTime},</if>
         <if test="status != null">STATUS=#{status},</if>
     </set>
   </sql>

单表操作

1. 新增

   <insert id="insertOneUser" parameterType="cn.blue.项目工程名.bean.UserInfo"
           useGeneratedKeys="true" keyColumn="id" keyProperty="id">
       INSERT INTO
       user_info(ID,USER_NAME,PASSWORD,CREATE_TIME)
       VALUES(#{id},#{userName},md5(#{password}),now())
   </insert>

2. 查询: 根据封装进userInfo对象里的条件查询，结果可以是单个对象也可以是集合

   <select id="findOneUser" parameterType="cn.blue.项目工程名.bean.UserInfo" 
       resultType="cn.blue.项目工程名.bean.UserInfo">
       SELECT
       <include refid="Base_Column"/>
       FROM user_info
       <include refid="Base_Where"/>
   </select>

3. 删除：

   <delete id="deleteById" parameterType="string">
       DELETE FROM user_info
       <include refid="Base_Where"/>
   </delete>

4. 修改：

   <update id="deleteById" parameterType="string">
       UPDATE user_info SET STATUS = 1
       WHERE
       id=#{id}
   </update>

两表查询

1. resultMap：

   <resultMap type="cn.blue.项目工程名.bean.UserInfo" id="userInfoMap">
       <id column="ID" property="id"/>
       <result column="USER_NAME" property="userName"/>
       <result column="PASSWORD" property="password"/>
       <result column="CREATE_TIME" property="createTime"/>
       <result column="UPDATE_TIME" property="updateTime"/>
       <result column="HEAD_PORTRAIT" property="headPortrait"/>
       <result column="STATUS" property="status"/>
       <collection column="ID" property="roles" ofType="cn.blue.项目工程名.bean.Role"
                   select="cn.blue.项目工程名.mapper.RoleMapper.findRoleById"/>
   </resultMap>

2. UserInfoMapper中的查询语句：

   <select id="findAll" parameterType="cn.blue.项目工程名.bean.UserInfo"
           resultMap="userInfoMap">
       SELECT
       <include refid="Base_Column"/>
       FROM user_info
       <include refid="Base_Where"/>
   </select>

3. RoleMapper表中的查询

   <select id="findById" parameterType="int"
       resultMap="roleMap">
       select
       <include refid="Base_Column"></include>
       from role
       where id=#{id}
   </select>

利用中间表过渡的两表查询

1. resultMap：

   <resultMap type="cn.blue.项目工程名.bean.UserInfo" id="userInfoMap">
       <id column="ID" property="id"/>
       <result column="USER_NAME" property="userName"/>
       <result column="PASSWORD" property="password"/>
       <result column="CREATE_TIME" property="createTime"/>
       <result column="UPDATE_TIME" property="updateTime"/>
       <result column="HEAD_PORTRAIT" property="headPortrait"/>
       <result column="STATUS" property="status"/>
       <collection column="ID" property="roles" ofType="cn.blue.项目工程名.bean.Role"
                   select="cn.blue.项目工程名.mapper.RoleMapper.findRoleByUserId"/>
   </resultMap>

2. UserInfoMapper中的查询语句：

   <select id="findAll" parameterType="cn.blue.项目工程名.bean.UserInfo"
           resultMap="userInfoMap">
       SELECT
       <include refid="Base_Column"/>
       FROM user_info
       <include refid="Base_Where"/>
   </select>

3. RoleMapper中的查询语句

   <select id="findRoleByUserId" parameterType="int" resultType="cn.blue.项目工程名.bean.Role">
       SELECT
       <include refid="Base_Column"/>
       FROM role r, user_role ur
       WHERE r.id=ur.role_id
       AND ur.user_id=#{userId}
   </select>

遍历操作

1. 根据ids来删除:
   注：前面ids是个数组，故需要以此表达：String[] ids

   /**
   *  支持批量删除用户
   * @param ids 多个用户id
   * @return 受影响行数
   */
   int deleteByIds(@Param("ids")String... ids);

<delete id="deleteByIds" parameterType="string">
    DELETE FROM user_info
    WHERE
    <foreach collection="ids" open="id in (" close=")"
              separator="," item="id">
        #{id}
    </foreach>
</delete>

2. 根据用户id来在中间表中新增与之对应的角色ids：一对一或者一对多的关系

   /**
     * 修改中间表user_role的id
     * @param userId 用户id
     * @param roleIds 角色id
     * @return 受影响行数
     */
   int addRolesByUserId(@Param("userId") String userId, @Param("roleIds") String[] roleIds);

<insert id="addRolesByUserId">
    INSERT INTO user_role
    <foreach collection="roleIds" open="values" item="roleId" separator=",">
        (#{userId},#{roleId})
    </foreach>
</insert>