MyBatis小笔记

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

基本规范:

  1. Base_Column

    1
    2
    3
    <sql id="Base_Column">
    ID,USER_NAME,PASSWORD,CREATE_TIME,UPDATE_TIME,HEAD_PORTRAIT,STATUS
    </sql>
  2. Base_Where

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    <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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    <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. 新增

    1
    2
    3
    4
    5
    6
    <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对象里的条件查询,结果可以是单个对象也可以是集合

    1
    2
    3
    4
    5
    6
    7
    <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. 删除:

    1
    2
    3
    4
    <delete id="deleteById" parameterType="string">
    DELETE FROM user_info
    <include refid="Base_Where"/>
    </delete>
  4. 修改:

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

两表查询

  1. resultMap:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    <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中的查询语句:

    1
    2
    3
    4
    5
    6
    7
    <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表中的查询

    1
    2
    3
    4
    5
    6
    7
    <select id="findById" parameterType="int"
    resultMap="roleMap">
    select
    <include refid="Base_Column"></include>
    from role
    where id=#{id}
    </select>

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

  1. resultMap:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    <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中的查询语句:

    1
    2
    3
    4
    5
    6
    7
    <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中的查询语句

    1
    2
    3
    4
    5
    6
    7
    <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
    1
    2
    3
    4
    5
    6
    /**
    * 支持批量删除用户
    * @param ids 多个用户id
    * @return 受影响行数
    */
    int deleteByIds(@Param("ids")String... ids);
1
2
3
4
5
6
7
8
<delete id="deleteByIds" parameterType="string">
DELETE FROM user_info
WHERE
<foreach collection="ids" open="id in (" close=")"
separator="," item="id">
#{id}
</foreach>
</delete>
  1. 根据用户id来在中间表中新增与之对应的角色ids:一对一或者一对多的关系
    1
    2
    3
    4
    5
    6
    7
    /**
    * 修改中间表user_role的id
    * @param userId 用户id
    * @param roleIds 角色id
    * @return 受影响行数
    */
    int addRolesByUserId(@Param("userId") String userId, @Param("roleIds") String[] roleIds);
1
2
3
4
5
6
<insert id="addRolesByUserId">
INSERT INTO user_role
<foreach collection="roleIds" open="values" item="roleId" separator=",">
(#{userId},#{roleId})
</foreach>
</insert>
打赏
  • 版权声明: 本博客所有文章除特别声明外,均采用 Apache License 2.0 许可协议。转载请注明出处!
  1. © 2020 Liu Yang    湘ICP备20003709号

请我喝杯咖啡吧~

支付宝
微信