CODEWITHSUNDEEP
hibernatejpaspring-data-jpa
Ferdous Wahid
Ferdous Wahid

Reputation: 3367

How to return objects from multiple entity by joining by using spring data jpa?

I have three entities: EntityA, EntityB and EntityC. From those entities I need to get value from the Joining Query into a list of objects by using spring data jpa. Query is:

select x.id,x.formNo,x.name, z.testScore, y.semester 
   from  EntityA as x left join EntityB as z on x.id = z.a_id 
    inner join EntityC as y on x.c_id = y.id where x.id=1

The entities are:

EntityA:

  @Entity
  public class EntityA {        
    @Id
    @GeneratedValue
    private Integer id;         
    private String name;        
    private String formNo;

    @OneToOne(mappedBy = "a",fetch=FetchType.LAZY, cascade = CascadeType.REMOVE)    
    private EntityB b;

    @ManyToOne
    @JoinColumn(name = "EntityC_id")
    private EntityC c;
}

EntityB:

@Entity
public class EntityB {

@Id
@GeneratedValue
private Integer id;     
private double testScore;

@OneToOne
@JoinColumn(name = "EntityA_id")
private EntityA a;  
}

EntityC:

@Entity
public class EntityC {
@Id
@GeneratedValue
private Integer id;     
private String semester;

@OneToMany(mappedBy = "c",fetch=FetchType.LAZY, cascade = CascadeType.REMOVE)
private List<EntityA> a;    
}

I have tried like this

@Repository
public interface SomeObjectRepository extends JpaRepository<Object, Integer>{   
public final static String FIND_WITH_QUERY 
    = "select x.id,x.formNo,x.name, z.testScore, y.semester 
   from  EntityA as x left join EntityB as z on x.id = z.a_id 
    inner join EntityC as y on x.c_id = y.id where x.id=:id";

    @Query(FIND_WITH_QUERY)
    public List<Object> getObjects(@Param("id") String id);
  }

Upvotes: 6

Views: 10947

Answers (2)

Nilupul Heshan
Nilupul Heshan

Reputation: 728

JPQL is a way more different language than SQL if You are not familiar with that you can use raw SQL query we call in Native Query

Add this line to @Query as follows nativeQuery = true

@Repository
public interface SomeObjectRepository extends JpaRepository<Object, Integer>{   
public final static String FIND_WITH_QUERY 
    = "select x.id,x.formNo,x.name, z.testScore, y.semester 
   from  EntityA as x left join EntityB as z on x.id = z.a_id 
    inner join EntityC as y on x.c_id = y.id where x.id=:id";

    @Query(FIND_WITH_QUERY,nativeQuery = true)
    public List<Object> getObjects(@Param("id") String id);
  }

This will return Object Array as Object you need to cast it as Array

Object[] objArray = (Object[]) result_from_the_repositoryLayer;

Upvotes: 0

JB Nizet
JB Nizet

Reputation: 691755

You just need to realize that JPQL is a different language from SQL, and learn it. JPQL never uses table and column names. JPQL joins rely on associations between entities, and not on an ON clause.

The query should thus simply be

select x.id,x.formNo,x.name, z.testScore, y.semester
from EntityA x 
left join x.b z
inner join x.c y
where x.id = :id

Upvotes: 1

Related Questions