CODEWITHSUNDEEP
springhibernatejpaspring-data-jpa
S Atah Ahmed Khan
S Atah Ahmed Khan

Reputation: 1343

Joining two table entities in Spring Data JPA

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();
}

Upvotes: 90

Views: 422894

Answers (3)

Kunal Vohra
Kunal Vohra

Reputation: 2846

This has been an old question but solution is very simple to that. If you are ever unsure about how to write criterias, joins etc in hibernate then best way is using native queries. This doesn't slow the performance and very useful. Eq. below

    @Query(nativeQuery = true, value = "your sql query")
returnTypeOfMethod methodName(arg1, arg2);

Upvotes: -5

Yrineu Rodrigues
Yrineu Rodrigues

Reputation: 1492

@Query("SELECT rd FROM ReleaseDateType rd, CacheMedia cm WHERE ...")

Upvotes: -1

Luis Vargas
Luis Vargas

Reputation: 2524

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);

Upvotes: 119

Related Questions