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, count( FROM Product p left join p.category c WHERE GROUP BY

By example:

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

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


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, count( " +
    "FROM Product p LEFT JOIN p.category c " +
    "WHERE = :id " +
    "GROUP BY", 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]);

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

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

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

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

create the query and execute

QueryModelDTO qm = (QueryModelDTO) em.createQuery(
                "SELECT as categoryId, count( as count FROM Product p
                left join p.category c WHERE 
                GROUP BY",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();

I hope this helps

Need Your Help

conversion of a varchar data type to a datetime data type resulted in an out-of-range value

sql sql-server service sqlexception

I have the following piece of inline SQL that I run from a C# windows service:

Basic JUnit test for JavaFX 8

junit javafx-2 javafx junit4 javafx-8

I want to create basic JUnit test for JavaFX 8 application. I have this simple code sample: