• 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
  • Liutauras Vilda
  • Junilu Lacar
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Knute Snortum
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Ron McLeod
  • Carey Brown
Bartenders:
  • Paweł Baczyński
  • Piet Souris
  • Vijitha Kumara

JPA / Hibernate sort by the name of a referenced entity

 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello.

I have the following problem:
Entity Alpha has a @OneToMany reference to a Set<Beta>. The Beta entity has an attribute 'name', which is a String.
In the application we have a search UI to find entities of type Alpha. The result table show a colum 'current Beta' which presents the name of the latest/actual Beta in the Set.
Now, the result table should be sortable by this column.

This leads to the requirement of sorting Alphas by the Name of the Beta with this highest id in the database.

After trying lots of Criteria API things, I found this:
https://hibernate.atlassian.net/browse/HHH-256

It is excatly what I was trying to do, filter Betas in order by clause, return the name of the latest to order the sql result.. which is not going to work anyway

The sql which is generated by hibernate looks like this:


The code brings no Exception, but the sorting is always done on the first element of the set of BETA. So sorting does not work correctly when more then 1 BETA is in the Set.

Does anybody has an idea how to find a solution for this problem?

kind regards
Lars

 
Saloon Keeper
Posts: 21254
137
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I agree with Hibernate. It appears to be doing exactly what you told it to do.

So the question is - did you tell it to do the right thing?

You didn't mention what order the alpha records should be returned by other than in order of Beta.name. And in cases where there is more than one Alpha referencing the same Beta name, the order of alphas has not been indicated, so you've basically said you're OK with random alpha order.

I suspect that what you really want is something more like "ORDER BY alpha.id ASC, beta.name ASC".
 
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
My first impulse would be to minimize coding of SQL on the Java side at all, especially the more complex the joins, filter conditions and order by clauses become. Instead:
• Create a view on your database that encapsulates all of the SQL you need to write, and work out any relational/SQL problems such as the one you describe there, independent of Java, JPA or Hibernate.
• Call the view from Hibernate as simply as you would a single table with no joins, filters or ordering required, other than bind variables for filter condition/WHERE clause values that are not hard-coded into the view, such as a primary key ID.
 
Tim Holloway
Saloon Keeper
Posts: 21254
137
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Actually, that SQL is what Hibernate generated based on the @OneToMany linkage and @OrderBy and was supposed to give us a clue as to what was happening.

Though as I said, lacking further information on what is supposed to happen, I think Hibernate did exactly what it was told to do.
 
it's a teeny, tiny, wafer thin ad:
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!