Count the number of rows in many to many relations

问题:

I have three of many tables in Oracle (10g) database as listed below. I'm using Hibernate Tools 3.2.1.GA with Spring version 3.0.2.

1. Product - parent table
2. Colour - parent table
3. ProductColour - join table - references colourId and prodId of Colour and Product tables respectively

Where the ProductColour is a join table between Product and Colour. As the table names imply, there is a many-to-many relationship between Product and ProductColour. I think, the relationship in the database can easily be imagined and is clear with only this much information. Therefore, I'm not going to explore this relationship at length.

One entity (row) in Product is associated with any number entities in Colour and one entity (row) in Colour can also be associated with any number of entities in Product.

---

Let's say as for an example, I need to count the number of rows available in the Product table (regarding Hibernate), it can be done something like the following.

Object rowCount = session.createCriteria(Product.class)
                  .setProjection(Projections.rowCount()).uniqueResult();

---

What if I need to count the number of rows available in the ProductColour table? Since, it is a many-to-many relationship, it is mapped in the Product and the Colour entity classes (POJOs) with there respective java.util.Set and no direct POJO class for the ProductColour table is available. So the preceding row-counting statement doesn't seem to work in this scenario.

Is there a precise way to count the number of rows of such a join entity in Hibernate?

回答1:

I think you should be able to do a JPQL or HQL along the lines.

SELECT count(p.colors) FROM Product AS p WHERE p.name = :name ... other search criteria etc 

or

SELECT count(c.products) FROM Color AS c WHERE c.name = :name .... other search criteria 

From Comment below, this should work:

Long colours=(Long) session.createQuery("select count(*) as cnt from Colour colour where colour.colourId in(select colours.colourId from Product product inner join product.colours colours where product.prodId=:prodId)").setParameter("prodId", prodId).uniqueResult();