General join fetch

[Ibrahim Aziz]

Hi All,

As a graduation project for my bachelor degree, I am developing a system for the Academic Affairs at our university. The database is somewhat large (53 tables) and I use annotations to set the mapping between the tables.
The default lazy loading works fine in most situations, but in one specific situation I need to retrieve all the information about a specific instructor form the database. Setting fetch mode to join in my annotations would result in always using join fetch mode, which I do not want. If I set fetchMode.Join in my Criteria I face the problem of having objects inside lists which themselves contain lists, so I can's set fetchMode for these.

I wondered, is there a way to set fetch mode once for the entire object tree?

Thanks in advance for any help

[James Sutherland]

Not sure I understand, perhaps some more details.

In general you can use join fetching in JPQL using "join fetch" (i.e. Select e from Employee e join fetch e.address).

See,
http://en.wikibooks.org/wiki/Java_Persistence/Relationships#Join_Fetching

[Ibrahim Aziz]

Hi James,

Thank you very more for your response. Here some more detail to my problem:
I have 53 tables connected to each other using annotations. All theses tables hold different information about an instructor, meaning the instructor class is the top level class. In a specific circumstance I need to fetch all the information about an instructor from the database, so I user join fetching. Here the code of the fetch function I have reached so far:

public instructor retrieveInst(instructor inst){
List<instructor> retrievedInstructors = new ArrayList<instructor>();
Session ss = hibernateUtil.getSession();
Transaction tx = ss.beginTransaction();
Criteria crit = ss.createCriteria(instructor.class);
crit.add(Restrictions.eq("index", inst.Index));

crit.setFetchMode("nationality", FetchMode.JOIN);
crit.setFetchMode("birthGov", FetchMode.JOIN);
crit.setFetchMode("acCard", FetchMode.JOIN);
crit.setFetchMode("als", FetchMode.JOIN);
crit.setFetchMode("aw", FetchMode.JOIN);
crit.setFetchMode("collRes", FetchMode.JOIN);
crit.setFetchMode("dt", FetchMode.JOIN);
crit.setFetchMode("diss", FetchMode.JOIN);
crit.setFetchMode("externalExp", FetchMode.JOIN);
crit.setFetchMode("fixationAnnReports", FetchMode.JOIN);
crit.setFetchMode("fixCard", FetchMode.JOIN);
crit.setFetchMode("govServs", FetchMode.JOIN);
crit.setFetchMode("specMixServs", FetchMode.JOIN);
crit.setFetchMode("internalActs", FetchMode.JOIN);
crit.setFetchMode("nonPromResearches", FetchMode.JOIN);
crit.setFetchMode("promResearches", FetchMode.JOIN);
crit.setFetchMode("raises", FetchMode.JOIN);
crit.setFetchMode("reports", FetchMode.JOIN);
crit.setFetchMode("retCard", FetchMode.JOIN);
crit.setFetchMode("seminars", FetchMode.JOIN);
crit.setFetchMode("socServices", FetchMode.JOIN);
crit.setFetchMode("supervisedRes", FetchMode.JOIN);
crit.setFetchMode("upperStudCard", FetchMode.JOIN);
crit.setFetchMode("vacations", FetchMode.JOIN);
crit.setFetchMode("partner", FetchMode.JOIN);
crit.setFetchMode("children", FetchMode.JOIN);
crit.setFetchMode("otherRelations", FetchMode.JOIN);
crit.setFetchMode("instructorsDataAddUpdateEvents", FetchMode.JOIN);
crit.setFetchMode("iDS", FetchMode.JOIN);
crit.setFetchMode("acCard.instCerts", FetchMode.JOIN);
crit.setFetchMode("acCard.instSintDeg", FetchMode.JOIN);
crit.setFetchMode("nationality.nationalityInstructors", FetchMode.JOIN);
crit.setFetchMode("DetailedSpecialization.instCerts", FetchMode.JOIN);
crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

retrievedInstructors = crit.list();
instructor retrievedInst = retrievedInstructors.get(0);
tx.commit();
ss.close();
return retrievedInst;
}

Now consider the following situation:
An instructor may have more than one promotion research, meaning the instructor class incorporates a set of promotion researches. A promotion research instance contains a general specialization object, which in turn has a number of lists in it. How can I set join fetch for these lists without putting it in the annotation?
A simpler solution for me is if I could set join fetch for the entire instructor (the whole object tree) in just one command instead of needing to set join fetch for each object or set inside the instructor.

Fast help would be very appreciated, since I need to deliver the project on Friday, and google didn't help me any further.

[Ibrahim Aziz]

Yo guys,

is that problem really that strange and nobody knows a solution to it, or is it just than nobody is reading that post?

Yesterday I put the fetch join in the annotation as a temporary solution, which resulted in a recursive query somewhere. I just have a few records in my tables, but when I retrieve an instructor I get an out of memory exception. Will trace the code today to find out where and why that happens.

Meanwhile I started thinking about forgetting the fetch join idea and just writing a function that traverses the object tree and calls the getters of all the objects / collections I need to initialize. That is not really a professional solution, because each collection will be retrieved by n queries (where n is the number of entries in the respective table), instead of one if I could use a fetch join.

Doesn't anybody here know a more professional solution?

[James Sutherland]

Joining 53 tables is probably not a good idea.

Refer to the blog post in my previous response for information on query optimization.

Looping over the objects and accessing want you need is a common solution. You should only need to do this if you are serializing the objects.

EclipseLink also supports a LoadGroup feature that allows forcing instantiation to the specified tree of attributes which can over the loop.

[Ibrahim Aziz]

Thank you very much James for you response. Will check out the EclipseLink LoadGroup to see if it will suit my needs. Otherwise I will use the solution looping over the objects.

The inter-linkage of the 53 tables is mandatory, because the database must precisely represent the information domain of the system.

If I find any other solution to the problem, I will post it here for anybody who might need it.

Greetings
Ibrahim

[Ibrahim Aziz]

Bye the way, I'm using hibernate as ORM framework. Thought that EclipseLink is some jar extension to hibernate, but after some googling I unfortunately realized that it is an independent ORM framework, so no help for me. But thanks for the information anyway...

Greetings
Ibrahim

[Hemant Thard]

Hi Ibrahim Aziz,

Just curiously, why don't you create a view with all your join in your database table.

wouldn't that help.

Regards,
Hemant.

[Ibrahim Aziz]

Hi Hemant,

I didn't think about creating views using hibernate before that. Thank you very much for the hint. Will google a bit to find out the best way to do that.

Greetings
Ibrahim

[Ibrahim Aziz]

I just asked uncle google about creating views in hibernate, but had to realize that hibernate does not support views natively. So I would need to write the sql to create the views, and considering the number of tables in my database (53), this would be a lot of work. I would also loose the portability of my application between different dbms. So I will keep the approach I have taken, iterating through all the collections I need and calling the getters on them. Except causing a performance degradation (n + 1 problem), this approach works well.

By the way, I got an infinite loop of queries when I tried using fetch join on collections, even when putting the fetch join in the annotations. My current code is too long and complicated to be used as an example here, but I will write an example to illustrate the problem as soon as I get a little more time...

Greetings
Ibrahim

[Hemant Thard]

HI Ibrahim,

Here's what I think

hibernate does not support views natively

Yes true, only if you allow hibernate to generate your Schema Table(DDL Statement). Otherwise i don't think its going to be of any problem. View can be consider as a table in hibernate and you will have to define HBM file as you do for normal table.

So I would need to write the sql to create the views, and considering the number of tables in my database (53). this would be a lot of work.

I think this is best approach. If something needs to be changes (like age property be > 18), you just have to change your view. No code change required.

I would also loose the portability of my application between different dbms

Can you explain how creating view can hamper application portability.





Regards,
-Hemant