mysql - 即使没有显示错误，在 Spring Boot 中使用 @query 更新查询也不起作用

Question

我正在使用 h2 数据库(MYSQL 投诉)的 spring boot 应用程序工作

我有以下存储库:

public interface IGrpAvgMetricTimeSeriesRepository extends
    CrudRepository<GrpAvgMetricTimeSeriesEntity, GrpAvgMetricTimeSeriesEntityPK> {

    @Transactional
    @Modifying(clearAutomatically = true)
    @Query(
        "update GrpAvgMetricTimeSeriesEntity ge Set ge.sampleCount = ge.sampleCount + 1 ,ge.sumVal = ge.sumVal + ?1 "
            + "where ge.groupId = ?2 and ge.metric = ?3 and ge.normalizedTimeStamp = ?4 ")
    void updateSumAndCounter(double value, String grpId, String metric, long normalizedTimeStamp);

    @Query("select g from GrpAvgMetricTimeSeriesEntity g where g.normalizedTimeStamp >= ?1 "
        + "and g.normalizedTimeStamp <= ?2 and g.groupId = ?3 and g.metric = ?4 order by g.normalizedTimeStamp ")
    List<GrpAvgMetricTimeSeriesEntity> getPointsBetween(long fromTime, long toTime, String grpId,
        String metric);
}

具有以下实体:

@Entity
@IdClass(GrpAvgMetricTimeSeriesEntityPK.class)
public class GrpAvgMetricTimeSeriesEntity {

    @NotNull
    @Id
    private String groupId;

    @NotNull
    @Id
    private String metric;


    @NotNull
    @Id
    private long normalizedTimeStamp;

    @NotNull
    private double sumVal;

    @NotNull
    private long sampleCount;


    public GrpAvgMetricTimeSeriesEntity(){
        //For mapper
    }

    public GrpAvgMetricTimeSeriesEntity(String groupId, String metric, long normalizedTimeStamp,
        float sumVal, long sampleCount) {
        this.groupId = groupId;
        this.metric = metric;
        this.normalizedTimeStamp = normalizedTimeStamp;
        this.sumVal = sumVal;
        this.sampleCount = sampleCount;
    }

    public String getGroupId() {
        return groupId;
    }

    public void setGroupId(String groupId) {
        this.groupId = groupId;
    }

    public String getMetric() {
        return metric;
    }

    public void setMetric(String metric) {
        this.metric = metric;
    }

    public long getNormalizedTimeStamp() {
        return normalizedTimeStamp;
    }

    public void setNormalizedTimeStamp(long normalizedTimeStamp) {
        this.normalizedTimeStamp = normalizedTimeStamp;
    }

    public double getSumVal() {
        return sumVal;
    }

    public void setSumVal(float sumVal) {
        this.sumVal = sumVal;
    }

    public long getSampleCount() {
        return sampleCount;
    }

    public void setSampleCount(long sampleCount) {
        this.sampleCount = sampleCount;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) {
            return true;
        }
        if (o == null || getClass() != o.getClass()) {
            return false;
        }
        GrpAvgMetricTimeSeriesEntity that = (GrpAvgMetricTimeSeriesEntity) o;
        return normalizedTimeStamp == that.normalizedTimeStamp &&
            Double.compare(that.sumVal, sumVal) == 0 &&
            sampleCount == that.sampleCount &&
            Objects.equal(groupId, that.groupId) &&
            Objects.equal(metric, that.metric);
    }

    @Override
    public int hashCode() {
        return Objects.hashCode(groupId, metric, normalizedTimeStamp, sumVal, sampleCount);
    }

    @Override
    public String toString() {
        return "GrpAvgMetricTimeSeriesEntity{" +
            "groupId='" + groupId + '\'' +
            ", metric='" + metric + '\'' +
            ", normalizedTimeStamp=" + normalizedTimeStamp +
            ", sumVal=" + sumVal +
            ", sampleCount=" + sampleCount +
            '}';
    }
}

以下sql脚本:

create table host_time_series_entity (
    time_stamp long not null,
    host_name varchar(255) not null,
    metric_name varchar(255) not null,
    metric_value double(255),
    primary key (time_stamp, host_name, metric_name)
    );

create table grp_avg_metric_time_series_entity (group_id varchar(255) not null, metric varchar(255) not null, normalized_time_stamp bigint not null, sum_val DOUBLE not null,sample_count bigint not null, primary key(group_id, metric, normalized_time_stamp));

当应用程序运行并执行此查询时，不会引发异常。

在 Jpa 单元测试中，一切都通过了。

H2 正在创建具有所有正确列的所有模式..

但在执行查询后，h2 中仍然没有更新新行(第二个表的其他存储库，仅执行“保存”查询正在工作。)

问题是什么？

感谢您的帮助!!

更新

我认为的问题是我需要首先检查该行是否已存在于表中 - 如果不创建一个，否则更新现有的一个。

如何使用 @Query 来做到这一点？我看到了一些东西 @SQLinsert但我无法让它工作!!

帮助:(

Answer 1

很难判断问题出在哪里。

我看到两个主要候选者:事务存在一些问题，因此更改实际上确实发生了，但没有提交。或者你的 where 子句关闭了。

为了调试此问题，请激活 SQL 日志记录和事务日志记录。

此外，删除 where 子句并检查不带 where 子句的更新是否有效。如果确实将where子句的元素添加回去，则一一查找有问题的条件。