Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 248
Piotr Kozlowski Avatar asked Jun 19 '13 22:06

Piotr Kozlowski


2 Answers

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]);
like image 88
DannyMo Avatar answered Nov 13 '22 21:11

DannyMo


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

like image 3
Nayan Wadekar Avatar answered Nov 13 '22 20:11

Nayan Wadekar