Mybatis标签使用

标签的认识：

resultType : 表的字段名称和类的属性名称一致的情况下，完成对象的封装

resultMap：表的字段名和类的属性名称不一致的情况下，完成对象的封装

<where> 标签  ：

1.代替where关键字

2.智能把where后面的第一个条件的and关键字去掉

3.如果where后面没有条件，智能把where去掉

<select id = “findAll” resultType=“实体类全名”>
      select * from user 
        <where>
              <if test="username!=null and username!=‘’">
                    and username like #{username }
                </if>
                <if test="start!=null">
                    and birthday >= #{start}
                </if>
                <if test="end!=null">
                    and birthday &lt;= #{end}
                </if>
          </where>
    </select>

set 标签 :

1.代替set关键字

2.智能把最后一个set条件的逗号去掉

<update id="update”>
        update user
        <set>
            <if test="username!=null and username!=''">
              username = #{username},
            </if>
            <if test="birthday!=null">
              birthday = #{birthday},
            </if>
            <if test="sex!=null and sex!=''">
              sex = #{sex},
            </if>
            <if test="address!=null and address!=''">
              address = #{address},
            </if>
        </set>
        where id = #{id}
    </update>

MySQL批量insert/update/select写法:

<insert id="saveSheeet" parameterType="java.util.List">
        insert into ts_visit_tracks (id,created_date,business_id, store_id, product_id, 
        member_id, area_id)
        values
        <foreach collection="list" item="item" separator=",">
            (#{item.id},#{item.created_date}, #{item.business_id}, #{item.store_id},
            #{item.product_id}, #{item.member_id}, #{item.area_id})
        </foreach>
</insert>

//缺点：这种方式需要设置jdbc连接 allowMultiQueries=true 
<update id="updateBatch"  parameterType="java.util.List">  
    <foreach collection="list" item="item" index="index" open="" close="" separator=";">
        update course
        <set>
            name=${item.name}
        </set>
        where id = ${item.id}
    </foreach>      
</update>

<update id="updateBatch"  parameterType="java.util.List">  
    update mydata_table 
    set  status =
    <foreach collection="list" item="item" index="index" 
        separator=" " open="case ID" close="end">
        when #{item.id} then #{item.status}
    </foreach>
    where id in
    <foreach collection="list" index="index" item="item" 
        separator="," open="(" close=")">
        #{item.id}
    </foreach>
</update>

Oracle批量insert/update/select写法:

//useGeneratedKeys="false" 要加上否则报错
<insert id="addBatch" useGeneratedKeys="false" parameterType="java.util.List">  
    insert into user(  
    id,  
    name,  
    sex)  
    //注意这里没有values 
    <foreach collection="list" item="item" index="index" separator="union all" >  
     //多了select   from  dual  separator="union all"
       ( select
        #{item.id}, #{item.name}, #{item.sex}  
        from dual )
    </foreach>  
</insert> 

<update id="addBatch" parameterType="java.util.List">  
     begin
       <foreach collection="list" item="item"  separator=";" >  
         update user
          <set>
            <if test="item.name != null and item.name != ''">
               name = #{item.name}
            </if>
            <if test="item.sex != null and item.sex != ''">
               sex = #{item.sex}
            </if>
          </set>
         where id = #{item.id}
    </foreach>  
 ;end;
</update> 

 Oracle 分页查询

pageNumber：第几页
pageSize：页的数据条数
单表查询
select * from (select *, rownum r from table where rownum <= pageNumber * pageSize) where r > (pageNumber - 1) * pageSize
多表查询
select * from (select rownum rn , t.* from (select t1.*, t2.* from table t1 , table t2 where t1.字段 = t2.字段) t 
 where rownum <= pageNumber * pageSize) where rn > (pageNumber - 1) * pageSize