CODEWITHSUNDEEP
javapostgresqljpa
J. H
J. H

Reputation: 124

How I can Join this 3 Tables with JPA

I want to join these 3 Tables. enter image description here

Here you see my Person Entity 

    @Entity
@Table(name = "Person", schema = "public")

public class PatientEntity {

@Id
@Column(name = "id")
private Long id;
@Column(name = "lastname")
private String name;

@OneToMany
@JoinTable(name = "person_contact", joinColumns = { @JoinColumn(name = "person_id") }, inverseJoinColumns = { @JoinColumn(referencedColumnName = "id") })
@Column(name = "contact")
private Set<ContactEntity> contacts;
//Getter Setters

And here is my contact entity:

@Entity
@Table(name="contact",schema="public")
public class ContactEntity {
    @Id
    @Column(name="id")
    private Long id;
    @Column(name="phone")
    private String phone;
//Getter Setters

I just read the Persons from the Table with findById with a Spring JPARepository, but there is no Contact mapped. There is no error during my HTTP request, but instead of a Contact there is null and this error message: com.sun.jdi.InvocationException occurred invoking method.

The business case is, that every Person can have one or more contact. Is it possible to make it with JPA Annotations or do I need to map it by myself with a JPQL? Or should I create an Entity for the middle table? (person_contact)

The Database is a PostgreSQL Database. There is this notification too in the Log:

ERROR: column contacts0_.contacts_id does not exist
  Perhaps you meant to reference the column "contacts0_.contact_id".
  Position: 306

Upvotes: 1

Views: 317

Answers (1)

Lesiak
Lesiak

Reputation: 25956

Your @JoinTable has incorrect @JoinColumn specifications and corresponds to the following ddl.

create table person_contact (person_id bigint not null, contacts_id bigint not null, primary key (person_id, contacts_id))

To map your db structure, use following (note removed @Column annotation)

@OneToMany
@JoinTable(name = "person_contact", joinColumns =
        {
                @JoinColumn(name = "person_id", referencedColumnName = "id"),
        },
        inverseJoinColumns = {
                @JoinColumn(name = "contact_id", referencedColumnName = "id")
        })
private Set<ContactEntity> contacts;

I also encourage you to read https://vladmihalcea.com/the-best-way-to-map-a-onetomany-association-with-jpa-and-hibernate/ and reconsider a db structure without a join table (depending on your load and the effort to make this db change)

Upvotes: 2

Related Questions