Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 13 posts ] 
Author Message
 Post subject: Cannot use subqueries in from clause. Is there an alternativ
PostPosted: Sun Aug 14, 2005 5:47 pm 
Newbie

Joined: Sun Aug 14, 2005 5:29 pm
Posts: 6
Location: zurich switzerland
With hibernate I cannot use subqueries in from clause. I got an exception telling me that the token "(" is not expected. I read in this forum that hibernate is not implementing subqueries in from clause.

So my question ist: what is the alternative?

I have the following concrete problem:

- I have 1 table with column "from" and "to"
and I want to ask "from A to C" and that the db answers me
"segment A-B, segment B-C"

My SQL is like this:
select s1, s2 from (select * from segment where departure='A') as s1, (select * from segment where destination='C') as s2

How can I translate this SQL in HSQL??

thanks

_________________
-----------------------------------------------
Dr. Alain Hsiung
dipl ing ETH, SCEA, IT-Project+
Founder and CEO
Ideartis, Zurich, Switzerland
-----------------------------------------------


Top
 Profile  
 
 Post subject: Re: Cannot use subqueries in from clause. Is there an altern
PostPosted: Sun Aug 14, 2005 8:15 pm 
Expert
Expert

Joined: Thu Dec 04, 2003 12:36 pm
Posts: 275
Location: Bielefeld, Germany
hsiung wrote:
With hibernate I cannot use subqueries in from clause. I got an exception telling me that the token "(" is not expected. I read in this forum that hibernate is not implementing subqueries in from clause.

Right, that is not implemented.

hsiung wrote:
So my question ist: what is the alternative?

A join, in your case a selfjoin. :)

hsiung wrote:
I have the following concrete problem:

- I have 1 table with column "from" and "to"
and I want to ask "from A to C" and that the db answers me
"segment A-B, segment B-C"

My SQL is like this:
select s1, s2 from (select * from segment where departure='A') as s1, (select * from segment where destination='C') as s2

In SQL your query using a selfjoin looks like this.
By the way, this is definitely even faster than two subqueries in one select statement.
Code:
SELECT *
  FROM segment as s1
  JOIN segment as s2 ON (s1.destination = s2.departure)
WHERE s1.departure   = 'A'
   AND s2.destination = 'C'


hsiung wrote:
How can I translate this SQL in HSQL??

I've never tried to use a selfjoin in HQL, but I'm quite sure that this is possible like any other type of join.

Well, if don't get it, you could also use a native SQL query. You could even use your from-clause subqueries within native SQL.

See: Chapter 17. Native SQL
http://www.hibernate.org/hib_docs/v3/re ... /#querysql

Best regards
Sven

_________________
Please don't forget to give credit, if this posting helped to solve your problem.


Top
 Profile  
 
 Post subject: instead of subqueries in from clause: theta-style
PostPosted: Mon Aug 15, 2005 7:17 am 
Newbie

Joined: Sun Aug 14, 2005 5:29 pm
Posts: 6
Location: zurich switzerland
Thank you Sven!

your response helped me a lot.

You proposed a (self) join with join-condition ON-statement (ANSI-style) to circumvent the "subqueries in from clause" problem of HSQL.

I'm not the guru in SQL and HSQL, so I had to notice that the ANSI-style ON-statement in SQL join doesn't work in HSQL.
Exception with token "on" unexpected.

But a join in Theta-style works in HSQL. So, the solution is to write a (self) join in theta-style. here the Java code-snippet:

String oneStopQuery ="select new list(s1, s2) from Segment as s1, Segment as s2 where s1.destination=s2.departure and
s1.departure = :departure and s2.destination = :destination)";

NB: I want to translate SQL in HSQL just as an exercise. I want to know if it's just syntax or another way to think about queries.

Best regards
Alain Hsiung


Top
 Profile  
 
 Post subject:
PostPosted: Mon Aug 15, 2005 7:53 am 
Expert
Expert

Joined: Thu Dec 04, 2003 12:36 pm
Posts: 275
Location: Bielefeld, Germany
Well, just to be sure to not misunderstand you.
What do you mean by HSQL? Do you mean the HSQLDB (Java database) or HQL (Hibernate Query Language)?

HSQLDB supports joins using the ON clause and HQL does as well.
But you won't be able to use the keyword "ON" within a HQL query. In HQL you write queries using object-oriented information, not tables. The relations between the objects/classes are stored in XML mapping files (or annotations using Java 5). The SQL statement which is generated when executing a HQL query will contain an ON clause, but you won't use the ON clause within HQL itself.

Read the documentation to get more information about this.

Consequently, you'll have to adjust your mapping files and write a HQL query in order to get the result in SQL I wrote in my last posting.

Best regards
Sven

_________________
Please don't forget to give credit, if this posting helped to solve your problem.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Aug 15, 2005 1:15 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
HQL does not support from-clause subqueries because unlike SQL, HQL does not have the relational property of *closure*. ie. A query against an entity does not necessarily return an entity. (In SQL, a query returns a table.)


Top
 Profile  
 
 Post subject:
PostPosted: Fri Sep 02, 2005 7:32 am 
Newbie

Joined: Sun Aug 14, 2005 5:29 pm
Posts: 6
Location: zurich switzerland
gavin wrote:
HQL does not support from-clause subqueries because unlike SQL, HQL does not have the relational property of *closure*. ie. A query against an entity does not necessarily return an entity. (In SQL, a query returns a table.)


Hi Gavin,

thanks for your comment on from-clause subqueries.
But would it be possible to specify the notion of from-clause subqueries where the subqueries return an entity?
In my case it would work. I have only one table with the corresponding entity in the OO-Model and the 2 subqueries would return this table mapping to this known entity.

There is also a more practical problem with pure HQL: performance! I tried theta-style cartesian product in HQL (instead of from-clause subqueries that are not supported) and the query tooks 7 seconds. I tried the subqueries in native SQL and it tooks 30ms. My table has 1400 rows only. I will get around 500 000 soon..

Gavin King said "We can’t think of many good uses for subqueries in the from clause" in his book "hibernate in action". I would be happy if someone tells me how to make my query in HQL (approximately) as fast as the SQL from-clause subqueries. For now my SQL query is 250x faster than my HQL query...

Regards
Alain


Top
 Profile  
 
 Post subject:
PostPosted: Fri Sep 02, 2005 7:43 am 
CGLIB Developer
CGLIB Developer

Joined: Thu Aug 28, 2003 1:44 pm
Posts: 1217
Location: Vilnius, Lithuania
Probably you do not have index on foreign key if this stuff is faster than join (most databases do not create index for foreign key by default)


Top
 Profile  
 
 Post subject:
PostPosted: Fri Sep 02, 2005 8:09 am 
Hibernate Team
Hibernate Team

Joined: Mon Aug 25, 2003 9:11 pm
Posts: 4592
Location: Switzerland
SQL Tuning, Dan Tow, O'Reilly


Top
 Profile  
 
 Post subject:
PostPosted: Sat Sep 03, 2005 5:59 am 
Newbie

Joined: Sun Aug 14, 2005 5:29 pm
Posts: 6
Location: zurich switzerland
christian wrote:
SQL Tuning, Dan Tow, O'Reilly


did I missed the point of your reference to SQL?
I already optimize the SQL query and it performs well.
My problem ist not the SQL query but the HQL (or EBJQL) query which doesn't perform. I though that with Hibernate (and EJB 3.0) I wouldn't need to use SQL to perform well.
In the case of from-clause subqueries in HQL shall I really dig into SQL optimization?
I originally thought HQL (and EJBQL) would raise the abstraction to a level I do not have to care about SQL performance optimization. Am I wrong with this assumption? when is it true, when not?

Best Regards
Alain


Top
 Profile  
 
 Post subject:
PostPosted: Sat Sep 03, 2005 6:02 am 
Hibernate Team
Hibernate Team

Joined: Mon Aug 25, 2003 9:11 pm
Posts: 4592
Location: Switzerland
When you need a subquery in the FROM clause, as explained several times already. If you still want to optimize your query without resorting to guessing and ad-hoc optimization, read the book I've recommended.


Top
 Profile  
 
 Post subject:
PostPosted: Sat Sep 03, 2005 6:03 am 
Hibernate Team
Hibernate Team

Joined: Mon Aug 25, 2003 9:11 pm
Posts: 4592
Location: Switzerland
And, of course, HQL's job is to keep queries _portable_, not to "raise the level to avoid optimization".


Top
 Profile  
 
 Post subject:
PostPosted: Sat Sep 03, 2005 2:42 pm 
Newbie

Joined: Sun Aug 14, 2005 5:29 pm
Posts: 6
Location: zurich switzerland
christian wrote:
And, of course, HQL's job is to keep queries _portable_, not to "raise the level to avoid optimization".


Thank you for the precision. I understand the contraint.
Just funny that my first HQL queries finished in a dead-end (because of a limitation of HQL I needed SQL). But don't take it as a critic, I highly appreciate your job and that of Gavin. Fabulous!
Perhaps one last remark: my need for from-clause subqueries are real-world requirements from the transportation industry. I can think of many other good examples of from-clause subqueries. Your sentence "We can’t think of many good uses for subqueries in the from clause" was a bit irritating for someone who almost only need such queries... just in case you write a second edition.

Best regards and many thanks
Alain


Top
 Profile  
 
 Post subject: Re: Cannot use subqueries in from clause. Is there an alternativ
PostPosted: Wed Jul 13, 2011 10:38 pm 
Newbie

Joined: Wed Jul 13, 2011 10:18 pm
Posts: 2
In my case I have this code and i didn't find a solution without the subquerie in FROM clause

Code:
          "SELECT MAX(Soma) FROM " +
          "(" +
          " SELECT d.data,SUM(d.valor) AS Soma" +
          " FROM Dados d" +
          " JOIN d.idAlimentadorFk a" +
          " JOIN a.idConjuntoFk c" +
          " GROUP BY d.data,c.idConjuntoPk" +
          " ) WHERE tb2.data = :data");


bellow the code that works in SQL

Code:

          SELECT max(soma) from
          (SELECT data_dado,sum(valor_dado) as soma
          FROM dados INNER JOIN alimentador ON dados.id_alimentador_fk = alimentador.id_alimentador_pk
          INNER JOIN conjunto ON  conjunto.id_conjunto_pk = alimentador.id_conjunto_fk
          GROUP BY dados.data_dado, conjunto.id_conjunto_pk)tb2 WHERE data_dado = ('2011-01-01');


anyone has a solution??


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 13 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.