记一次insert on duplicate key update的使用

　　基本需求描述：积分扣减/增加操作,记录每条积分明细的同时需要更新一下积分总数，积分不足扣减的时候需要返回异常提示信息。

初始方案：

 @Modifying
    @Query(value = "insert ignore into points_total(customer_id, phone_no, pin, points, create_time, update_time) values(?2,?3,?4,?1,?5,?5) on duplicate key update points =if( points + ?1 >= 0,points + ?1,points),update_time= if(points + ?1 >= 0,?6,update_time) ", nativeQuery = true)
    int saveOrUpdate(Integer newPoints, String customerId, String phoneNo, String pin, Date createTime, Date updateTime);

上面是使用if语法判断积分是否足够扣减，积分不足扣减的话就保持原值；

实践中在初次插入或者积分不足来update的时候都返回1，而积分足够更新的时候返回2，所以我们没办法区别初次插入和积分不足扣减这两种情况，而业务上需要知道什么时候发生了积分不足扣减。

改进方案：

@Modifying
    @Query(value = "insert ignore into points_total(customer_id, phone_no, pin, points, create_time, update_time) values(?2,?3,?4,?1,?5,?5) on duplicate key update points = points + ?1,update_time= ?6 ", nativeQuery = true)
    int saveOrUpdate(Integer newPoints, String customerId, String phoneNo, String pin, Date createTime, Date updateTime) throws DataIntegrityViolationException;

定义积分总数字段points为无符号整数（满足积分总数不能为负数的要求），当积分扣减为负数时会抛出DataIntegrityViolationException异常，代码逻辑通多细致识别此异常可以判定是发生了积分不足扣减。

 catch (DataIntegrityViolationException e) {
            Throwable cause1 = e.getCause();
            if (cause1 != null && cause1 instanceof DataException) {
                Throwable cause2 = cause1.getCause();
                if (cause2 != null && cause2 instanceof MysqlDataTruncation) {
                    //积分不足扣减
                    if (cause2.getMessage() != null && cause2.getMessage().startsWith("Data truncation: BIGINT UNSIGNED value is out of range")) {
                        return new Result<>(0, null, new PointChangeResult(false, RetCode.point_change_ret_code.E03.name()));
                    }
                }
            }
            throw new RuntimeException(e);
        }

 

参考1

参考2