I am trying to get my Spring Boot Backend to do an INNER JOIN Query and then I can display the data from that query in my Angular Frontend.

Spring Boot say me: "Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: persona is not mapped" "persona is not mapped [SELECT name,apellidos,fechaturno,medico FROM persona c INNER JOIN turnos c1 ON c.id = c1.idturno]"

Entity/Class "Persona"

@Entity @Table(name="persona")
public class Persona {
    
    @Id @Column @GeneratedValue(strategy=GenerationType.IDENTITY) private int id;
    @Column private String name;
    @Column private String apellidos;
    
    
    
    @OneToMany(mappedBy = "persona")
    List<Turno> turnos;
    

    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    
    
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    
    
    public String getApellidos() {
        return apellidos;
    }
    public void setApellidos(String apellidos) {
        this.apellidos = apellidos;
    }

    public List<Turno> getTurnos() {
        return turnos;
    }

    public void setTurnos(List<Turno> turnos) {
        this.turnos = turnos;
    } 
}

PersonaServiceImp

@Service
public class PersonaServiceImp implements PersonaService{
    
    @Autowired
    private PersonaRepositorio repositorio; 
    
    @Override
    public List<Persona> listar() {
        return repositorio.findAll();
    }

    @Override
    public Persona listarId(int id) {
        return repositorio.findByid(id);
    }

    @Override
    public Persona add(Persona p) {
        return repositorio.save(p);
    }

    @Override
    public Persona edit(Persona p) {
        return repositorio.save(p);
    }

    @Override
    public Persona delete(int id) {
        Persona p = repositorio.findByid(id);
        if(p!=null){
           repositorio.delete(p); 
        }
        return p;
    }
    
}

Entity/Class "Turno"

@Entity
@Table(name = "turnos")
public class Turno {
    
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "idturno")
    private int idturno;
    
    
    @Column(name = "fechaturno")
    private String fechaturno;
    
    @Column(name = "medico")
    private String medico;
    
    
    @ManyToOne
    private Persona persona;

    public Turno(){
        
    }

    public int getIdturno() {
        return idturno;
    }

    public void setIdturno(int idturno) {
        this.idturno = idturno;
    }

    public String getFechaturno() {
        return fechaturno;
    }

    public void setFechaturno(String fechaturno) {
        this.fechaturno = fechaturno;
    }

    public String getMedico() {
        return medico;
    }

    public void setMedico(String medico) {
        this.medico = medico;
    }

    public Persona getPersona() {
        return persona;
    }

    public void setPersona(Persona persona) {
        this.persona = persona;
    }   
}

now, the services/Repositorys "Persona Repositorio"

public interface PersonaRepositorio extends JpaRepository<Persona, Integer> {
    //list all person
    List<Persona>findAll();
    //list one person
    Persona findByid(int id);
    //save changes news or update
    Persona save(Persona p);
    //delete
    void delete(Persona p);
}

Persona Service

@Service
public interface PersonaService {
    // object type list
    List<Persona>listar(); //list all person

    Persona listarId(int id); //list one person
    Persona add(Persona p);//add one person
    Persona edit(Persona p);//edit one person
    Persona delete(int id);//delete one person 
}

and Turno Repository (where I think I would have to do the Query)

@Repository
public interface TurnoRepository extends JpaRepository<Turno, Integer> {
    
    @Query("SELECT name,apellidos,fechaturno,medico FROM persona c INNER JOIN turnos c1 ON c.id = c1.idturno")
    List<Turno> findByIdturno(int idturno);
    
    Turno findById(int idturno);
    
    
    
}

Context: The relation is that a person can have many shifts and when doing a query on that it will bring the names and surnames of the persons with the date of shift and the corresponding physician

but I can't get it to do the query

Obtaining data from an Inner Join query, to display the information in Angular.

CodePudding user response：

I'm a little bit confused, what you are trying to achieve.

1. If you are trying to find turnos by persona id, then do this, it will do the join for you under the hood.

List<Turno> findAllByPersonaId(int id);

2. If you are trying to get all turno, just simply do this. (Probably it's not it, because you wanna do a join)

List<Turno> findAll();

3. If you want to find all Turno by Persona and retrieve Persona data as well, then do this:

a, Create a Projection, which contains de necesary information you want to retrieve: (like this: https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#projections )

public interface TurnoPersonaProjection {
  String getName();
  String getApellidos();
  String getFechaturno();
  String getMedico()
}

b, then retreive projection with the query:

@Query("SELECT p.name,p.apellidos,c.fechaturno,c.medico FROM Turno t INNER JOIN Persona p ON t.idturno = p.id WHERE p.id = :personaId")
    List<TurnoPersonaProjection> findTurnoPersonaProjectionByPersonaId(int personaId);

PS: PLEASE develop in English