How to make a SELECT query in Hibernate includes Subquery COUNT(*)

Question

Let's say we have a Category - Items one-to-many relation. I would like to do this

SELECT c.*, 
   (SELECT COUNT(*) FROM items i WHERE i.catId=c.id)
    AS itemCount
FROM category c

And let's say we have a Hibernate POJO "class Category".

My first question is I really couldn't figure out that from that query I get a List<Category> object right? And how can I access the "itemCount"? Because there's no Category.getItemCount()

And secondly, how can I write the Criteria query?

Thanks

Answer 1

Seems like this is the answer I was looking for (into to POJO):

@Formula(value="(SELECT COUNT(*) FROM Items i WHERE i.id = id)")
@Basic(fetch=FetchType.EAGER)
public Integer getItemCount() {
   return this.taskCount;
}

Answer 2

Depending on your circumstances and ability to create a view. I would just create a view out of your query:

CREATE VIEW CategoryItemsView AS 
    SELECT c.*,  
   (SELECT COUNT(*) FROM items i WHERE i.catId=c.id) 
    AS itemCount 
FROM category c 

afterwards you can query how ever you like...

SELECT * FROM CategoryItemsView WHERE ItemCount = 5

Additionally, you could use a GROUP BY to achieve a similar result but that depends on your columns and the schema of your tables.

So, something like this:

SELECT c.COLUMN1, c.COLUMN2, COUNT(*) AS ItemCount
FROM category c inner join items i on i.catID = c.Id
GROUP BY c.COLUMN1, c.COLUMN2
HAVING COUNT(*) = 2