Relationship of one column to several

Question

I have an entity class called Doctor, in which it has an Idmedic column. It relates to the class entity Care, which in turn has an Idmedico column, which would be a foreign key of this first.

There is also in this table, Attendance, another column called Idmedicoindica, which is also a foreign key of the Idmedico column of the entity Medico. That is, in the table Doctor has only one column with ID of the doctors and in the care I need to have the id of the doctor who performs and what indicates.

When I try to relate the same way I did with Idmedico, but changing the name of the column, I return an error saying that it was not possible to find the Idmedicoindica column in the Medical entity. How can I proceed and what is the best practice in this case?

Follows the code:

@Entity
@Table(name = "Sis_Medico")
@PrimaryKeyJoinColumn(name = "IDMedico", referencedColumnName = "IDPessoa")
public class Sis_Medico extends Sis_Pessoa {

    @Column(name = "IDMedico", insertable = false, updatable = false)
    private Long IDMedico;

        @OneToMany(mappedBy = "medico", fetch=FetchType.LAZY, cascade = CascadeType.ALL)
        private List<Sis_Atendimento> atendimentos = new ArrayList<>();

        @OneToMany(mappedBy = "medicoIndica", fetch=FetchType.LAZY, cascade = CascadeType.ALL)
        private List<Sis_Atendimento> atendimentosIndica = new ArrayList<>();

@Entity
@Table(name = "Sis_Atendimento")
public class Sis_Atendimento {

    @Id
    @Column(name = "IDAtendimento")
    private Long idAtendimento;

    @ManyToOne
    @JoinColumn(name = "IDMedico", 
                referencedColumnName = "IDMedico",
                foreignKey = @ForeignKey(name = "SisMedico_SisAtendimento"))
    private Sis_Medico medico;

    @ManyToOne
    @JoinColumn(name = "IDMedicoIndica", 
                referencedColumnName = "IDMedico",
                foreignKey = @ForeignKey(name = "SisMedico_SisAtendimento"))
    private Sis_Medico medicoIndica;

The code does so until deploye runs, but the problem is that when I try to call the Help column Index in a @Query it gives error.

@Query("select Atendimento.idAtendimento, Atendimento.dataAtendimento, \n"
            + "paciente.IDPaciente, medico.IDMedico, Atendimento.IDMedicoIndica, Atendimento.desativado \n" 
            + "from Sis_Atendimento Atendimento")

Answer 1

When using JPA entities and writing a JPQL, you need to reference the column names in the entity and not the column names in the table, except you want to make a native consultation (Native query).

If your wish is to run an SQL and not a JPQL, just include the attribute nativeQuery with the value true and use real table and column names:

@Query("select Atendimento.idAtendimento, Atendimento.dataAtendimento, "
        + "Atendimento.IDMedico, Atendimento.IDMedicoIndica, Atendimento.desativado " 
        + "from Sis_Atendimento Atendimento", nativeQuery = true)

If your wish is to use JPQL, do:

@Query("select at.idAtendimento, at.dataAtendimento, "
        + "medico.IDMedico, at.IDMedicoIndica, at.desativado " 
        + "from Sis_Atendimento at JOIN at.medico medico", nativeQuery = true)

PS.: I removed the part of the paciente.IDPaciente for not sharing the patient’s relationship with the rest of the tables, but the idea is the same.