A bit of history and a problem statement of sorts

I’ve been a big fan of criteria queries for writing dynamic JPA
queries for a long time. Sure I could make the same thing happen with a bit of logic and a whole lot of string
concatenation but that gets old really quick. My first experience with criteria queries was with Hibernate 3 so lets
start there and we will follow a query from SQL to JPA 2 Criteria.

We are all probably all most familiar with SQL so lets make that our starting point.

Assuming we have table in the database of tasks lets select those which are assigned to a specific user which have
not been closed.

select * from Task t where t.assignee=1 and t.dateClosed is null

However, we are living in an ORM world though so lets see it in
JPQL
(same syntax in HQL)

Obviously this is more verbose but it also has some readability advantages in my opinion. Even if you disagree with
regard to readability you can see that adding restrictions to the query in a programmatic way is much simpler than with
JPQL/HQL.

However the one thing always bugged with with JPQL/HQL and Hibernate’s Criteria API is that everything is done with strings
and doing things with strings makes refactoring difficult even with good tooling. This is Java and we want something
type-safe.

I will readily admit that upon first taking a look at the JPA 2.0 Criteria API I was really turned off by its verbosity and
complexity. I was turned off so much that I flat out ignored it for the first six months it was available to me. I
still however desired a type-safe way to write my queries so that they would hold up better during refactoring.

Finally I blocked out a day to tinker with the API and see if I could convert a few of my more simple queries. After only a few
minutes I was off and running and haven’t looked back. Here are a couple things I’ve learned during my time tinkering.

The code is longer but I can read and understand it much faster than a JPQL/HQL query.

I can write a criteria query faster due to IDE code completion and not having to review my entity structure to find the name
of that specific column I’m trying to reference.

As the complexity of the query goes up the benefits of the criteria query grow, but you will be forced to do some learning.
I have yet to find a query that I have not been able to convert. The API was very well thought out from this perspective.

My speed of development is faster almost 100% of the time as my criteria queries execute and return the desired results on the
first try. I can’t say the same for my JPQl/HQL which are parsing a potentially very long string with lots of
opportunities for syntax issues.

It isn’t however all sunshine and lollipops

This isn’t to say the API is perfect. It actually fights programmatic creation of queries a little bit. The API is designed
so that the base of your query can reused. For example each time you call the criteriaQuery.where(Predicate... predicates)
it replaces what was previously set. To work around this you need to store your predicates in a separate list and then
add them all at once in array form (varargs). It would be nice if a criteriaQuery.where(List<Predicate> predicates) was
exposed like it is for groupBy and orderBy. Additionally here are some other pain points.

Why is a Fetch<Z,X> not a
Path like
Join<Z,X> is? This means I need to define my "join"
twice it I want it "join fetched". Hopefully this is fixed in JPA 2.1

Metamodel generation is broken in Eclipse. It doesn’t handle the
cyclic nature of the metamodel classes. Thus when I do a project clean I will end up with 20 or so import failed errors.
These are easily resolved by making a meaningless edit to the class to trigger an incremental build but it shouldn’t be this way.

Just do it

Hopefully I’ve inspired you to take another look if you’ve previous dismissed the API as I had. Block out a few hours
and give it a shot. I’ll try to share my utility classes that I’ve written in an upcoming post that have made things a
it easier for me, but in the meantime you really should get down and dirty with the api to understand it fully.

Here is a fun issue that I ran into the other day while working with a legacy database.

Three tables:

Nodes

NodeID(pk)

Interfaces

InterfaceID(pk)

NodeID(fk)

InterfaceTraffic_Detail

InterfaceID(pk)

DateTime(pk)

NodeID(fk)

The InterfaceTraffic_Detail table turned out to be a little tricky for me since I have never worked with composite
indexes in hibernate. It took a little trial and error but here is what I came up with

The important part here is the @EmbeddedId and @Embeddable on the InterfaceTraffic_Detail and
InterfaceTraffic_PrimaryKey as well as the name "id.nmsInterface" on the @AssociationOverride so that I could
reference the variable on the embedded class and define the @JoinColumn

So I ran into a fun little issue the other day while working with MySQL and Hibernate.

Hibernate provides optimistic locking via the Version
function. This can either be a numeric field or a timestamp. I use Hibernate Annotations, so for me it simply involves
placing the following annotation inside my entity.

This worked great for me, and provided both an optimistic lock as well as a dateModified field I could use to show
the last time the entity was updated. I should say that this worked great during development while I was using
hypersonic as my database. When I deployed to a lab server for testing against our MySQL database I started to run
into some OptimisticLockExceptions

After doing some digging around I found out that MySQL doesn’t store date/time with millisecond precision.
This is a well known feature request / bug and it doesn’t appear that it will be fixed anytime soon. So I was forced to
refactor my code so that the "version" was a int and still maintain a
dateModified field since the user interface had made use of it in various places.

Thankfully Hibernate provides Call Back
annotations which allow me to trigger an update of the dateModified when a Persist or Update is called. I also used
this logic to automatically set the dateCreated field on entities when needed.

I also added an @Deprecated to the setters for dateCreated and dateModified to discourage others from setting those
fields manually.

It looks like mysql has this on their roadmap and outlines their plans with a fairly extensive
worklog. However this is slated for MySQL 6.0 or 7.0 and thus will be
a few years out. Maybe it’s time to checkout what PostgreSQL is all about.