How can I get around this limitation on hibernate?

Question

I have 3 objects in a hierarchy, for example:

MyInterface | ----------------- | | Entity1 Entity2 

MyInterface is NOT displayed in Hibernate (because I am using an implicit polymorphism strategy to match this inheritance)

And, in fact, if I run a query like this:

 "FROM MyInterface" 

It works just fine (because it extracts all instances of Entity1 and all instances of Entity2, concatenates them and returns a List<MyInterface> ).

If we look at the SQL generated by Hibernate, it runs 2 independent SQL queries to first get Entity1 instances for another Entity2 instance, but I'm fine with that.

The BIG problem occurs when you try to do something like this:

 "FROM MyInterface ORDER BY someField" 

Because it applies ORDER BY to the first SQL query, and then the same ORDER BY to the second SQL query, and does not apply them to the WHOLE query (I know this because I see my own SQL queries launched by Hibernate).

This is obviously a sleep error.

How can I get around this to get Hibernate to apply ORDER BY to the whole query? (I cannot do this in memory, because later I will also need to add pagination).

Answer 1

I would say that the problem is that Hibernate has to create these 2 SQL queries, because you need to read from 2 tables.

I’m not sure if reading from 2 tables and ordering by 2 columns (one from each table) in one query is possible in simple SQL (this means the absence of any extensions for a particular provider), and if not, Hibernate will have to execute ordering in memory anyway.

What you can do when applying paging: read the identifiers and values ​​you want to sort only (not the whole object), then sort by memory and read the entire object for all the identifiers contained on the page. For paging to be consistent, you may need to save the results of this initial request (id + order criteria).

Answer 2

What you think cannot be mapped to SQL as is. Suppose you have Entity1 with fields field1A , field1B ... and Entity2 with fields field2A , field2B , ... Now you want to run the following query:

 SELECT Entity1.* FROM Entity1 UNION SELECT Entity2.* FROM Entity2 ORDER BY CommonField 

which is impossible in the SQL world, because entities have a different number of fields and different types of fields.

So, you need to think about extracting the common fields into a separate CommonEntity table, converting your interface into a standalone one-to-one Entity1 with Entity1 and Entity2 (see the table for the subclass ). Then SQL will look like this:

 SELECT * from CommonEntity LEFT OUTER JOIN Entity1 ON Entity1.refId = CommonEntity.id LEFT OUTER JOIN Entity2 ON Entity2.refId = CommonEntity.id ORDER BY CommonField 

Or you can create a view from your tables and introduce an artificial discriminator (the discriminator is what will “distinguish” identifiers from different tables, which caused a problem in your solution ), and then match the entity with this view (so we get a table for the hierarchy classes ):

 CREATE VIEW EntityAandEntityB AS SELECT 'A' as discriminator, Entity1.ID, CommonField1, ... CommonFieldZ, Entity1.field1A, ... Entity1.field1N, NULL, NULL, ... NULL(M) FROM Entity1 UNION SELECT 'B' as discriminator, Entity2.ID, CommonField1, ... CommonFieldZ, NULL, NULL, ... NULL(N), Entity2.field2A, ... Entity2.field2M FROM Entity2 ORDER BY CommonField1, ... 

Other alternatives (for example, those mentioned by @UdoFholl , which are also some kind of “outer join” for EntityAandEntityB ) will result in 2 SQL and, therefore, order an “entire” query, and scrolling is not possible .

Answer 3

Hibernate will do this for you if you use the following.

 @Entity @Inheritance(strategy=InheritanceType.TABLE_PER_CLASS) public abstract class AbstractEntity implements MyInterface { private int someField; } 

Then create your subclasses

 @Entity @Table(name="entity_1") public class EntityOne extends AbstractEntity { private int someOtherField; } @Entity @Table(name="entity_2") public class EntityTwo extends AbstractEntity { private int anotherSomeOtherField; } 

Then you can write such a query to get a single SQL connection request with the DB executing the order.

 FROM AbstractEntity ORDER BY someField