How can I work-around this Hibernate Limitation?

Question

I have 3 entities in a Hierarchy like this:

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

The MyInterface is NOT mapped in Hibernate (because I am using implicit polymorphism strategy to map this inheritance)

And, in fact, if I launch a query like this one:

"FROM MyInterface"

It works just fine (because it retrieves all the instances of Entity1 and all the instances of Entity2, puts them together, and returns a List<MyInterface>).

If we look at the SQL generated by Hibernate, it is launching 2 independent SQL queries to first retrieve the Entity1 instances an another one to retrieve Entity2 instances, but I am fine with this.

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

"FROM MyInterface ORDER BY someField"

Because it is applying the ORDER BY to the first SQL query and then the same ORDER BY to the second SQL query, instead of apply them to the WHOLE query (I know this because I can see the native SQL queries launched by Hibernate).

This is clearly a missbehaviour of Hibernate.

How can I work around this to force Hibernate to apply the ORDER BY to the whole query? (I cannot do it in memory because later I will have to add pagination also).

Answer 1

The way you are thinking cannot be mapped to SQL as is. Suppose you have Entity1 with fields field1A, field1B ... and Entity2 with fields field2A, field2B, ... Now you want the following query to be executed:

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

which is not possible in SQL world, as entities have different number of fields and different field types.

So you need to think about extracting common fields into separate table CommonEntity, converting your interface into standalone entity with with one-to-one mapping to Entity1 & Entity2 (see Table per subclass). Then SQL will look like:

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 over your tables and introduce an artificial discriminator (discriminator is something which will "distinguish" IDs from different tables, which caused a problem in your solution) and then map an entity to this view (so we get Table per class hierarchy):

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 (e.g. mentioned by @UdoFholl which is also kind of "outer join" for EntityAandEntityB) will result 2 SQLs and thus there is no way to order the "whole" query, and scrolling is not possible.

Answer 2

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 have your subclasses that do this

@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;
}

You should then be able to write a query like this to get a single union SQL query with the DB doing the ordering.

FROM AbstractEntity ORDER BY someField

Answer 3

I'd say the problem is that Hibernate has to create those 2 SQL queries because you have 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 plain SQL (means no vendor specific extensions), and if not, Hibernate would have to do the ordering in memory anyways.

What you could do when applying paging is: read the ids and the values you want to sort by only (not the entire entity), then sort in memory and read the entire entity for all ids contained in the page. For paging to be consistent you might have to store the results of that initial query (id + order criteria) anyways.