JPA...Duvida ao fazer createQuery...com join

Assuntos Gerais
#1

Olá,

umas duvidas pois estou acostumado a usar DAO ( fazendo sql) e estou convertendo um projeto
para JPA usando Hibernate.

Tenho no modelo o seguinte :

NotaFiscal (1-1) Cliente
NotaFiscal (1-N) NotaFiscalItem

Tenho uma tela de consulta onde de como parametro o estado e tenho que retornar
todas as notas fiscais , notas fiscais itens e clientes desse estado .

Estou tentando fazer join com createQuery (JPA) mas não vai ???

Alguém teria algum exemplo pra sanar essa minha duvida ???

Se alguem puder me ajudar agradeceria ???

{code]

– to fazendo isso mas da erro
– e não sei se está certo

select nota , item,cliente from notafiscal nota join nota.cliente as cliente join nota.notafiscalitem as item
where cliente.estado=“SP”
[/code]


public static void main(String[] args) {
		
		//org.apache.log4j.BasicConfigurator.configure();
		EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPA9");
        EntityManager em = emf.createEntityManager();
		
		
		System.out.println("Inico ...testCreateQuery()");

		List listas = em.createQuery("select nota from NotaFiscal as nota where nota.id=3").getResultList();
					
		System.out.println("Size listas = "+listas.size());
        
        for (int i=0;i<listas.size();i++){
        	NotaFiscal  rs = (NotaFiscal) listas.get(i);
        	if (i==0){
            	System.out.println("Nota  ID  ="+rs.getId());
            	System.out.println("Nome  ="+rs.getDescricao());
	      	    System.out.println("___________________________________________");
        		
	    		System.out.println("   Cliente ID ="+rs.getCliente().getId());
	    		System.out.println("   Cliente Nome = "+rs.getCliente().getNome());
	    		System.out.println("   Cliente Estado = "+rs.getCliente().getEstado());
	      	    System.out.println("___________________________________________");

        	}         	
        	
        	
        	for (NotaFiscalItem it :rs.getItenss()){
        		System.out.println("   Item ID ="+it.getId());
        		System.out.println("   Seq Item = "+it.getSeqItem());
        		System.out.println("   Qtde Item = "+it.getQtdeItem());
        		System.out.println("   Valor Item = "+it.getValor());
        	}
        	System.out.println("___________________________________________");
        	
        }

	}	

---

import java.util.ArrayList;
import java.util.List;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToMany;
import javax.persistence.OneToOne;
import javax.persistence.Table;



@Entity
@Table(name="Nota_Fiscal")
public class NotaFiscal {
	
	@Id
	@GeneratedValue
	@Column(name="ID_NOTA")
	private int id;
	@Column(name="descricao")
	private String descricao;
	@OneToMany(mappedBy= "notafiscal",cascade =javax.persistence.CascadeType.ALL)
	private List><NotaFiscalItem> itenss = new ArrayList<NotaFiscalItem>();

	@OneToOne
	@JoinColumn(name = "ID_CLIENTE")

	private Cliente cliente;
	public NotaFiscal(){
		
	}
	public int getId() {
		return id;
	}

	public void adicionaNotaFiscalItem(NotaFiscalItem item) {
		itenss.add(item);
	}
	public void setId(int id) {
		this.id = id;
	}


	
	
	public String getDescricao() {
		return descricao;
	}
	public void setDescricao(String descricao) {
		this.descricao = descricao;
	}
	public Cliente getCliente() {
		return cliente;
	}
	public void setCliente(Cliente cliente) {
		this.cliente = cliente;
	}
	public List<NotaFiscalItem> getItenss() {
		return itenss;
	}
	public void setItenss(List<NotaFiscalItem> itenss) {
		this.itenss = itenss;
	}
	

---

import java.util.ArrayList;
import java.util.List;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToMany;
import javax.persistence.OneToOne;
import javax.persistence.Table;



@Entity
@Table(name="Nota_Fiscal")
public class NotaFiscal {
	
	@Id
	@GeneratedValue
	@Column(name="ID_NOTA")
	private int id;
	@Column(name="descricao")
	private String descricao;
	@OneToMany(mappedBy= "notafiscal",cascade =javax.persistence.CascadeType.ALL)
	private List<NotaFiscalItem> itenss = new ArrayList<NotaFiscalItem>();

	@OneToOne
	@JoinColumn(name = "ID_CLIENTE")

	private Cliente cliente;
	public NotaFiscal(){
		
	}
	public int getId() {
		return id;
	}

	public void adicionaNotaFiscalItem(NotaFiscalItem item) {
		itenss.add(item);
	}
	public void setId(int id) {
		this.id = id;
	}


	
	
	public String getDescricao() {
		return descricao;
	}
	public void setDescricao(String descricao) {
		this.descricao = descricao;
	}
	public Cliente getCliente() {
		return cliente;
	}
	public void setCliente(Cliente cliente) {
		this.cliente = cliente;
	}
	public List<NotaFiscalItem> getItenss() {
		return itenss;
	}
	public void setItenss(List<NotaFiscalItem> itenss) {
		this.itenss = itenss;
	}
	
	
}
---
package br.com.exemplojpa.modelo;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

@Entity
@Table( name="Nota_Fiscal_Item")
public class NotaFiscalItem {
	
	
	@Id
	@GeneratedValue
	@Column(name="id_item")
    private int id;
	
	@Column
	private int seqItem;
	
	@Column
	private int qtdeItem;

    private double valor;
	
	@ManyToOne(fetch = FetchType.EAGER)
	@JoinColumn (name = "FK_ID_NOTA")	
	private NotaFiscal notafiscal;

	public NotaFiscalItem(int xqtde){
		this.qtdeItem= xqtde;
	}
	
    public NotaFiscalItem( String nome){
		
	}
    
    public NotaFiscalItem(){
	
	}
	public int getId() {
		return id;
	}


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


	public int getSeqItem() {
		return seqItem;
	}


	public void setSeqItem(int seqItem) {
		this.seqItem = seqItem;
	}


	public int getQtdeItem() {
		return qtdeItem;
	}


	public void setQtdeItem(int qtdeItem) {
		this.qtdeItem = qtdeItem;
	}


	public NotaFiscal getNotafiscal() {
		return notafiscal;
	}


	public void setNotafiscal(NotaFiscal notafiscal) {
		this.notafiscal = notafiscal;
	}

	public double getValor() {
		return valor;
	}

	public void setValor(double valor) {
		this.valor = valor;
	}
	
	
}


---
#2

E mais

tentei usar



	List listas = em.createQuery("Select nota FROM NotaFiscal nota JOIN nota.NotaFiscalItem item JOIN nota.cliente cliente where estado='SP'").getResultList();

[code]

e deu esse erro


Exception in thread "main" java.lang.IllegalArgumentException: org.hibernate.QueryException: could not resolve property: NotaFiscalItem of: br.com.exemplojpa.modelo.NotaFiscal [Select nota FROM br.com.exemplojpa.modelo.NotaFiscal nota JOIN nota.NotaFiscalItem item JOIN nota.cliente cliente where estado='SP']
	at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:624)
	at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:96)
	at br.com.exemplojpa.teste.TesteNFCreateQuery1.main(TesteNFCreateQuery1.java:30)
Caused by: org.hibernate.QueryException: could not resolve property: NotaFiscalItem of: br.com.exemplojpa.modelo.NotaFiscal [Select nota FROM br.com.exemplojpa.modelo.NotaFiscal nota JOIN nota.NotaFiscalItem item JOIN nota.cliente cliente where estado='SP']
	at org.hibernate.persister.entity.AbstractPropertyMapping.propertyException(AbstractPropertyMapping.java:44)
	at org.hibernate.persister.entity.AbstractPropertyMapping.toType(AbstractPropertyMapping.java:38)
	at org.hibernate.persister.entity.AbstractEntityPersister.toType(AbstractEntityPersister.java:1362)
	at org.hibernate.hql.ast.tree.FromElementType.getPropertyType(FromElementType.java:279)
	at org.hibernate.hql.ast.tree.FromElement.getPropertyType(FromElement.java:386)
	at org.hibernate.hql.ast.tree.DotNode.getDataType(DotNode.java:567)
	at org.hibernate.hql.ast.tree.DotNode.prepareLhs(DotNode.java:241)
	at org.hibernate.hql.ast.tree.DotNode.resolve(DotNode.java:188)
	at org.hibernate.hql.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:94)
	at org.hibernate.hql.ast.HqlSqlWalker.createFromJoinElement(HqlSqlWalker.java:308)
	at org.hibernate.hql.antlr.HqlSqlBaseWalker.joinElement(HqlSqlBaseWalker.java:3275)
	at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3067)
	at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:2945)
	at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromClause(HqlSqlBaseWalker.java:688)
	at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:544)
	at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:281)
	at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:229)
	at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:228)
	at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:160)
	at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
	at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
	at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
	at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
	at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
	at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
	at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)
	at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:93)
	... 1 more

public static void main(String[] args) {

	EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPA9");
    EntityManager em = emf.createEntityManager();
	
	
	System.out.println("Inico ...testCreateQuery()");

	List listas = em.createQuery("Select nota FROM NotaFiscal nota JOIN nota.NotaFiscalItem item JOIN nota.cliente cliente where estado='SP'").getResultList();

	System.out.println("Size listas = "+listas.size());
    
    for (int i=0;i<listas.size();i++){
    	NotaFiscal  rs = (NotaFiscal) listas.get(i);
    	if (i==0){
        	System.out.println("Nota  ID  ="+rs.getId());
        	System.out.println("Nome  ="+rs.getDescricao());
      	    System.out.println("___________________________________________");
    		
    		System.out.println("   Cliente ID ="+rs.getCliente().getId());
    		System.out.println("   Cliente Nome = "+rs.getCliente().getNome());
    		System.out.println("   Cliente Estado = "+rs.getCliente().getEstado());
      	    System.out.println("___________________________________________");

    	}         	
    	
    	
    	for (NotaFiscalItem it :rs.getItenss()){
    		System.out.println("   Item ID ="+it.getId());
    		System.out.println("   Seq Item = "+it.getSeqItem());
    		System.out.println("   Qtde Item = "+it.getQtdeItem());
    		System.out.println("   Valor Item = "+it.getValor());
    	}
    	System.out.println("___________________________________________");
    	
    }

}

[/code]>

#3

E mais

tentei usar

Alguém poderia me ajudar ???





		List listas = em.createQuery("Select nota FROM NotaFiscal nota JOIN nota.NotaFiscalItem item JOIN nota.cliente cliente where estado='SP'").getResultList();

e deu esse erro


Exception in thread "main" java.lang.IllegalArgumentException: org.hibernate.QueryException: could not resolve property: NotaFiscalItem of: br.com.exemplojpa.modelo.NotaFiscal [Select nota FROM br.com.exemplojpa.modelo.NotaFiscal nota JOIN nota.NotaFiscalItem item JOIN nota.cliente cliente where estado='SP']
	at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:624)
	at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:96)
	at br.com.exemplojpa.teste.TesteNFCreateQuery1.main(TesteNFCreateQuery1.java:30)
Caused by: org.hibernate.QueryException: could not resolve property: NotaFiscalItem of: br.com.exemplojpa.modelo.NotaFiscal [Select nota FROM br.com.exemplojpa.modelo.NotaFiscal nota JOIN nota.NotaFiscalItem item JOIN nota.cliente cliente where estado='SP']
	at org.hibernate.persister.entity.AbstractPropertyMapping.propertyException(AbstractPropertyMapping.java:44)
	at org.hibernate.persister.entity.AbstractPropertyMapping.toType(AbstractPropertyMapping.java:38)
	at org.hibernate.persister.entity.AbstractEntityPersister.toType(AbstractEntityPersister.java:1362)
	at org.hibernate.hql.ast.tree.FromElementType.getPropertyType(FromElementType.java:279)
	at org.hibernate.hql.ast.tree.FromElement.getPropertyType(FromElement.java:386)
	at org.hibernate.hql.ast.tree.DotNode.getDataType(DotNode.java:567)
	at org.hibernate.hql.ast.tree.DotNode.prepareLhs(DotNode.java:241)
	at org.hibernate.hql.ast.tree.DotNode.resolve(DotNode.java:188)
	at org.hibernate.hql.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:94)
	at org.hibernate.hql.ast.HqlSqlWalker.createFromJoinElement(HqlSqlWalker.java:308)
	at org.hibernate.hql.antlr.HqlSqlBaseWalker.joinElement(HqlSqlBaseWalker.java:3275)
	at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3067)
	at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:2945)
	at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromClause(HqlSqlBaseWalker.java:688)
	at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:544)
	at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:281)
	at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:229)
	at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:228)
	at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:160)
	at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
	at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
	at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
	at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
	at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
	at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
	at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)
	at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:93)
	... 1 more

public static void main(String[] args) {

	EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPA9");
    EntityManager em = emf.createEntityManager();
	
	
	System.out.println("Inico ...testCreateQuery()");

	List listas = em.createQuery("Select nota FROM NotaFiscal nota JOIN nota.NotaFiscalItem item JOIN nota.cliente cliente where estado='SP'").getResultList();

	System.out.println("Size listas = "+listas.size());
    
    for (int i=0;i<listas.size();i++){
    	NotaFiscal  rs = (NotaFiscal) listas.get(i);
    	if (i==0){
        	System.out.println("Nota  ID  ="+rs.getId());
        	System.out.println("Nome  ="+rs.getDescricao());
      	    System.out.println("___________________________________________");
    		
    		System.out.println("   Cliente ID ="+rs.getCliente().getId());
    		System.out.println("   Cliente Nome = "+rs.getCliente().getNome());
    		System.out.println("   Cliente Estado = "+rs.getCliente().getEstado());
      	    System.out.println("___________________________________________");

    	}         	
    	
    	
    	for (NotaFiscalItem it :rs.getItenss()){
    		System.out.println("   Item ID ="+it.getId());
    		System.out.println("   Seq Item = "+it.getSeqItem());
    		System.out.println("   Qtde Item = "+it.getQtdeItem());
    		System.out.println("   Valor Item = "+it.getValor());
    	}
    	System.out.println("___________________________________________");
    	
    }

}

[/code]>

#4

ufa consegui descobrir o que estava fazendo de errado …

lá vai se alguém cair no mesmo erro

!!!


StringBuffer jhl = new StringBuffer();
jhl.append("SELECT user FROM ImsUserImpl user JOIN user.documents doc where doc.user=6 ");
			
			
List  usuariosdoctos = imsDBMng.getManager().createQuery(jhl.toString()).getResultList();

o join é feito em cima do atributos que estão dentro do objeto User
e o documents é um List declarado dentro do objeto User

public class User{
	@Id
	@GeneratedValue
	@Column(name = "USER_ID")
	/** Identificador único */
	private Long id;
	@Column(nullable = false)
	/** Nome do usuário */
	private String name;
	@Column(nullable = false)
	/** E-mail do usuário */
	private String email;

                @OneToMany(mappedBy = "user",cascade =javax.persistence.CascadeType.ALL)
	private List<Document> documents = new ArrayList<Document>();

//

classe Document

@ManyToOne
private User user
#5