Calculating and storing precomputed average with Hibernate

Say I have a set of products and each product has a set of reviews. Each review has a score. I will need the average of the scores much more often then I will need the actual reviews. Are there any best practices/clean ways of precomputing the average and sto开发者_如何学运维ring it with hibernate.

I am currently thinking of adding review count and review sum columns to the product table and rigging the add review method to update the count and sum.

I would be very glad to hear of a cleaner way of doing this.

---

I would advise against keeping a sum column on your parent table. If two competing requests attempt to update the sum column, one will fail or you risk clobbering the sum with a stale read (unless the system doesn't have high traffic...).

The queries you require are quite simple and any decent db should give good performance for awhile (lazy psuedo SQL/HQL):

1) A particular avg review score for a product:

select sum(score)/count from reviews where product_id = ?

2) List of products and their avg review scores

select product_name, sum(r.score)/count(r.score)
from products p
join reviews r
group by product_name

For the first query, make sure you have some sort of index on product_id on the reviews table.

Using hibernate, you can use projection queries either through HQL or the Criteria objects. If that's not fast enough, then I would look into second level caching and query caching.

Turn on show_sql and TestTestTest to make sure hibernate is only hitting the database once, and that your cache is invalidated when new items are added/updated.

---

I think there is a better way, a background process to run every now and then and do this update for count and total of rating in product table based on review table.

Another idea is to use a trigger whenever there is a update in review/rating table it will take it into account (using time stamp) and update the product table.

---

Hibernate query caching might be the way to go. Otherwise, go with your original idea.