2019年12月17日

1.使用批量新增和批量修改

java接口:

//批量新增
int batchInsertGoods(List<Goods> list);
 
//批量修改
int batchUpdateGoodsByIds(List<Map<String, Object>> list);
 
//根据ids批量获取Goods列表
List<Map<String, Object>> getGoodsList(List<String> ids);

mybatis的xml:

<!-- 批量新增-->
<insert id="batchInsertGoods" parameterType="java.util.List">
    INSERT INTO goods(i_id, goods_name)
    VALUES
    <foreach collection="list" item="item" separator=",">
        (#{item.id,jdbcType=VARCHAR},#{item.goodsName,jdbcType=VARCHAR})
    </foreach>
</insert>
 
<!-- 批量修改-->
<update id="batchupdateGoodsByIds" parameterType="java.util.List">
      UPDATE goods
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="goods_name = case" suffix="end">
                <foreach collection="list" item="item" index="index" >
                    <if test="item.goodsName != null and item.goodsName != ''">
                        when i_id = #{item.id, jdbcType=VARCHAR} then #{item.goodsName, jdbcType=VARCHAR}
                    </if>
                    <if test="item.goodsName == null or item.goodsName == ''">
                        when i_id = #{item.id, jdbcType=VARCHAR} then goods.goods_name
                    </if>
                </foreach>
            </trim>
            <trim prefix="iorder = case" suffix="end">
                <foreach collection="list" item="item" index="index" >
                    <if test="item.iorder != null and item.iorder != ''">
                        when i_id = #{item.id, jdbcType=VARCHAR} then #{item.iorder, jdbcType=VARCHAR}
                    </if>
                    <if test="item.iorder == null or item.iorder == ''">
                        when i_id = #{item.id, jdbcType=VARCHAR} then goods.iorder
                    </if>
                </foreach>
            </trim>
        </trim>
        WHERE i_id in
        <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
            #{item.id, jdbcType=VARCHAR}
        </foreach>
</update>
 
<!-- 根据ids批量获取Goods列表-->
<select id="getGoodsList" parameterType="java.util.List" resultType="java.util.Map" >
    SELECT id, goods_name WHERE id in
       <foreach collection="list" item="item" index="index" open="(" separator="," close=")">
           #{item}
       </foreach>
</select>

应用场景，对成批数据进行修改。

2.是用group  by优化代码，减少与数据库的交互次数，应用场景，进行不同状态的时间统计。

<select id="getDurationByPlanningIdAndStatus" resultType="net.vtstar.cloudmes.business.mobileterminal.domain.StatusTimeSeriesVO" parameterType="java.lang.Integer">
        select
        status,
        sum(duration) as executionTime
        from
        PRODUCTION_EXECUTION_STATUS_TIME_SERIES
        where planning_id = #{planningId}
        group by status
    </select>

3.使用mybatis标签实现一对多查询，应用场景，对不同的数据进行统计

参考自:

https://www.cnblogs.com/crazylover/p/10568289.html