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

Right, that is not implemented.


A join, in your case a selfjoin. :)


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.



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

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

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

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

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)

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

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.

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

In my case I have this code and i didn't find a solution without the subquerie in FROM clause



bellow the code that works in SQL



anyone has a solution??