四.Mybatis动态SQL

一.if choose
if:
pojo:jikeReader readerID,userID,money

mapping:

实例化pojo设置属性传入，返回pojo
<select id="selectReaderMoney" resultType="jikeReader" parameterType="jikeReader">
        select * from reader 
            where 1=1
            <if test="money!=null">
                and money>#{money}
            </if>
</select>   

test:

JiKeReader oneReader=new JiKeReader();
oneReader.setMoney(200);
List<JiKeReader> ap=    session.selectList("selectReaderMoney",oneReader);
for(JiKeReader temp:ap) {       
    System.out.println("用户ID="+temp.getReaderID());
}

choose:

mapping:
<select id="selectJiKeUserChoose" resultType="JiKeUser" parameterType="JiKeUser">
            select * from jikeuser where 1=1
            <choose>
                <when test="userName!=null">
                    and userName like #{userName}
                </when>
                <when test="id!=0">
                    and id =#{id}
                </when>

                <otherwise>
                    and password is not null
                </otherwise>
            </choose>
    </select>

test:

JiKeUser oneUser=new JiKeUser();
//oneUser.setUserName("%j%");
oneUser.setId(10);
List<JiKeUser> ap=session.selectList("selectJiKeUserChoose",oneUser);

二.where,set,trim
where:智能去除下面的and

<select id="selectJiKeUserWhere" resultType="JiKeUser" parameterType="JiKeUser">
        select * from jikeuser  
            <where>
                <if test="userName!=null">
                    and userName like #{userName}
                </if>
                <if test="id!=null">
                    and id =#{id}
                </if>
            </where>
</select>

测试：老样子:selectList(id,pojo)

set:智能去除后面的，

<update id="updateJiKeUserSet" parameterType="JiKeUser">
        update JiKeUser
        <set>
            <if test="userName != null">userName=#{userName},</if>
            <if test="password != null">password=#{password},</if>
        </set>
        where id=#{id}
</update>
测试老样子:selectList(id,pojo)

trim:最智能，有四个属性 prefix,suffix
,prefixOverrides, suffixOverrides

mapping:

<update id="updateUserTrim" parameterType="JiKeUser">
        UPDATE JiKeUser 
            <trim prefix="SET" suffixOverrides="," suffix="WHERE id = #{id}" >  
                <if test="userName != null and userName != '' ">  
                        userName = #{userName},
                </if>
                <if test="password != null and password != '' ">  
                        password=#{password},
                </if>  
        </trim>
</update>

另一个例子：

select * from jikeuser  
  <trim prefix="where"   prefixOverrides="and|or">  
            <if test="userName!=null">
                and userName like #{userName}
            </if>
            <if test="id!=null">
                and id =#{id}
            </if>
    </trim>

三.foreach
例子1：
mapping:

<select id="selectJiKeUserForeach" resultType="JiKeUser" parameterType="list"> 
        select * from jikeuser  
            <where>
                id in
                <foreach item="item" index="index" collection="list"
                    open="(" separator="," close=")">
                    #{item}
                </foreach>
            </where>
</select>

测试：

ArrayList<Integer> ides=new ArrayList();
ides.add(2);
ides.add(8);
ides.add(9);
List<JiKeUser> ap=session.selectList("selectJiKeUserForeach", ides);

例子2：
key为循环序号

<insert id="insertJiKeUserForeach">
         insert into jikeUser (userName, password) values  
      <foreach item="item" index="key" collection="list"  
           open="" separator="," close="">(#{key}, #{item.password})
       </foreach>   
</insert>

测试：

ArrayList<JiKeUser> jkuList=new ArrayList();
JiKeUser one=new JiKeUser("jt1","8866");
JiKeUser two=new JiKeUser("jt2","8866");
jkuList.add(one);
jkuList.add(two);
session.insert("insertJiKeUserForeach",jkuList);
session.commit();