mybatis详解（三）

一,动态sql,where,trim,set和foreach

parameterType的属性可以不用写

xml文件sql的书写

<select id="queryByParams"  parameterType="string" resultMap="usermap" resultType="user">
        select id,
        <choose>
            <when test="realname!=null and realname!='' ">
                username
            </when>
            <otherwise>
                password
            </otherwise>
        </choose>
        from user
    </select>

    <delete id="deleteUser" parameterType="int" >
        delete from user where id=#{id}
    </delete>

    <insert id="insertUser" parameterType="user" >
        insert into user (username,realname,password) values (#{user_name},#{realname},#{password})
    </insert>

    <select id="selectByNameAndPassword" resultType="user" resultMap="usermap">
        select * from user
        <where>
            <if test="username!=null and username!=''">
                username=#{username}
            </if>
            <if test="password!=null and password!=''">
                and password=#{password}
            </if>
        </where>
    </select>

    <select id="selectByNameAndPassword02"  resultMap="usermap">
        select * from user
      <trim prefix="where" prefixOverrides="and | or">
          <if test="username!=null and username!=''">
              username=#{username}
          </if>
          <if test="password!=null and password!='' ">
            and  password=#{password}
          </if>
      </trim>
    </select>
    <select id="selectByNameAndPassword03" resultMap="usermap">
        select * from user
        <trim prefix="where" suffixOverrides="and | or">
            <if test="@Ognl@isNotEmpty('username')">
                username=#{username}
            </if>
            <if test="@Ognl@isNotEmpty('password')">
             and   password=#{password}
            </if>
        </trim>
    </select>

    <update id="updateUser" >
        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>

    <update id="updateUser02" >
        update user
        <trim prefix="set" suffixOverrides=",">
            <if test="username!=null and username!=''">
                username=#{username} ,
            </if>
            <if test="password!=null and password!=''">
                password=#{password} ,
            </if>
        </trim>

        where id=#{id}
    </update>

接口代码的书写

  public List<User> selectByNameAndPassword(@Param("username") String username,@Param("password") String password);
    public List<User> selectByNameAndPassword02(@Param("username") String username,@Param("password") String password);
    public List<User> selectByNameAndPassword03(@Param("usern