oracle学习之 merge语句 有则更新 没有则插入

用merge语句实现插入或者更新
注意on条件后面跟的字段 不能放到update中

   <update id="batchMerge" parameterType="WechatImageShareRecord">
        merge into wechat_image_share a
        using (select #{id, jdbcType=INTEGER}         id,
                      #{refDate, jdbcType=TIMESTAMP}  ref_date,
                      #{userSource, jdbcType=INTEGER} user_source,
                      #{shareScene, jdbcType=INTEGER} share_scene,
                      #{shareUser, jdbcType=INTEGER}  share_user,
                      #{shareCount, jdbcType=INTEGER} share_count
               from dual) b
        on (a.ref_date = b.ref_date and a.user_source = b.user_source and a.share_scene = b.share_scene)
        when matched then
            update
            set a.share_user  = b.share_user,
                a.share_count = b.share_count
        when not matched then
            insert (ref_date, user_source, share_scene, share_user, share_count)
            values (b.ref_date, b.user_source, b.share_scene, b.share_user, b.share_count)
    </update>

处理查出来的日期类型转换成string to_char

   <select id="selectRefDateFromImageShare" resultType="java.lang.String">
        SELECT to_char(max(ref_date), 'yyyy-mm-dd')
        FROM wechat_image_share
    </select>

批量处理的逻辑

    /**
     * 图文分享转发数据表 wechat_image_share
     *
     * @param list
     */
    @Override
    public boolean batchMergeWechatImageShare(List<WechatImageShareRecord> list) {
        boolean resultFlag = true;
        SqlSession batchSqlSession = null;
        try {
            batchSqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
            int batchCount = 500;//每批commit的个数
            for (int i = 0; i < list.size(); i++) {
                WechatImageShareRecord imageShareRecord = list.get(i);
                batchSqlSession.getMapper(IWechatImageShareDao.class).batchMerge(imageShareRecord);
                if (i != 0 && i % batchCount == 0) {
                    // 手动提交,提交后无法回滚
                    batchSqlSession.commit();
                    // 清理缓存,防止溢出
                    batchSqlSession.clearCache();
                }
            }
            batchSqlSession.commit();
            batchSqlSession.clearCache();
        } catch (Exception e) {
            // 异常回滚
            logger.error("批处理插入数据失败:{}", e.getMessage());
            if (batchSqlSession != null) {
                batchSqlSession.rollback();
            }
            resultFlag = false;
        } finally {
            if (batchSqlSession != null) {
                batchSqlSession.close();
            }
        }
        return resultFlag;
    }