CODEWITHSUNDEEP
javajpa
kctang
kctang

Reputation: 11202

Perform JPA query with LIKE operator on entity with enum (EnumType.STRING)

I am able to store, retrieve and query entities with enum types (using JPA/Hibernate). The enum fields are annotated with @Enumerated(EnumType.STRING).

Is it possible to do things like "SELECT a FROM MyEntity a WHERE a.myEnum LIKE :param" ?

The idea is to have it match any entity where the enum value string (in DB) matches a pattern like "SYSTEM_%" or "BUSINESS_%".

Thanks for helping out.

--- Update:

When I tried to query with this:

`@Query("SELECT e FROM MyEntity e WHERE e.myEnum LIKE :value")`

Using the values (to indicate match whatever...):

%%

I get this exception:

  Caused by: java.lang.IllegalArgumentException: Parameter value [%%] did not match expected type [mypackage.MyEntity$MyEnum]
        at org.hibernate.ejb.AbstractQueryImpl.validateParameterBinding(AbstractQueryImpl.java:370)
        at org.hibernate.ejb.AbstractQueryImpl.registerParameterBinding(AbstractQueryImpl.java:343)
        at org.hibernate.ejb.QueryImpl.setParameter(QueryImpl.java:374)
        at org.hibernate.ejb.QueryImpl.setParameter(QueryImpl.java:71)

Upvotes: 5

Views: 8801

Answers (5)

Rogol
Rogol

Reputation: 742

None of the above answers worked for me, although I managed to find solution that worked for me and it's outrageously simple - you just cast your Enum to String:

   @Query("SELECT a FROM MyEntity a WHERE CAST(a.myEnum AS string) LIKE %?1%")
    List<MyEntity> findByParam(String param);

where "?1" is the param

Upvotes: 3

Michael
Michael

Reputation: 358

I ran into the same problem and I did the following:

  • define a second attribute mapping to the same db column
  • type of that attribute is String
  • it is neither updateable nor insertable
  • adapt the set methods of the enum attribute
  • make the String attribute a readonly property
    @Column(name = "status")
    @Enumerated(EnumType.STRING)
    private StatusEnum status;

    public void setStatus(StatusEnum status) {
        this.status = status;
        this.statusString = status.toString();
    }
    
    @Column(name ="status", updatable = false, insertable = false)
    private String statusString;

Now you can make queries using 'like' against the statusString:

TypedQuery<MyEntity> query = 
    em.createQuery("select e from MyEntity e where e.statusString like 'CLOSED%', MyEntity.class);

Upvotes: 3

zbig
zbig

Reputation: 3956

As a workaround you can build a query by concatenating pattern to the query.

em.createQuery("SELECT e FROM MyEntity e WHERE e.myEnum LIKE '" + pattern +"'")

It works with Hibernate as well as Eclipselink.

However don't try this with pattern made from user input because constructing queries this way is vulnerable to sql injection attack.

Upvotes: 0

tibi
tibi

Reputation: 677

saidly there is a shortcut in the org.springframework.orm.hibernate3.HibernateTemplate: 

protected void applyNamedParameterToQuery(Query queryObject, String paramName, Object value)
        throws HibernateException {

    if (value instanceof Collection) {
        queryObject.setParameterList(paramName, (Collection) value);
    }
    else if (value instanceof Object[]) {
        queryObject.setParameterList(paramName, (Object[]) value);
    }
    else {
        queryObject.setParameter(paramName, value);
    }
}

would be greate if there is an extra else if (value instanceoff String)...

Upvotes: 0

Jeshurun
Jeshurun

Reputation: 23186

Absolutely. Just use query.setString("param", "BUSINESS_%");. Note that using setParameter() instead of setString() throws a ClassCastException as Hibernate tries to cast it to an enum, however setString() does exactly what you want.

Upvotes: 3

Related Questions