Hibernate 3.5/JPA 2.0 - New Query Expressions

Hi all,

As usual it took me a long time to write to my blog again but finally here it is - in this post I continue writing about JPA 2.0 (my previous post is here), more than that this is the first entry I'm publishing in my new (the old one is here) blog - Congratulations to me :-). Anyhow in this post I'm trying to go over some of the new functions introduced by JPA 2.0 to the JPA query language. As always my platform is Hibernate but I am using only JPA standard annotation and query syntax. The post includes the following:

• The INDEX function (and the @OrderColumn annotation)
• The TYPE expression, and
• The four types of case expressions

My environment

• Hibernate 3.5.2-Final
• PostgreSQL 8.3

The INDEX Function and the @OrderColumn Annotation

Before going over the INDEX function it is worth (and maybe even more interesting) to take a look at the @OrderColumn annotation. This annotation, introduced in JPA 2.0, is a new way to implement ordered associations (such as List) and making the provider the only responsible for maintaining the index value of the associated objects (entity or embeddable). For example let's assume we have a Book entity containing an ordered list of Chapters, this association should be mapped as:

public class Book {

 ...
  @OneToMany(orphanRemoval=true, cascade=CascadeType.ALL)
  @OrderColumn @JoinTable(name="BOOK_CHAPTERS")
  private List<Chapter> chapters = new ArrayList<Chapter>();
  ...
  public void addChapter(Chapter c) {
    // That is it - no need to manage the positions manually!
    chapters.add(c);
    c.setBook(this);
  }
}


public class Chapter {
 .....
 // There is no need for order property in here! This is managed by the provider 
// and hidden from the developers!
}

Whenever I add or remove a chapter to a book Hibernate, as my JPA provider, is responsible to maintain the chapter's position value for me. It is important to understand the following quote from the specification: "The persistence provider is responsible for maintaining the order upon retrieval and in the database. The persistence provider is responsible for updating the ordering upon flushing to the database to reflect any insertion, deletion, or reordering affecting the list." (From JSR-317, section 11.1.39). This means that even if we had a position property on the chapter we couldn't trust its value in memory - the provider is only responsible to maintain it when a flush operation takes place. This is why "The order column is not visible as part of the state of the entity or embeddable class" (from the spec.).


I first tried the @OrderColumn on a classic bidirectional one-to-many association in which the child entity has an order property (or just a database column in this case) but it didn't work. After thinking about it a little bit
it I understood why: first if I maintained the order property on the child entity I wouldn't be able to share the entity in more than one collection (the order values will be mixed by the different collections), the other reason is that in such a mapping the collection is the inverse side of the association (the one that uses the mappedBy member in the annotation), following the JPA philosophy the inverse side of the association cannot update the association itself. This is the reason why the mapping in the example above is a unidirectional one..

Now we can go to the new INDEX function, this function returns an integer value corresponding to the position of its argument in an ordered list (ordered using the @OrderColumn annotation). The function argument is an identification variable denoting types for which an order column has been specified. Here is
an example in which I want to fetch all of the books which their fifth chapter is named 'the fifth chapter':

Select b from Book b join b.chapters c where INDEX(c)=5 and c.name='the fifth chapter'

Notice the use of the chapters' collection identification variable in the index function. Hibernate will issue the following query to the database:

select book0_.id as id5_, book0_.TITLE as TITLE5_ from BOOKS book0_ 
inner join BOOK_CHAPTERS chapters1_ on book0_.id=chapters1_.BOOKS_id inner join CHAPTERS chapter2_ on chapters1_.chapters_id=chapter2_.id 
where chapters1_.chapters_ORDER=5 and chapter2_.NAME='the fifth chapter'

Entity Type Expression (Non-polymorphic Queries)

JPA 2.0 defines the TYPE expression. This expression is similar to HQL's '.class' property but its syntax is different. The idea behind the type expression is to restrict the JPA query polymorphism nature so it will fetch
only entities from the exact type as it was defined in the query. Let's add a subclass to Book named RareBook:

@Entity @Table(name="BOOKS") @Inheritance(strategy=InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(discriminatorType=DiscriminatorType.STRING) @DiscriminatorValue("BOOK")
public class Book {
  ...
}


@DiscriminatorValue("RARE") @Entity
public class RareBook extends Book {
}

If I would like my query to fetch only instances of the Book entity it would look like that:

"Select b from Book b where TYPE(b) = " + Book.class.getName()

or

"Select b from Book b where TYPE(b) = " + Book.class.getSimpleName()

And the generated SQL query, for both cases,  will be:

/* Select b from Book b where TYPE(b) = par2.books.Book */ select book0_.id as id5_, book0_.TITLE as TITLE5_, book0_.NUM_OF_COPIES as NUM4_5_, book0_.DTYPE as DTYPE5_ from BOOKS book0_ where book0_.DTYPE='BOOK'

Since I'm using the single table approach to persist my hierarchy Hibernate appends the discriminator value to the query. From that we can understand that the query is strongly restricted to the desire type. The database
cannot tell that RareBook is a subclass of Book so by restricting the requested type to Book the result will include only entities which their concrete type is Book.


As always there are the small prints:

• As with Hibernate '.class' property if we try to use the TYPE expression on an entity which doesn't participate in an entity hierarchy we will get an error
• The specification says that we can use a parameter for entity type expression (as in "select.....where TYPE(t)=?") - Currently this is not working and there is an open bug for that: HH-5282

Case Expressions

JPA 2.0 added support of case expressions in its query language, there are four different case constructions: general, simple, coalesce, and nullif. The simple case construction includes an expression to evaluate, one or
more when clauses including possible value for the evaluated expression, and an else clause. For example I want to select all the book titles on my database and I want to concatenate a star to each rare books title:

select case TYPE(b)                       //We evaluate the TYPE(b) expression
  when RareBook then CONCAT(b.title, '*') // If TYPE(b)==RareBook
  else b.title                            // All other values
end 
from Book b

The general case construct can evaluate different expressions in each of the when statement (so no expression to evaluate appears after the CASE reserved word):

select case 
  when TYPE(b) = RareBook then CONCAT(b.title, '*') 
  when LOCATE('story', b.title)>0 then CONCAT(b.title, '**')
  else b.title
end 
from Book b

In the sample above each rare book title will have a star appended to it and each book containing the string 'story' in its title will have two stars appended to it. The sample illustrates how each case branch evaluates a
different expression. It also illustrates how careful we have to be about it: obviously replacing the first when clause with the second one will give different results (in the current form rare books will have one star appended to their title even if they include the string 'story', switching the two when clauses will append two stars to rare books with the word 'story' in their title).

The third case form is the coalesce function, this function takes one or more arguments and returns the first none-null expression among its arguments; lets assume that my book has two properties: a title, and a
temporaryTitle. When a book is first submitted the author must provide at least one of the two but once I have a title I can ignore the temporary one. If I would like to fetch a list of the titles in which the 'title' property hides the 'temporaryTitle' property the following select will do it easily:

select coalesce(b.title, b.temporaryTitle) from book b


// This is equivalent to the following general case
case
   when title is not null then title
   else temporaryTitle
end

The type of the coalesce expression is the type of its arguments so they have to be 'typely correlated' (it means that, as an example, the expression coalesce (b.title, b.releaseDate) will yield an error).

The last case form is the nullif function, this function gets two arguments and returns a null value if and only if its two arguments are equal, otherwise it returns the first argument. Here is an example (a little bit
unnaturall but nevertheless):

// Make a list of all book ids and their titles, if the temporary title 
// equals to the title the returned value in will be 'null', otherwise 
// it will be the title
select b.id, nullif(b.title, b.temporaryTitle) from Book b

It is important to understand that the provider actually delegates the case operation to the database (the last example generated the following SQL query: 'select book0_.id as col_0_0_, nullif(book0_.TITLE, book0_.TEMPORARY_TITLE) as col_1_0_ from BOOKS book0_' so portability might be an issue in here.

See you next time.