Joining two table entities in Spring Data JPA

Question

I want to write a query like SELECT * FROM Release_date_type a LEFT JOIN cache_media b on a.id=b.id. I am new to Spring Data JPA. I don't know how to write entities for Join query. Here is an attempt:

@Entity @Table(name = "Release_date_type") public class ReleaseDateType {      @Id     @GeneratedValue(strategy=GenerationType.TABLE)     private Integer release_date_type_id;     // ...     @Column(nullable = true)      private Integer media_Id;     // with getters and setters... } 

Another entity is:

@Entity @Table(name = "Cache_Media") public class CacheMedia {      @Id     @GeneratedValue(strategy=GenerationType.TABLE)     private Integer id;     // ...     private Date loadDate; //with the getter and setter .. } 

I want to write a crudRepository interface such as

public interface ReleaseDateTypeRepository extends CrudRepository<ReleaseDateType, Long>{     @Query("SELECT * FROM Release_date_type a LEFT JOIN cache_media b on a.id=b.id")     public List<ReleaseDateType> FindAllWithDescriptionQuery(); } 

Answer 1

For a typical example of employees owning one or more phones, see this wikibook section.

For your specific example, if you want to do a one-to-one relationship, you should change the next code in ReleaseDateType model:

@Column(nullable = true)  private Integer media_Id; 

for:

@OneToOne(fetch = FetchType.LAZY) @JoinColumn(name="CACHE_MEDIA_ID", nullable=true) private CacheMedia cacheMedia ; 

and in CacheMedia model you need to add:

@OneToOne(cascade=ALL, mappedBy="ReleaseDateType") private ReleaseDateType releaseDateType; 

then in your repository you should replace:

@Query("Select * from A a  left join B b on a.id=b.id") public List<ReleaseDateType> FindAllWithDescriptionQuery(); 

by:

//In this case a query annotation is not need since spring constructs the query from the method name public List<ReleaseDateType> findByCacheMedia_Id(Integer id);  

or by:

@Query("FROM ReleaseDateType AS rdt WHERE cm.rdt.cacheMedia.id = ?1")    //This is using a named query method public List<ReleaseDateType> FindAllWithDescriptionQuery(Integer id); 

Or if you prefer to do a @OneToMany and @ManyToOne relation, you should change the next code in ReleaseDateType model:

@Column(nullable = true)  private Integer media_Id; 

for:

@OneToMany(cascade=ALL, mappedBy="ReleaseDateType") private List<CacheMedia> cacheMedias ; 

and in CacheMedia model you need to add:

@ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name="RELEASE_DATE_TYPE_ID", nullable=true) private ReleaseDateType releaseDateType; 

then in your repository you should replace:

@Query("Select * from A a  left join B b on a.id=b.id") public List<ReleaseDateType> FindAllWithDescriptionQuery(); 

by:

//In this case a query annotation is not need since spring constructs the query from the method name public List<ReleaseDateType> findByCacheMedias_Id(Integer id);  

or by:

@Query("FROM ReleaseDateType AS rdt LEFT JOIN rdt.cacheMedias AS cm WHERE cm.id = ?1")    //This is using a named query method public List<ReleaseDateType> FindAllWithDescriptionQuery(Integer id);