当前位置：首页 > news >正文

【异常】因为忘加了租户查询条件，导致重复ID导入失败Duplicate entry ‘XXX‘ for key ‘PRIMARY‘

news 2025/6/22 1:03:08

一、异常说明

 Error updating database.  Cause: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '670' for key 'PRIMARY'The error may exist in /mall/admin/mapper/GoodsCategoryMapper.java (best guess)The error may involve .admin.mapper.GoodsCategoryMapper.insert-InlineThe error occurred while setting parametersSQL: INSERT INTO goods_category (id, tenant_id, enable, parent_id, name, sort, create_time, update_time, del_flag) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)Cause: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '670' for key 'PRIMARY'
; Duplicate entry '670' for key 'PRIMARY'; nested exception is java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '670' for key 'PRIMARY'at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:247)at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91)at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)at com.sun.proxy.Proxy185.insert(Unknown Source)

二、看看代码逻辑

出错的代码逻辑，其实就是个存类目信息到数据库的逻辑

public void saveGoodsCategory(GoodsCategoryImportDTO goodsCategoryImportDTO) {JSONArray goodsCategoryJsonArray = getJSONArray(goodsCategoryImportDTO);for (int i = 0; i < goodsCategoryJsonArray.size(); i++) { List<GoodsCategory> goodsCategoryList = baseMapper.selectList(Wrappers.<GoodsCategory>lambdaQuery().eq(GoodsCategory::getId, JSONUtil.parseObj(goodsCategoryJsonArray.get(i)).getStr("id")));//重复类目数据的过滤if (ObjectUtil.isNotEmpty(goodsCategoryList)) {log.info("重复类目数据:levelOneCategoryId = [{}],导入失败，类目数据记录已经存在", goodsCategoryImportDTO.getLevelOneCategoryId());continue;}GoodsCategory goodsCategory = new GoodsCategory();goodsCategory.setId(JSONUtil.parseObj(goodsCategoryJsonArray.get(i)).getStr("id"));goodsCategory.setName(JSONUtil.parseObj(goodsCategoryJsonArray.get(i)).getStr("name"));goodsCategory.setParentId(JSONUtil.parseObj(goodsCategoryJsonArray.get(i)).getStr("parentId"));int sortSize = getSort(GOODS_CATEGORY_JD_KEY);goodsCategory.setSort(sortSize);addSize(GOODS_CATEGORY_JD_KEY);goodsCategory.setTenantId(jDConfigProperties.getTenantId());goodsCategory.setEnable(CommonConstants.YES);goodsCategory.setCreateTime(LocalDateTime.now());goodsCategory.setDelFlag(DelFlagEnum.SHOW.getValue());goodsCategory.setDescription(ProductChannelEnum.JINGDONG.getDesc());baseMapper.insert(goodsCategory);}
}

因为ID设置了主键，因此，当ID重复时，就会提示已经重复了，Duplicate entry '670' for key 'PRIMARY'
故，新增了如下查询逻辑，用于插入前判断，防止重复导入。

List<GoodsCategory> goodsCategoryList = baseMapper.selectList(Wrappers.<GoodsCategory>lambdaQuery().eq(GoodsCategory::getId, JSONUtil.parseObj(goodsCategoryJsonArray.get(i)).getStr("id"))
);
//重复类目数据的过滤
if (ObjectUtil.isNotEmpty(goodsCategoryList)) {log.info("重复类目数据:levelOneCategoryId = [{}],导入失败，类目数据记录已经存在", goodsCategoryImportDTO.getLevelOneCategoryId());continue;
}

但是，这段逻辑，却永远不会进入到if判断语句中。这样就不会按照语句希望的那样，继续下一个循环。
那，为什么不会进到if判断语句中呢？原因就肯定是那段Mybatis的逻辑有问题啦。
怎么看Mybatis的实时输出的SQL脚本呢？IDEA有个插件能帮到你。
【项目实战】IDEA插件推荐——Mybatis Log Plugin查看Mybatis实时输出的SQL脚本

因为项目是基于多租户架构实现的，因此我们编写的Mybatis的脚本一般都需要加上租户信息，才能查询出来结果

本项目中多租户的实现方式，见以下技术文章
【项目实战】商城中多租户功能逻辑设计

三、问题解决

定位了问题，解决就快了呢。直接加上租户进查询条件即可，如下

 @Override
public void saveGoodsCategoryShop(GoodsCategoryImportDTO goodsCategoryImportDTO) {JSONArray goodsCategoryJsonArray = getJSONArray(goodsCategoryImportDTO);for (int i = 0; i < goodsCategoryJsonArray.size(); i++) {// 设置租户IDTenantContextHolder.setTenantId(jDConfigProperties.getTenantId());List<GoodsCategoryShop> goodsCategoryShopList = baseMapper.selectList(Wrappers.<GoodsCategoryShop>lambdaQuery().eq(GoodsCategoryShop::getId, JSONUtil.parseObj(goodsCategoryJsonArray.get(i)).getStr("id")));//重复类目数据的过滤if (ObjectUtil.isNotEmpty(goodsCategoryShopList)) {log.info("重复店铺类目数据:levelOneCategoryId = [{}],导入失败，店铺类目数据记录已经存在", goodsCategoryImportDTO.getLevelOneCategoryId());continue;}GoodsCategoryShop goodsCategoryShop = new GoodsCategoryShop();goodsCategoryShop.setId(JSONUtil.parseObj(goodsCategoryJsonArray.get(i)).getStr("id"));goodsCategoryShop.setName(JSONUtil.parseObj(goodsCategoryJsonArray.get(i)).getStr("name"));goodsCategoryShop.setParentId(JSONUtil.parseObj(goodsCategoryJsonArray.get(i)).getStr("parentId"));int sortSize = getSort(GOODS_CATEGORY_SHOP_JD_KEY);goodsCategoryShop.setSort(sortSize);addSize(GOODS_CATEGORY_SHOP_JD_KEY);goodsCategoryShop.setShopId(jDConfigProperties.getShopId());goodsCategoryShop.setTenantId(jDConfigProperties.getTenantId());goodsCategoryShop.setEnable(CommonConstants.YES);goodsCategoryShop.setCreateTime(LocalDateTime.now());goodsCategoryShop.setDelFlag(DelFlagEnum.SHOW.getValue());goodsCategoryShop.setDescription(ProductChannelEnum.JINGDONG.getDesc());baseMapper.insert(goodsCategoryShop);}
}

注意：重点是加了这一句！

 // 设置租户ID
TenantContextHolder.setTenantId(jDConfigProperties.getTenantId());

加了之后，就可以正常进if分支咯。至此，问题解决！