Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to query using an Enum parameter mapped as ORDINAL using JPA and Hibernate

I need to get data from database by enum type. I have following enum:

public enum ShopType {
    VANS("VANS"), ATTICUS("ATTICUS"), FAMOUS("FAMOUS")

    ShopType(String label) {
        this.label = label;
    }

    private String label;

    public String getLabel() {
        return label;
    }

    public void setLabel(String label) {
        this.label = label;
    }
}

In my DAO class i have method which returns list of objects by selected type on jsp page. On jsp page i send selected value like String, is it right?

That how looks my method

@Transactional
public List<Shop> findByType(String type) {
    return sessionFactory.getCurrentSession().createQuery("from Shop where type=" + .....  .list();
}

I don't know how to create right query. Enum i store in my database like tinyint.

Here is a model.

@Column(name = "type")
@Enumerated(EnumType.ORDINAL)
private ShopType type;
like image 579
user3127896 Avatar asked Sep 29 '14 18:09

user3127896


2 Answers

The problem in your query is that you concatenated the bind parameter value which, apart from causing your issue, may expose your application to SQL injection attacks.

If you write the query using bind parameter values:

Post post = entityManager.createQuery(
    "select p " +
    "from Post p " +
    "where p.status = :status", Post.class)
.setParameter("status", PostStatus.PENDING)
.getSingleResult();

assertEquals("High-Performance Java Persistence", post.getTitle());

Hibernate will properly use the ORDINAL value in the SQL query:

Query:["
    select 
        p.id as id1_0_,
        p.status as status2_0_, 
        p.title as title3_0_ 
    from 
        post p 
    where 
        p.status=?
"], 
Params:[
    0
]

For a working example, check out the EnumOrdinalTest in my high-performance-java-persistence GitHub repository.

like image 33
Vlad Mihalcea Avatar answered Oct 06 '22 00:10

Vlad Mihalcea


As you set your enum as ordinal, then in query you should use ordinal. Example;

@Transactional
public List<Shop> findByType(String type) {
    return sessionFactory.getCurrentSession().createQuery("from Shop where type=" + ShopType.valueOf(type).ordinal()).list();
}

If you change @Enumerated(EnumType.STRING), then your query will look like;

@Transactional
public List<Shop> findByType(String type) {
    return sessionFactory.getCurrentSession().createQuery("from Shop where type=" + ShopType.valueOf(type).name()).list();
}

ShopType.valueOf(type), This will work only if string type is same as enum name. Also if your label is same as enum name, then you don't need label. ShopType.VANS.name()is equals"VANS" and name() method is final, you can be sure that can't be overridden.

like image 107
Petar Butkovic Avatar answered Oct 06 '22 00:10

Petar Butkovic