四.Mybatis动态SQL

来源：互联网发布：陈一发淘宝店地址编辑：程序博客网时间：2024/06/06 01:04

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

0 0