Popular @
Forum Controls
Spotlight Features
The Rich Engineering Heritage Behind Dependency Injection
Andrew McVeigh takes us on a tour of the rich heritage behind dependency injection, what it represents, and tells us why its here to stay.
NetBeans 6: Matisse Updates
NetBeans 6 delivers great updates to the Matisse GUI builder. Spend a few minutes with Roman Strobl and get an expert briefing on what's new and what has changed. (sponsored)
Introduction to Groovy Part 3
In this, the third and final installation of Andres' Introduction to Groovy series, you learn about how Groovy handles variable numbers of arguments, named parameters, currying, and more about Groovy operators. Including, some new operators.
Easier Custom Components with Swing Fuse
Swing Fuse (actually just Fuse), is a framework designed to make it easier to create your own custom desktop components. In this article, Daniel Spiewak shows you how to get started and provides sample source code you can download.
Benchmark Analysis: Guice vs Spring
Willam Louth shows how he uses JXInsight Probes to investigate probable performance issues with code bases that he is not familiar with. He also highlights possible pitfalls in creating a benchmark, as well as in the analysis of results.
Hibernate Querying 101 : tips and tricks
At 11:35 AM on Jul 6, 2005, Matthew Schmidt wrote:
Fresh Jobs for Developers Post a job opportunity
Read the whole article now!
26 replies so far (
Post your own)
Re: Hibernate Querying 101 : tips and tricks
Nice article, a must read for anyone with few experience with Hibernate.I usually add Spring in the mix to further simplify coding, but the techniques I use are more or less the same (besides the final twist using reflection, I usually get away with view object or Object[]).
The remark about queries becoming too big is interesting, in that it's database dependent. In my experience, for each database there is a certain level of joins that represent a threshold, after which the optimizer is no more able to do a good work, and thus is preferable to go the n+1 query road.
Yet, so far I've found it difficult to find such a limit for Oracle, provided that the required indexes are in place, whilst I hit the limit much sooner with SapDB (5-7 tables).
Re: Hibernate Querying 101 : tips and tricks
Hello,firstly, thanks for the article. I wonder, however, why you dont mention the possibility to declare even single-valued associations as lazy. IMO, doing that would alleviate many of the problems you mention in the first part of your article..
I must also admit that I was not able to completely follow the approach in the second part of the article. I would have appreciated a little more human prose explanation, instead of sample code.
Re: Hibernate Querying 101 : tips and tricks
Thanks for your comments Christian,In Hibernate 2.1, to enable lazy-loading on single-valued associations, you need to use a proxy relation, which is a bit more complicated. In Hibernate 3, you can declare a property to be lazy, but since in our application we are still using Hibernate 2.1, I haven't tried out this yet.
Do you mean the 'More advanced techniques' when you talk about the second part of the article ? If so, I'll redo this part and publish an annotated version on my blog (http://www.jroller.com/page/wakaleo) some time soon
Re: Hibernate Querying 101 : tips and tricks
Thanks for you comments Andrea. My experience of the limitations you mention are with PostgreSQL : when a query returns a few 100 thousand results, you feel the difference between the two approachesRe: Hibernate Querying 101 : tips and tricks
> Do you mean the 'More advanced techniques' when you> talk about the second part of the article ? If so,
yeah, thats what I meant. I am looking at (my own) source code all day long, so I am not too keen on reading code also in the short breaks that I allow myself
Another immediate reaction is "if the approach is really obvious and/or efficient, why is it not part of the framework proper?".
Anyway, thanks again, and I will look at your blog to find out..
christian
Overly complicated
I use both Hibernate and JasperReports. Hibernate is a wonderful ORM tool; very powerful. But as a query tool to feed a report framework? I don't think so.> Why don't we use plain old SQL queries for the reports? Well, if we
> did that, we'd loose the abstraction level we get with Hibernate ...
A report is a direct view into the data model. Why in the world would I want a layer of abstraction?
> ... and the queries would be much longer and more complicated.
I call bullsh*t on this one. What you're writing in HQL is, for the most part, the SAME THING you'd write in SQL. Why not just put that SQL code in the JasperReport file and bind any required parameters at report fill time? Seems like your layer of abstraction is actually a layer of distraction.
Besides, if you've got a query that you use a lot that implements a bunch of joins, any DBA worth his salt is likely going to create a database view, allowing you to write something like:
SELECT id, label, post_code, mayor_name FROM v_mayor_list;
Much nicer, no?
As a final rant I've got to ask, what genius came up with those table/column names? What the hell is wrong with just naming your table SALE and your columns SALE_ID, SALE_DATE and SALE_PRICE?!? If I need to know what the data types are, that's what a data dictionary is for. And don't get me started on the use of strings for primary keys ...
Re: Overly complicated
Jakob, your assumption may be correct in your work enviroment, but are completely broken in mine. Let's have a look at them:* "A report is a direct view into the data model. Why in the world would I want a layer of abstraction?"
Because the whole application is coded in terms of beans, there are no tables around
* "if you've got a query that you use a lot that implements a bunch of joins, any DBA worth his salt is likely going to create a database view"
First, we don't have any DBA since we are software vendors, not a corporation. Second, I want to code the report the same way I've coded the rest of the application.
If your team is composed with few people, there are no specialists and the people who write the reports are the same that write the UI, the beans, the service layer and so on.
Having to deal with HQL in one part of the application and SQL in another one _is_ distracting, since I have to think in two different layers of abstraction.
Generally speaking, I have to deal with too many things in a single day to afford having multiple tools to do the same job. I want one, that everybody uses and everybody can understand, so that if I'm not there someone else can pick up my job and fix the problem without waiting for me to come back, and without having to use a different tool than the ones he has been using during the last hours.
Re: Overly complicated
IMO it has to be mentioned in this context, that this is a matter of compromise. I think both points are right - having dedicated expertise in a field like database modelling and access is usually a very wise decision, but being able to focus on one toolset is very convenient and helpful with regard to maintainability, etc.Still, one has to ask what you would you think about a graphics design oriented person that said "I want to do all the application development in my graphical tool, and not have to deal with tradtitional programming"?. My answer: "you may need someone to do the programming for you, or else you will likely fail".
If you restrict yourself to HQL and "bean thinking", you may indeed be missing out on some huge optimization potential - something that could hurt you as a product vendor just as much (or more?) as an inhouse developer. As an aside - I do think that HQL can carry you far enough in the vast majority of cases.
I am saying this mostly because I have met too many programmers with a "yuck, SQL" or "yuck, database" attitude. That, IMO, simply does not work.
chris
Re: Overly complicated
> If you restrict yourself to HQL and "bean thinking",> you may indeed be missing out on some huge
> optimization potential - something that could hurt
> you as a product vendor just as much (or more?) as an
> inhouse developer. As an aside - I do think that HQL
> can carry you far enough in the vast majority of
> cases.
Well, I'm not restricting myself because of laziness. I use quite a bit of SQL in import/export routines with legacy databases and when the application is so small that I don't need to use an O/R mapper. I also use raw SQL if and when I'm loading ten of thousands of record.
But at the same time I try to avoid it when it does not hurt to, because, as Dijkstra said, our skull is limited (http://www.cs.utexas.edu/users/EWD/transcriptions/EWD03xx/EWD340.html)
Re: Overly complicated
> Still, one has to ask what you would you think about> a graphics design oriented person that said "I want
> to do all the application development in my graphical
> tool, and not have to deal with tradtitional
> programming"?.
As I said, this is not my context. We are a small company, everyone must be able to do everything is needed, from database/application server installation to programming, UI design (both Swing and JSF), software requirements, and so on. If you try to limit yourself like this, you get fired
But that's, at the same time, the very reason we have to limit the number of tools used. One can learn just a limited number of tools.
> If you restrict yourself to HQL and "bean thinking",
> you may indeed be missing out on some huge
> optimization potential
I agree when I think about people that don't want to use HQL, and want to just use session.get()/session.load(), since they lack set oriented operations that may be at the same time more efficient and more compact, but once you master HQL, I don't see how you are limiting yourself (besides mass data handling or legacy apps, where you cannot use Hibernate at all).
Re: Overly complicated
> Jakob, your assumption may be correct in your work> enviroment, but are completely broken in mine. Let's
> have a look at them:
My given name is Steve and is clearly indicated at the top of my post. If you're going to address me by my surname, I'm "Mr. Jakob".
First of all, my post was a critique of the article posted by John Ferguson Smart. If you want to debate my comments, please do so in that context. At this point I know NOTHING about your application environment.
> * "A report is a direct view into the data model. Why
> in the world would I want a layer of abstraction?"
>
> Because the whole application is coded in terms of
> beans, there are no tables around
Mr. Smart's article clearly stated that they were using a PostgreSQL database. In this case, there are definitely tables around.
> First, we don't have any DBA since we are software
> vendors, not a corporation. Second, I want to code
> the report the same way I've coded the rest of the
> application.
>
> If your team is composed with few people, there are
> no specialists and the people who write the reports
> are the same that write the UI, the beans, the
> service layer and so on.
You don't need to lecture me about working in a small shop. That's exactly the environment I've worked in for the last 15+ years. Still, someone needs to play the part of the DBA, and that person should be willing to obtain more than just a passing familiarity with such an important part of the application stack.
If you're unwilling or unable to do so, such skills can be sub-contracted. In any non-trivial multi-user data-driven application, proper database and query design go a LONG way toward improving perceived performance.
> Having to deal with HQL in one part of the
> application and SQL in another one _is_ distracting,
> since I have to think in two different layers of
> abstraction.
Indeed, such is the nature of the so-called Object/Relational impedance mismatch, and is the reason Gavin King started the Hibernate project. But where there is an advantage to dealing with the UI and business domain in an object-oriented manner, when it comes time to aggregate the data we've collected, relational databases have some big advantages. What I'm saying is, USE THEM.
> Generally speaking, I have to deal with too many
> things in a single day to afford having multiple
> tools to do the same job. I want one, that everybody
> uses and everybody can understand, so that if I'm not
> there someone else can pick up my job and fix the
> problem without waiting for me to come back, and
> without having to use a different tool than the ones
> he has been using during the last hours.
That's a cop-out. Compared to Hibernate or JasperReports or Java, learning proper SQL query techniques and rudimentary database design is simple. As Gavin King says in "Hibernate in Action" (which I highly recommend), "To use Hibernate effectively, a solid understanding of the relational model and SQL is a prerequisite."
Re: Overly complicated
Steve, sorry for sounding rude. It was not my intention.I see we disagree. I can design a database in third normal form, write SQL queries with multiple outer joins, subqueries and the like, but this does not mean I like it.
When I say there are no tables around, I mean the concept of database table, records and foreign keys are not part of the concepts my code uses (besides error handling). I don't mean I don't use a database.
For something as simple as a report, I prefer to keep coding like in the rest of the application, that is, with Hibernate. I simply find it more natural.
And I don't see what I'm loosing using HQL in place of SQL... it just has a simpler sintax, but it's declarative and set oriented just as SQL.
Re: Overly complicated
> I see we disagree. I can design a database in third> normal form, write SQL queries with multiple outer
> joins, subqueries and the like, but this does not
> mean I like it.
Then you're going to run into trouble with Hibernate if your database is going to be accessed by more than a few users simultaneously or contains millions of records. To some extent Hibernate does shield the developer from these issues, but in order to ensure peak performance you still have to deal with ALL these issues, PLUS all the new issues that arise (eg. lazy loading, collection handling, detached objects) as a result of converting relational data to an object model.
> When I say there are no tables around, I mean the
> concept of database table, records and foreign keys
> are not part of the concepts my code uses (besides
> error handling). I don't mean I don't use a
> database.
Then you most certainly DO have the concept of database tables, records, etc. You simply choose to ignore it because you're dealing with many of those issues in your Hibernate mapping files.
> For something as simple as a report, I prefer to keep
> coding like in the rest of the application, that is,
> with Hibernate.
Simple is a relative term. For something like an invoice which encompasses a relatively small number of objects, sure, reporting directly from the object model makes sense. BUT, the original article discussed reporting requirements where the object model could be on the order of hundreds of thousands of objects. In these cases it makes much more sense to bypass the object model and have the reporting engine work directly with the data model (via SQL).
Heck, if all your data access is via walking the object graph, why not dump the relational database altogether and use an OODBMS, or something like Prevayler? No more impedance mismatch.
> I simply find it more natural.
> And I don't see what I'm loosing using HQL in place
> of SQL... it just has a simpler sintax, but it's
> declarative and set oriented just as SQL.
The problem is not what you're losing; the Hibernate Query Language is very expressive. The problem is what you're GAINING, which is an extra layer between your reporting engine and the database. When a report is taking a long time to generate, the original article author would have to determine how HQL is handling the query AND how the database is handling the query. For performance tuning, they're BOTH relevant.
If possible, why not cut out the middleman?
I have to admit, I'm a bit mystified that you had taken my original comments so personally. Do you work with John Ferguson Smart?
Re: Overly complicated
> Simple is a relative term. For something like an> invoice which encompasses a relatively small number
> of objects, sure, reporting directly from the object
> model makes sense. BUT, the original article
> discussed reporting requirements where the object
> model could be on the order of hundreds of thousands
> of objects. In these cases it makes much more sense
> to bypass the object model and have the reporting
> engine work directly with the data model (via SQL).
And how is going to read this mighty report? That's either data warehousing, or you end up with a thousand pages report (hundred of thousand of object, let's say you are using small chars and can fit 100 rows per page).
> Heck, if all your data access is via walking the
> object graph, why not dump the relational database
> altogether and use an OODBMS, or something like
> Prevayler? No more impedance mismatch.
OODBMS have failed so far. Prevailer is memory based if my memory serves me well.
> The problem is not what you're losing; the Hibernate
> Query Language is very expressive. The problem is
> what you're GAINING, which is an extra layer between
> your reporting engine and the database. When a report
> is taking a long time to generate, the original
> article author would have to determine how HQL is
> handling the query AND how the database is handling
> the query. For performance tuning, they're BOTH
> relevant.
>
> If possible, why not cut out the middleman?
>
> I have to admit, I'm a bit mystified that you had
> taken my original comments so personally. Do you work
> with John Ferguson Smart?
No, not at all
It's just that lately I've decided to code an application using pure JDBC and I've been burned, and burned badly. The application takes a lot more to develop (the persistence code is 5 times bigger than the equivalent HQL one) and to get decent performance I usually resort to write more or less the same queries that Hibernate would write, with the aggravation than I have to write all those damn outer joins by hand, the code is not type safe and in my opinion less readable. With Hibernate I get batch updates and prepared statement caching for free, with JDBC I have to write that support by hand.
On the other side I have a workflow application that uses Hibernate. The workflow model designer treats workflows as documents, but in the end they are save in 20 some tables.
I don't even want to think about the amount of code I would have to write in order to load and save the workflow definition in an object graph (keeping it into rowsets would not be funny).
So, in the end I'm very happy to deal with detached object, collections and lazy loading. To me is much easier than handling the ton of details I have to deal with using pure JDBC. But that's just my opinion