Mybatis练习

mybatis的练习

• 动态SQL练习

<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dhc.batis.dao.UserDao">
    <select id="queryUserList" resultType="com.dhc.batis.model.User"
            parameterType="com.dhc.batis.model.User">
        select *
        from user
        <trim prefix="where" prefixOverrides="and |or ">
            <if test="id != null and id != ''">
                and id = #{id}
            </if>
            <if test="name != null and name != ''">
                and name = #{name}
            </if>
            <if test="age != null and age != 0">
                or age = #{age}
            </if>
        </trim>

        <!-- 测试：当🆔id为空时where关键字去除 -->
        <!--<where>
            <if test="id != null and id != ''">
                and id = #{id}
            </if>
        </where>-->

        <!-- 测试：and 和 or 关键字能被where标签去除 -->
        <!--<where>
            <choose>
                <when test="id != null and id != ''">
                    and id = #{id}
                </when>
                <when test="name != null and name != ''">
                    and name = #{name}
                </when>
                <otherwise>
                    or age = #{age}
                </otherwise>
            </choose>
        </where>-->
    </select>

    <select id="queryUserListByIdList" parameterType="java.util.List" resultType="com.dhc.batis.model.User">
        select * from user where id in
        <foreach item="item" collection="idList" open="(" separator="," close=")" index="index">
            #{item}
        </foreach>
    </select>

    <select id="queryUserListByUserList" parameterType="java.util.List" resultType="com.dhc.batis.model.User">
        select * from user where (id,name) in
        <foreach item="item" collection="userList" open="(" separator="," close=")" index="index">
            (#{item.id},#{item.name})
        </foreach>
    </select>

    <select id="queryUserListByUserMap" parameterType="java.util.Map" resultType="com.dhc.batis.model.User">
        select * from user where (id,name) in
        <!--<foreach item="key" collection="userMap.keys" open="(" separator="," close=")" index="index">
            (#{key},#{userMap[${key}]})
        </foreach>-->
        <foreach item="value" collection="userMap.entrySet()" open="(" separator="," close=")" index="key">
            (#{key},#{value})
        </foreach>
    </select>

    <update id="updateUser" parameterType="com.dhc.batis.model.User">
        update user
        <trim prefix="set" prefixOverrides=",">
            <if test="name != null and name != ''">
                , name = #{name}
            </if>
            <if test="age != null and age != 0">
                , age = #{age}
            </if>
            <if test="searchKey != null and searchKey != ''">
                , search_key = #{searchKey}
            </if>
        </trim>
        <!--<set>
            <if test="name != null and name != ''">
                ,name = #{name}
            </if>
            <if test="age != null and age != 0">
                ,age = #{age}
            </if>
            <if test="searchKey != null and searchKey != ''">
                ,search_key = #{searchKey}
            </if>
        </set>-->
        where id = #{id}
    </update>
</mapper>

• 一对一、一对多结果映射问题

<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dhc.batis.dao.AccountDao">

    <resultMap id="accountResultMap1" type="com.dhc.batis.model.Account">
        <constructor>
            <idArg column="id" name="id"/>
            <arg column="user_name" name="userName"/>
            <arg column="password" name="password"/>
        </constructor>
        <id property="id" column="id"/>
        <result property="userName" column="user_name"/>
        <result property="password" column="password"/>
        <association property="user" column="id">
            <id property="id" column="id"/>
            <result property="name" column="name"/>
            <result property="age" column="age"/>
            <result property="createDate" column="create_date"/>
            <result property="searchKey" column="search_key"/>
        </association>
    </resultMap>

    <resultMap id="accountResultMap" type="com.dhc.batis.model.Account">
        <id property="id" column="id"/>
        <result property="userName" column="user_name"/>
        <result property="password" column="password"/>
        <association property="user" column="id">
            <id property="id" column="id"/>
            <result property="name" column="name"/>
            <result property="age" column="age"/>
            <result property="createDate" column="create_date"/>
            <result property="searchKey" column="search_key"/>
            <collection property="articleList" column="user_id" ofType="com.dhc.batis.model.Article">
                <id property="articleId" column="article_id"/>
                <result property="userId" column="user_id"/>
                <result property="articleContent" column="article_content"/>
                <result property="createDate" column="create_date"/>
            </collection>
        </association>
    </resultMap>

    <select id="queryAccountUser" resultMap="accountResultMap1" useCache="false">
        select *
        from account a
                 inner join user u
                            on a.id = u.id
        where a.id = #{id}
    </select>

    <select id="queryAccountUserArticle" resultMap="accountResultMap">
        select *
        from account a
                 inner join user u
                            on a.id = u.id
                 inner join article ar
                            on u.id = ar.user_id
        where a.id = #{id}
    </select>
</mapper>