I have a relatively large class hierarchy in terms of width, so many classes inheriting from one root class. The maximum depth of the hierarchy is only 4 levels when counting from the root to the leaves of the hierarchy tree. Let’s call the root class RootClass and the leaves Lx (== L1, L2, ..., L20). I only store objects of type Lx in the database, never of RootClass or an intermediate type.
The Lxes are referred to by various other classes but only stored as RootClasses there. The ORM is as follows: RootClass has a table annotated with a single table inheritance strategy. As storing all Lx's varying properties in this RootClass table is extremely inefficient, I have secondary tables for each Lx that contains additional properties and theoretically I can determine the actual type of an object - and hence table - by the discriminator value in the RootClass's table. My reasoning is here that Hibernate should be able to efficiently query a stored object, as it can infer which two tables to join from the discriminator value.
The problem: The RootClass contains a many-to-many relation to (a Set of) other RootClass objects. When I try to fetch that set to iterate over its content hibernate produces a query with over 80 joins. This causes a java.sql.SQLException: Too many tables.
I assume hibernate is trying to fetch the data I require with a single statement and the only way to do this is such a massive join. Is there a way to get hibernate to fetch the data more intelligently by using the discriminator values and several queries? Is my ORM bad and a way to fix this issue by improving the ORM? I have tried a lot of mappings for this class hierarchy and haven’t come up with anything better so far.
The problem : The RootClass contains a many-to-many relation to (a Set of) other RootClass objects. When I try to fetch that set to iterate over its content hibernate produces a query with over 80 joins. This causes a java.sql.SQLException: Too many tables.
The solution: Replace the Set with a query. A query is much more flexible than an association. You can use the treat operator or you could break it into multiple queries against a limited number of subclasses.
Thank you, that sounds promising! So would I effectively mark the Set as transient and write my own queries to fetch/store the objects when I need them by executing them instead of using getMySet()? Or is there a way to integrate it in a more automatic manner with hibernate, also including the whole lazy fetching functionality hibernate uses anyway (I imagine defining a query somewhere which hibernate will then use when I call getMySet())? I did not really find much on ways to go when replacing associations or collections with queries.
How do I now elegantly tell Hibernate to create the join table in the first place? As this association is not part of the mapping any more it does not do so automatically and randomly executing a CREATE TABLE command seems a little odd to me at first glance.
Just like the Hibernate User Guide tells you, it’s much better if the schema is handled by an automatic migration script tool:
Although Hibernate provides the update option for the hibernate.hbm2ddl.auto configuration property, this feature is not suitable for a production environment.
An automated schema migration tool (e.g. Flyway, Liquibase) allows you to use any database-specific DDL feature (e.g. Rules, Triggers, Partitioned Tables). Every migration should have an associated script, which is stored on the Version Control System, along with the application source code.
Relying on hbm2ddl for that is only useful for the very first initial script or for running integration tests when developing the Hibernate ORM.
Okay, I had hoped to be able to set up the database more or less effortlessly for testing but using a script there as well won’t be an issue.
Please excuse my repeated queries, perhaps I am missing something fundamental here but I am still not clear on everything.
The problem is larger than originally thought, I simply happened to notice it first in aforementioned situation and have only realised what the issue is in its entirety now. I have various objects referring to instances of the Lx classes, including but not limited to some Lx-typed objects themselves. Every time one of those referenced objects is lazily loaded a huge query with far too many joins is produced.
Is the way to go here to create native queries for all of these cases? So effectively not using Hibernate, its ORM and lazy loading with proxies in these situations? Two advantages of Hibernate for our project are not having to specify as many queries manually and the ability to plug in different back-end database systems – but by using native queries we would surely sacrifice that? (Native queries because we need to be able to access unmapped tables for associations).
Unfortunately this only shifts the issue to the moment at which I try to access the property in question. As soon as Hibernate goes out to fetch the required data lazily it suffers the same gigantic-join-problem. This even occurs when simply performing a regular query with e.g. session.get(RootClass.class, 123), the result is always a massive amount of joins.
If you are using lazy loading, no association will be fetched unless you need it.
If you have a gigantic inheritance structure that you have to fetch at once, you could use 2nd-level cache to avoid the joins or reconsider the design/model since you might not really need all that data for every use case.
This. The associations each added joins to the queries when fetched eagerly - I just wasn’t aware of that connection and was thinking in a totally different direction with my next post. Thanks, loading XToOne associations lazily solved it :).
