How to do Inner Join in Jpa Springboot

Question

I have the following appointment:

select m.id, m.nome, m.crm, e.descricao especialidade
              from medico m
              inner join especialidade e on e.id = m.especialidade_id

Whose database return is like this:

'5', 'FLAVIO FIGUEIREDO', '5255', 'ORTOPEDIA'
'6', 'MARCELO BREVES', '1050', 'ORTOPEDIA'
'4', 'ANA HAOXOWELL', '5155', 'PEDIATRIA'
'1', 'ALBERTO SOUZA', '3215', 'CLINCA GERAL'
'2', 'MARIA DA SILVA', '1234', 'ENDOCRINOLOGIA'
'3', 'PAULO COSTA', '3210', 'CARDIOLOGIA'

I would like to do this query using Jpa and DTO class,

I’m trying to do it like this:

My DTO class

Medicoespecialty

import lombok.Data;
import org.modelmapper.ModelMapper;

@Data
@AllArgsConstructor
public class MedicoEspecialidadeDTO {
    private Long id;
    private String nome;
    private String crm;
    private String descricao;    
}

Repository

@Query(value = "SELECT m.id, m.nome, m.crm, e.descricao especialidade" +
                   "  FROM medico m" +
                   "  INNER JOIN especialidade e ON e.id = m.especialidade_id")
    List<MedicoEspecialidadeDTO> findByMedicoEspecialidade();

I’m trying the following error while trying to run my project

antlr.Noviablealtexception: Unexpected token: especialidade at org.hibernate.hql.internal.antlr.HqlBaseParser.aliasedExpression(Hqlbaseparaser.java:2620)

2020-10-11 17:43:55 WARN o.s.b.w.s.c.Annotationconfigservletwebserverapplicationcontext - Exception encountered During context initialization - cancelling refresh Attempt: org.springframework.Beans.factory.Beancreationexception: Error Creating bean with name 'medicoRepository': Factorybean threw Exception on Object Creation; nested Exception is java.lang.Illegalargumentexception: Validation failed for query for public method Abstract java.util.List com.vibesaude.saude.repository.Medicorepository.findByMedicoEspeciality()!

org.springframework.Beans.factory.Beancreationexception: Error Creating bean with name 'medicoRepository': Factorybean threw Exception on Object Creation; nested Exception is java.lang.Illegalargumentexception: Validation failed for query for public method Abstract java.util.List com.vibesaude.saude.repository.Medicorepository.findByMedicoEspeciality()!

Entities

Medic

@Data
@Entity(name = "medico")
public class Medico {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    private String nome;
    private String crm;
    private String sexo;
    @Column(name = "dt_nascimento")
    private Date dtNascimento;
    @OneToOne(cascade=CascadeType.ALL)
    @JoinColumn(name = "especialidade_id",nullable=false)
    private Especialidade especialidade;

}

Specialty

@Entity(name = "especialidade")
@Data
public class Especialidade {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;
    private String descricao;
}

Answer 1

To do Inner Join with JPQL it is not necessary to compare the ids, you can do so.

select m.id, m.nome, m.crm, e.descricao
from Medico m
inner join m.especialidade e

If you want your result to be returned in a DTO you need to specify the return object in the query,DTO needs to have a constructor with the fields you want to return.

select NEW pacote.meu.dto.MedicoEspecialidadeDTO(m.id, m.nome, m.crm, e.descricao)
from Medico m
inner join m.especialidade e

The order of the parameters must be the same as the constructor’s.