CODEWITHSUNDEEP
spring-boothibernatejpqlsql-timestamp
user3459821
user3459821

Reputation: 123

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

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.

Upvotes: 12

Views: 6761

Answers (3)

Leandro Vieira
Leandro Vieira

Reputation: 166

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.

Upvotes: 15

Kacper Wolkowski
Kacper Wolkowski

Reputation: 1607

Another way that will be more in line with what you had initially would be to use casting. So your example will look like:

@Modifying
@Query("update Ingredient set active = false, modifiedAt = cast(current_timestamp as instant)  where id in :removedIds")
int deactivate(@Param("removedIds") Collection<String> toBeRemoved);

the only difference is cast(current_timestamp as instant).

Upvotes: 5

Shakirov Ramil
Shakirov Ramil

Reputation: 1554

https://thorben-janssen.com/migrating-to-hibernate-6/

When Hibernate introduced the proprietary mapping for these types in version 5, it mapped Instant to SqlType.TIMESTAMP and Duration to Types.BIGINT. Hibernate 6 changes this mapping. It now maps Instant to SqlType.TIMESTAMP_UTC and Duration to SqlType.INTERVAL_SECOND.

These new mappings seem to be a better fit than the old ones. So, it’s good that they changed it in Hibernate 6. But it still breaks the table mapping of existing applications. If you run into that problem, you can set the configuration property hibernate.type.preferred_instant_jdbc_type to TIMESTAMP and hibernate.type.preferred_duration_jdbc_type to BIGINT.

Upvotes: 1

Related Questions