Search code examples
javadatabasehibernateormrdbms

Should i use ORM if my application requires a lot of join queries

I have 3 entities i. Asset (PK asset_id, FK category_id) ii. Category(PK category_id, FK: size_id) iii. Size(PK size_id)

Now every asset belongs to a category (many to one ) and every category has a size (many to one) In a typical ORM I would fire a query for asset retrieval which would in turn give call to (SELECT * from asset) Now when i call Asset.getCategory(), it will call to (SELECT * from category) and a call to Caegory.getSize() will call to (SE:ECT * from size)

so a single entity retrieval in OOP makes 3 DB calls Where as if i write a join query it makes on DB call. Makes me wonder, Why we use ORM.

Solution

  • Your assumption is wrong. In JPA, you can query whole object graph by using "fetch" keyword, which will basically get all of the data from database in one query. So you would do something like

    entityManager.createQuery("select asset from Asset asset join fetch asset.category cat join fetch cat.size size");

    which will get Asset with its Category, and that Category with its Size in one query. Apart from that 3 tables are very little, you can usually join way more tables with basic joins without performance problems.