Hibernate Criteria Query select where clause

Question

person:

id | owner_email               | firstname | lastname 
----+-------------+-----------+----------
 44 | john@gmail.com           | john       | wood
 45 | alex@gamil.com           | alex       | greenwood
 49 | peter@gamil.com          | peter      | blacksmith
 50 | john@gmail.com           | lee        | wu
 51 | john@gmail.com           | jane       | li

i am making a spring hibernate web application, i have a table like above. what i m trying to get is something like:

select * from person where owner_email='john@gmail.com'

so the method will return me list of person objects related to john@gmail.com

here is my query code but doesnt work...

    @PersistenceContext
    EntityManager em;


    @Transactional
    public List<Person> listPerson() {
        CriteriaQuery<Person> c = em.getCriteriaBuilder().createQuery(Person.class);
        Root<Person> from = c.from(Person.class);
        c.multiselect(from.get("owner_email"));
        c.orderBy(em.getCriteriaBuilder().asc(from.get("firstname")));
        return em.createQuery(c).getResultList();
    }

here is the error i get....

HTTP Status 500 - Request processing failed; nested exception is java.lang.IllegalArgumentException: org.hibernate.hql.ast.QuerySyntaxException: Unable to locate appropriate constructor on class [com.example.model.Person] [select new com.example.model.Person(generatedAlias0.owner_email) from com.example.model.Person as generatedAlias0]

please help me with code example thanks...

Your question is very unclear.If you want a list of persons, why does your method return a List and not a List? If you want to select from theperson table, why do you use c.from(PrayTimes.class) and not c.from(Person.clas)? If you want to add a predicate on the owner_email, why don't you add one? The code doesn't match at all with what your description says. Finally, why in hell do you use criteria instead of a damn simple JPQL query: `select p from Person p where p.ownerEmail = :email`. — JB Nizet, Aug 10 '14 at 21:57
And now you changed the code, but the error doesn't match with the code. — JB Nizet, Aug 10 '14 at 22:00
Also none of what you have here is Hibernate-specific; you're using all JPA stuff. — Jason C, Aug 10 '14 at 22:42

Maarten Winkels · Accepted Answer · 2014-08-12T01:28:57.857

3

@PersistenceContext 
EntityManager em;

@Transactional 
public List<Person> listPerson() { 
    CriteriaQuery<Person> c = em.getCriteriaBuilder().createQuery(Person.class); 
    Root<Person> from = c.from(Person.class); 

    c.select(from);
    c.where(em.getCriteriaBuilder().equal(from.get("owner_email"),"john@gmail.com")); // <- this will add the restriction. 

    c.orderBy(em.getCriteriaBuilder().asc(from.get("firstname"))); 
    return em.createQuery(c).getResultList(); 
}

edited Aug 12 '14 at 01:28

answered Aug 10 '14 at 22:34

Maarten Winkels

2,407
16
15

I've edited your answer to format your code. This answer could be better if you updated it to draw attention to the important parts of the code you've changed. – Jason C Aug 10 '14 at 22:44
1

@JasonC Thanx! I'm struggling with the wiki syntax in the android app. – Maarten Winkels Aug 10 '14 at 22:46
Suggestion: I would set up a `builder` variable instead of calling `em.getCriteriaBuilder()` multiple times. – user1438038 Apr 26 '21 at 07:18

Hibernate Criteria Query select where clause

1 Answers1