With an application requiring many different (eventually into hundreds of) subclasses, I'm getting the "MySQL can only use 31 table in a join" problem when I try to use the table per subclass strategy. I've tried setting fetch="select" and max_fetch_depth to 0 recommended in various postings with no luck for a quick fix so am looking to understand more the limitation and how best to work around it.

Thinking in terms of a classifcation hierarchy (e.g. Animal -> Mammal -> Dog -> WorkingBreeds -> GermanShepherd), I definitely want to use table per subclass to produce the intuitive table structure where each level's attributes/references are joined with the commonly maintained id. Given the 31 table MySQL limit - not to mention the unwieldiness of a query that handles all combinations - I'm stuck with using 1 (big, fat, ugly) table per hiearchy to continue development knowing I've got to eventually find a answer.

Regardless of whether we are trying to query the base table (select all animals w/ weights > some #) or load a collection (what animals are at this zoo), we've got to get records from the "base" table and then join down the appropriate branch of the table tree. It appears from all the examples/docs we've got 2 retrieval options, 1 outer-join (which has the table limitation problem) or n+1 selects, which I can't seem to get to work as it throws the same exception. Would like to at least have the option for the creating the proxies and then let subsequent fetches pull the rest in the latter. Probably something I'm missing with there so will keep pluggin away w/ simple examples; would like any pointers in that area or if at some level there just has to be these monster joins, need to know that ASAP.

Looking for a better approach, seems to my poor brain that using discriminators in the mapping, there's at least a chance of figuring out which of the potential subclasses we're trying to retrieve and in some way find a middle ground. I'm thinking in terms of 1+m selects where there are m distinct subclasses among the n to be retrieved (and joined) records. Would need to do one query to figure out which recs in the base table match the criteria and then m inner joins on each specific subclass hierarchy to get all the info. Theoretically would be more advantageous in numerous apps I can think of in addition to the 1 that's got me stymied.

Maybe this is old stuff w/ a ready solution that I've been stumbling around but not into. If so, sorry - please point me in the right direction. Otherwise, any help is appreciated.

You probably want to drop inheritance completely. It's certainly not working for you, and it's probably wrong, anyway. If you have that many relations, delegation is probably a more suitable pattern.

Implement your hierarchy through interface extension, so the interfaces model your preferred class hierarchy. Then implement your classes independently. The animals example won't help, because it follows a true is-a model, but let's try company employees to illustrate.

You've got a Person object, containing names, social security details, etc. You planned on extending person with Employee, Manager, ITSpecialist, Surgeon, HeartSurgeon, what-have-you. For anything that doesn't really describe an is-a relationship, use delegation. Managers aren't necessarily Employees: they could be contractors. They're certainly not Persons (pun intended, but what I actually mean is that Manager is a role that a Person plays, not something that a Person inherently is). So if you wanted to model a hospital administrator who is a heart surgeon, you'd have something like this:

Person: Name Al the HeartSurgeon/Manager
getJobs() -> { HeartSurgeon (interface extending Surgeon, Doctor), Manager (interface extending AdminStaff) }

If you particularly wanted to model this as a PersonHeartSurgeonManager interface, you have all methods from all interfaces in one class (that probably doesn't extend anything, or at best something like "AbstractPersistableObject"), then have things like

Thanks for the response. Certainly there are many times when we delegate and our entire framework is based on interfaces w/ nominal implementations both automatically generated. The problem I've got is definitely one of structural inheritance so is-a's the way to go. Have avoided the type of complexity I'm into "for years" w/ techniques such as role-oriented relationships, avoiding queries over parts of the hierarchy that are too vast, etc. This is one app where the structure dominates and, if we can make 'em work, simple high-level relationships and queries with extensive polymorphism will afford a huge advantage.

Also, since we are intensely "model-centric" (hate to use the phrase these days), it can make sense for classes that from an application point of view seem to be their own hiearchy root to have a common framework basis. Definitely pros and cons there but have gotten way too much mileage out of our current architecture to "drop inheritance".

Basically looking for another technique in the bag to make hibernate play nicely in the admittedly few cases where large inheritance trees make sense. Would be interested to know if it's only MySQL that has this problem or just happens to be where I ran into it.

Many (all?) DBMSs have limitations of this variety. You will have to work around it. However, you can still implement inheritance in your POJOs via delegation in your mapping. Do this every 2 or 3 levels in your hierarchy (i.e. replace subclasses with many-to-one associations), and then those associations will be fetched via select.

The main problem, in my eyes, with the subclassing thing is that hibernate knows that it must do all the joins in order to fully describe a single object. Breaking the single object up into several hibernate-level objects, even if you keep it as a single java-level object, allows hibernate to say "oh, that's a different object that referenced from this one, so I don't have to load it to fully describe this object". And that's what you want.

It does mean that when you think you've got an entire object, some of it still won't be loaded, so you need to keep your session around, but with the prevalence of the OpenSessionInView pattern, that's not too much of an issue.

Was kinda assuming that the best I could do with switching DBMSs would be to get a little more "space" until the problem reared its head again. One can always hope ...

Had considered the break-out you indicated at certain levels, though was assuming the associations would be one-to-one. Let me push into your thoughts a bit more to see if I understand some of the implications. If we're on the same track, the POJO interfaces could be as deep/broad as desired with straight inheritance; the POJO "impls" could either follow the inheritance hiearchy or could assocate with disjoint hierarchies at appropriate levels.

For example, if we chose to break out the info at the "species" level would have

POJO interfaces

Animal <= Mammal <= Dog <= WorkingBreeds <= GermanShepherd

POJO impls

AnimalImpl <= MammalImpl <---> AbstractKindOfMammal <= DogImpl <= WorkingBreedsImpl <= GermanShepherdImpl

(with <= inheritance and <---> bi-navigational 1-1 association)

With this scheme AbstractKindOfMammal would have to wrap the getters/setters of MammalImpl hierarchy to satisfy the Mammal interface inherited by Dog. Certainly doable, albeit a little messy, and would pose limitations on adding to the hierarchy "later" where the break-out could be suboptimal.

Where I'm not sure I'm following is how the mapping would (potentially) span POJO classes or tables. Given we've got multiple objects references via a single interface, looks like the straightforward table per (impl) subclass would work - obviously w/ 1-1 assoc appropriately defined. Were you thinking something else?

Before I go too much farther down that path, would like to get some feedback on my 1+m select strategy. It's too far into hibernate's design/implementation for me to speak w/ valid specifics, but conceptually seems like could do a relatively simple query (i.e. "upward" inner joins) on the select from table to get all the unique (m) discriminators matching the where criteria. Then could do a series of (m) select queries where we again inner join upward (enumerating the select fields) from the tables associated w/ the matching discriminators to instantiate and fill the correct POJOs. Appears like we're fairly deep into the core architecture here and don't know whether hibernate has any exposed "pluggables" that comes close to supporting such a scheme.

I'm sure there are more than a few gotchas to address (e.g. if you've got Rover who's just a Dog and Spot who's a Shepherd that both match the criteria, Spot will get picked up when pulling out the matching Dogs; also got the problem of complex HQL versus the simple query this table or load that collection). Given everything else hibernate developers and users are doing, I'm hopeful somebody far more knowledgeable than me in this area can step up and say yea, maybe, or no way.

This is delegation. A little messy, but doable. I would contest your assertion that limitations would be imposed when modifying the hierarchy: it is more difficult to do that when the hierarchy is implemented using inheritance than it is when delegation is used. It's much easier to edit DogImpl to implement a Quadruped interface than it is to get it to extend a QuadrupedImpl abstract class. Sure there's more stumpy methods that you've got to add, but that's about it. And consider the ramifactions if you were to remove a level from the hierarchy: removing one item from a class' implements clause is a pain, but changing its extends clause is horrific. Especially when you've got to go through all code that refers to the now-removed class to figure out if the subclasses need to be individually referenced at that point, or if you can change the reference to the superclass (with conditions), or.. I dunno. Loads more pain in the neck stuff.

No comments on your strategy question, sorry. One hour to beer o'clock, mental functions in graceful degradation mode.