四.Mybatis动态SQL

系统管理员 2022-06-15 23:46 198阅读 0赞

**一.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();