Hi,

I have tried to defined the following:

<hibernate-mapping>
<class name="mlisting.entity.DatedMessage" mutable="false">
<subselect>
SELECT a.Id AS id,
a.date_sent AS date,
a.message AS message,
FROM table1 AS a
UNION ALL
SELECT s.Id AS id,
s.date_received AS date,
s.message AS message,
FROM table2 AS s
</subselect>
<synchronize table="table1"/>
<synchronize table="table2"/>

<id name="id" type="int"/>
<property name="message" type="string" />
<property name="date" type="string" />
</class>
<sql-query name="SMSInbox">
<return alias="sms" class="mlisting.entity.SMSBox"/>
SELECT a.Id AS {sms.id},
a.date_sent AS {sms.date},
a.message AS {sms.message},
from table1 a
union all
select s.Id AS {sms.id},
s.date_received AS {sms.date},
s.message AS {sms.message},
from table2 s
</sql-query>
</hibernate-mapping>


I tried to query the SQL from DAO using

List l = session.getNamedQuery("SMSInbox")
.setString("subscriber_id", subscriber_id)
.list();

But still I didn't work. It returned the same result which am not expecting to.

Please guide.Thank you!

No, you don't need the query with that mapping.

Also, I suppose you only need DatedMessage class, no need for SMSBox class.

subscriber_id ????????

Something like: ???

Hi,

I tried this before using createQuery() but it didn't work for UNION.


Sorry I provide the wrong info. here is the correct info:
<hibernate-mapping>
<class name="mlisting.entity.DatedMessage" mutable="false">
<subselect>
SELECT a.Id AS id,
a.date_sent AS date,
a.message AS message,
FROM table1 AS a
UNION ALL
SELECT s.Id AS id,
s.date_received AS date,
s.message AS message,
FROM table2 AS s
</subselect>
<synchronize table="table1"/>
<synchronize table="table2"/>

<id name="id" type="int"/>
<property name="message" type="string" />
<property name="date" type="string" />
</class>
<sql-query name="SMSInbox">
<return alias="sms" class="mlisting.entity.DatedMessage"/>
SELECT a.Id AS {sms.id},
a.date_sent AS {sms.date},
a.message AS {sms.message},
from table1 a
union all
select s.Id AS {sms.id},
s.date_received AS {sms.date},
s.message AS {sms.message},
from table2 s
</sql-query>
</hibernate-mapping>


I tried to query the SQL from DAO using

List l = session.getNamedQuery("SMSInbox")
.list();

Anyway to resolve it by not upgrading MySQL 4.1 to 5.0?

:(

Do you get what i mean?

Hi Peplnm,

I got what you mean.Thanks!

To make it more constructive, if I need to include a WHERE statement
as below,

<hibernate-mapping>
<class name="mlisting.entity.DatedMessage" mutable="false">
<subselect>
SELECT a.Id AS id,
a.date_sent AS date,
a.message AS message,
FROM table1 AS a
where a.username_id = :username_id
UNION ALL
SELECT s.Id AS id,
s.date_received AS date,
s.message AS message,
FROM table2 AS s
where s.username_id = :username_id
</subselect>

<synchronize table="table1"/>
<synchronize table="table2"/>

<id name="id" type="int"/>
<property name="message" type="string" />
<property name="date" type="string" />
</class>
</hibernate-mapping>

I tried to call the query in such way by passing the username_id parameter:

String hql = "from mlisting.entity.DatedMessage";
List datedMessages = session.createQuery(hql).
.setString("username_id", username_id);
list();

But it didn't work.

Also even if I don't pass in the username_id parameter, the result returned with the defined <subselect>is still as below

id message date
1 XXXX 12/06/2005
2 XXXX 13/09/1998
3 XXXX 14/09/1999
1 XXXX 12/06/2005
2 XXXX 13/09/1998

Instead of

id message date
1 XXXX 12/06/2005
2 XXXX 13/09/1998
3 XXXX 14/09/1999
1 ABC 14/08/1999
2 ABC 13/07/1980

Am afraid that I need to upgrade to MySQL5.0 :(

Please guide.Thank you!

You can't parameterized <subselect>.

I have modified the query to include username_id and to have a true id/primary key.





Don't forget to add type and username_id properties to DatedMessage class.

Your DatedMessage class must override equals() and hashCode() to implement composite identifier equality. It
must also implements Serializable.

Maybe the problem with tenwit SQL query approach was the lack of a true id/primary key.

A possible implementation:

Hi,

Thanks for your possible solutions.

Am abit confused. Hope you don't mind.

Where do I define equals() and hashCode() to implement composite identifier equality. Is in the DatedMessage class or somewhere else?

Thanks

you got it right!
In the DatedMessage class.

Hmmmm.....

The hashCode() which you have defined is supposed to return int but it returned a boolean.....

Sorry.
Corrected code.

Hi Peplmn,

The exact query which I suppose to execute is as below:

======================================
<!-- <sql-query name="DatedMessage">
<return alias="dtdmsg" class="mlisting.entity.DatedMessage"/>
SELECT a.Id AS {dtdmsg.id},
a.date_sent AS {dtdmsg.date},
a.message AS {dtdmsg.message},
a.sender AS {dtdmsg.sender},
a.recipient AS {dtdmsg.recipient}
from table1 a where a.recipient = :subscriber_id
union all
select s.Id AS {dtdmsg.id},
s.date_received AS {dtdmsg.date},
s.message AS {dtdmsg.message},
s.sender AS {dtdmsg.sender},
l.subscriber_id AS {dtdmsg.recipient}
from table2 s, Listing l
where l.subscriber_id = :subscriber_id and l.id = s.listing_id
</sql-query>-->
======================================

In table table2 I have a condition
where l.subscriber_id = :subscriber_id and l.id = s.listing_id

I wonder how can I define in the <subselect> based on your sample but don't think is correct :(


I tried this:

<hibernate-mapping>
<class name="mlisting.entity.DatedMessage" mutable="false">
<subselect>
SELECT a.Id AS id,
'S' AS type,
a.date_sent AS date,
a.message AS message,
a.sender AS sender,
a.recipient AS recipient,
a.recipient AS username_id
from table1 a
union all
select s.Id AS id,
'R' AS type,
s.date_received AS date,
s.message AS message,
s.sender AS sender,
l.subscriber_id AS recipient,
l.subscriber_id AS username_id
from table2 s, Listing l
where l.id = s.listing_id
</subselect>

<synchronize table="table1"/>
<synchronize table="table2"/>

<composite-id>
<key-property name="id" type="int"/>
<key-property name="type" type="string"/>
</composite-id>

<property name="message" type="string" />
<property name="sender" type="string" />
<property name="recipient" type="string" />
<property name="date" type="string" />
<property name="username_id" type="string" />
</class>
</hibernate-mapping>


I have defined getter and setter method for type and username_id in DatedMessage class.

I have also defined the hashCode() and equals() in DatedMessage class but seems like I can't even start the server after compiling it. The server returned having a problem of HbmBinder.bindCompositeId(Element, RootClass, Mappings, Map) line: 417

Please guide.Thanks

post DatedMessage class.

but between [code] [/code] tags please

Here it goes:




Thank you!