Bohdan Myslyvchuk Bohdan Myslyvchuk - 3 months ago 31
SQL Question

Finding query using spring data with joining tables

I have such situation. I have two tables with relation One-to-Many. For example Table A with columns

idA, someOtherIdA
and table B with columns
idB, forenKeyIdA, someImportanFieldFromB
.
Also I'm using spring data and now I need to create method

public B findBySomeOtherIdAAndsomeImportanFieldFromB(int someOtherIdA, int someImportanFieldFromB);


For this I need to join two table by id

Select * From A Join B On A.idA = B.idB Where someOtherIdA = 1 someImportanFieldFromB = 1


and I'm using annotation @Query to retrieve result.
But the problem is that I can't automatically map result to object B.
How could I do that? And is it possible at all to do so without using nativeQuery=true?

Answer Source

The best solution I found is to use JPQL or HQL to get entity and map it. In example I have written it would be like

@Query(SELECT b FROM A as a RIGHT JOIN a.forenKeyToAFromB b Where Somevalue=1)
public B findBySomeOtherIdAAndsomeImportanFieldFromB(int someOtherIdA, int someImportanFieldFromB);

This method with query will map entity to your existent entity B and solve your tasl