How to retrieve only certain fields of an entity in JPQL or HQL? What is the equivalent of ResultSet in JPQL or HQL?

Question

In JPQL, I can retrieve entities by :

query = entityManager.createQuery("select c from Category c");
List<Category> categories = query.getResultList();

But, if I wish to retrieve the id and name fields (only) of the Category entity, I need something like the ResultSet object, through which I can say : rs.getString("name") and rs.getString("id"). How to do this through JPQL, without retrieving the entire entity ?

Basically, for a how to retrieve information from a query like : select c.id,c.name from Category c ?

dimas · Accepted Answer · 2012-08-04T10:42:31.683

49

In HQL you can use list() function to get a list of Object[] array that contains result rows:

Query query = session.createQuery("select c.id,c.name from Category c");
List<Object[]> rows = query.list();

in returned array 1-st element will be id, second - name.

for (Object[] row: rows) {
    System.out.println(" ------------------- ");
    System.out.println("id: " + row[0]);
    System.out.println("name: " + row[1]);
}

If you want to use hibernate's Criteria API, you should use Projections.

With JPA it will work the same way:

List<Object[]> rows = entityManager.createQuery(queryString).getResultList();

edited Aug 04 '12 at 10:42

answered Aug 04 '12 at 10:23

dimas

6,033
36
29

I see queries like : "select c.id,c.name from Category c" often in JPA books, but they never tell u how to retrieve the results. Any way to do it without being Hibernate specific ? – Daud Aug 04 '12 at 10:32
1

With JPA it will work the same way. I added example to my answer. But note that if you will, for example, select only name column - then you will get the List, not List – dimas Aug 04 '12 at 10:45
What is the `session` variable in this context? `org.hibernate.Session` does not have a `createQuery` method, at least in 4.3. – P.Péter Jul 26 '16 at 15:00

score 12 · Answer 2 · answered Aug 04 '12 at 18:20

It is not the use of the .list() function itself which makes the result a List<Object[]>. It is the specification of fields (c.id, c.name) in the HQL query. If your query is

    "select c from Category c"

Then query.list() will return a List<Category> object.

score 2 · Answer 3 · answered Sep 23 '18 at 15:32

You can also directly map to the class

public class UserData {

    private String name;
    private Date dob;
    private String password;
//setter
}



  public UserData getUserData() {
        String queryString = "select user.name as name, user.dob as dob, user.userses.password as password from UserProfile user where user.userEmailId='faiz.krm@gmail.com'";
        Query query = sessionFactory.getCurrentSession().createQuery(queryString);
        query.setResultTransformer(Transformers.aliasToBean(UserData.class));
        return query.uniqueResult();
    }

dos4dev · Answer 4 · 2020-11-02T12:18:16.170

0

The JPA specification allows us to customize results in an object-oriented fashion.

More information you can find at this link

There is an even more elegant way

edited Nov 02 '20 at 12:18

answered Oct 31 '20 at 11:37

dos4dev

429
2
10
26

How to retrieve only certain fields of an entity in JPQL or HQL? What is the equivalent of ResultSet in JPQL or HQL?

4 Answers4

Linked