初识Mybatis(六)之动态sql

Mybatis的标签

在写sql语句中,有两个连接条件如果一个没有值,则查询结果会为空
select * from user where username = ” 唐宝宝” and adderss = “林大”
如果在username传入的参数为null,或者每一传递,则查询结果为空

• 可以用if来判断,得出结果

<if test="username != null and username!=''">
    username  = #{username }
</if>
<if test="adderss != null and adderss !=''">
    and adderss= #{adderss}
</if>

• where

<!-- where标签可以自动添加where，同时处理sql语句中第一个and关键字 -->
<where>
<if test="username != null and username!=''">
    username  = #{username }
</if>
<if test="adderss != null and adderss !=''">
    and adderss= #{adderss}
</if>
</where>

• sql片段:
  在书写sql语句时候,会有一些重复的sql片段,比如,select * from user
  Mybatis中可以使将这些相同的片段组合起来,罅隙调用直接用相依的标签就可以

    <!-- sql片段 -->

    <sql id="selector">
        select * from user
    </sql>

<!--调用sql片段-->
<select id="findUserByIdsArray" resultType="User" parameterType="QueryVO">
        <include refid="selector"></include>
    </select>

• foreach标签:

在查询的时候,有时候要查询某几个特定的值,普通sql要这样写:
select * from user where user_id in(1,2,3,4);
但是要用之前的办法无法达到动态修改,要想在程序中动态修改,Mybatis为我们带来了Foreach标签

    <!-- foreach查询多个ID -->

    <!-- 包装集合 -->
    <select id="findUserByIds" resultType="User" parameterType="QueryVo">
        <include refid="selector"></include>
        <where>
            id in
            <foreach collection="idlist" item="id" separator="," open="("
                close=")">
                #{id}
            </foreach>
        </where>
    </select>

    <!-- 包装数组 -->
    <select id="findUserByIdsArray" resultType="User" parameterType="QueryVO">
        <include refid="selector"></include>
        <where>
            id in
            <foreach collection="ids" item="id" separator="," open="("
                close=")">
                #{id}
            </foreach>
        </where>
    </select>

    <!-- 普通集合 -->
    <select id="selectUserByids" resultType="user" parameterType="QueryVo">
        <include refid="selector">
        </include>
        <where>
            id in
            <foreach collection="list" item="id" separator="," open="("
                close=")">
                #{id}
            </foreach>
        </where>
    </select>
    <!--普通数组 -->
    <select id="selectUserByIdsArray" resultType="User">
        select * from user where id in
        <foreach collection="array" item="id" separator="," open="("
            close=")">
            #{id}
        </foreach>
    </select>

    <!-- ofType:泛型的类型 -->
    <resultMap type="User" id="userOrders">
        <id column="uid" property="id"/>
        <result  column="username" property="username"/>
        <collection property="orders" ofType="Orders">
            <id column="oid" property="id"/>
            <result  column="number" property="number"/>
            <result  column="user_id" property="userId"/>
        </collection>
    </resultMap>