By Justin Freeman
I recently discovered an awesome annotation in Hibernate that somehow escaped me in my consulting adventures. It is called
@Formula. It is pretty straightforward to use and once you've seen it's benefits you will want to use it all the time (I caution you on using it all the time for reasons I will explain later in this post). For now, let me explain
@Formula and what it is useful for.
Let’s says you have a database and some objects mapped to the database. Now if you have been in the web application development business long enough, you know those objects don’t always perfectly map to your tables. Sometimes you need to combine values or perform computations on the data to get those objects. The reasons vary, but mostly it boils down to ever changing databases, changing requirements and changing objects. Pretty soon you end up with objects that no longer have 1-to-1 relationship with the tables. Now you have to grab data from multiple tables or do some massaging of the data until you get your perfect object. In the old days, this was tedious.
You would have to do something like this:
//Query for data
//Loop through each object
//For each object, do another query to get data
//Combine data elements to get pieces of necessary data
As you can see this is awful. Even if the code is one place, you have to maintain this atrocity. Now you don’t have to! Using the
@Formula keyword, you can place the second query right on the object like this:
@Formula(“(select b.someData from table b where b.key = key order by p.something)”)
private String someData;
Now whenever this object is loaded, this query is also executed and the data will already be there! No more after the fact query to get your data. This annotation not only works for queries but also for other database functions:
@Formula(“CONCAT_WS(‘ ‘, first_name, last_name)”)
private String fullName;
This example assumes you already have columns in your database with those names.
So now you have seen how powerful the
@Formula annotation is. It makes you want to use it all the time, right? Well here is my word of advice: this annotation is very helpful but it does incur a performance hit. IF you think about it, this makes sense.
You are doing additional database lookups. The only difference is that it is in the same transaction as your original query. So for a few thousand records, this is not a big deal. But if you are using this in a high-performance, high-transaction environment, you should run some very extensive performance tests before you approve the use of this annotation. The convenience of the annotation may or may not be worth the performance hit. Just make sure you do your due diligence when you decide to use the
@Formula annotation. With great power comes great responsibility.