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

You need an association between your entities. Don't store IDs of other entities. Store references to other entities, and use OneToOne, ManyToOne, OneToMany and ManyToMany asociations. Your JPA tutorial (or the Hibernate documentation), should cover that. — JB Nizet, Nov 14 '13 at 12:20
Hi Nizet, Thanks for the quick response, Do you have any Tutorials which can give me more info about association with the entities. — S Atah Ahmed Khan, Nov 14 '13 at 13:03
Search for OneToOne, OneToMany, ManyToOne and ManyToMany in http://docs.jboss.org/hibernate/orm/4.2/manual/en-US/html_single — JB Nizet, Nov 14 '13 at 13:12
How to add association between entities? I don't know much but i added as `@OneToMany @JoinColumn( name="Description") Set cacheMedia;' in ReleaseDateType entity. Still issue persists. — S Atah Ahmed Khan, Nov 19 '13 at 13:40
Do yourself a favor and do not use capital letters in column names... https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS — four-eyes, Aug 21 '23 at 06:49

score 115 · Answer 1 · edited Oct 24 '18 at 03:34

115

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

edited Oct 24 '18 at 03:34

miguelmorin

5,025
4
29
64

answered Mar 10 '14 at 17:55

Luis Vargas

2,466
2
15
32

2

Hi Bigluis, Are you sure that your answer is correct? I tried it and received it: `Caused by: org.hibernate.AnnotationException: Illegal attempt to map a non collection as a @OneToMany, @ManyToMany or @CollectionOfElements: CacheMedia.releaseDateType` – qizer Feb 01 '16 at 20:57
it looks, you are right, it should be `List cachedMedias` instead of `CacheMedia cachedMedia` – Luis Vargas Feb 02 '16 at 16:35
or if you prefer a one-to-one relationship you should use `@OneToOne` in both models – Luis Vargas Feb 02 '16 at 16:40
`public List findByCacheMedia_Id(Integer id)` What if I have array of cacheMediaId instead of Single Integer in case of one to many or vice versa? @bigluis – kamal Oct 30 '16 at 14:25
1

@karmal, you should use `In` at the end of the method method, for example: `public List findByCacheMedia_IdIn(List ids)` – Luis Vargas Mar 02 '17 at 16:14
1

Luis Vargas are you sure that your answer is correct? For example how do you map ManyToOne association to List cacheMedias? Map to List<> possible only for OneToMany association – Oleg Ushakov Aug 02 '17 at 17:05
sorry, I put them at the contrary. I'll correct the answer. You can take a look into: https://en.wikibooks.org/wiki/Java_Persistence/ManyToOne – Luis Vargas Aug 03 '17 at 01:53
@LuisVargas I would clarify that the code substitutions should remove the fields, but not the getters. – miguelmorin Oct 23 '18 at 14:54
Excuse me, what problem do you solve? "public List findByCacheMedia_Id(Integer id);" returns only attributes of "ReleaseDateType" which has specific media_id. Whereas topicstarter's "SELECT * FROM Release_date_type a LEFT JOIN cache_media b on a.id=b.id" does another thing altogether, it returns fields from both Release_date_type AND cache_media, for all media_id's. When we filter by Release_date_type.media_id, there is no need to join anything at all. – Maksim Gumerov Nov 30 '18 at 16:13
`@Query("FROM ReleaseDateType AS rdt WHERE cm.rdt.cacheMedia.id = ?1")` `public List FindAllWithDescriptionQuery(Integer id);` @LuisVargas are you sure that this code works? You didn't show what is `cm` in this case. – Seydazimov Nurbol Jan 07 '20 at 11:16
saw first time this syntax ``findByCacheMedias_Id`` solved my problem. – Muhammad Azam Jul 07 '21 at 10:06

score 0 · Answer 2 · answered Apr 15 '20 at 00:50

0

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

answered Apr 15 '20 at 00:50

Yrineu Rodrigues

1,306
14
12

3

Be careful, this does a CROSS JOIN between ReleaseDateType and CacheMedia which could yield an unexpected result in some cases (when counting and grouping for instance). – Ramy Arbid Dec 04 '20 at 07:27

score -5 · Answer 3 · answered Apr 15 '20 at 03:30

-5

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

answered Apr 15 '20 at 03:30

Kunal Vohra

2,703
2
15
33

3

Using native queries locks you to a specific database implementation. This prevents you from swapping the database easily. – Jezor Sep 30 '20 at 08:37
4

@Jezor I find ORM or JPA very difficult if there are complex joins and tables with 300 or 400 columns. especially in datawarehousing projects. So JDBC comes handy. – Stunner Oct 23 '20 at 10:25
1

`If you are ever unsure about how to write criterias, joins etc in hibernate then best way is` to learn the tools you're using. It's not that hard. – Vsevolod Golovanov Dec 25 '20 at 19:16

Joining two table entities in Spring Data JPA

3 Answers3

Linked