Select Join with JPQL

Question

Talk guys! I’m busting my chops here on my TCC and I need your help.

I know the trouble I’m in must be very silly, but I don’t have much knowledge. I searched everywhere, JPQL documentation and everything, but I couldn’t fix it. I need to log in to the system, searching matricula in class Pessoa and senha in class Usuario, but I’m having trouble creating the JPQL query

Method login of my bean of Usuario

public void login() {
        try {
            usuarioLogado = usuarios.login(usuario.getPessoa().getMatricula(), usuario.getSenha());
            System.out.println("MATRICULA: "+ getMatricula() + "SENHA: " + usuario.getSenha());

            if (usuarioLogado == null) {
                messages.info("CPF e/ou senha incorretos");
                return;
            }

            Faces.redirect("./paginas/Index.xhtml");
        } catch (IOException erro) {
            erro.printStackTrace();
            Messages.addGlobalError(erro.getMessage());
        }
    }

Method login that queries the database that is called from the repository Usuarios

public Usuario login(Long matricula, String senha) {
        try {
            Usuario usuario = (Usuario) manager
                       .createQuery(
                                   "SELECT u from Usuario u where u.pessoa.matricula = :matricula and u.senha = :senha ")
                       .setParameter("matricula", matricula)
                       .setParameter("senha", senha).getSingleResult();

            return usuario;
      } catch (NoResultException e) {
            return null;
      }
   }

Pessoa.java

   package com.daniel.monografia.model;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.validation.constraints.NotNull;

import org.hibernate.validator.constraints.Email;

@Entity
@Table(name="pessoa")
public class Pessoa implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(length = 50, nullable = false)
    private String nome;

    @Column(length = 100, nullable = false)
    @Email
    private String email;

    @NotNull
    @Column(nullable = false, length = 8)
    private Long matricula;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getNome() {
        return nome;
    }

    public void setNome(String nome) {
        this.nome = nome;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Long getMatricula() {
        return matricula;
    }

    public void setMatricula(Long matricula) {
        this.matricula = matricula;
    }

    @Override
    public int hashCode() {
        final int prime = 31;
        int result = 1;
        result = prime * result + ((id == null) ? 0 : id.hashCode());
        return result;
    }

    @Override
    public boolean equals(Object obj) {
        if (this == obj)
            return true;
        if (obj == null)
            return false;
        if (getClass() != obj.getClass())
            return false;
        Pessoa other = (Pessoa) obj;
        if (id == null) {
            if (other.id != null)
                return false;
        } else if (!id.equals(other.id))
            return false;
        return true;
    }

    @Override
    public String toString() {
        return "Pessoa [id=" + id + "]";
    }


}

Java user.

package com.daniel.monografia.model;

import java.io.Serializable;
import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToOne;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import javax.persistence.Transient;
import javax.validation.constraints.NotNull;

import org.hibernate.validator.constraints.NotEmpty;

@Entity
@Table(name="usuario")
public class Usuario implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @NotEmpty
    @Column(nullable = false, length = 8)
    private String senha;

    @Column(nullable = false)
    private Character tipo;

    @OneToOne
    @JoinColumn(nullable = false)
    private Pessoa pessoa;

    //Campos para usar no envio de email
    @Transient
    private String usuarioEmail = "[email protected]";
    @Transient
    private String senhaEmail = "monografia123";
    @Transient
    private String assunto;
    @Transient
    private String mensagem;


    public Usuario() {

    } 

    public Usuario(Long matricula, String senha) {
        this.senha = senha;

    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getSenha() {
        return senha;
    }

    public void setSenha(String senha) {
        this.senha = senha;
    }

    public String getUsuarioEmail() {
        return usuarioEmail;
    }

    public void setUsuarioEmail(String usuarioEmail) {
        this.usuarioEmail = usuarioEmail;
    }

    public String getSenhaEmail() {
        return senhaEmail;
    }

    public void setSenhaEmail(String senhaEmail) {
        this.senhaEmail = senhaEmail;
    }

    public String getAssunto() {
        return assunto;
    }

    public void setAssunto(String assunto) {
        this.assunto = assunto;
    }

    public String getMensagem() {
        return mensagem;
    }

    public void setMensagem(String mensagem) {
        this.mensagem = mensagem;
    }

    public Character getTipo() {
        return tipo;
    }

    public void setTipo(Character tipo) {
        this.tipo = tipo;
    }

    public Pessoa getPessoa() {
        return pessoa;
    }

    public void setPessoa(Pessoa pessoa) {
        this.pessoa = pessoa;
    }

    @Override
    public int hashCode() {
        final int prime = 31;
        int result = 1;
        result = prime * result + ((id == null) ? 0 : id.hashCode());
        return result;
    }

    @Override
    public boolean equals(Object obj) {
        if (this == obj)
            return true;
        if (obj == null)
            return false;
        if (getClass() != obj.getClass())
            return false;
        Usuario other = (Usuario) obj;
        if (id == null) {
            if (other.id != null)
                return false;
        } else if (!id.equals(other.id))
            return false;
        return true;
    }

    @Override
    public String toString() {
        return "Usuario [id=" + id + "]";
    }



}

Answer 1

Given the entities, this would be the JPQL query:

select usuario from Usuario usuario
JOIN usuario.pessoa pessoa
WHERE usuario.senha = :senha
AND pessoa.matricula = :matricula

As a hint, always avoid JPQL isto.aquilo.aquele. Explain the Joins, as I did above. This can cause Hibernate to generate confusing, poorly optimized queries and even cause Hibernate to get lost (and generate an error) when mounting more complex queries.

Answer 2

My login method looked like this and solved my problem

//Método login usando JPQL
public Usuario login(Long matricula, String senha) {
    try {
        Usuario usuario = (Usuario) manager
                   .createQuery(
                               "SELECT u from Usuario u join u.pessoa p where p.matricula = :matricula and u.senha = :senha ", Usuario.class)
                   .setParameter("matricula", matricula)
                   .setParameter("senha", senha).getSingleResult();

        return usuario;
  } catch (NoResultException e) {
        return null;
  }
}