I use Hibernate 3.1.1 and Oracle 10.
I have a table "customer" with an ordered collection (list) "comchannels" containing phone numbers, fax numbers, e-mail addresses and the like.
This is from the code of Customer.java:
Code:
/**
* Referenced com channels
* @hibernate.list cascade="all-delete-orphan" lazy="false" inverse="false"
* @hibernate.index column="comchannel_index"
* @hibernate.one-to-many class="ComChannel"
* @hibernate.key foreign-key="customer_comchannel_fk"
* @hibernate.key-column name="customer_id" index="customer_comchannel_i"
*/
public List getComChannels() {
return comChannels;
}
My original query is quite big, so I simplified it and made sure I ran into the same error as before. I'm trying to construct an object from a customer's name and the value of the customer's first "Fax" ComChannel.
Here's my HQL query:
Code:
select new MyObject(
customer.name,
(select comchannel.value
from ComChannel comchannel
where comchannel = customer.comChannels[0]
and comchannel.type = 'Fax'
)
)
from Customer customer
where customer.profession = 'Actor'
Hibernate generates the following syntactically incorrect SQL:
Code:
select customer0_.name as col_0_0_,
(select comchannel1_.value
from ComChannel comchannel1_ComChannel comchannel2_
where customer0_.id=comchannel2_.customer_id
and comchannel2_.com_channel_index = 0
and comchannel1_.id=comchannel2_.id
and comchannel1_.comchanneltype_id='Fax'
)
as col_1_0_
from Customer customer0_
where customer0_.profession='Actor'
As you can see, there is a comma missing in the FROM part of the subselect.
Apart from that, I'm asking myself why Hibernate uses two variables "comchannel1_" and "comchannel2_". Wouldn't one be enough? The SQL I have in mind goes like this:
Code:
select customer0_.name as col_0_0_,
(select comchannel1_.value
from ComChannel comchannel1_
where customer0_.id=comchannel1_.customer_id
and comchannel1_.com_channel_index = 0
and comchannel1_.comchanneltype_id='Fax'
)
as col_1_0_
from PersonBaseData customer0_
where customer0_.profession='Actor'
Login
FAQ
Search