Next to the common SQL databases, hibernate allows us to perform an aggregate operation using a hibernate API. We can perform an aggregation operation such as sum
, avg
, min
, max
, count
, and so on.
We will discuss the use of some aggregate functions by example.
To perform an aggregation operation, we will consider a predefined table structure with the data so that it's easy to understand how the aggregate functions work.
The predefined table and class structure we mentioned earlier can be found in two different classes, product
and category
, with their relationship. The following code and script can be used to create a Java class and a database table.
Use the following script to create the tables if you are not using hbm2dll=create|update
:
Use the following code to create the category
table:
CREATE TABLE `category` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `created_on` datetime DEFAULT NULL, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) );
Use the following code to create the product
table:
CREATE TABLE `product` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `price` double DEFAULT NULL, `category_id` bigint(20) DEFAULT NULL, PRIMARY KEY (`id`), KEY `FK_CATEGORY_ID` (`category_id`), CONSTRAINT `FK_CATGORY_ID` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) );
Use the following code to create the classes:
Source file: Category.java
@Entity @Table(name = "category") public class Category { @Id @GeneratedValue @Column(name = "id") private long id; @Column(name = "name") private String name; @Column(name = "created_on") private Date createdOn; public long getId() { return id; } public void setId(long id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Date getCreatedOn() { return createdOn; } public void setCreatedOn(Date createdOn) { this.createdOn = createdOn; } }
@Entity @Table(name = "product") public class Product { @Id @GeneratedValue @Column(name = "id") private long id; @Column(name = "name") private String name; @Column(name = "price") private double price; @ManyToOne @JoinColumn(name = "category_id") private Category category; public long getId() { return id; } public void setId(long id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public double getPrice() { return price; } public void setPrice(double price) { this.price = price; } public Category getCategory() { return category; } public void setCategory(Category category) { this.category = category; } }
We can determine that every product is associated with at least one category. Consider the following table and it's data.
This is the data for the
category
table:
id |
created_on |
name |
---|---|---|
|
|
|
|
|
|
This is the data for the
product
table:
id |
name |
price |
category_id |
---|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Now for this recipe, we are considering the preceding table structure and data.
Now, we will do the exercise for the different aggregation functions mentioned in the following list using a hibernate API:
Sum
Avg
Min
Max
Count
The aggregate function sum
is used to obtain the sum of the values of a particular column.
Let's take a look at a scenario where we want the sum of prices by category, and our expected output is as follows:
Category name: Furniture, Sum of price: 290.59
Category name: Stationary, Sum of price: 91.53
In this case, execute the following code:
Criteria criteria = session.createCriteria(Product.class); ProjectionList projectionList = Projections.projectionList(); /* Line 4 */projectionList.add(Projections.groupProperty("category")); /* Line 6 */ projectionList.add(Projections.alias(Projections.sum("price"), "price")); criteria.createAlias("category", "category"); projectionList.add(Projections.alias(Projections.property("category.name"), "cat_name")); criteria.setProjection(projectionList); criteria.setResultTransformer(criteria.ALIAS_TO_ENTITY_MAP); List list = criteria.list(); for (Iterator iterator = list.iterator(); iterator.hasNext();) { Map map = (Map) iterator.next(); System.out.println("Category name: " + map.get("cat_name")); System.out.println("SUM(price): " + map.get("price")); }
Hibernate: select this_.category_id as y0_, sum(this_.price) as y1_, category1_.name as y2_ from product this_ inner join category category1_ on this_.category_id=category1_.id group by this_.category_id Hibernate: select category0_.id as id1_0_, category0_.created_on as created2_1_0_, category0_.name as name1_0_ from category category0_ where category0_.id=? Hibernate: select category0_.id as id1_0_, category0_.created_on as created2_1_0_, category0_.name as name1_0_ from category category0_ where category0_.id=? Category name: Furniture Sum(price): 290.59000000000003 Category name: Stationary Sum(price): 91.53
The Projections.sum("price"
) code from Line 6
shows that we wanted the sum of the prices, and the Projections.groupProperty("category")
code from Line 4
shows that we used "category"
as a group property while obtaining the sum of the prices.
The aggregate function avg
is used to find the average of values.
Let's consider a scenario where we want the average of the prices by category, and our expected output is as follows:
Category name: Furniture Average of price: 96.86
Category name: Stationary Average of price: 30.51
Here, we change a small part of the code in Line 6
from the sum example; we just change the Projection.sum(…)
method to Projection.avg(…)
, as shown in the following code:
Hibernate: select this_.category_id as y0_, avg(this_.price) as y1_, category1_.name as y2_ from product this_ inner join category category1_ on this_.category_id=category1_.id group by this_.category_id Hibernate: select category0_.id as id1_0_, category0_.created_on as created2_1_0_, category0_.name as name1_0_ from category category0_ where category0_.id=? Hibernate: select category0_.id as id1_0_, category0_.created_on as created2_1_0_, category0_.name as name1_0_ from category category0_ where category0_.id=? Category name: Furniture AVG(price): 96.86333333333334 Category name: Stationary AVG(price): 30.51
The aggregate function
min
is used to find the product having the minimum value in a particular category. Execute the following code:
Criteria criteria = session.createCriteria(Product.class); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.groupProperty("category")); /* Line 6 */ projectionList.add(Projections.alias(Projections.min("price"), "price")); criteria.createAlias("category", "category"); projectionList.add(Projections.alias(Projections.property("category.name"), "cat_name")); projectionList.add(Projections.alias(Projections.property("name"), "prod_name")); criteria.setProjection(projectionList); criteria.setResultTransformer(criteria.ALIAS_TO_ENTITY_MAP); List list = criteria.list(); for (Iterator iterator = list.iterator(); iterator.hasNext();) { Map map = (Map) iterator.next(); System.out.println(" Category name: " + map.get("cat_name")); System.out.println("Product name: " + map.get("prod_name")); System.out.println("MIN(price): " + map.get("price")); }
Hibernate: select this_.category_id as y0_, min(this_.price) as y1_, category1_.name as y2_, this_.name as y3_ from product this_ inner join category category1_ on this_.category_id=category1_.id group by this_.category_id Hibernate: select category0_.id as id1_0_, category0_.created_on as created2_1_0_, category0_.name as name1_0_ from category category0_ where category0_.id=? Hibernate: select category0_.id as id1_0_, category0_.created_on as created2_1_0_, category0_.name as name1_0_ from category category0_ where category0_.id=? Category name: Furniture Product name: Lighting MIN(price): 70.36 Category name: Stationary Product name: Paper clips MIN(price): 20.61
From the output, it's clear that we have a product named Lighting
in the Furniture
category that has the minimum price in that category, and product with name Paper clips
in the Stationary
category with minimum price in its category.
The aggregate function max
is used to find the maximum value in a particular category.
Here, we will change a small part of the code in Line 6
from the Min example; we will just change the Projection.min(…)
method to Projection.max(…)
, as shown in the following code:
/* Line 6 */ projectionList.add(Projections.alias(Projections.max("price"), "price"));
Hibernate: select this_.category_id as y0_, max(this_.price) as y1_, category1_.name as y2_, this_.name as y3_ from product this_ inner join category category1_ on this_.category_id=category1_.id group by this_.category_id Hibernate: select category0_.id as id1_0_, category0_.created_on as created2_1_0_, category0_.name as name1_0_ from category category0_ where category0_.id=? Hibernate: select category0_.id as id1_0_, category0_.created_on as created2_1_0_, category0_.name as name1_0_ from category category0_ where category0_.id=? Category name: Furniture Product name: Meeting room table MAX(price): 120.0 Category name: Stationary Product name: Business envelopes MAX(price): 40.92
The aggregate function count
is used to count the number of occurrences of a value.
Here again, we will change a small part of the code in Line 6
from the Max example; we will just change the Projection.max(…)
method to Projection.cont(…)
, as shown in the following code:
/* Line 6 */ projectionList.add(Projections.alias(Projections.count("price"), "price"));
Hibernate: select this_.category_id as y0_, count(this_.price) as y1_, category1_.name as y2_, this_.name as y3_ from product this_ inner join category category1_ on this_.category_id=category1_.id group by this_.category_id Hibernate: select category0_.id as id1_0_, category0_.created_on as created2_1_0_, category0_.name as name1_0_ from category category0_ where category0_.id=? Hibernate: select category0_.id as id1_0_, category0_.created_on as created2_1_0_, category0_.name as name1_0_ from category category0_ where category0_.id=? Category name: Furniture COUNT(price): 3 Category name: Stationary COUNT(price): 3
From the output, it's clear that we have three products in each category.