Select join with JPQL

2

Talk the guys! I'm grating here in my TCC and need some help.

I know the problem that I am must be very silly, but I do not have much knowledge. I searched everywhere, JPQL documentation and everything, but I could not solve it. I need to login to the system, looking for 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 makes the query in 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;
      }
   }

Persona.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 + "]";
    }


}

User.java

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 + "]";
    }



}
    
asked by anonymous 24.11.2017 / 00:07

2 answers

0

My login method got this way 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;
  }
}
    
05.03.2018 / 23:58
0

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 lead to Hibernate generating confusing, poorly optimized queries and even causing Hibernate to get lost (and make a mistake) by building more complex queries.

    
03.03.2018 / 13:11