CODEWITHSUNDEEP
javahibernatejpaenumshibernate-mapping
user3127896
user3127896

Reputation: 6563

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;

Upvotes: 6

Views: 19407

Answers (3)

Vlad Mihalcea
Vlad Mihalcea

Reputation: 153780

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.

Upvotes: 6

Petar Butkovic
Petar Butkovic

Reputation: 1900

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.

Upvotes: 8

vp8106
vp8106

Reputation: 201

Just convert String to Enum and use named query parameter 

@Transactional
public List<Shop> findByType(String type) {
    ShopType enumType = shopTypeFromString(type);
    return sessionFactory.getCurrentSession().createQuery("from Shop where type=:p_type")
        .setParameter("p_type", enumType).list();
}

private ShopType shopTypeFromString(String type) {
    // You can implement this convertion in your own way
    return ShopType.valueOf(type);
}

Upvotes: 4

Related Questions