java.time.Instant field cannot be filled by JPQL query using "current_timestamp" after update to SpringBoot 3 / Hibernate 6

Question

I have a base class for persisted entities like this:

@EntityListeners(AuditListener.class)
@MappedSuperclass
public abstract class BaseEntity {
    @Id
    private String id;
    private Instant createdAt;
    private String createdBy;
    private Instant modifiedAt;
    private String modifiedBy;
    ...

A listener to fill the created/modified fields on persiste/update:

public class AuditListener {
    @PrePersist
    private void onCreate(BaseEntity entity) {
        entity.setCreatedAt(Instant.now());
        entity.setCreatedBy(getIdUserLogged());
    }
    @PreUpdate
    private void onUpdate(BaseEntity entity) {
        entity.setModifiedAt(Instant.now());
        entity.setModifiedBy(getIdUserLogged());
    }
}

For updates made by query the listener would now work and I've manually set the value:

@Repository
public interface IngredientRepository extends CrudRepository<Ingredient, String>  {
    @Modifying
    @Query("update Ingredient set active = false, modifiedAt = current_timestamp where id in :removedIds")
    int deactivate(@Param("removedIds") Collection<String> toBeRemoved);
}

This worked fine with SpringBoot 2.7.x but after update to SpringBoot 3.0.2, app fails on start:

org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'ingredientRepository' defined in ...: Could not create query for public abstract int de.digitale_therapiebegleitung.manager_app.services.local.IngredientRepository.deactivate(java.util.Collection,java.lang.String); Reason: Validation failed for query for method public abstract int de.digitale_therapiebegleitung.manager_app.services.local.IngredientRepository.deactivate(java.util.Collection,java.lang.String)
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.resolveFieldValue(AutowiredAnnotationBeanPostProcessor.java:712)
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:692)
    at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:133)
...
Caused by: java.lang.IllegalArgumentException: org.hibernate.query.SemanticException: The assignment expression type [java.sql.Timestamp] did not match the assignment path type [java.time.Instant] for the path [...] [update Ingredient set active = false, modifiedAt = current_timestamp where id in :removedIds]
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:138)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:175)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:182)
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:760)
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:662)
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:126)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:360)
    at jdk.proxy2/jdk.proxy2.$Proxy166.createQuery(Unknown Source)
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:94)
    ... 76 common frames omitted
Caused by: org.hibernate.query.SemanticException: The assignment expression type [java.sql.Timestamp] did not match the assignment path type [java.time.Instant] for the path [...] [update Ingredient set active = false, modifiedAt = current_timestamp where id in :removedIds]
    at org.hibernate.query.sqm.internal.QuerySqmImpl.verifyUpdateTypesMatch(QuerySqmImpl.java:363)
    at org.hibernate.query.sqm.internal.QuerySqmImpl.validateStatement(QuerySqmImpl.java:312)
    at org.hibernate.query.sqm.internal.QuerySqmImpl.<init>(QuerySqmImpl.java:212)
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:744)
    ... 85 common frames omitted

I couldn't find any change in Hibernate 6 to explain what was changed. One solution is to change the modifiedAt field to java.sql.Timestamp... but is not nice.

Answer 1

I had a similar problem while upgrading from Spring Boot 2 to 3. I tried to follow the suggested configs like changing hibernate.type.preferred_instant_jdbc_type to TIMESTAMP with no luck.

The only thing that worked in my very similar case, was replacing the current_timestamp literal in my query for instant.

So, in your case:

update Ingredient set active = false, modifiedAt = instant where id in :removedIds

You can read https://docs.jboss.org/hibernate/orm/6.1/userguide/html_single/Hibernate_User_Guide.html#hql-datetime-literals for more info.