ibatis与mybatis批量更新

批量更新目的在于减少数据库io时间，缩短执行时间，提高效率。

ibatis批量配置：

xml：

<update id="batchUpdate"  parameterClass="com.***.entity.UserOrderDetail"  >
   UPDATE t_order_detail
   SET
   used = #used:INTEGER#,
   day_used = #dayUsed:INTEGER#,
   total = #total:INTEGER#,
   flow_type = #flowType:TINYINT#
   WHERE order_id=#orderId:BIGINT#
</update>

java代码

public void batchUpdate( final List<UserOrderDetail> list){
    try {
        if (list != null && ! list.isEmpty() ) {
            this .getSqlMapClientTemplate().execute( new SqlMapClientCallback() {

                public Object doInSqlMapClient(SqlMapExecutor executor) throws SQLException {
                    executor.startBatch();
                    final int batchSize = 200;//此处为一次批量更新大小，可以自己测试一下
                    int count = 0;
                    for (UserOrderDetail userOrderDetail : list){
                        executor.update(getNameSpace() + ".batchUpdate",userOrderDetail);//生成一条sql，对应xml里面语句
                        if(++count%batchSize==0){
                            executor.executeBatch();//此处一次性提交200条sql。
                        }
                    }
                    executor.executeBatch();
                    return null ;
                }

            });
        }
    }catch (Exception e){
        e.printStackTrace();
    }
}

mybatis批量更新：

xml配置；

<update id="batchUpdate" parameterType="java.util.List">
  update t_order_detail
  <trim prefix="set" suffixOverrides=",">

<!--此处对应数据库表字段，需要更新多少写多少trim-->
    <trim prefix="day_used =case" suffix="end,">      <foreach collection="list" item="item" index="index">        when order_id=#{item.orderId} then #{item.dayUsed}      </foreach>    </trim>    <trim prefix="used =case" suffix="end,">      <foreach collection="list" item="item" index="index">        when order_id=#{item.orderId} then #{item.used}      </foreach>    </trim>  </trim>  where order_id in  <foreach collection="list" index="index" item="item" separator="," open="(" close=")">    #{item.orderId,jdbcType=BIGINT}  </foreach></update>

java代码

public String test(HttpServletRequest request, HttpServletResponse response){
    TOrderDetailExample tOrderDetailExample = new TOrderDetailExample();
    TOrderDetailExample.Criteria criteria = tOrderDetailExample.createCriteria();
    criteria.andDayUsedLessThan( 0 );
    List<TOrderDetail> list = tOrderDetailMapper.selectByExample( tOrderDetailExample );
    System.out.println(list.size());
    for (TOrderDetail tOrderDetail : list){
        tOrderDetail.setDayUsed( 0 );
        tOrderDetail.setUsed( 2 );
    }

//可以设置批量更新大小

    tOrderDetailMapper.batchUpdate( list );
    return "1";
}

可以查看sql日志，看一下最终执行sql语句。