计算Hibernate中多对多关系中的行数 [英] Count the number of rows in many to many relationships in Hibernate

问题描述

我在Oracle（10g）数据库中有三个表，如下所示。我使用Spring 3.0.2版的Hibernate Tools 3.2.1.GA。

1. 产品


颜色和产品的<$ c> colourId 和 prodId / code>表

ProductColour 产品和颜色之间的连接表。正如表名称所暗示的， Product 和 ProductColour 之间存在多对多关系。我认为，数据库中的关系很容易想象，只有这么多的信息是清楚的。因此，我不打算详细探讨这种关系。

产品中的一个实体与 Color 中的任何数量实体相关联，并且 Color 中的一个实体（行）也可以与任何数量的实体产品。

---

需要计算 Product 表中的可用行数（关于Hibernate），可以做如下类似的操作。

 对象rowCount = session.createCriteria（Product.class）
 .setProjection（Projections.rowCount（））。uniqueResult 
  

---

如果我需要计算行数可在 ProductColour 表中找到？因为它是一个多对多关系，它映射在 Product 和 Color 实体类POJO），其中有 java.util.Set ，没有直接的POJO类为 ProductColour 表可用。所以前面的行计数语句似乎在这种情况下不工作。

是否有精确的方法来计算Hibernate中 join实体的行数？

 
我认为你应该能够做一个JPQL或者HQL。  SELECT count（p.colors）FROM Product AS p WHERE p.name =：name ... other search criteria etc 
  

或

  SELECT count（c.products）FROM Color AS c WHERE c.name =：name ....其他搜索条件
  

 长颜色=（长）session.createQuery（select count（*）as cnt from Color color colour.colourId in（select colours.colourId from Product product inner join product.colours colors where product.prodId =：prodId））。setParameter（prodId，prodId）.uniqueResult（）; 
  

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?

解决方案

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

这篇关于计算Hibernate中多对多关系中的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！