sql-query elements go outside the class element, not inside. This is why I earlier said "You don't have to put the mapping and the query together, but it's a good idea to do that." The query can go in its own mapping file, if you like, or in a file with loads of other queries. I prefer to put named queries in the file that defines the class that the query returns.

Can you please provide an example?

Thanks

Hi,

I have tried the way you have mentioned to me, but still it didn't work out.
"FindDatedMessage" cannot be identified.

By the way, I have defined the sql-query at the DatedMessage.hbm.xml file.
I wonder am I right to save the file as .hbm.xml?

Thanks

Yes, you are right to save it as .hbm.xml. Have you included it in your configuration? Your .cfg.xml file needs to include it via a mapping element.

Hi

I didn't configure those hbm.xml files at .cfg.xml file.Instead all my .hbm.xml files are defined at applicationContext.xml file as below:

<bean name="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="dataSource">
<ref bean="dataSource"></ref>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
<prop key="hibernate.show_sql">true</prop>
</props>
</property>
<property name="mappingResources">
<list>
<value>mlisting/entity/Subscriber.hbm.xml</value>
<value>mlisting/entity/Listing.hbm.xml</value>
<value>mlisting/entity/Alert_rules.hbm.xml</value>
<value>mlisting/entity/Seller_notification_msg.hbm.xml</value>
<value>mlisting/entity/Alert_notification_msg.hbm.xml</value>
<value>mlisting/entity/DatedMessage.hbm.xml</value>
</list>
</property>

</bean>

I have no idea why the "FindDatedMessage" couldn't be found :(


Am totally lost :( All the while all the defined entity class are defined in this applciationContext.xml file and it works fine.

Thank you!

Doublecheck the spelling and capitalization. The string you pass to getNamedQuery must be exactly the same as the name parameter of the sql-query.

Hi Tenwit,

It works now :) Thank you for your continuos support and patience on me.
I greatly appreciate it !

Any time. Good to hear that it works.

Hi Tenwit,

You have given alot of motivation to keep me goin. Thanks again :)

Hi Tenwit,

I have another question which have some relationship to the problem that I have voiced out.

I have defined the hibernate-mapping as below:

==============================
<hibernate-mapping>
<class name="mlisting.entity.DatedMessage">
<id name="id" type="int">
<generator class="assigned"/>
</id>

<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>
==============================

Table1 and Table2 has it's own primary key which is ID and is autogenerated starting from 1, 2,...etc.
Here are the data in table1 and table2

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


table2
id message date_received
1 ABC 14/08/1999
2 ABC 13/07/1980

When I execute the same query in mySQL console:
SELECT a.Id AS id,
a.date_sent AS date,
a.message AS message,
from table1 a
union all
select s.Id AS id,
s.date_received date,
s.message AS message,
from table2 s
the result return is

It returned the result as below:

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

But when the same query is executed from the defined hibernate mapping sql-query, the result returned is

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

The first two rowswith ID 1 and 2 from table1 have override the two rows from table2 which has the same unique ID 1, 2 as table1

This is not what am expecting to see.I would like to know is there anyway to resolve this problem? Am suspecting the defined
<id name="id" type="int">
<generator class="assigned"/>
</id>

at the hibernat-mapping.

If I don't defined in such a manner and replaced with
<property name="id" type="int" />

there will be a compilation error.

Please guide.Thank you!

If your mySQL is release 5 you could use a view.

Define a view with the UNION.

Then treat the VIEW from Hibernate as if it were the TABLE where mlisting.entity.SMSBox objects are stored.

Something like:



for mySQL:

Hi,

Occasionally, Am using MySQL 4.1. Is there any ways that I don't need to upgrade MySQL 5.0 to resolve this problem?

Thank you!

maybe this could work:
(no need for view)