Spring CrudRepository- How do I insert a record by foreign key ID?

Question

When inserting a record using post request foreign key related reference record is not linking.

@RestController
@RequestMapping("auth")
public class PatientController {

    @Autowired
    private PatientService patientService;  

    @PostMapping(value = "patient/register", consumes = MediaType.APPLICATION_JSON_VALUE, produces = MediaType.APPLICATION_JSON_VALUE)
    public String registerPatient(@RequestBody Patient patient) {   
        String response = patientService.registerPatient(patient);
        return "{'result':" + response + "}";
    }
}

@Service
public class PatientService {

    @Autowired
    private PatientRepository patientRepo;  

    public String registerPatient(Patient patient) {                
        patient = patientRepo.save(patient);            
    }
}

@Repository
public interface PatientRepository extends CrudRepository<Patient, Integer> {

}

Entity Classes:

@Entity
@Table(name = "patient")
public class Patient implements java.io.Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "patient_id")
    private int patientId;  

    @Column(name = "patient_name", length = 200) 
    private String patientName; 

    @Column(name = "problem", length = 200) 
    private String problem;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "doctor_id", nullable = false, insertable = false, updatable = false)
    private Doctor doctor;  

}

@Entity
@Table(name = "doctor")
public class Doctor implements java.io.Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "doctor_id")
    private int doctorId;   

    @Column(name = "doctor_name", length = 200) 
    private String doctorName;  

    @Column(name = "department", length = 200) 
    private String department;

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "doctor")
    private Set<Patient> patients = new HashSet<Patient>(0);

}

Database - Doctor Table: doctor_id doctor_name department 12345678 Dfirstname Dlastname ENT

POST Request - JSON Body { "patientName":"Pfirstname Plastname", "problem:"visibility problem - difficulty in low light", "doctor":{"doctorId":"12345678"} }

When I am sending this request the patient table doctor_id column is not being populated with the docortId.

Answer 1

at first glance (as service layer is not provided) You have to remove insertable=false and updatable=false from @JoinColumn

@JoinColumn(name = "doctor_id", nullable = false, insertable = false, updatable = false)

change this to:

@JoinColumn(name = "doctor_id", nullable = false)

As this directives doesn't let jpa to insert/update the DOCTOR_ID column

Also I prefer using werappers over primitive type as @Id change int to Integer as suggested here Using wrapper Integer class or int primitive in hibernate mapping

Also it seems that you have already persisted doctor (as it has already assigned id) you should firstly select doctor to db and add patient to it with both ends:

public void assignToDoctor(Doctor doctor) {
        doctor.patients.add(this);
        this.doctor = doctor;
}

here is full example:

    public static void main(String[] args) {
        SpringApplication.run(DemostackApplication.class, args);
    }


    @Component
    public static class AppRunner implements ApplicationRunner {

        @Autowired
        MainService mainService;

        @Override
        public void run(ApplicationArguments args) throws Exception {
            Doctor doctor = new Doctor();
            doctor.department = "a";
            doctor.doctorName = "Covid19 Ninja";
            doctor = mainService.saveDoctor(doctor);

            Patient patient = new Patient();
            patient.patientName = "test";
            patient.problem = "test";
            patient.assignToDoctor(doctor);
            Patient newPatient = mainService.savePatient(patient);
        }
    }

    @Service
    public static class MainService {
        @Autowired
        DoctorRepo doctorRepo;
        @Autowired
        PatientRepo patientRepo;

        @Transactional
        public Doctor saveDoctor(Doctor doctor) {
            return doctorRepo.save(doctor);
        }

        @Transactional
        public Patient savePatient(Patient patient) {
            return patientRepo.save(patient);
        }
    }

    @Entity
    @Table(name = "patient")
    public static class Patient implements java.io.Serializable {
        @Id
        @GeneratedValue(strategy = GenerationType.AUTO)
        @Column(name = "patient_id")
        private Integer patientId;

        @Column(name = "patient_name", length = 200)
        private String patientName;

        @Column(name = "problem", length = 200)
        private String problem;

        @ManyToOne(fetch = FetchType.LAZY)
        @JoinColumn(name = "doctor_id", nullable = false)
        private Doctor doctor;

        public void assignToDoctor(Doctor doctor) {
            doctor.patients.add(this);
            this.doctor = doctor;
        }
    }

    @Entity
    @Table(name = "doctor")
    public static class Doctor implements java.io.Serializable {
        @Id
        @GeneratedValue(strategy = GenerationType.AUTO)
        @Column(name = "doctor_id")
        private Integer doctorId;
        @Column(name = "doctor_name", length = 200)
        private String doctorName;

        @Column(name = "department", length = 200)
        private String department;

        @OneToMany(fetch = FetchType.LAZY, mappedBy = "doctor")
        private Set<Patient> patients = new HashSet<Patient>(0);
    }

I have not used getter/setters but you should :)

EDIT

your registerPatient() logic should be something like this:

    @Transactional
    public String registerPatient(Patient patient) {
         Integer doctorId= patinet.getDoctor().getId();
         //fetch the doctor from database
         Doctor doctor = doctorRepository.findById(doctorId).orElseThrow(() -> new RuntimeException("doctor not found"));
         //create bidirectional reference between patient and doctor
         patient.setDoctor(doctor);
         doctor.getPatients().add(patient);
         //save patient
         patient = patientRepo.save(patient);
         return "OK";
    }