Hi,

I have two tables defined as

(i)Table1 having columns: id, date_received, message
(ii) Table2 having columns: id, date_sent, message, status

I would like to join the these two tables(no relationship) as follows:

select id, date_received as date, message from Table1
union all
select id, date_sent as date, message from Table2


I have checked on the Hibernate online reference manual on the HQL but is not mention how a union can e done in Hibernate.

Would appreciate if anyone can assist on this.
Can Hibernate support on UNION ALL?
How do I define UNION at table1.hbm and table2.hbm files?
How can I return the date_received and date_sent as date? where can I define date?In table1 or table 2 entity bean class?
The result is returned to which entity class bean since it unions 2 tables?


I am using dao to execute this query.

Please guide. Thank you!

You should set this up as an SQL named query. It will look something like this:

Hi,

Thanks for the feedback.

But am working on a POJO framework. Am acutually executing the query statement using

DAO.find("select id, date_received as date, message from Table1
union all
select id, date_sent as date, message from Table2");

but somehow it didn't work.

The given example of the SQL query statement as above is required to be defined in a new .hbm file or at the table1.hbm or table2.hbm?
If it is defined as SQL query, how can I called for the SQL statement from DAO?

Please assist.Thank you!

You need to call createSQLQuery instead of createQuery.

Maybe you had to add another method to you framework like DAO.SQLfind
or
a parameter signaling that it's a SQL query or a HQL query.

Hi All,

Am pretty new to this Hibernate. If anyone can assist me , I would appreciate that! I have been looking around for a solution and trying to understand how it works but I fail.

All the while am using DAO.find() to call for any select statements for Hibernate query. How the hibernate and Dao can communicate using UNION query?

I have defined 2 tables as entity bean and which class the esult should return?

Please guide.Thank you!

What the hell do you call DAO?

No need to get snippy. If you can't say anything nice, don't say anything at all.

Meileng, where did you get "DAO.java"? It's not part of hibernate. You should probably read the hibernate reference docs to figure out how to write HQL queries, how to set up mapping files, what the difference between a query and an sqlquery is, etc.

Two things

1. What the hell means "to get snippy" exactly? (I'm not an native English speaker)
2. Whatever you ask maileng insist not talking about their DAO.

Take it easy and peace to everybody.
Come on, this is not a kindergarten. If I were talking with one of my mates the question wouldn't had been much more different.

Will it be more correct to ask?:
What the hell do you name DAO?

"Getting snippy" means to make snide remarks, not direct insults but remarks intended to be insulting. Much like "what the hell". That phrasing is rude and uncalled-for. Perhaps it's just that you're not a native English speaker and don't realize it, but it is rude.


Meileng (why did you highlight the incorrectly-spelled name?) can ask about his/her DAO, this is the hibernate board, after all. I'd also appreciate not being told what I can and cannot ask people that I'm trying to help.


Yes, it's not kindergarten. It's also not one of your mates, and you should phrase things more politely than you would with your mates. Manners, as my grandma used to say, maketh the man.


I don't know what would be more correct, because I don't know what you're trying to ask. The sentence "What the hell do you name DAO?" doesn't mean anything. I think perhaps you're trying to ask "What are kind of class are you calling a DAO?", or something like that.

Anyway, it looks like you've scared off Meileng, so it's no longer important.

I suppose it's a cultural clash. What the hell is very low rude language doing direct translation to my language (well, rude for three years children).
How can I ask "What do you call DAO?" but expressing surprise, astonishment.

Really sorry I were misunderstood. I wanted to say (look the comma):
Whatever you ask, meileng insist not talking about their DAO.
(maileng is simply a keyboard mistake; I tend to use bold in user names, I didn't want to remark a bad-spelled name).

I wanted to say colleague instead of mate (another language false friend)

Well, I really doubt I've scared off Meileng.

Hi Pepelnm & Tenwit,

Am so sorry that my question has caused such a quarel between you both.

To be frank Pepelnm, I find it abit rude and harsh of your word "hell".I am not a native English speaker as well. Such word shouldn't be up for the public and I think is resonable if it is used between friends or mates. I am abit dismotivated since your reply.

Or maybe is my fault to cause you saying such words to me, being not well knowledgable enough to post such a question. Am totally new to Hibernate and there are times I miss understand some concept in it and people might think my question is kind of silly. Am sorry Pepelnm if my question sounds unprofessional and cause you an annoy.

Tenwit, thank you for your support and I hope you are not angry with Pepelnm. We are all from different cultures, and I think Pepelnm didn't mean it to be snippy or harsh of his words. Is just misunderstanding of different cultures.

I really hope the little quarrel between Pepelnm and Tenwit can be over. After all this is just a forum for us to share information and helping each other to gain interest in Hibernate.

Am here to seek help and learn new knowledge and not to cause trouble of such a small question.

But anyway Pepelnm and Tenwit, thank you for your help and support. Cheers!

Back to my question:

I noticed there are two ways of doing it (correct me if am wrong).

(i) Tenwit has proposed the solution to include :

<sql-query name="FindDatedMessages">
<return alias="dtdMsg" class="eg.DatedMessage"/>
SELECT table1.ID AS {dtdMsg.id},
table1.date_received AS {dtdMsg.date},
table1.message AS {dtdMsg.message}
from table1
union all
select table2.ID AS {dtdMsg.id},
table2.date_sent AS {dtdMsg.date},
table2.message AS {dtdMsg.message}
from Table2
</sql-query>

I am not sure, where to defined this sql-query as it returns the result to the class eg.DatedMessage. Do I need to define a new DatedMessage entity class and define the sql-query in the mapping file which is DatedMessage.hbm? But DatedMessage is not a table exist in the database? Also, table1 and table2 has no relationship and they are independent classes and not subclass of each other. Am confused with the concept, Tenwit.

(ii) Peplenm has proposed a solution to use createSQLquery():
As what I can understand, instead of using getNamedQuery(“FindDatedMessages”) for the defined sql-query as above, we can use createSQLquery().

Here is the Hibernate DAOSupport class defined to implement DAO support. The defined function is as follows:

public List findMessages() throws DAOException {
List result=dao.executeFind(new HibernateCallback(){
public Object doInHibernate(Session session) throws
HibernateException, SQLException {
String sql = " select t1.id as {t1.id}, t1.date_received as {t1.date_received}, t1.message as {t1.message} from table1 t1 " +
" union all "+
" select t2.id as {t2.id}, t2.date_sent as {t2.date_sent}, t2.message as {t2.message} from table2 t2";
List l = session.createSQLQuery(sql)
.addEntity("t1", eg.table1.class)
.addEntity("t2", eg.table2.class)
.list();

return l;
}
});
return result;
}

Am having a difficulty here. How can I define the sql to union two tables(I think I define it wrongly) and where can I return the union result?


Is the soluions in (i) and (ii) need to be combined? Which way is better?

Please guide. Thank you!

The query created by getNamedQuery where the named query is defined in an sql-query element will be the same as one that's been created by in-code strings and createSQLQuery. Either will work, depending on what you prefer. My preference is that all queries should be in XML files, because they don't have to be recompiled/redeployed if the query needs to change.

You do need a class to hold the objects returned from the query. I called that class DatedMessage, but you should use whatever class you were intending to use.

Hi Tenwit,

If I create a new class(given example is DatedMessage) to hold the returned result query, do I need to create DatedMessage.hbm file and define the sql-query in DatedMessage.hbm?

Also in the DatedMessage.hbm, do I need to defined union-subclass for table1 and table2?


Please guide.Thank you!

You can put the new class mapping and query anywhere you like. There's no requirement to create a new .hbm.xml file if you don't want to. You don't have to put the mapping and the query together, but it's a good idea to do that.

You don't want to use union-subclass. That's for implementing polymorphism, where a class' subclasses are mapped to different tables. The only place you use the word "union" is in the query.

Hi Tenwit,

I have tried to defined a new class DatedMessages and a new mapping hibernate file which is named DatedMessages.hbm.xml

here are the DatedMessages.hbm.xml file defined:
=================================

<?xml version="1.0" encoding='UTF-8'?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
<hibernate-mapping>
<class name="eg.DatedMessage">
<id name="id" column="id" type="int">
<generator class="assigned"/>
</id>

<property name="message" column="message" type="string" />

<property name="date" column="date" type="string" />
<sql-query name="FindDatedMessages">
<return alias="dtdMsg" class="eg.DatedMessage"/>
SELECT table1.ID AS {dtdMsg.id},
table1.date_received AS {dtdMsg.date},
table1.message AS {dtdMsg.message}
from table1
union all
select table2.ID AS {dtdMsg.id},
table2.date_sent AS {dtdMsg.date},
table2.message AS {dtdMsg.message}
from Table2
</sql-query>

</class>
</hibernate-mapping>
==================================


And I ended up having a problem to start up the server returning the error as below:

[ERROR] XMLHelper - Error parsing XML: XML InputStream(27) The content of element type "class" must match "(meta*,subselect?,cache?,synchronize*,comment?,(id|composite-id),
discriminator?,natural-id?,(version|timestamp)?,
(property|many-to-one|one-to-one|component|dynamic-component|properties|any|map|set|list|bag|idbag|array|primitive-array|query-list)*,
((join*,subclass*)|joined-subclass*|union-subclass*),
loader?,sql-insert?,sql-update?,sql-delete?,filter*)".


If I don't define a new DatedMessage class and hb.xml file to hold the sql-query, then is it possible for me to define the sql-query at the table1.hbm.xml file or table2.hbm.xml file?

Can you please provide me further details. Am totally lost n stuck :(

Please guide.Thank you!