Here's a little stumper for the Hibernate team. I posted this in another thread, but I think it got lost, so I'm re-posting it as its own topic.

How can I query joined subtypes using custom SQL (the createSQLQuery() method)? That is, suppose I have the query:


Hibernate will expand {subtype.*} to including both subtype and supertype columns. However, I have no way of knowing what name Hibernate is going to substitute for the supertype table.... How do I tell it to substitute {super} with the name of the joined supertype?

It looks like I can cheat as follows:


...but now I'm just presuming that Hibernate will always generate a name for the joined supertype in exactly the same way -- by suffixing "_1_" -- yuck! What I want is a way of telling hibernate how to substitute the name of the supertype table, just like I can already with the subtype.

Does this question make sense? Is there an answer yet?

(And yes, for various tricky reasons, we do need to use custom SQL and not HQL; yes, the subtype has to be in a separate table -- so there's no way around this question for us.)

Yeah the question makes sense, I think Max plans to implement support for joined-subclass mappings in createSQLQuery, but it is not there yet.

hmmm....we need to find a way to refer to those types without "forcing" the user to return them.

How would the syntax look for this ?

Any ideas are welcome ;)

How about:


select .... from persons {person}, customers {(Customer)person} where ....


where Customer is a <joined-subclass> of Person.


Just a suggestion. Its a bit verbose, perhaps.

well - it's hard to get nonverbose with this one ;)

Another one would be to provide the names in a second list to createSQLQuery like:


createSQLQuery("select .... from persons {person}, customers {customer} where ....", Person.class, "person", new Class[] { Customer.class, Employee.class }, new String[] { "customer", "employee" });

...this could also be used for defining scalar queries where you also want to seperate what you select and what you want to define a where clause on.

I'd rather keep the method signature simple. You know how I am about the Session interface.

I also like the "cast" like syntax better ;) Now I just hope it won't be to hard to parse ;)

well - now we are at it .....can we think up a good way to make scalar queries happen with createSQLQuery() ?

Its eaaasy to parse.

You know what I think of scalar SQL queries.


(Isn't there some other framework for that? Oh yeah ..... JDBC.)

;)

well - we can't use the class and property names in JDBC ....and if we had scalar sqlqueries we would be able to encapsulate the yucky stuff - all good in my book (and also in yours on blessed days ;)

We can't use class and property names in native sql queries either.


Show me some JDBC code that is much simpler using createSQLQuery.


;)

List l = s.getNamedQuery("scalarquery_name").list();

That's simple and encapsulates the underlying gory details - not possible today ;)