2

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?

Tiny
  • 27,221
  • 105
  • 339
  • 599

1 Answers1

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();
ams
  • 60,316
  • 68
  • 200
  • 288
  • I have tried with this HQL `Object o=session.createQuery("select count(p.colours) from Product p where prodId=:prodId").setParameter("prodId", prodId).uniqueResult();` but it unfortunately threw an exception - `org.hibernate.exception.SQLGrammarException: could not execute query` where `p.colours` is a valid property in the `Product` entity class. Thanks for the reply. – Tiny Nov 14 '12 at 06:27
  • Hi, It worked with this HQL statement - `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();`. According to [this](http://stackoverflow.com/a/13388254/1391249) answer. You may now update your answer to reflect as much. Thank you. – Tiny Nov 15 '12 at 18:18