Database view for multiple one to many relationships

Question

Hi,

What is the best way to design a view for a Java/Hibernate Application for the following scenario:

There is Entity A that has one to many relation with Entity B, Entity C and Entity D.

There is a need to show all the relations of Entity A in a single Table in the UI.

Does it make sense to create a database view and map that with hibernate.

or

Have all the logic in Java and populate a POJO with the results from multiple queries done via Hibernate ?

In the first case, if views are used, then is such a view Possible ? I have been unable to find info on creating views with a structure such as Entity Id, Component Id, Component Type ( where Component Id and Component Type would have values from Entity B,C,D).

Is there something that I am doing fundamentally wrong ?

Answer 1

I don't see how a view can possibly help you here, as you need all the relationships which are, unfortunately, one-to-many.

My suggestion is to go with what you already have. The only thing I recommend is to tell hibernate to eagerly load all relations using join, as you need to show all relations of EntityA.

Further, are you sure that you need to show all the relations at once. I mean there may be the case that the relations are too many and you might need some kind of pagination mechanism. In that case you should consider loading the relations, separately, may be.

Answer 2

SELECT a.Id AS AID, b.Id AS BID, c.Id AS CID
FROM EntityA a
LEFT JOIN EntityB b ON a.Id = b.aId
LEFT JOIN EntityC c ON a.Id = b.aId

You could make that a view but it's pretty much the same thing as executing that query each time unless your database can optimize it in some way (pre-compute it, etc.). I'm no expert on hibernate but this is a rather simple query. I can't imagine any problems.