Performing aggregate operations

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.

Getting ready

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.

Creating the tables

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

Creating the classes

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;
  }

}

Source file: Product.java 

@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;
  }

}

Inserting data in the tables

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

1

2015-01-01 15:34:54

Furniture

2

2015-01-22 15:35:02

Stationary

This is the data for the product table:

id

name

price

category_id

1

Meeting room table

100.23

1

2

Metal bookcases

120

1

3

Lighting

70.36

1

4

Business envelopes

40.92

2

5

Paper clips

20.61

2

6

Highlighters

30

2

Now for this recipe, we are considering the preceding table structure and data.

How to do it…

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

Sum

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:

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")); 
}

Output

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.

Avg

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:

Code

/* Line 6 */ projectionList.add(Projections.alias(Projections.avg("price"), "price"));

Output

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

Min

The aggregate function min is used to find the product having the minimum value in a particular category. Execute the following code:

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"));
}

Output

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.

Max

The aggregate function max is used to find the maximum value in a particular category.

Code

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

Output

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

Count

The aggregate function count is used to count the number of occurrences of a value.

Code

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

Output

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.

Note

count only calculates the number of records. If you need a distinct count of the values, another method available is as follows:

Projections.countDistinct(String propertyName);

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.147.47.166