CODEWITHSUNDEEP
javahibernatejpahqlcriteria
user1120144
user1120144

Reputation:

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

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");

Upvotes: 2

Views: 2228

Answers (2)

Lukas Novicky
Lukas Novicky

Reputation: 952

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

works for me:)

Upvotes: 1

Alan Hay
Alan Hay

Reputation: 23226

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.

Upvotes: 0

Related Questions