Quick Note – Creating ad hoc Relations with the Hibernate Query Language

When using an ORM tool such as Hibernate, relations between multiple entities are typically resolved directly by means of associations, which are defined within your metadata that will map the entities classes to the underlying persistence unit. Inside your classes, those associations are usually represented as fields (or properties) which reference either a single related entity or a whole collection of related entities.
As long as only one of the related entities holds a reference to the other one, we have an association which is unidirectional – this may be the most usual style in practice.

To make things clear, say we have an Entity Book which holds a unidirectional 1:n association to another Entity called Review. The reference to the related Reviews is represented by a property called reviews, which is a Collection of instances of class Review.
Both Entities hold a property called isbn, which is used as the primary key of a Book and as the foreign key inside of Review.

class Book{
	private String author;
	private String isbn;
	private List<Review> reviews;

class Review{
	private String isbn;
	private String reviewer;

For the sake of simplicity, we use simple Strings for the isbn as well as for the name of a books author and a reviewer.
Now if we want to draw all relations between all books of a certain author and and its reviews, we can do so easily by using a rather standard form of retrieval via Hibernates Query Language:

from Book book where book.author = 'Goethe'

As long as we use an eager fetch mode, using a dynamic fetching strategy with a fetch join directly within the query or staying within an open Hibernate session, the related Reviews can be referenced directly, simply by accessing the corresponding property, like book.getReviews().
Those kind of associations are kind of static to Hibernate, because they are well defined (therefore announced to Hibernate) and established within the mapping metadata.

What if we want to draw all relations between all Reviews of a certain reviewer and the related Book respectively? In this case, there’s no declaration for the backward association. Of course we could declare a bidirectional association, but sometimes its not desired to establish a ‘static’ association between some of your entities (maybe polluting your domain model or not considered as essential associations).

For this purpose, we can rely on something that’s called a Theta-style join in Hibernate jargon. You’ll use Theta-style joins on the basis of retrieving all possible combinations (cartesian product) of two or more entities in conjunction with restricting this combinations by an adequate where clause.
Consider the following cartesian product of Reviews with books:

from Review, Book

This kind of query isn’t very helpful since it will bring up all combinations of all instances of Review with all instances of Book.
Since we are only interested in the relations between Reviews and the related book, we can benefit from property isbn which represents the foreign key:

from Review review, Book book where book.isbn = review.isbn

Now we (theta-) joined only those Reviews with those Books that offer the same isbn. if we additionally only want those reviews of a certain reviewers (along with the related Books), we can of course extend the where clause:

from Review review, Book book where book.isbn = review.isbn and review.reviewer = 'John Foo'

When executing this Hibernate Query, we receive a List of ‘tuples’ that will represent a kind of ad hoc relation between those entities that have participated within the Theta-Join query. Those ‘tuples’ are represented by an Array of Objects.
In the above example, we’ll receive a List of Arrays, each containing two elements. The first element is of type Review, the second one is of type Book.

List<Object[]> tuples = session.createQuery( "from Review review, Book book where ..." ).list();

for( Object[] tuple : tuples ){
		new Summary( (Review) tuple[0], (Book) tuple[1] ) );

Note, that the entities order within the from clause is relevant for the elements order inside the tuple (and therefore if we face a n:1 association like in the above case or an 1:n association the other way round).

If we want to create a 1:n association on ad hoc basis, we have to pay attention to the nature of Theta-style joins: we’ll always receive a (at most restricted) cartesian product that we have to concentrate on ourselfes.

from Book book, Review review where book.isbn = review.isbn and review.reviewer like 'Joe%%'

So even if there are i.e. five Reviews from some Joes that belong to a single Book, we’ll receive five single tuples for that relation, each one holding the ‘same’ Book as first element and an according Review of that book as second.
It’s important to know, that we’ll receive five different instances of that ‘same’ book, so if we want to concentrate the relation between those book and the relating Reviews (i.e. by using a Map<Book,Collection<Review>>), we have to come up with a suitable implementation for Book.equals(), resp. Book.hashCode():

List<Object[]> tuples = session.createQuery( "from Book book, Review review where ..." ).list();

for( Object[] tuple : tuples ){
	MapHelper.addTo( bookReviews, (Book) tuple[0], (Review) tuple[1] ) )

As seen, Theta-style joins within a Hibernate Query are an appropriate aid if you are in need of relating different entities for which no association is established ‘statically’ within the mapping metadata.
Especially building 1:1 associations on ad hoc basis is rather straightforward. Most importantly, you have to keep in mind, that there are always cartesian products that will be delivered. It’s on you to restrict those combinations by an appropriate where clause.
With those constraints in mind, there should be no major barricades to build your own ad hoc relations.


5 Responses to “Quick Note – Creating ad hoc Relations with the Hibernate Query Language”

  1. Book Reviews Says:

    […] Read the rest of this great post here […]

  2. how we evaluate Says:

    […] R-Charleston, who led the Senate Judiciary shttp://www.thestate.com/local/story/365229.htmlCreating ad hoc Relations with the Hibernate Query LanguageQuick overview on how to leverage Hibernate Query Language to dynamically create ad hoc relations […]

  3. Nikunj Says:

    How about outer join. I have two tables which are not associated from hibernate perspective.
    I want something like:

    List tuples=session.createQuery(“select a,b from A a full join B b on a.col1=b.col1”).list();

    Object[1] should be null when Object[0] does not have corresponding row.

  4. Soongy Team Says:


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: