Win a copy of Microservices in Action this week in the Web Services forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Devaka Cooray
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Knute Snortum
  • Junilu Lacar
  • paul wheaton
Saloon Keepers:
  • Ganesh Patekar
  • Frits Walraven
  • Tim Moores
  • Ron McLeod
  • Carey Brown
Bartenders:
  • Stephan van Hulst
  • salvin francis
  • Tim Holloway

Squeezing Four HQL Queries Into One  RSS feed

 
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Ranchers,

I have a database for a maritime app that maps a shipping Movements table to a Berth table to a Port table.  One of the queries I need to perform is for displaying a list of vessel movements for a particular port.  There is no foreign key in the movement table that directly connects movements with a port.  Instead movement records contain an originBerth and/or destinationBerth key, and the berth record in turn contains the foreign key to a port.  Currently my application builds a list of movements to a port by running four separate queries:



This is not efficient.  Firstly, I must weed out any duplicate records (some movement records contain both originBerth and destinationBerth for the same port - an internal movement).  Secondly, I have four seperately ordered lists when I really want a master list, ordered as a whole.  And, with a single query, I would no longer have to be concerned with movement type: inbound, outbound, other (I simply want a list containing all types).

So, I had a crack at squeezing these four queries into a single query, nicely ordered and without duplicates:



Unfortunately, this query returns no results - it doesn't work.  I would appreciate some direction on forming an HQL query where the foreign key for a berth may be in one or both fields : originBerth or destinationBerth.

Thanks in advance,

Chris.
 
Christopher Dodunski
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It seems that inner joins are not usable in this situation.

[Movement] entities are related to [Berth] entities which are related to [Port] entities.  But, movements are related to berths via one of two fields: originBerth or destinationBerth.  The relationship between berths and ports is simpler - just a single foreign key in the berth table.

Below is my latest attempt at extracting movements associated with a particular port (either a ship is berthing or it is sailing).  I stripped away all the explicit join syntax.  It too returns no results, even though the movement table contains a number of movements to or from a berth at the port.



Playing with adding parantheses made no difference.



I've found no example HQL which deals with table relationships via more than a single foreign key column (as mentioned, the movement table contains an origin berth column and a destination berth column).

Any help with forming the correct HQL would be much appreciated.
 
Rancher
Posts: 3767
40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Are you able to turn up the debugging (either DEBUG or TRACE, can't remember which) for hibernate to see what SQL it is generating?
 
Bartender
Posts: 19996
95
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is this really HQL or is it JPQL?

To the best of my knowledge, whether or not your physical database schema has foreign keys defined has nothing to do with whether or not you can use the JPA joining attributes (@OneToOne, @OneToMany, @ManyToMany, etc.). In fact, foreign keys aren't even actually part of SQL itself. Or, for that matter, even primary and secondary keys.

Keys (indexes) are meta-information used by physical DBMS's to enhance performance on searches that are commonly done. There's nothing in the SQL language that cares whether a SELECT item was ever defined in DDL as a key or not, much less primary, secondary, foreign or even part of a compound key. SQL is an abstract language, not a physical one and in the abstract world, performance is not a consideration.

So if it's practical, try ditching all the explicit joins and annotate the Entity classes the way they are used, not the way that the schema says they are. You'll probably end up with the same underlying SQL, but simpler application code.

And, of course, if performance is a concern, get the DBA to define the foreign keys in the physical schema. Preferably after you have your code working so that the real-world operation can be better tuned.
 
Christopher Dodunski
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:Are you able to turn up the debugging (either DEBUG or TRACE, can't remember which) for hibernate to see what SQL it is generating?



Hi Dave, thanks for responding.  I've copied the below from my IDE's output window.  It doesn't resemble the typical commandline SQL I might type to query MySQL directly, but perhaps it gives what you ask.  This is what gets triggered when I click the HTML button for displaying shipping movements associated with a particular port.

 
Christopher Dodunski
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Holloway wrote:Is this really HQL or is it JPQL?

To the best of my knowledge, whether or not your physical database schema has foreign keys defined has nothing to do with whether or not you can use the JPA joining attributes (@OneToOne, @OneToMany, @ManyToMany, etc.). In fact, foreign keys aren't even actually part of SQL itself. Or, for that matter, even primary and secondary keys.

Keys (indexes) are meta-information used by physical DBMS's to enhance performance on searches that are commonly done. There's nothing in the SQL language that cares whether a SELECT item was ever defined in DDL as a key or not, much less primary, secondary, foreign or even part of a compound key. SQL is an abstract language, not a physical one and in the abstract world, performance is not a consideration.

So if it's practical, try ditching all the explicit joins and annotate the Entity classes the way they are used, not the way that the schema says they are. You'll probably end up with the same underlying SQL, but simpler application code.

And, of course, if performance is a concern, get the DBA to define the foreign keys in the physical schema. Preferably after you have your code working so that the real-world operation can be better tuned.



Hi Tim, thank you for responding.

I'd assumed, rightly or wrongly, that under the hood JPA annotations were still reliant on the database's primary and foreign keys for establishing relationships.

Below I've pasted the relevant annotations for each of these three entities (aka tables).



 
Christopher Dodunski
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
An observation: On the face of it, it appears that the SQL generated by Hibernate doesn't accord with the intent of my HQL.  Either the originBerth or the destinationBerth field of a Movement may be null, hence the reason I use "OR" in my HQL.  Is the SQL expecting that they must have values?
 
Tim Holloway
Bartender
Posts: 19996
95
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Christopher Dodunski wrote:I'd assumed, rightly or wrongly, that under the hood JPA annotations were still reliant on the database's primary and foreign keys for establishing relationships.



Well, you see, that's the thing. JPA has to generate SQL and SQL doesn't know nor care about keys. They're logically invisible to SQL. All of the key-related stuff that gets encoded is DDL, not SQL.*

Now, without a well-tuned set of keys, performance on a query may suffer horribly, but there's no difference in the syntax of the SQL itself.

Give it a whirl and let us know how it goes.

====

*Note that there is one case where JPA would care, and that's when you have it set up to generate missing schema parts. In such a case, JPA would then have to generate DDL. Not only for relationships, but also for the tables and columns themselves.
 
Christopher Dodunski
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Holloway wrote:
So if it's practical, try ditching all the explicit joins and annotate the Entity classes the way they are used, not the way that the schema says they are. You'll probably end up with the same underlying SQL, but simpler application code.


Hi Tim,

My HQL below contains no explicit joins, but seemingly the dot notation in the 'where' clause causes Hybernate to generate SQL with joins anyway (re my reply to Dave above with the SQL output).

My first concern is forming the correct HQL, given that the Movement entity is related to a port's berth via one or two fields: originBerth and/or destination Berth.  And typically one is null (only with internal berth to berth movements are both non null).  Internal berth to berth movements are rare, as usually a vessel goes to sea after loading/discharging at a single berth.  The trick seems to be with writing HQL that extracts movement records where either the originBerth belongs to a given port, or the destinationBerth belongs to a given port, but not necessarily both.  I've not found any HQL examples online with this type of 'OR' construct.

Once I get a working HQL, then I can look into performance improvements.

I appreciate your help.

Regards,

Chris.
 
Christopher Dodunski
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
 
Tim Holloway
Bartender
Posts: 19996
95
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Christopher Dodunski wrote:



I take it you figured it out then.
 
Christopher Dodunski
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Holloway wrote:

Christopher Dodunski wrote:



I take it you figured it out then.


Not yet.

The above HQL produces the below SQL.

How would you write HQL, Tim, to select movement records where EITHER originBerth OR destinationBerth - but not necessarily both - provides a match?
 
Tim Holloway
Bartender
Posts: 19996
95
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You have the right general shape, at least for what I understand is what you're looking for. And the HQL/JPQL looks good. But I don't like that it's generating cross joins. I think probably that you have some one/many-many/one annotations mapped in the wrong directions, but it's a bit late at night for me to be thinking straight.
 
Christopher Dodunski
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Holloway wrote:You have the right general shape, at least for what I understand is what you're looking for. And the HQL/JPQL looks good. But I don't like that it's generating cross joins. I think probably that you have some one/many-many/one annotations mapped in the wrong directions, but it's a bit late at night for me to be thinking straight.


Indeed, the joins are undesirable.  Evidently Hibernate is deducing the joins from the dot notation in the where clause: m.originBerth.port.id and m.destinationBerth.port.id.  The JPA annotations for all three entities are pasted above in this thread, but I can't find fault there.
 
Christopher Dodunski
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
SOLVED!!

For whatever reason, it seems Hibernate chokes with the 'OR' operator when the value to the left or to the right is a nullable foreign key.

The solution was to use explicit left joins in the HQL.

Below is both the HQL and the generated SQL.  Hopefully this proves useful to others who might encounter this unexpected behaviour from Hibernate.

 
Don't get me started about those stupid light bulbs.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!