Transformar sql em JPQL

Boa noite, galera estou com um problema eu tenho minha entidade ‘Cliente’ e uma outra que chama ‘ClienteFilial’ com os campos idMatriz e idFilial.

Estou precisando fazer uma consulta que me mostre todas as possiveis filiais para um determinado cliente sendo que o id do cliente deve ser diferente do cliente e que ele nao seja uma filial deste cliente a ser mostrado segue o sql agora como transformar em jpql

SELECT c.* FROM Cliente c LEFT OUTER JOIN ClienteFilial cf on cf.idMatriz=c.id WHERE c.id<> 1 and((cf.idFilial <> 1)or (cf.idFilial is null))

Segue minha entidade cliente

package br.com.atlantica.atlanticaSped.orm.entity;

import java.io.Serializable;
import java.util.Collection;
import javax.persistence.Basic;
import javax.persistence.CascadeType;
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.ManyToOne;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.OneToMany;
import javax.persistence.PrePersist;
import javax.persistence.Table;
import javax.persistence.UniqueConstraint;
import br.com.atlantica.atlanticaSped.util.JPAUtil;


@Entity
@Table(name = "Cliente", catalog = "validadorSped", schema = "", uniqueConstraints = {
		@UniqueConstraint(columnNames = {"codigo"}), @UniqueConstraint(columnNames = {"cpf"}),
		@UniqueConstraint(columnNames = {"cnpj"})})
@NamedQueries( {
		@NamedQuery(name = "Cliente.findAll", query = "SELECT c FROM Cliente c"),
		@NamedQuery(name = "Cliente.findById", query = "SELECT c FROM Cliente c WHERE c.id = :id"),
		@NamedQuery(name = "Cliente.findByCodigo", query = "SELECT c FROM Cliente c WHERE c.codigo = :codigo"),
		@NamedQuery(name = "Cliente.findByRazao", query = "SELECT c FROM Cliente c WHERE c.razao = :razao"),
		@NamedQuery(name = "Cliente.findByFantasia", query = "SELECT c FROM Cliente c WHERE c.fantasia = :fantasia"),
		@NamedQuery(name = "Cliente.findByCnpj", query = "SELECT c FROM Cliente c WHERE c.cnpj = :cnpj"),
		@NamedQuery(name = "Cliente.findByCpf", query = "SELECT c FROM Cliente c WHERE c.cpf = :cpf"),
		@NamedQuery(name = "Cliente.findByCnpjAndIDDiferente", query = "SELECT c FROM Cliente c WHERE c.id<> :id and c.cnpj = :cnpj"),
		@NamedQuery(name = "Cliente.findByCpfAndIDDiferente", query = "SELECT c FROM Cliente c WHERE c.id<> :id and c.cpf = :cpf"),
		@NamedQuery(name = "Cliente.findByInscrEstadual", query = "SELECT c FROM Cliente c WHERE c.inscrEstadual = :inscrEstadual"),
		@NamedQuery(name = "Cliente.findByInscrMunicipal", query = "SELECT c FROM Cliente c WHERE c.inscrMunicipal = :inscrMunicipal"),
		@NamedQuery(name = "Cliente.findByFone", query = "SELECT c FROM Cliente c WHERE c.fone = :fone"),
		@NamedQuery(name = "Cliente.findByFax", query = "SELECT c FROM Cliente c WHERE c.fax = :fax"),
		@NamedQuery(name = "Cliente.findByEndereco", query = "SELECT c FROM Cliente c WHERE c.endereco = :endereco"),
		@NamedQuery(name = "Cliente.findByNumero", query = "SELECT c FROM Cliente c WHERE c.numero = :numero"),
		@NamedQuery(name = "Cliente.findByComplemento", query = "SELECT c FROM Cliente c WHERE c.complemento = :complemento"),
		@NamedQuery(name = "Cliente.findByCep", query = "SELECT c FROM Cliente c WHERE c.cep = :cep"),
		@NamedQuery(name = "Cliente.findByBairro", query = "SELECT c FROM Cliente c WHERE c.bairro = :bairro"),
		@NamedQuery(name = "Cliente.findByCidade", query = "SELECT c FROM Cliente c WHERE c.cidade = :cidade"),
		@NamedQuery(name = "Cliente.findByEmail", query = "SELECT c FROM Cliente c WHERE c.email = :email")})
public class Cliente implements Serializable
{
	private static final long				serialVersionUID	= 1L;
	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	@Basic(optional = false)
	@Column(name = "id", nullable = false)
	private Integer							id;
	@Basic(optional = false)
	@Column(name = "codigo", nullable = false)
	private int									codigo;
	@Basic(optional = false)
	@Column(name = "razao", nullable = false, length = 100)
	private String								razao;
	@Basic(optional = false)
	@Column(name = "fantasia", nullable = false, length = 60)
	private String								fantasia;
	@Column(name = "cnpj", length = 14)
	private String								cnpj;
	@Column(name = "cpf", length = 11)
	private String								cpf;
	@Basic(optional = false)
	@Column(name = "inscrEstadual", nullable = false, length = 14)
	private String								inscrEstadual;
	@Column(name = "inscrMunicipal", length = 255)
	private String								inscrMunicipal;
	@Column(name = "fone", length = 10)
	private String								fone;
	@Column(name = "fax", length = 10)
	private String								fax;
	@Basic(optional = false)
	@Column(name = "endereco", nullable = false, length = 60)
	private String								endereco;
	@Column(name = "numero", length = 10)
	private String								numero;
	@Column(name = "complemento", length = 60)
	private String								complemento;
	@Basic(optional = false)
	@Column(name = "cep", nullable = false, length = 8)
	private String								cep;
	@Basic(optional = false)
	@Column(name = "bairro", nullable = false, length = 60)
	private String								bairro;
	@Basic(optional = false)
	@Column(name = "cidade", nullable = false, length = 60)
	private String								cidade;
	@Column(name = "email", length = 255)
	private String								email;
	@OneToMany(cascade = CascadeType.ALL, mappedBy = "cliente")
	private Collection<ClienteFilial>	clienteFilialCollection;
	@OneToMany(cascade = CascadeType.ALL, mappedBy = "cliente1")
	private Collection<ClienteFilial>	clienteFilialCollection1;
	@JoinColumn(name = "idUnidadeFederativa", referencedColumnName = "id", nullable = false)
	@ManyToOne(optional = false)
	private UnidadeFederativa				unidadeFederativa;
	@OneToMany(cascade = CascadeType.ALL, mappedBy = "cliente")
	private Collection<ClienteEmpresa>	clienteEmpresaCollection;

	public Cliente()
	{
	}

	public Cliente(Integer id)
	{
		this.id = id;
	}

	public Cliente(Integer id, int codigo, String razao, String fantasia, String inscrEstadual, String endereco, String cep, String bairro, String cidade)
	{
		this.id = id;
		this.codigo = codigo;
		this.razao = razao;
		this.fantasia = fantasia;
		this.inscrEstadual = inscrEstadual;
		this.endereco = endereco;
		this.cep = cep;
		this.bairro = bairro;
		this.cidade = cidade;
	}

	public Integer getId()
	{
		return id;
	}

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

	public int getCodigo()
	{
		return codigo;
	}

	public void setCodigo(int codigo)
	{
		this.codigo = codigo;
	}

	public String getRazao()
	{
		return razao;
	}

	public void setRazao(String razao)
	{
		this.razao = razao;
	}

	public String getFantasia()
	{
		return fantasia;
	}

	public void setFantasia(String fantasia)
	{
		this.fantasia = fantasia;
	}

	public String getCnpj()
	{
		return cnpj;
	}

	public void setCnpj(String cnpj)
	{
		this.cnpj = cnpj;
	}

	public String getCpf()
	{
		return cpf;
	}

	public void setCpf(String cpf)
	{
		this.cpf = cpf;
	}

	public String getInscrEstadual()
	{
		return inscrEstadual;
	}

	public void setInscrEstadual(String inscrEstadual)
	{
		this.inscrEstadual = inscrEstadual;
	}

	public String getInscrMunicipal()
	{
		return inscrMunicipal;
	}

	public void setInscrMunicipal(String inscrMunicipal)
	{
		this.inscrMunicipal = inscrMunicipal;
	}

	public String getFone()
	{
		return fone;
	}

	public void setFone(String fone)
	{
		this.fone = fone;
	}

	public String getFax()
	{
		return fax;
	}

	public void setFax(String fax)
	{
		this.fax = fax;
	}

	public String getEndereco()
	{
		return endereco;
	}

	public void setEndereco(String endereco)
	{
		this.endereco = endereco;
	}

	public String getNumero()
	{
		return numero;
	}

	public void setNumero(String numero)
	{
		this.numero = numero;
	}

	public String getComplemento()
	{
		return complemento;
	}

	public void setComplemento(String complemento)
	{
		this.complemento = complemento;
	}

	public String getCep()
	{
		return cep;
	}

	public void setCep(String cep)
	{
		this.cep = cep;
	}

	public String getBairro()
	{
		return bairro;
	}

	public void setBairro(String bairro)
	{
		this.bairro = bairro;
	}

	public String getCidade()
	{
		return cidade;
	}

	public void setCidade(String cidade)
	{
		this.cidade = cidade;
	}

	public String getEmail()
	{
		return email;
	}

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

	public Collection<ClienteFilial> getClienteFilialCollection()
	{
		return clienteFilialCollection;
	}

	public void setClienteFilialCollection(Collection<ClienteFilial> clienteFilialCollection)
	{
		this.clienteFilialCollection = clienteFilialCollection;
	}

	public Collection<ClienteFilial> getClienteFilialCollection1()
	{
		return clienteFilialCollection1;
	}

	public void setClienteFilialCollection1(Collection<ClienteFilial> clienteFilialCollection1)
	{
		this.clienteFilialCollection1 = clienteFilialCollection1;
	}

	public UnidadeFederativa getUnidadeFederativa()
	{
		return unidadeFederativa;
	}

	public void setUnidadeFederativa(UnidadeFederativa unidadeFederativa)
	{
		this.unidadeFederativa = unidadeFederativa;
	}

	public Collection<ClienteEmpresa> getClienteEmpresaCollection()
	{
		return clienteEmpresaCollection;
	}

	public void setClienteEmpresaCollection(Collection<ClienteEmpresa> clienteEmpresaCollection)
	{
		this.clienteEmpresaCollection = clienteEmpresaCollection;
	}

	@Override
	public int hashCode()
	{
		int hash = 0;
		hash += (id != null ? id.hashCode() : 0);
		return hash;
	}

	@Override
	public boolean equals(Object object)
	{
		// TODO: Warning - this method won't work in the case the id fields are
		// not set
		if(!(object instanceof Cliente))
		{
			return false;
		}
		Cliente other = (Cliente) object;
		if((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id)))
		{
			return false;
		}
		return true;
	}

	@Override
	public String toString()
	{
		return razao;
	}

	@PrePersist
	public void prePersistEvent()
	{
		if(codigo == 0)
		{
			Object max = JPAUtil.getEntityManager().createQuery("select max(codigo) from Cliente").getSingleResult();
			if(max != null)
				setCodigo(Integer.parseInt(max.toString()) + 1);
			else
				setCodigo(1);
		}
	}
}

E miha entidade ClienteFilial

package br.com.atlantica.atlanticaSped.orm.entity;

import java.io.Serializable;
import javax.persistence.Basic;
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.ManyToOne;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;
import javax.persistence.UniqueConstraint;


@Entity
@Table(name = "ClienteFilial", catalog = "validadorSped", schema = "", uniqueConstraints = {@UniqueConstraint(columnNames = {
		"idMatriz", "idFilial"})})
@NamedQueries( {@NamedQuery(name = "ClienteFilial.findAll", query = "SELECT c FROM ClienteFilial c"),
		@NamedQuery(name = "ClienteFilial.findById", query = "SELECT c FROM ClienteFilial c WHERE c.id = :id")})
public class ClienteFilial implements Serializable
{
	private static final long	serialVersionUID	= 1L;
	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	@Basic(optional = false)
	@Column(name = "id", nullable = false)
	private Integer				id;
	@JoinColumn(name = "idFilial", referencedColumnName = "id", nullable = false)
	@ManyToOne(optional = false)
	private Cliente				cliente;
	@JoinColumn(name = "idMatriz", referencedColumnName = "id", nullable = false)
	@ManyToOne(optional = false)
	private Cliente				cliente1;

	public ClienteFilial()
	{
	}

	public ClienteFilial(Integer id)
	{
		this.id = id;
	}

	public Integer getId()
	{
		return id;
	}

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

	public Cliente getCliente()
	{
		return cliente;
	}

	public void setCliente(Cliente cliente)
	{
		this.cliente = cliente;
	}

	public Cliente getCliente1()
	{
		return cliente1;
	}

	public void setCliente1(Cliente cliente1)
	{
		this.cliente1 = cliente1;
	}

	@Override
	public int hashCode()
	{
		int hash = 0;
		hash += (id != null ? id.hashCode() : 0);
		return hash;
	}

	@Override
	public boolean equals(Object object)
	{
		// TODO: Warning - this method won't work in the case the id fields are
		// not set
		if(!(object instanceof ClienteFilial))
		{
			return false;
		}
		ClienteFilial other = (ClienteFilial) object;
		if((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id)))
		{
			return false;
		}
		return true;
	}

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

Ou se alguem tiver ideia de uma consulta que funcione!
Desde já grato

1 Resposta

Topicos relacionados