Spring Boot & MyBatis-Plus: Exception-Isolated Upsert with Remote Data Enrichment

Scenario: when a user views a product detail page, a log entry (user_code + product_code unique) must be recorded and enriched with the user name and product name obtained from remote services. The logging must not affect the main product‑detail response.

1. Table Definition

CREATE TABLE `t_user_product_visit_log` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_code` varchar(50) NOT NULL COMMENT '用户编码',
  `user_name` varchar(100) DEFAULT NULL COMMENT '用户名称',
  `product_code` varchar(50) NOT NULL COMMENT '商品编码',
  `product_name` varchar(200) DEFAULT NULL COMMENT '商品名称',
  `visit_count` int(11) NOT NULL DEFAULT 1 COMMENT '访问次数',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '首次访问时间',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '最近访问时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_product` (`user_code`, `product_code`)
) ENGINE=InnoDB DEFAULT charset=utf8mb4 COMMENT='用户商品访问记录表';

Key points: composite unique index uk_user_product guarantees one row per user‑product pair; ON UPDATE CURRENT_TIMESTAMP refreshes update_time on each update; visit_count is used to demonstrate incremental updates.

2. Entity Class

package com.example.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import java.util.Date;

@TableName("t_user_product_visit_log")
@Data
public class UserProductVisitLogEntity {
    @TableId(type = IdType.AUTO)
    private Long id;
    private String userCode;
    private String userName;
    private String productCode;
    private String productName;
    private Integer visitCount;
    private Date createTime;
    private Date updateTime;
}

Annotations map the class to the table, auto‑generate the primary key, and convert camel‑case fields to snake_case columns.

3. Mapper Interface

package com.example.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.entity.UserProductVisitLogEntity;
import org.apache.ibatis.annotations.Param;

public interface UserProductVisitLogMapper extends BaseMapper<UserProductVisitLogEntity> {
    /**
     * Insert or update based on the unique index uk_user_product.
     * If a record exists, update the names and increment visit_count; otherwise insert.
     */
    void insertOrUpdate(@Param("entity") UserProductVisitLogEntity entity);
}

4. Mapper XML (Upsert Logic)

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.mapper.UserProductVisitLogMapper">
    <insert id="insertOrUpdate" parameterType="com.example.entity.UserProductVisitLogEntity">
        INSERT INTO t_user_product_visit_log
            (user_code, user_name, product_code, product_name, visit_count, create_time, update_time)
        VALUES
            (#{entity.userCode}, #{entity.userName}, #{entity.productCode}, #{entity.productName}, 1, NOW(), NOW())
        ON DUPLICATE KEY UPDATE
            user_name = #{entity.userName},
            product_name = #{entity.productName},
            visit_count = visit_count + 1,
            update_time = NOW()
    </insert>
</mapper>

The SQL first attempts an INSERT; if the composite unique key conflicts, MySQL executes the ON DUPLICATE KEY UPDATE clause, incrementing visit_count and refreshing timestamps.

5. Remote Feign Clients (Simulated)

package com.example.feign;

import org.springframework.cloud.openfeign.FeignClient;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;

@FeignClient(name = "userService", url = "{feign.product-service.url}")
public interface ProductFeign {
    @GetMapping("/products/{productCode}/name")
    String getProductName(@PathVariable("productCode") String productCode);
}

6. Service Implementation (Core Logic)

package com.example.service.impl;

import com.example.entity.UserProductVisitLogEntity;
import com.example.feign.ProductFeign;
import com.example.feign.UserFeign;
import com.example.mapper.UserProductVisitLogMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

@Slf4j
@Service
public class ProductServiceImpl {
    private final UserProductVisitLogMapper visitLogMapper;
    private final UserFeign userFeign;
    private final ProductFeign productFeign;

    public ProductServiceImpl(UserProductVisitLogMapper visitLogMapper, UserFeign userFeign, ProductFeign productFeign) {
        this.visitLogMapper = visitLogMapper;
        this.userFeign = userFeign;
        this.productFeign = productFeign;
    }

    /** Query product detail – main flow */
    public ProductDetailVO getProductDetail(String userCode, String productCode) {
        // auxiliary: record visit log (exceptions must not affect main flow)
        saveVisitLog(userCode, productCode);
        // main flow: fetch product detail
        return queryProductFromDB(productCode);
    }

    /** Save visit log with layered exception isolation */
    private void saveVisitLog(String userCode, String productCode) {
        try {
            UserProductVisitLogEntity entity = new UserProductVisitLogEntity();
            entity.setUserCode(userCode);
            entity.setProductCode(productCode);
            // Remote call 1: fetch user name
            try {
                String userName = userFeign.getUserName(userCode);
                entity.setUserName(userName);
            } catch (Exception e) {
                log.warn("Failed to fetch user name, userCode={}, error={}", userCode, e.getMessage());
            }
            // Remote call 2: fetch product name
            try {
                String productName = productFeign.getProductName(productCode);
                entity.setProductName(productName);
            } catch (Exception e) {
                log.warn("Failed to fetch product name, productCode={}, error={}", productCode, e.getMessage());
            }
            // Perform upsert
            visitLogMapper.insertOrUpdate(entity);
        } catch (Exception e) {
            // Top‑level catch: ensure any unexpected error does not propagate
            log.error("Saving visit log failed without affecting main flow, userCode={}, productCode={}, error={}",
                    userCode, productCode, e.getMessage());
        }
    }
}

7. Three‑Layer Exception Isolation Diagram

saveVisitLog()
├── try {               // first layer: catch all exceptions
│   ├── try { userFeign... }   // second layer: user service failure ignored
│   ├── try { productFeign... } // second layer: product service failure ignored
│   └── visitLogMapper.insertOrUpdate() // database upsert
│} catch (Exception e) {
│   log.error(...); // only log, do not rethrow
│}

8. Execution Demonstration

First visit (INSERT) : User U001 accesses product P001 → INSERT with visit_count=1, names filled, succeeds.

Second visit (UPDATE) : Same user accesses the same product again → unique‑key conflict triggers ON DUPLICATE KEY UPDATE, visit_count becomes 2, timestamps refreshed.

Remote call failure : If userFeign.getUserName throws, the inner catch logs a warning, leaves userName null, but the product name is still fetched and the INSERT proceeds.

Database failure : If the upsert throws (e.g., connection timeout), the outer catch logs an error; the main product‑detail flow continues unchanged, so the user does not notice the logging failure.

9. Core Takeaways

Use INSERT ON DUPLICATE KEY UPDATE for atomic upsert, avoiding “select‑then‑insert” race conditions.

Wrap auxiliary logging in layered try‑catch blocks so that any exception—whether from remote services or the database—does not affect the primary business logic.

Leverage a composite unique index to enforce data uniqueness at the database level rather than in application code.

The pattern is suitable for non‑core data‑record scenarios such as access logs, audit trails, telemetry, or cache warm‑up.