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);
}
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With