Search code examples
mysqlspringspring-data-jpaquerydsl

Is there any way to update SET type column using JPA query?

What I want to do is update field in table by JPA query. I set my MySQL table with SET type.

create table staff (
   id    BIGINT,
...
   roles  SET('A', 'B', 'C')    not null,
...
)

and class

public class Staff {
    private Long id;
    @Converter(someConverterHere.class)
    private Set<Role> roles;
}

and I tried to update this field like the way below

@Modifying
@Query("update Staff s SET s.roles = :newRoles where s.id = :id")
Integer updateStaffRoles(@Param("id") Long id, @Param("newRoles") Set<Role> newRoles);

Then, When I gave this Set.of(Role.A, Role.B) as argument, it showed me the error like this,

Caused by: java.lang.IllegalArgumentException: Parameter value [A] did not match expected type [java.util.Set (n/a)]
at org.hibernate.query.spi.QueryParameterBindingValidator.validate(QueryParameterBindingValidator.java:54)
at org.hibernate.query.spi.QueryParameterBindingValidator.validate(QueryParameterBindingValidator.java:27)
at org.hibernate.query.internal.QueryParameterBindingImpl.validate(QueryParameterBindingImpl.java:90)
at org.hibernate.query.internal.QueryParameterBindingImpl.setBindValue(QueryParameterBindingImpl.java:55)
at org.hibernate.query.internal.QueryParameterBindingsImpl.expandListValuedParameters(QueryParameterBindingsImpl.java:636)
at org.hibernate.query.internal.AbstractProducedQuery.doExecuteUpdate(AbstractProducedQuery.java:1629)
at org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1612)
at org.springframework.data.jpa.repository.query.JpaQueryExecution$ModifyingExecution.doExecute(JpaQueryExecution.java:238)
at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:88)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:154)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:142)
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor$QueryMethodInvoker.invoke(QueryExecutorMethodInterceptor.java:195)
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:152)
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:130)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:80)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:367)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
... 81 common frames omitted

It seems jpa query builder validates all single value in newRoles and compare its type with type of Staff.roles, which cannot be true (Role and Set)

Is there any way to solve this? I was not able to find any solution for this.

dependency

  • org.springframework.data:spring-data-jpa:2.3.2.RELEASE

=====================================================

Temporal solution: I changed paramter type from Set to Set<Set> and it works. And now, This problem becomes problem when using QueryDsl. Using "set" of querydsl, this problem happens again :(

Querydsl

// newRoles : Set<Role>
// Error: IllegalArgumentException
query.set(QStaff.Staff.roles, newRoles));

// Of course, compile error
query.set(QStaff.Staff.roles, Set.of(newRoles)));
Solution

  • Custom types need to be mapped by a custom type in your JPA implementation. For Hibernate that is through the User Type interface. In order to use extended types through QueryDSL, you need to create custom expression types for it. Furthermore, for any of the operations you want to support (like set contains, equality), you will have to register custom functions. It can be done, but not using the regular API's. You're probably best off denormalizing your metamodel and using an @ElementCollection instead. However, if you insist on getting to work, here are some pointers:

    • hibernate-types is a widely popular project that implements many custom types for Hibernate. It doesn't implement SET, but you can probably use the implementation for arrays as basis.
    • hibernate-types-querydsl-apt is a project that extends metamodel generation of QueryDSL for some of the custom types. You should probably mimic anything in that repository for sets as well.