Big subject, easy question. I have an HQL query that looks like this:



The "Category" reference is to an object that defines a category hierarchy (each record has a reference to its parent and top level categories have NULL parents). This query allows you to search for a business assigned to category 10 and as long as 10 is a top level, first level or second level category, the business will be returned. However, the query *must* be an outer join. Likewise, I only want to pull back online businesses (an online businesses is set by a discriminator value in a BUSINESS_LOCATION table). Hence, the "location.class" reference.

Using the current 2.1 Criteria API, is there any way to mimick this functionality? The search we're doing would be a perfect use for it, but I'm stumped.

Thanks,
James[/code]

I don't think so, only because you can't define an alias in setFetchMode(), and you can't use an outer join in createCriteria() or createAlias().


It would not be at all hard to have overloaded versions of createCriteria() or createAlias() - very trivial, in fact .... but I'm trying really hard to not complexify the Criteria API (it is meant to be the "simple, user friendly" option).

What!? ;)
I'm all for simple and user friendly... however, I am of the opinion that the Criteria API is where the real abstraction power lies and that no matter how great and powerful the HQL is it is still a query language and that forces developers to stop writing Java code and start writing 'X' Query Language (in this case 'H') code and I don't really think that is "clean" seperation.
Don't get me wrong, I'm not knocking what you guys have made with HQL, it is impressive, and certainly mainy times better than raw SQL and playing with result sets. Certainly some people will want to, or even need to, use a query language but I personally I think it is best to keep the two worlds completely seperate and use a purely Java/OO approach, thus the reason for O/R mapping.

Long live the Criteria API! ;)

I'm new here, so take my opinion for what it's worth (i.e. not much ;) ).

I'd like to voice my complete and whole-hearted agreement with DavidNDuffy. I've just begun evaulating Hibernate and I'm already getting headaches from the HQL.

Don't misunderstand; I'm sure it's a wonderful and powerful tool that I'll learn to like, but at the moment it's giving me nightmares. I've been writing SQL code for 8 years now and the fact that HQL uses syntax that is "exactly" like SQL and yet works with completely different entities and does completely different things than SQL is making my learning curve a little steeper than I would like.

It's very difficult for me to remove myself from a SQL-context when I see syntax like "left join". When I see those words my brain automatically jumps to SQL-mode and I have to really struggle to make myself step back and look at the statements from a different perspective. As odd as it may sound, for me personally (and a few other folks on my team with lots of SQL experience), it would have been easier to get a grip on HQL if it were completely different from SQL instead of just like it.

Anyway, just my 2 cents.

Regardless of our struggles in learning some of the tools idiosyncrosies, Hibernate is already saving us a ton of time. It's a great tool! We owe a huge thank you to the Hibernate Team.

-Watter

P.S. oh, and "LONG LIVE THE CRITERIA API!"

As much as it pains me to agree with David Duffy I alas have to concede.

With that out of the way, is there any plan for support of Outer Joins in the Criteria API at this date? (The original post is ancient so alot could have changed by now)

I've traced a bit through the source code to the following in the getJoin method in the CriteriaLoader.



which essentially forces an Inner Join as soon as it is seen in the aliasByAssociationPath HashMap in the CriteriaImpl (which gets populated as createAlias and createCriteria methods are called).

Has anyone out there implemented this already (or is it planned) before I go ahead and hack it up for us as it would be a very handy feature.

We've dumped a layer on top of the criteria API such that our developers aren't tasked with handling sessions, transactions, etc.

They simply build up a list of Criterion and then pass it off to super class method that build up a session, creates a criteria, dump the criterion on, executes the fetch and then cleans up after itself. History has taught us that developers are not to be trusted with database resources :)

Additionally, the Criteria API tends to be very strong in allowing us to handle dynamic queries that need to get built up depending on parameters that a user has specified without having to jack around with String manipulation.

So, anyone? :)

Digressing ever so little from the thread's subject an issue i see with the criteria API is regarding polymorphic queries for association classes. Among the criteria enthusiasts I hope someone has workarounds for this.

Here are the demo classes and their members.



the relationships are as


now the ONLY way i can find all children of Parent which match a certain value for 'location' seems to be as follows (note that 'location' is not on the Child class but in the subclass SubChild)



and all of the following attempts (which i think are very valid usage patterns), do not work



Eventhough this can be somehow made to work in HQL but the extensibility I need for various types of queries that are formed in my app, is only available through the criteria API.

There has to be way for each createCriteria(associatoinPath) to retrieve not just the associated class but also all of it's subclasses.

If anyone has already got around to hacking at CriteriaImpl please let us know. Gavin if you already have a prototype that's not yet part of the product, we'll be glad to use it :-)

thanks - for bearing with me till here
ravi

"Typecasts" are not supported in Hibernate 2.1.

This code *does* work in HIbernate3 branch, I think.

sweet. is this functionality already in CVS or still on the drawing board? i'll try with latest from cvs and retry my tests. i see that Steve E is handling all the work on criteria. can he chip in here (if i knew his austin address, i would walk over there :-) )

how was the top part of this thread solved? gavin says it would be "trivial" to make a custom createAlias... but I'm pretty new at this and would love to know if anyone implemented the criteria/alias/outer join problem. or could someone point me in the right direction on how to solve it?

thanks!!

maulin

It is good to have a simple API as long as it simplify the use of it
but if simple means you cannot use it I have to agree with the others.
For now this just makes it even more complicated.

N.B. Based on version 2.1.4
Here is what I got and tried after reading the previous postings.

I tried to replace

if ( criteria.isJoin(path) ) {
return JoinFragment.INNER_JOIN;
}

by

if ( criteria.isJoin(path) ) {
FetchMode fm = criteria.getFetchMode(path);
if ( fm==FetchMode.EAGER ) {
return JoinFragment.LEFT_OUTER_JOIN ;
}
return JoinFragment.INNER_JOIN;
}

and I used criteria.setFetchMode(<association>, FetchMode.EAGER);
setting the association to match the <path> above.

It worked for some cases, but I ended up having problems
in OuterJoinLoader.walkAssociationTree with the line

boolean enable = (joinType==JoinFragment.INNER_JOIN) || ....

So I ended up with

if ( criteria.isJoin(path) ) {
FetchMode fm = criteria.getFetchMode(path);
if ( fm==FetchMode.EAGER ) {
return 99;
}
return JoinFragment.INNER_JOIN;
}

and

boolean enable = (joinType==99) || joinType==JoinFragment.INNER_JOIN) || ...

if (enable) {
if (joinType == 99)
{
joinType = JoinFragment.LEFT_OUTER_JOIN;
}
...

There is surely a better solution, but if it helps anyone.
Daniel

Isn't there any other solution to solve this problem ?
Does hibernate 3 can solve this ?

I don't think this forum post has a JIRA issue (though I recall seeing some over there). Here I filed one:

http://opensource2.atlassian.com/projec ... e/HHH-1005

Just in case anyone wants to try and convince Gavin of implementing a common requirement ;) (put a vote or something).

The Criteria API is excellent for creating advanced search functionality in applications--it is the backbone for creating lists of entities in numerous locations in our application. I think it is one of the most important features in the Hibernate library, primarily because it lets developers create variable SQL that executes in a PreparedStatement. That alone is a great security enhancement, thwarting SQL injection attacks. Implementing advanced search using string-based queries would be much more involved.

The only limitation that I have seen thus far is the inability to create subcriteria and perform sorts that are based on outer joins. I agree with the other posters that there are many use cases where outer joins are necessary.

For example, if a Task entity has an assignee property, the assignee could legitimately be NULL on unassigned Tasks. My advanced search screen should let the user sort by assignee name, displaying both the assigned Tasks and the ones that aren't assigned. Because of the limitation in the API, sorting won't work as users expect.

So, with that said, could this be added as a feature in Hibernate? I don't think the Criteria API should be considered a second-class citizen to HQL. Both have their place, with one more appropriate than the other depending on the use case. It would be very valuable to me in my application. I'm sure it is a feature that others would find valuable, too.

If others agree, please chime in.

Thanks

Hey, you can post a reply here:

http://opensource2.atlassian.com/projec ... e/HHH-1005

I'm trying to get Gavin to reopen this issue and reconsider.

If others think this would be a valuable addition, vote for the issue here:

http://opensource2.atlassian.com/projects/hibernate/browse/HHH-1166