JPA, Entity manager, select many columns and get result list custom objects

Question

How I can get list of custom objects, like results below query:

SELECT p.category.id, count(p.id) FROM Product p left join p.category c WHERE p.seller.id=:id GROUP BY c.id

By example:

return getEntityManager().createQuery("SELECT p.category.id, count(p.id) FROM Product p left join p.category c WHERE p.seller.id=:id GROUP BY c.id").setParameter("id", id).getResultList();

I need a map with category id and number of products in category.

i recommend check out [this answer](http://stackoverflow.com/a/12702437/1211174). this answer has two examples 1. using tuples 2. using a class and generics — oak, Dec 08 '13 at 08:50

score 37 · Accepted Answer · answered Jun 19 '13 at 22:28

Unfortunately, JPA doesn't provide a standard way to retrieve the results in a Map. However, building up your map manually by walking through the result list is simple enough:

TypedQuery<Object[]> q = getEntityManager().createQuery(
    "SELECT c.id, count(p.id) " +
    "FROM Product p LEFT JOIN p.category c " +
    "WHERE p.seller.id = :id " +
    "GROUP BY c.id", Object[].class).setParameter("id", id);

List<Object[]> resultList = q.getResultList();
Map<String, Long> resultMap = new HashMap<String, Long>(resultList.size());
for (Object[] result : resultList)
  resultMap.put((String)result[0], (Long)result[1]);

score 3 · Answer 2 · answered Jun 20 '13 at 09:18

3

Assuming you are using hibernate(tagged), can try the below HQL query, I haven't tested.

SELECT new map(p.category.id as category_id, count(p.id) as id_count) FROM Product p left join p.category c WHERE p.seller.id=:id GROUP BY c.id

answered Jun 20 '13 at 09:18

Nayan Wadekar

11,444
4
50
73

1

I just used tested this myself on a custom object created from a custom join query and it worked. I have a list of objects with keys rather than numeric indexes. The order is not preserved, but that doesn't matter since we have named indexes to grab with... – Hatem Jaber Sep 28 '15 at 15:27

lalokana · Answer 3 · 2015-03-09T19:56:18.190

Using JPA 2.0 and EclipseLink impl

For the first question: list of custom objects(no table objects):

answer: create a custom model and use the @Entity and @Id

@Entity
public class QueryModelDTO implements Serializable{ 
    @Id
    private Integer categoryId;
    private int count;
    ---gets and sets
}

create the query and execute

QueryModelDTO qm = (QueryModelDTO) em.createQuery(
                "SELECT p.category.id as categoryId, count(p.id) as count FROM Product p
                left join p.category c WHERE p.seller.id=:id 
                GROUP BY c.id",QueryModelDTO.class)
                .setParameter("id", id).getSingleResult();

For the second: how to read the response on a map

answer: Use the QueryHints and ResultTypes (this is one variant for the @DannyMo answer)

Query q  = em.createNativeQuery("SELECT * FROM Foo f");
q.setHint(QueryHints.RESULT_TYPE, ResultType.Map);
List<Map> lm = q.getResultList();
    for (Map map : lm) {
        for (Object entry : map.entrySet()) {
            Map.Entry<DatabaseField, Object> e = (Map.Entry<DatabaseField, Object>) entry;
            DatabaseField key = e.getKey();
            Object value = e.getValue();
            log.debug(key+"="+value);
        }            
    }

I hope this helps

JPA, Entity manager, select many columns and get result list custom objects

3 Answers3

Linked