Hibernate report query
I am still in training, but the last few days' material and lab work has been heavier than the first day's, so I didn't have any time to blog. We are deep in hibernate territory now, and I have to say, hibernate report query is pretty impressive stuff.
I've been using ORM tools for a while, first with JDO, then hibernate. Every time I try to tell some seasoned JDBC developer to use hibernate, their first question is always "does it support table joins?" I would say "yes, you can create relationships between your objects, and navigate between them." Then they'd say, "what if I want some columns from one table, some other columns from another one, etc. etc.?" I never had a good answer for that.
Until this week when I learned about hibernate report queries. This stuff is tailor made to answer the questions above. Unfortunately it's not mentioned at all in the hibernate reference manual, and I couldn't find any information on their web site. So let's look at an example here that might give you some idea about the power of report query.
Suppose we have a CD management system. A CD belongs to a Category; it has a list of Tracks; it also has an Author, which has an AuthorInfo that lists the bio, and an Address. If it's a bit confusing, don't worry as it's just an example. Now let's say we want to display some of this informaiton on a web page, but instead of everything, we just want to show the title of the cd, the name of the artist who authored it, the price of the cd, the name of the category, the bio of the artist, and which state the artist is from. Without report query, you'd have to retrieve all these objects, navigate through them to build up the result: it's hard to do it efficiently and there's a lot of code to write. Even with JDBC, the SQL gets kinda hairy too: you need to do a four way table join.
Let's see how we do this with a report query. First, we create a POJO class, CDInfo, to hold the attributes we need. On hibernate 2.x, you can do this to populate it in one shot:
Pretty neat, isn't it? In hibernate 3.x, you can even do this:
That kinda gives you the "WOW" effect, doesn't it?
I've been using ORM tools for a while, first with JDO, then hibernate. Every time I try to tell some seasoned JDBC developer to use hibernate, their first question is always "does it support table joins?" I would say "yes, you can create relationships between your objects, and navigate between them." Then they'd say, "what if I want some columns from one table, some other columns from another one, etc. etc.?" I never had a good answer for that.
Until this week when I learned about hibernate report queries. This stuff is tailor made to answer the questions above. Unfortunately it's not mentioned at all in the hibernate reference manual, and I couldn't find any information on their web site. So let's look at an example here that might give you some idea about the power of report query.
Suppose we have a CD management system. A CD belongs to a Category; it has a list of Tracks; it also has an Author, which has an AuthorInfo that lists the bio, and an Address. If it's a bit confusing, don't worry as it's just an example. Now let's say we want to display some of this informaiton on a web page, but instead of everything, we just want to show the title of the cd, the name of the artist who authored it, the price of the cd, the name of the category, the bio of the artist, and which state the artist is from. Without report query, you'd have to retrieve all these objects, navigate through them to build up the result: it's hard to do it efficiently and there's a lot of code to write. Even with JDBC, the SQL gets kinda hairy too: you need to do a four way table join.
Let's see how we do this with a report query. First, we create a POJO class, CDInfo, to hold the attributes we need. On hibernate 2.x, you can do this to populate it in one shot:
select new CDInfo(cd.title, artist.name, cd.price, cat.name, info.bio, artist.address.state)
from Category cat
join cat.cds cd
join cd.author artist
join artist.authorInfo info
where cat.id = 1
Pretty neat, isn't it? In hibernate 3.x, you can even do this:
select new CDInfo(cd.title, cd.author.name, cd.price, cd.category.name, cd.author.authorInfo.bio, cd.author.address.state)
from CD cd
where cd.category.id = 1
That kinda gives you the "WOW" effect, doesn't it?
posted by Rong at 1:29 PM
3 Comments:
For the record: If you want to join stuff, you should look in the Hibernate documentation under "joins".
You can find a reference about report queries in Hibernate in Action book.
How can I display the result from this query on JSP ?
Post a Comment
<< Home