工作中常用的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>

更多内容，请点击下方：

My Blog: http://coderblue.cn/
Github:https://github.com/onecoderly
My Project:http://coderblue.cn/project/