Spring服务PUT请求数据库更新成功但响应未同步问题排查

我在Spring服务中实现了一个处理PUT请求的方法，由Controller调用。该方法接收反馈ID和新的Feedback对象，通过PostgreSQL扩展对非原生类型进行转换，执行自定义数据库查询。
目前观察到数据库更新操作正常，但使用Postman测试时仍返回旧数据，直接在数据库执行查询则能获取更新后的结果。

相关代码 #

Repository #

@Query(value = "SELECT * FROM updateFeedback (:feedback_id, :location, CAST(:feedDate AS timestamp with time zone), :sensCode, :feedValue, :clientCode, CAST (:feedEntity AS text)) AS (feedback_id integer, location geography(PointZ,4326), feed_date timestamp with time zone, sens_code text, feed_value double precision, client_code text, feed_entity text, feed_creation_date timestamp with time zone)" , nativeQuery = true)
Feedback updateFeedback(@Param("feedback_id") Integer feedback_id, @Param("location") String location, @Param("feedDate") LocalDateTime feedDate, @Param("sensCode") String sensCode, @Param("feedValue") Double feedValue, @Param("clientCode") String clientCode, @Param("feedEntity") String feedEntity);

Controller #

@PutMapping(path = "/{id}")
public @ResponseBody
Feedback updateFeedback(@Valid @RequestBody Feedback newFeedback, @PathVariable Integer id, HttpServletRequest request) {
    AuthenticationParameters parameters = new AuthenticationParameters(request);
    
    if (newFeedback.getClientCode()==null) newFeedback.setClientCode(parameters.getValue()[3]);
    
    newFeedback.setFeedLocation(newFeedback.toWKBString(newFeedback.getLatitude(), newFeedback.getLongitude(), newFeedback.getAltitude()));
    return service.updateFeedback(newFeedback,id);
}

Service #

public Feedback updateFeedback(Feedback newFeedback, Integer id) {
     return repository.findByFeedbackId(id).map(oldFeedback -> {
         return repository.updateFeedback(newFeedback.getFeedbackId()!=null ? newFeedback.getFeedbackId() : oldFeedback.getFeedbackId(), newFeedback.getFeedLocation()!=null ? newFeedback.getFeedLocation() : oldFeedback.getFeedLocation(), newFeedback.getFeedDate()!=null ? newFeedback.getFeedDate() : oldFeedback.getFeedDate(), newFeedback.getSensCode()!=null ? newFeedback.getSensCode() : oldFeedback.getSensCode(), newFeedback.getFeedValue()!=null ? newFeedback.getFeedValue() : oldFeedback.getFeedValue(), newFeedback.getClientCode()!=null ? newFeedback.getClientCode() : oldFeedback.getClientCode(), newFeedback.getFeedEntity()!=null ? newFeedback.getFeedEntity() : oldFeedback.getFeedEntity());
     })
      .orElseGet(() -> {
        newFeedback.setFeedbackId(id);
        return repository.addFeedback(newFeedback.getFeedLocation(), newFeedback.getFeedDate(), newFeedback.getSensCode(), newFeedback.getFeedValue(), newFeedback.getClientCode(), newFeedback.getFeedEntity());
      });
}

PostgreSQL函数 #

CREATE OR REPLACE FUNCTION iot.updateFeedback(feedbackid integer, loc text, feeddate timestamp with time zone, senscode text, feedvalue double precision, clientcode text, feedentity text)
  RETURNS record
  LANGUAGE plpgsql
  SECURITY DEFINER
AS $function$
DECLARE
  result record;
BEGIN

    WITH replaced_feedback AS (
    UPDATE feedback 
    SET location = loc::geography(PointZ,4326),
        feed_date = feeddate,
        sens_code = senscode,
        feed_value = feedvalue,
        client_code = clientcode,
        feed_entity = feedentity
    WHERE feedback_id = feedbackid
    RETURNING feedback_id, location, feed_date,sens_code,feed_value,client_code, feed_entity, feed_creation_date
    )
    SELECT * INTO result FROM replaced_feedback;
    
    return result;
    
END;
$function$

问题原因及解决方案 #

核心原因：Hibernate一级缓存未更新 #

Service方法中先调用repository.findByFeedbackId(id)，该查询会将旧的Feedback对象加载到Hibernate的Session缓存中。后续通过原生SQL调用PostgreSQL函数执行更新时，Hibernate无法自动感知原生SQL对数据库的修改，缓存中的旧对象不会被同步更新，最终返回给客户端的是缓存里的旧数据（尽管数据库已经完成更新）。

可行解决方案 #

1. 更新后重新查询数据库
   修改Service逻辑，执行更新操作后重新查询数据库获取最新数据，绕过缓存：

public Feedback updateFeedback(Feedback newFeedback, Integer id) {
     return repository.findByFeedbackId(id).map(oldFeedback -> {
         // 执行更新操作
         repository.updateFeedback(newFeedback.getFeedbackId()!=null ? newFeedback.getFeedbackId() : oldFeedback.getFeedbackId(), 
                                  newFeedback.getFeedLocation()!=null ? newFeedback.getFeedLocation() : oldFeedback.getFeedLocation(), 
                                  newFeedback.getFeedDate()!=null ? newFeedback.getFeedDate() : oldFeedback.getFeedDate(), 
                                  newFeedback.getSensCode()!=null ? newFeedback.getSensCode() : oldFeedback.getSensCode(), 
                                  newFeedback.getFeedValue()!=null ? newFeedback.getFeedValue() : oldFeedback.getFeedValue(), 
                                  newFeedback.getClientCode()!=null ? newFeedback.getClientCode() : oldFeedback.getClientCode(), 
                                  newFeedback.getFeedEntity()!=null ? newFeedback.getFeedEntity() : oldFeedback.getFeedEntity());
         // 重新查询获取最新数据
         return repository.findByFeedbackId(id);
     })
      .orElseGet(() -> {
        newFeedback.setFeedbackId(id);
        return repository.addFeedback(newFeedback.getFeedLocation(), newFeedback.getFeedDate(), newFeedback.getSensCode(), newFeedback.getFeedValue(), newFeedback.getClientCode(), newFeedback.getFeedEntity());
      });
}

2. 手动清除Session缓存
   在Service中注入EntityManager，执行更新后手动清除缓存，让后续查询直接从数据库获取数据：

@Autowired
private EntityManager entityManager;

public Feedback updateFeedback(Feedback newFeedback, Integer id) {
     return repository.findByFeedbackId(id).map(oldFeedback -> {
         Feedback updated = repository.updateFeedback(newFeedback.getFeedbackId()!=null ? newFeedback.getFeedbackId() : oldFeedback.getFeedbackId(), 
                                  newFeedback.getFeedLocation()!=null ? newFeedback.getFeedLocation() : oldFeedback.getFeedLocation(), 
                                  newFeedback.getFeedDate()!=null ? newFeedback.getFeedDate() : oldFeedback.getFeedDate(), 
                                  newFeedback.getSensCode()!=null ? newFeedback.getSensCode() : oldFeedback.getSensCode(), 
                                  newFeedback.getFeedValue()!=null ? newFeedback.getFeedValue() : oldFeedback.getFeedValue(), 
                                  newFeedback.getClientCode()!=null ? newFeedback.getClientCode() : oldFeedback.getClientCode(), 
                                  newFeedback.getFeedEntity()!=null ? newFeedback.getFeedEntity() : oldFeedback.getFeedEntity());
         // 清除缓存
         entityManager.clear();
         return updated;
     })
      .orElseGet(() -> {
        newFeedback.setFeedbackId(id);
        return repository.addFeedback(newFeedback.getFeedLocation(), newFeedback.getFeedDate(), newFeedback.getSensCode(), newFeedback.getFeedValue(), newFeedback.getClientCode(), newFeedback.getFeedEntity());
      });
}

3. 禁用查询缓存
   在Repository的updateFeedback方法上添加查询提示，禁用缓存：

@Query(value = "SELECT * FROM updateFeedback (:feedback_id, :location, CAST(:feedDate AS timestamp with time zone), :sensCode, :feedValue, :clientCode, CAST (:feedEntity AS text)) AS (feedback_id integer, location geography(PointZ,4326), feed_date timestamp with time zone, sens_code text, feed_value double precision, client_code text, feed_entity text, feed_creation_date timestamp with time zone)" , nativeQuery = true)
@QueryHints({@QueryHint(name = org.hibernate.annotations.QueryHints.CACHEABLE, value = "false")})
Feedback updateFeedback(@Param("feedback_id") Integer feedback_id, @Param("location") String location, @Param("feedDate") LocalDateTime feedDate, @Param("sensCode") String sensCode, @Param("feedValue") Double feedValue, @Param("clientCode") String clientCode, @Param("feedEntity") String feedEntity);

额外检查点 #

确认Service中传递给updateFeedback的feedback_id参数是否正确：当前逻辑优先使用newFeedback.getFeedbackId()，否则用oldFeedback.getFeedbackId()，但实际应该绑定到URL路径中的id参数，避免因newFeedback传入错误ID导致更新错误记录（虽然你提到数据库更新正常，但可以进一步确认）。

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