update query using @query in spring boot not working eventhough no error is showing

Question

Im working in spring boot app with h2 data base (MYSQL complaint)

I have the following repository :

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);
}

with the following entity :

@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 +
            '}';
    }
}

The following sql script :

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));

when the app is running and this query is executed , no exceptions are thrown .

In Jpa unit tests everything is passing .

H2 is creating all the schemas with all the right columns ..

but still, not new row is updated in h2 after the query is executed (other repo for the second table, which just do "save" query is working..)

what is the problem ??

Thank for your help !!

UPDATE

The problem I think is that I need to check first if the line exists already in table - if not create one , else update the existing one .

How do I do it with @Query ? I saw something with @SQLinsert but I cant get it to work !!

helppp :(

Answer 1

Hard to tell what the issue is here.

I see two major candidates: some issues with transactions, so the changes actually do happen but don't get commited. Or your where clause is off.

In order to debug this activate SQL logging and transaction logging.

Also, remove the where clause and check if the update without where clause works. If it does add the elements of the where clause back, one by one to find the problematic condition.