JPA Repository findByEnum does not cast the Argument Enum to String or Postgres Cast not working as expected

Question

I have a Spring Boot (2.5.4) backend pointing to a Postgres (9.6) database. I have an entity in Spring that makes use of the @Enumerated(EnumType.String) annotation on a field of an Enum type. Persisting this entity works as expected and converts the Enum into a String. In Postgres, I have the respective enum casted to character varying. Things are working to this point except invoking a custom findBy "Enum" method in the JPA Repository interface. Now in Spring and Postgres I have defined the following:

Enum:

public enum EnumExampleType {
    TYPE1, TYPE2
}

Entity:

@Entity
@Table(name = "enumexampletable")
@Data
@NoArgsConstructor
public class EnumExampleTable {

    @Id @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name = "enum_example_table_id",  columnDefinition="serial primary key")
    private int enumExampleTableId;
    
    @Column(unique = true, name="enum_example_type")
    @Enumerated(EnumType.STRING)
    public EnumExampleType enumExampleType;
}

Repo:

public interface EnumExampleTableRepo extends JpaRepository<EnumExampleTable, Integer> {

    EnumExampleTable findByEnumExampleType(EnumExampleType enumExampleType);
}

Working Code as Expected

        EnumExampleTable ex1 = new EnumExampleTable();
        EnumExampleTable ex2 = new EnumExampleTable();

        ex1.setEnumExampleType(EnumExampleType.TYPE1);
        ex2.setEnumExampleType(EnumExampleType.TYPE2);
        
        enumExampleTableRepo.save(ex1);
        enumExampleTableRepo.save(ex2);

RestController: (to invoke) (not working)

    @Autowired
    EnumExampleTableRepo enumExampleTableRepo;
    
    @GetMapping("/findByTest")
    public EnumExampleTable enumTest() {
        
        return enumExampleTableRepo.findByEnumExampleType(EnumExampleType.TYPE1);
    }

When calling this code the following error is received:

Blockquote org.postgresql.util.PSQLException: ERROR: operator does not exist: enumexampletype = character varying Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Postgres Database:

drop table if exists enumexampletable;

drop type if exists enumexampletype cascade;
drop cast if exists (character varying as enumexampletype);

create type enumexampletype as enum('TYPE1', 'TYPE2');
CREATE CAST (character varying as enumexampletype) with inout as implicit;

create table enumexampletable (
    enum_example_table_id serial primary key,
    enum_example_type enumexampletype

);

This suggests to me that either:

A: The findByEnumExampleType method does not convert the enum to a string

B: Postgres does not invoke this cast in this particular call

Also to Note: (A hard coded native query will function properly, but this is not the dynamic functionality I need)

    @Query(value="select * from enumexampletable e where e.emum_example_type = 'TYPE1'", nativeQuery=true)
    EnumExampleTable testNQ();

Thoughts or suggestions?

Since you have annotated your enum in your entity with `@Enumerated(EnumType.STRING)`, why you bother to create and cast in postgres this field? Just leave it as varchar. What about if you drop your current schema and let spring jpa create it from the entities? You can set this in `application.properties` add a line `spring.jpa.hibernate.ddl-auto=create-drop` — pleft, Sep 05 '21 at 10:54
P.S. `@Column(unique = true, name="enum_example_type")` I think is wrong, only one record of each enum value can be inserted, are you sure you want it this way? E.g. if you persist a record in `enumexampletable` with `enum_example_type` of value `TYPE1` then you won't be able to save another one with the same value `TYPE1` — pleft, Sep 05 '21 at 10:57
Well changing the field to varchar shows the problem does not lie on the spring side as I can now query with the findByEnumExampleType method, so the question would become, why is the cast being ignored on this particular type of call? As far as leaving the database as varchar, I cant imagine that would be good practice, but I would consider it. Regardless I would like the to know the solution with a cast. — JustinK, Sep 05 '21 at 16:17
And yes, while your statement of unique columns is valid, it is intended that each row has a unique value for the enum. — JustinK, Sep 05 '21 at 16:22
nwm, solution can be found here: https://stackoverflow.com/questions/27804069/hibernate-mapping-between-postgresql-enum-and-java-enum — M364M4N cro, Oct 14 '22 at 14:30

JPA Repository findByEnum does not cast the Argument Enum to String or Postgres Cast not working as expected

0 Answers0