Spring Data JPA更新Oracle数据库报错：ID不存在排查求助

问题详情 #

插入新Video实体正常，但更新操作触发以下错误：

Caused by: org.springframework.dao.IncorrectUpdateSemanticsDataAccessException: Failed to update entity [Video(videoId=10,30,35,112,-127,-72,79,-71,-32,99,116,-77,90,10,-28,-23, name=Test Name 1, description=Test Description, tags=Test Tag, url=Test url, active=1, createdBy=testcreator, createdDate=2023-11-14 13:55:24.682, updatedBy=testupdator, updatedDate=Tue Nov 14 17:30:00 GMT 2023)]; Id [10,30,35,112,-127,-72,79,-71,-32,99,116,-77,90,10,-28,-23] not found in database

数据库使用sys_guid()生成RAW类型ID，实体中ID字段定义为String类型，能打印出数据库中现有Video对象且ID字符串匹配，但更新时提示ID不存在。

相关代码 #

ServiceImpl实现

public Video insertVideoInDB(Video newVideo){
        Date date = new Date();
        //check if this is an update
        if(newVideo.getVideoId()!= null){
            Video existingVideo = videoRepository.findById(newVideo.getVideoId()).get();
            //then update the updated date and updated by and whatever else changed
            existingVideo.setUpdatedBy(newVideo.getUpdatedBy());
            existingVideo.setUpdatedDate(date);
            return videoRepository.save(existingVideo);
        }
        //new video
        else{
            newVideo.setCreatedDate(date);
            newVideo.setUpdatedDate(date);
        }
        return videoRepository.save(newVideo);
    }

更新测试用例

@Test
    void testUpdates(){
        Video video = new Video(
                "0A1E237081B84FB9E06374B35A0AE4E9", //id
                "Updating", //name
                "Test Description",  //desc
                "Test Tag",  //tags
                "Test url",  //url
                1,           //status
                "testcreator", //who created
                null,      //date created
                "new updater", //who updated -----only val i change here to test
                null //date updated
        );
        videoServiceImpl.insertVideoInDB(video);

    }

解决方案 #

核心问题是数据库RAW类型ID与实体String类型的转换不匹配，导致JPA无法正确识别数据库中的ID，以下是修复步骤：

1. 修正实体ID字段映射
   给Video实体的videoId字段添加类型转换注解，指定数据库列类型并关联转换器：

   @Id
   @Column(name = "VIDEO_ID", columnDefinition = "RAW(16)")
   @Convert(converter = StringToRawConverter.class)
   private String videoId;
   

2. 实现String与RAW双向转换器
   自定义JPA属性转换器，处理十六进制字符串和RAW字节数组的相互转换：

   import javax.persistence.AttributeConverter;
   import javax.persistence.Converter;
   
   @Converter(autoApply = true)
   public class StringToRawConverter implements AttributeConverter<String, byte[]> {
   
       @Override
       public byte[] convertToDatabaseColumn(String attribute) {
           if (attribute == null) {
               return null;
           }
           // 将十六进制字符串转为对应字节数组，匹配RAW类型存储
           int len = attribute.length();
           byte[] data = new byte[len / 2];
           for (int i = 0; i < len; i += 2) {
               data[i / 2] = (byte) ((Character.digit(attribute.charAt(i), 16) << 4)
                     + Character.digit(attribute.charAt(i+1), 16));
           }
           return data;
       }
   
       @Override
       public String convertToEntityAttribute(byte[] dbData) {
           if (dbData == null) {
               return null;
           }
           // 将RAW字节数组转回十六进制字符串
           StringBuilder sb = new StringBuilder(dbData.length * 2);
           for (byte b : dbData) {
               sb.append(String.format("%02X", b));
           }
           return sb.toString();
       }
   }
   

   注意：用循环处理十六进制转换比BigInteger更稳妥，避免前导零丢失问题

3. 优化Service中的查询逻辑
   原代码中findById().get()会直接抛出NoSuchElementException，建议改为明确的异常提示，方便排查：

   Video existingVideo = videoRepository.findById(newVideo.getVideoId())
       .orElseThrow(() -> new IllegalArgumentException("Video not found with ID: " + newVideo.getVideoId()));
   

4. 验证测试用例的ID格式
   确认测试传入的ID字符串与数据库中sys_guid()生成的ID完全一致，Oracle的sys_guid()生成的是大写十六进制字符串，注意大小写匹配。

内容的提问来源于stack exchange，提问作者ssnyc