CODEWITHSUNDEEP
javasqljpaenumsspring-data-jpa
Cribber
Cribber

Reputation: 2913

Spring Data JPA filter set of enum values with @query or by function name

I have a set of enums as an attribute in MyClass (which is an Entity) and want to filter the string values of the enum either with @Query or with the JPA function name querying (dont know the proper name for it...) .

My Enum:

public enum MyEnum {
    ONE("First string value"),
    TWO("Second string value"),
    THREE("Third string value");

    private MyEnum (String name) {
       this.name = name;
    }
    private final String name;

    @Override
    public String toString() {
        return name;
    }
}

Declaration of the Set of Enums in the MyClass-Entity 

@Size(min=1)
@ElementCollection (fetch = FetchType.EAGER)
@Enumerated(EnumType.STRING)
private Set<MyEnum> myenums;

The table it creates:

create table myclass_myenums (myclass_id bigint not null, myenum varchar(255));

@Query attempt in my Repository.

@Query(value = "SELECT * from mytable t " +
            " left join myclass_myenums e ON e.myclass_id= t.id " +
            " WHERE " +
            " e.myenum LIKE CONCAT('%',:filtertext,'%') " 
            , nativeQuery = true)
    List<RisikoEntity> findbyFilter(@Param("filtertext") String filtertext);

This works, the problem is though, it filters on the Strings "ONE", "TWO", "THREE" and not on "First string value", "Second ..." and so on.

Alternative attempt with the function-name thing attempt in my Repository: 

List<MyClass> findByMyenumsContainsIgnoreCase(String filterstring)

---> Error: "Parameter value [%First%] did not match expected type [path.to.MyEnum (n/a)]"

Second attempt: 

List<MyClass> findByMyenums_NameContainsIgnoreCase(String filterstring)

--> Error: "Illegal attempt to dereference path source [null] of basic type"

What am I doing wrong?

  1. How can I filter the actual enum string-values in the first solution?
  2. How can I filter sets of enum with the JPA function name thing?

Upvotes: 1

Views: 3121

Answers (2)

Nowhere Man
Nowhere Man

Reputation: 19545

If you use JPA 2.1, you may try to use its @Converter annotation as suggested here but this can create significant overhead as the converted String value will have to be stored in your entity:

@Converter(autoApply = true)
public class MyEnumConverter implements AttributeConverter<MyEnum, String> {

    @Override
    public String convertToDatabaseColumn(MyEnum myEnum) {
        if (myEnum == null) {
            return null;
        }
        return myEnum.toString();
    }

    @Override
    public MyEnum convertToEntityAttribute(String code) {
        if (code == null) {
            return null;
        }

        return Stream.of(MyEnum.values())
          .filter(m -> m.toString().equals(code))
          .findFirst()
          .orElseThrow(IllegalArgumentException::new);
    }
}

Upvotes: 1

Amir Schnell
Amir Schnell

Reputation: 651

Well I would say that this is not possible because JPA does not persist the name String of the enum but rather the actual name of the enum.

So your Database should contain ONE or TWO instead of First string value or Second string value inside myenum column.

The only way to solve this is to first filter the enum by code and then find by that enum

Upvotes: 1

Related Questions