mybatis的动态sql学习注意点!!!

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.HuaDiDemo.mapper.UserMapper">

    <select id="getLoginUser" parameterType="User"  resultType="User">
        select * from user
     <if test="username!=null">
         where username= #{username}  and password = #{password}
    </if>

    </select>

    <insert id="registerUser"  parameterType="User">
        insert into user (username,password) values (#{username},#{password})
    </insert>
<!--   条件 与 查询-->
    <select id="selectUser" parameterType="User" resultType="User">
        select * from user
        <if test="username!=null">
            where username= #{username}
        </if>
        <if test="password!=null">
            and password= #{password}
        </if>
        <if test="sex!=null">
            and sex= #{sex}
        </if>
        <if test="age!=null">
            and age=#{age}
        </if>
    </select>
<!--    条件或查询-->
    <select id="selectOrUser" parameterType="User" resultType="User">
        select * from user where 1=1
        <choose>
            <when test="username!=null">
                and username = #{username}
            </when>
            <when test="password!=null">
                and password= #{password}
            </when>
            <when test="sex!=null">
                and sex= #{sex}
            </when>
            <when test="age!=null">
                and age=#{age}
            </when>
        </choose>
    </select>
<!--    模糊查询-->
    <select id="queryUserLikeByUserName" parameterType="String" resultType="User">
        select * from user where userName like '%${value}%' limit 0,2
    </select>
<!--    返回影响条数-->
    <insert id="addUserId" parameterType="User">
--         keyProperty:主键    order:执行顺序  AFTER后   BEFORE先
        <selectKey keyProperty="id" order="AFTER" resultType="Integer">
            select LAST_INSERT_ID()
        </selectKey>
    insert into user(username,password,sex,age) values (#{username},#{password},#{sex},#{age})
    </insert>
<!--  添加用户:id为随机值  -->
    <insert id="addUserIdById" parameterType="User">
        <selectKey keyProperty="id" order="BEFORE" resultType="Integer">
<!--插入一个0-1000的随机数-->
            select FLOOR(0 + (RAND() * 1000))  <!--插入随机id-->
        </selectKey>
    INSERT INTO user (id,userName,password,sex,age) values (#{id},#{username},#{password},#{sex},#{age})
    </insert>
    <!-- 多条件查询-->
    <select id="getAlluser3" parameterType="User" resultType="User">
        select * from user
        <where>
            <if test="username!=null">
               and  userName = #{username}
            </if>
            <if test="age!=null">
                and age = #{age}
            </if>
        </where>
    </select>

    <!-- 多条件查询  prefixOverrides="AND | OR" 覆盖第一个and或者or-->
    <select id="getAlluser4" parameterType="User"
            resultType="User">
        select * from user
        <trim prefix="WHERE" prefixOverrides="AND | OR">
            <if test="username!=null">
                and username = #{username}
            </if>
            <if test="age!=null">
                and age = #{age}
            </if>
        </trim>
    </select>

</mapper>