Get collection count instead of the collection in JPA Criteria API?

Question

Lets say I have an entity:

@Entity
public class Person {

    @Id
    @GeneratedValue
    private Long id;

    @ManyToMany(fetch = FetchType.LAZY)
    private List<Role> roles;

    @ManyToMany(fetch = FetchType.LAZY)
    private List<Permission> permissions;
    // etc
    // other fields here
}

I want a to build a query using the Criteria API that filters these users and shows a list of people and among other info from the entity - how many roles does a person have.

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Person> query = builder.createQuery(Person.class);
Root<Person> personRoot = query.from(Person.class);
// predicates here

However, this limits me to returning only a list of Person entities. I can always add a @Transient field to the entity, but this seems ugly since I might have many different queries and might end up with many such fields.

On the other hand - I cant use HQL and write the query since I want complex filtering and I would have to deal with appending and removing things from the HQL query.

My question, besides the one in the title of this post is this: how do I query the database using the Criteria API and return a non-entity (in case I want to filter the Person table but return only the number of roles, permissions, etc) and how do I do it for something very close to the actual entity (like the example with the role counter instead of the roles collection)?

UPDATE Using Hibernate's projections I came up with this. But still don't know that to write in TODO. Projections.count doesn't work since it excpects some kind of grouping, and I don't seem to be able to find any examples in the Hibernate documentation.

Criteria cr = session.createCriteria(Person.class);

if (id != null) {
    cr.add(Restrictions.eq("id", id));
}

ProjectionList projectionList = Projections.projectionList();
projectionList.add(Projections.property("id"), "id");
projectionList.add(TODO, "rolesCount");

Are you looking for something like [this answer](http://stackoverflow.com/a/14676881/785663)? — mabi, Nov 18 '13 at 21:37
Or something like the following: http://stackoverflow.com/questions/19707529/jpa-select-count-distinct/19708805#19708805 ? — V G, Nov 18 '13 at 21:43
These two answers return a count/sum, which is only a part of what I need. I can always have multiple querries - for example one filtering the entities and one counting the roles and then mapping the rwo results, but I'm looking for a practical way to do these in one Criteria query. — , Nov 19 '13 at 06:52
http://stackoverflow.com/questions/2883887/in-jpa-2-using-a-criteriaquery-how-to-count-results — MariuszS, Nov 22 '13 at 08:39

score 1 · Answer 1 · answered Mar 07 '14 at 13:44

1

CriteriaQuery<Long> query = entityManager.getCriteriaBuilder().get().createQuery(Long.class);
query.select(builder.get().countDistinct(root));

works for me:)

answered Mar 07 '14 at 13:44

Lukas Novicky

921
1
19
44

score 0 · Answer 2 · answered Nov 18 '13 at 22:38

how do I do it for something very close to the actual entity (like the example with the role counter instead of the roles collection

You could make these values properties of your User entity by various means, for example using a Hibernate @Forumula property. This will issue an inline subquery on Entity load to get the count without touching the collection.

@Formula("select count(*) from roles where user_id = ?")
private int numberOfRoles;

Another (JPA compliant) option is to handle these calculated fields by creating a view at the database level and the mapping this to your User:

e.g.

@OneToOne
private UserData userData; //entity mapped to your view (works just like a table)

....

public int getNumberOfRoles(){
   return userData.getRoleCOunt();

or

by using @SecondaryTable to join this User data.

These are all workarounds. I'm looking for a general JPA-compliant way to achieve these queries with the help of the Criteria API. Also, what about the non-entity returning query? — , Nov 19 '13 at 06:48
Well if that's all you're looking for I imagine Projections and Constructor expressions are what you're after. — Alan Hay, Nov 19 '13 at 07:34

Get collection count instead of the collection in JPA Criteria API?

2 Answers2