Hibernate Group By Using Criteria Query

Java-PodcastIn our last Hibernate lesson, you learned how to fix duplicate data from hibernate queries.

In this lesson we’re going to focus on some of the aggregate functions that can be used with Hibernate, and of course that means diving into the Group By clause.

What’s important to note is that Hibernate doesn’t ever refer to it as group by, instead they use the term projections.

So, armed with this knowledge, let’s jump into the lesson!

Grouping Data with Hibernate

In our examples, I’m going to show you how to create a query with hibernate that will group your data in some manner.

When we build our query, we will be focusing on using Hibernate’s Criteria queries, which allows for a nice way to build queries without having to know any SQL.

First, let’s start off with an example that we will build on.

Let’s assume that we are taking care of Transactions at a Bank. The actual Transactions class could look like this:

import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import javax.persistence.Version;

import org.springframework.format.annotation.DateTimeFormat;

@Entity
public class Transactions
{

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private Long id;

	private Double amount;

	@Column(name = "transaction_type")
	private String transactionType;

	@Column(name = "transaction_date")
	@Temporal(TemporalType.TIMESTAMP)
	@DateTimeFormat(style = "M-")
	private Date transactionDate;

	@Column(name = "account_id")
	private Integer accountId;

	public Integer getAccountId()
	{
		return accountId;
	}

	public void setAccountId(Integer accountId)
	{
		this.accountId = accountId;
	}

	public Long getId()
	{
		return this.id;
	}

	public void setId(Long id)
	{
		this.id = id;
	}

	public Double getAmount()
	{
		return this.amount;
	}

	public void setAmount(Double amount)
	{
		this.amount = amount;
	}

	public String getTransactionType()
	{
		return this.transactionType;
	}

	public void setTransactionType(String transactionType)
	{
		this.transactionType = transactionType;
	}

	public Date getTransactionDate()
	{
		return this.transactionDate;
	}

	public void setTransactionDate(Date transactionDate)
	{
		this.transactionDate = transactionDate;
	}

}

Pretty standard stuff.

Now let’s talk about what we can do with respect to grouping our data.

It would be useful to know, for example, how many transactions are associated with each account. So let’s group together the information with the hopes of counting the number of transactions per account.

Here’s an example of the query we’ll create using Hibernate.

Note: there’s some more complex stuff going on behind the scenes with the ResultsTransformer that will be explained very soon.

public List<GroupedTransactionsDTO> getTransactionsByAccount ()
	{
		Session session = (Session) entityManager.getDelegate();
		@SuppressWarnings("unchecked")
    List<GroupedTransactionsDTO> results = session.createCriteria(Transactions.class).setProjection(Projections.projectionList().add(Projections.groupProperty("accountId"), "accountId").add(Projections.rowCount(), "count"))
				.setResultTransformer(Transformers.aliasToBean(GroupedTransactionsDTO.class))
				.list();

		return results;
	}

So you’ll see here that we are using the Projections.groupProperty here to actually do the job of grouping the transactions together by the accountId field. Cool!

But, we’re also using the Projections.rowCount() to return a count of all the transactions in their respective buckets.

Using two projections in one query is made possible by the use of the Projections.projectionList() method call (which uses chaining to make life easier).

So why are we using a Transformer?

Love it!

Any time I can legitimately use the word Transformer in my code, I’m a happy man.

Also, so why are we using it?

Well you see, when we execute our query, we’re going to be selecting two things:

  1. The accountId (as we’re grouping on it)
  2. The count of the transactions in each transaction “bucket”

Well, since we’re selecting these two things (and only these two things) we don’t have any way to “map” our results.

We DO have a Transactions class, but that expects not only an accountId but also a bunch of other information, and it certainly won’t know what a count is.

So what we do is we create a special DTO (Data Transfer Object)… it’s really just a fancy name for yet another Java bean class.

So we create a special bean whose sole purpose is to hold the results of our grouping query. Here’s what that class looks like:

public class GroupedTransactionsDTO
{
	private Integer accountId;
  private Long count;

	public Long getCount()
	{
		return count;
	}

	public void setCount(Long count)
	{
		this.count = count;
	}

	public Integer getAccountId()
	{
		return accountId;
	}

	public void setAccountId(Integer accountId)
	{
		this.accountId = accountId;
	}

}

Voila!

Simple, easy to understand, but extremely useful.

So now when Hibernate executes the grouping query, it will take the results (accountId and count) and “automagically” map them to our GroupedTransactionsDTO.

And now we can use the list of GroupedTransactionsDTO for whatever we want, as they are just plain old Java objects.

Neat right?