Hibernate version:
3.2.1
Name and version of the database you are using:
Oracle 10g
All I really want is for this HQL query to work as is. You'll notice it uses oracles left outer join syntax (+)= in the where clause for the user.contactid to the contact.id. This used to work in hibernate 2.1. We are in the process of upgrading to 3.2, which for whatever reason this functionality was removed. I understand you want to support the ansi standard, but why remove functionality that used to exist which supports Oracle? We aren't talking about a small time DB that no one uses.
The root of my problem is we have this overly complicated in house XML query template subsystem that pretty much requires a rewrite if this syntax isn't supported. We just don't have an easy conversion path for this one piece. Obviously my reasons for wanting this are selfish, but being objective, I can't see why this would be a bad thing, especially since it WAS there in 2.1.
select
contact.id, contact.statusCodeEnum, contact.personFirstName, contact.personLastName, contact.companyName, account.accountName, contact.phone, contact.email, contact.address.city, contact.VIPLevelDB, user.name
from
Contact as contact, User as user, Account as account
where
contact.contactTypeEnum = 0 and
account.ownerName = 'UK' and
contact.statusCodeEnum in (0) and
contact.upEmail like
'5APR11@SAMPLE.COM%' and contact.statusCodeEnum != 11 and
contact.statusCodeEnum != 12 and
contact.statusCodeEnum != 13 and
contact.statusCodeEnum != 14 and
contact.statusCodeEnum != 10 and
user.contactId (+)= contact.id and
contact.accountId = account.id
And yes, I know that if I want the above query to work, I need to convert it to this:
select
contact.id, contact.statusCodeEnum, contact.personFirstName, contact.personLastName, contact.companyName, account.accountName, contact.phone, contact.email, contact.address.city, contact.VIPLevelDB, user.name
from
Contact as contact LEFT OUTER JOIN User user on user.contactId = contact.id, Account as account
where
contact.contactTypeEnum = 0 and
account.ownerName = 'UK' and
contact.statusCodeEnum in (0) and
contact.upEmail like
'5APR11@SAMPLE.COM%' and contact.statusCodeEnum != 11 and
contact.statusCodeEnum != 12 and
contact.statusCodeEnum != 13 and
contact.statusCodeEnum != 14 and
contact.statusCodeEnum != 10 and
contact.accountId = account.id
As simple as that looks, it's gross difficult.
A different option, if you guys say, "No way in hell are we supporting Oracle's outer join". What about if I move the (+) support from 2.1 to 3.2 myself and build myself a custom hibernate.jar file? Where would that code be located, I just thought of this idea and haven't researched it at all. I also doubt that will fly at work, but, it's worth asking.