Spring boot hibernate update localdatetime field in query

Question

I have a query:

@Modifying
    @Query(value = "UPDATE table_name u SET u.status = 'COMPLETED', u.completed_at = :completedAt WHERE u.id IN (:ids)", nativeQuery = true)
    void setCompletedByIds(List ids, LocalDateTime completedAt);

it sets status, but not date.. I checked logs:

Hibernate: UPDATE table_name u SET u.status = 'COMPLETED', u.completed_at = ? WHERE u.id IN ( ? ) 2022-09-21 10:01:37,792 [scheduling-1] TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [1] as [TIMESTAMP] - [2022-09-21T08:01:37.616134243] 2022-09-21 10:01:37,793 [scheduling-1] TRACE o.h.type.descriptor.sql.BasicBinder

binding parameter [2] as [BINARY] - [9c9729d4-8e27-4e2d-9029-ee74b8777f4c]

and I tried this query on db directly:

UPDATE
        table_name u 
    SET
        u.status = 'COMPLETED', 
        u.completed_at = '2020-12-01T10:05:23.653'
    WHERE
        u.id IN (
            '9c9729d4-8e27-4e2d-9029-ee74b8777f4c'
        )

which works fine..

I tried also with HQL:

@Modifying
    @Query(value = "update TestDAO u set u.completedAt =:completedAt, u.status = 'COMPLETED' WHERE u.id IN (:ids)")
    void setCompletedByIds(List ids, LocalDateTime completedAt);

but does not work either.. completed_at field is not updated ( but status is updated properly ).

about mysql column type, tried with DATETIME and TIMESTAMP, nothing work :(

Dao code:

import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import org.hibernate.annotations.Type;

import javax.persistence.*;
import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.util.UUID;

@Entity
@NoArgsConstructor
@Getter
@Setter
@Table(name = "test")
public class TestDAO {

    public UserLabTestToCheckDAO(UserProfileDAO userProfile, String foreignId, String name, TestStatusDAO status) {
        this.id = UUID.randomUUID();
        this.userId = userProfile.getId();
        this.userProfile = userProfile;
        this.foreignId = foreignId;
        this.name = name;
        this.status = status;
        this.errorMessage = "";
        this.createdAt = LocalDateTime.now(ZoneOffset.UTC);
    }

    @Id
    @Type(type = "uuid-char")
    private UUID id;

    @Column(name = "user_id", nullable = false, insertable = false, updatable = false)
    @Type(type = "uuid-char")
    private UUID userId;

    public UUID getUserId() {
        return userId;
    }

    @OneToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "user_id")
    private UserProfileDAO userProfile;

    private String foreignId;
    @Enumerated(EnumType.STRING)

    private TestStatusDAO status;
    private String errorMessage;

    private LocalDateTime createdAt;

    @Column(name = "completed_at", columnDefinition = "TIMESTAMP")
    private LocalDateTime completedAt;

    private String name;

}

I use lombok getters and setters. btw. If I will do:

  dao.setStatus(UserLabTestToCheckStatusDAO.COMPLETED)
  dao.setCompletedAt(LocalDateTime.now(clock))
  userLabTestToCheckRepository.save(dao)

( but this will be many updates in loop ), instead of query, all works fine, and completed_at field is updated.

Spring boot hibernate update localdatetime field in query

0 Answers