重温MyBatis（五、动态SQL）

/**
     *  动态SQL===开始
     */
    // <if>元素---模糊查询
    List<UserInfo> findUserInfoByUserNameWithIf(UserInfo userInfo);

    //  <where>、<if>元素配合使用
    List<UserInfo> findUserInfoByUserNameAndStatus(UserInfo userInfo);

    // <set>、<if>元素配合使用
    int updateUserInfoWithSetAndIf(UserInfo userInfo);

    // <trim>元素替换<where>元素
    List<UserInfo> findUserInfoByUserNameWithIfAndTrim(UserInfo userInfo);

    // <trim>元素替换<set>元素
    int updateUserInfoWithIfAndTrim(UserInfo userInfo);

    // <choose>、<when>、<otherwise>元素的使用
    List<UserInfo> findUserInfoWithChoose(UserInfo userInfo);

    // <foreach>元素
    List<UserInfo> findUserInfoByIdsWithForeachForList(List<Integer> integerList);
    List<UserInfo> findUserInfoByIdsWithForeachForArray(int[] ids);
    /**
     *  动态SQL===结束
     */

<!--

        动态SQL===开始

    -->

    <!-- <if> -->
    <select id="findUserInfoByUserNameWithIf" parameterType="UserInfo" resultType="UserInfo">
        select * from user
        <if test="userName != null and userName != ''">
            where userName like concat(concat('%', #{userName}), '%')
        </if>
    </select>

    <!-- <where>、<if>元素配合使用 -->
    <select id="findUserInfoByUserNameAndStatus" parameterType="UserInfo" resultType="UserInfo">
        select * from user u
        <where>
            <if test="userName != null and userName != ''">
                u.userName like concat(concat("%", #{userName}), "%")
            </if>
            <if test="status > -1">
                and u.status = #{status}
            </if>
        </where>
    </select>

    <!-- <set>、<if>元素配合使用-->
    <update id="updateUserInfoWithSetAndIf" parameterType="UserInfo">
        update user
        <set>
            <if test="userName != null and userName != ''">
                userName = #{userName},
            </if>
            <if test="password != null and password !=''">
                password = #{password}
            </if>
        </set>
        where id = #{id}
    </update>

    <!-- <trim>元素替换<where>元素-->
    <select id="findUserInfoByUserNameWithIfAndTrim" parameterType="UserInfo" resultType="UserInfo">
        select * from user
        <trim prefix="where" prefixOverrides="and|or">
            <if test="userName != null and userName != ''">
                userName like concat(concat("%", #{userName}), "%")
            </if>
            <if test="status > -1">
                and status = #{status}
            </if>
        </trim>
    </select>

    <!-- <trim>元素替换<set>元素-->
    <update id="updateUserInfoWithIfAndTrim" parameterType="UserInfo">
        update user
        <trim prefix="set" prefixOverrides=",">
            <if test="userName != null and userName != ''">
                userName = #{userName},
            </if>
            <if test="password != null and password != ''">
                password = #{password}
            </if>
        </trim>
        where id = #{id}
    </update>

    <!-- <choose>、<when>、<otherwise>元素的使用-->
    <select id="findUserInfoWithChoose" parameterType="UserInfo" resultType="UserInfo">
        select * from user
        <where>
            <choose>
                <when test="userName != null and userName != ''">
                    userName like concat(concat("%", #{userName}), "%")
                </when>
                <when test="status > -1">
                    and status = #{status}
                </when>
                <otherwise></otherwise>
            </choose>
        </where>
    </select>

    <!-- <foreach>元素之list-->
    <select id="findUserInfoByIdsWithForeachForList" parameterType="Integer" resultType="UserInfo">
        select * from user where id in
        <foreach collection="list" item="ids" open="(" separator="," close=")">
            #{ids}
        </foreach>
    </select>

    <!-- <foreach>元素之array-->
    <select id="findUserInfoByIdsWithForeachForArray" parameterType="int" resultType="UserInfo">
        select * from user where id in
        <foreach collection="array" item="ids" open="(" separator="," close=")">
            #{ids}
        </foreach>
    </select>


    <!--

        动态SQL===结束

    -->

/**
     *
     * 动态SQL===开始
     *
     */

    /**
     * if元素---模糊查询
      */

//    @Test
    public void testFindUserInfoByUserNameWithIf() {

        // 获得UserInfoMapper接口的代理对象
        UserInfoMapper userInfoMapper = sqlSession.getMapper(UserInfoMapper.class);

        UserInfo userInfo = new UserInfo();
        userInfo.setUserName("");

        // 执行映射文件中定义的SQL，并返回映射结果
        List<UserInfo> userInfoList = userInfoMapper.findUserInfoByUserNameWithIf(userInfo);

        for (UserInfo userInfoTemp : userInfoList) {
            System.out.println(userInfoTemp.toString());
        }

    }

    /**
     * where、if元素搭配使用
     */
//  @Test
    public void testFindUserInfoByUserNameAndStatus() {

        // 获得UserInfoMapper接口的代理对象
        UserInfoMapper userInfoMapper = sqlSession.getMapper(UserInfoMapper.class);

        UserInfo userInfo = new UserInfo();
        userInfo.setUserName("张");
        userInfo.setStatus(0);

        // 执行映射文件中定义的SQL，并返回映射结果
        List<UserInfo> userInfoList = userInfoMapper.findUserInfoByUserNameAndStatus(userInfo);

        for (UserInfo userInfoTemp : userInfoList) {
            System.out.println(userInfoTemp.toString());
        }

    }

    /**
     * where、if元素搭配使用
     */
//    @Test
    public void testUpdateUserInfoWithSetAndIf() {

        // 获得UserInfoMapper接口的代理对象
        UserInfoMapper userInfoMapper = sqlSession.getMapper(UserInfoMapper.class);

        UserInfo userInfo = new UserInfo();
        userInfo.setId(10);
        userInfo.setUserName("王五五");
        userInfo.setPassword("778899");

        // 执行映射文件中定义的SQL，并返回映射结果
        int result = userInfoMapper.updateUserInfoWithSetAndIf(userInfo);

        if(result > 0){
            System.out.println("数据修改成功");
        }else{
            System.out.println("数据修改失败");
        }

    }

    /**
     * <trim>元素替换<where>元素
     */
//    @Test
    public void testFindUserInfoByUserNameWithIfAndTrim() {

        // 获得UserInfoMapper接口的代理对象
        UserInfoMapper userInfoMapper = sqlSession.getMapper(UserInfoMapper.class);

        UserInfo userInfo = new UserInfo();
//        userInfo.setUserName("王");
        userInfo.setUserName("王");
        userInfo.setStatus(0);

        // 执行映射文件中定义的SQL，并返回映射结果
        List<UserInfo> userInfoList= userInfoMapper.findUserInfoByUserNameWithIfAndTrim(userInfo);

        for(UserInfo userInfoTemp : userInfoList){
            System.out.println(userInfoTemp.toString());
        }

    }

    /**
     * <trim>元素替换<set>元素
     */
//    @Test
    public void tespUpdateUserInfoWithIfAndTrim(){
        UserInfoMapper userInfoMapper = sqlSession.getMapper(UserInfoMapper.class);

        UserInfo userInfo = new UserInfo();
        userInfo.setId(10);
        userInfo.setUserName("舞王舞王");
        userInfo.setPassword("wwwwww");

        int result = userInfoMapper.updateUserInfoWithIfAndTrim(userInfo);

        if(result > 0){
            System.out.println("数据修改成功");
        }else{
            System.out.println("数据修改失败");
        }
    }


    /**
     * <trim>元素替换<set>元素
     */
//    @Test
    public void testFindUserInfoWithChoose(){
        UserInfoMapper userInfoMapper = sqlSession.getMapper(UserInfoMapper.class);

        UserInfo userInfo = new UserInfo();
//        userInfo.setUserName("张");
//        userInfo.setStatus(0);

        List<UserInfo> userInfoList= userInfoMapper.findUserInfoWithChoose(userInfo);

        for (UserInfo userInfoTemp : userInfoList){
            System.out.println(userInfoTemp.toString());
        }

    }


    /**
     * <foreach>元素之list
     */
//    @Test
    public void testFindUserInfoByIds(){
        UserInfoMapper userInfoMapper = sqlSession.getMapper(UserInfoMapper.class);

        List<Integer> ids = new ArrayList<>();
        ids.add(1);
        ids.add(3);

        List<UserInfo> userInfoList= userInfoMapper.findUserInfoByIdsWithForeachForList(ids);

        for (UserInfo userInfoTemp : userInfoList){
            System.out.println(userInfoTemp.toString());
        }

    }


    /**
     * <foreach>元素之list
     */
    @Test
    public void testFindUserInfoByIds2(){

        UserInfoMapper userInfoMapper = sqlSession.getMapper(UserInfoMapper.class);

        int ids[] = {1, 5};

        List<UserInfo> userInfoList= userInfoMapper.findUserInfoByIdsWithForeachForArray(ids);

        for (UserInfo userInfoTemp : userInfoList){
            System.out.println(userInfoTemp.toString());
        }
    }




    /**
     *
     *  动态SQL===结束
     *
     */