How to JOIN (alternative to Mapping) multiple tables in Spring Webflux (IntelliJ) (Postgres database)?

Question

How to Join multiple tables in Spring WebFlux for Relational Database?

In Spring Boot, in order to join two tables, we can perform different mappings (@OneToOne, @ManyToOne, @OneToMany). One example is shown below for mapping between doctor and patient.

Doctor Class (Entity)

@Data
@AllArgsConstructor
@NoArgsConstructor
@Entity
public class Doctor {
    @Id
    private int did;
    private String dname;
    private String specs;
}

Patient Class (Entity)

@Data
@AllArgsConstructor
@NoArgsConstructor
@Entity
public class Patient {
    @Id
    private int id;
    private String name;
    private int age;

    @OneToOne(targetEntity = Doctor.class,cascade = CascadeType.ALL)
    @JoinColumn(name = "Id_Fk")
    private Doctor doctor;
}

For Spring Webflux, I have created two tables (postgresSQL) shown below, their respective repositories, a handler and a router in IntelliJ. How to code the above in Spring Webflux as we can't use @Entity or any mapping annotations there?

Doctor Table

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Doctor {
    @Id
    private int did;
    private String dname;
    private String specs;
}

Patient Table

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Patient {
    @Id
    private int pid;
    private String name;
    private int age;
}

You have to do as old school. In Patient should have the property int doctorId and save first the doctor and then the patient. — AleGallagher, Jul 16 '21 at 22:43
I did that...but now the problem is when I need to perform JOIN operation based on the common column of the two tables in webflux by using @Query, I mention it in one of the respective table repository. Then, it only shows the entry of that table and no column of the other. — NIKITA RATH, Jul 17 '21 at 12:44
To be more specific, I added a foreign key in doctor table referring to primary key of Patient Table. In the doctor repository, I added a @Query to perform JOIN between two tables. However, when I run in the localhost, it shows only the entry of doctor and none of patient. — NIKITA RATH, Jul 17 '21 at 12:45
@Query("select doctor.*, patient.id as Pat_Id, patient.name as Pat_Name, patient.age as Pat_Age from Doctor doctor join Patient patient on patient.id = doctor.pid ") public Flux findAlldetails(); — NIKITA RATH, Jul 17 '21 at 12:50
That should be because the repository doesn't know where set the Patient properties (patient.name patient.age) because Doctor doesn't have that properties, and the method has to return a Doctor. May you need to create a new class to keep all that properties together, or make 2 calls, in order to get a Doctorn and a Patient — AleGallagher, Jul 17 '21 at 14:51

How to JOIN (alternative to Mapping) multiple tables in Spring Webflux (IntelliJ) (Postgres database)?

0 Answers0