Hi,

We have one -to many relation between a table 1, table 2 and btw table 2 and table 3. How can we get all the data of all the 3 tables.

We have generated the hbm files by reverse engineering (myEclipse 5). The problem is that we do not want to filter the rows from table 2 and table 3 so that we donot have too many rows from table2 &3.

Any pointers/ references are appreciated. I read the reference pdf and the book also but could not get answers for this.

Kindly reply as i hardly get one :(

ravi

I am not able to visualize. But what I can make out. Get all the rows of table 1 by using query like from table1. Loop through the set for table2 and for each table2 object loop through set for table3.

Do I make any sense

Thanks for a reply,

Let me be more clear. Say i have tables as Tree which has one to many relation with other table Fruits.Now the fruits table has one to many relation with Vitamins table.

Now the problem is that i want in one shot some of the trees with a filtered set of fruits (say fruits starting with 'Ap' ) and each of the fruits with vitamins in A,B,D only.

or in other words i need to apply filters at each level of the one-many relation. Mainly because there are too many child objects and i do not need them all.

Hope this helps. Please let me know in case u need furter clarity.

We developed hbm files by reverse engineering and i see one-many relations in buth of the hbm files.

Kindly help.

[/b]

What I understand is that you need Trees with Fruits starting with 'Ap' (e.g.) and these fruits should have vitamins (A, B, D). If thats the case then :

Well, in my understanding you can try these 2 options

1. If the Query is constant you can use NamedQuery something like

select tree from Tree as tree, Fruit as fruit , Vitamin as vit where tree.id=fruit.tree_id and fruit.name=:fruitname and fruit.id=vit.fruit_id and vit.type=:vittype

2. If Query is dynamic you can use native SQL query or Criteria Query

For native SQL

create sql query which would be very close to one above and then invoke something like
session.createSQLQuery("YourQuery").addEntity(Tree.class).list();

You may be required to invoke addJoin also.

For Criteria Query

session.createCriteria(Tree.class).createCriteria("fruit").add(Restrictions.like("name", "App%")).createCriteria("vitramin").add(Restrictions.eq("type", "A")).add(Restrictions.eq("type", "B"))..................


Hope I am making some sense

In your definition of tree (in the hbm or the annotation), you need to make fruit a collection class, and you need to do a similar thing with fruit to vitamins.

That way when you load a tree, it will automatically load (or lazy load) your subsequent fruit associated with it, and at the same time load up the vitamins.

hi,

Thanks for the reply. It helped.

I went by the first way (select... etc) and i am able to get the elements but it is not happening at one shot. Initially i am getting all trees. When i say trees.getFruits() then again a query fires and the second set of objects get loaded. When i say fruits.toArray()[0].getVitamins a 3rd set of query is fired.

It seems like for every new object i need a new hit is happening.

I understand this a s lazy loading and if i make lazy=false, then all the hits happen at once but again there are more than one hit. Can we not get all elements in one hit.

kindly help

Change the query to use "join fetch" to populate the collections.

e.g. HQL

Hi Mike,

Great! the soln worked. Can you please explain it. Is it not possible by criteria.

any how, thank you very much

cheers,
Ravi C

"join fetch" instructs hibernate to populate the collections from the join results rather than issuing separate selects. However, it is not normally used with a where clause.

There's some discussion of this here (from 2003):
http://forum.hibernate.org/viewtopic.php?p=2203703
... where Gavin King says:


From your description I think this is what you want - i.e. for the returned collections to contain only those elements matching the query. Be careful never to update the returned objects (especially if relationships are cascaded) otherwise hibernate will delete collection entries that are in the database but not in your object.

Actually it _isn't_ possible to do this with the Criteria API. You can specify setFetchMode(JOIN) but hibernate ignores this when you add a restriction to elements of the collection. You could argue this is the correct behaviour because the objects returned match their database representation i.e. the collections contain all the elements.

Hibernate will need to issue separate selects for the collections (lazy or not) but the impact can be minimised by setting fetch="subselect" to get all the objects for a particular collection in one go.