Hibernate & MySQL : Pitfall

As you know, I work with EJBs, Seam & Hibernate. I have nothing against them. In fact, I think the use of those technologies together with MVC architecture has many advantage in terms of development. First, we programmers do not have to worry about low level architecture. The use of Hibernate in object mapping to database actually eases out a lot of troubles of writing database calls. So, we can focus more on code development and efficiency. Or you can argue that programmers are generally lazy and always trying to find a way to make life easier. But sometimes, it can backfire.

Hibernate does a lot of things for us. As long as we know some hibernate annotations and a little bit hibernate sql, we can write an application with Java at business logic layer and any relevant database at back end. But here's one important thing before you develop your data model and start implementing. If you decide to use MySQL as your back-end database, MySQL doesn't allow you to join more than 61 tables at once.

Depending on what type of data model you have, it might affect you. For examples, we had object hierarchy data model with one root and multiple children. Since we rely Hibernate on querying the data from MySQL, Hibernate generates those queries automatically. If you have one parent with more than 61 child tables, hibernate will generate a query that joins all those child tables. You might be wondering how you can have a data model that has one parent and more than 61 child tables. Well.. you just can.. So, one way to avoid is not to use MySQL at all or try to have a better data model.