JOIN with CriteriaQuery?

Question

I'm trying do a JOIN using CriteriaQuery to get informations of my database but returns an exception about unable to resolve attribute and doesn't work. I'm looking for solution but still havent found. My code is bellow.

@Entity
@Table(name="cargo")
public class Cargo implements Serializable{
    private static final long serialVersionUID = 1L;

    @Id @GeneratedValue
    private Integer idCargo;    

    @NotNull @NotEmpty @Column(unique=true) 
    private String cargo;
}


@Entity
@Table(name="curriculum")
public class Curriculum implements Serializable{
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue
    private Long idCurriculum;

    @Transient @OneToOne @JoinColumn(name="idCurriculum")
    private Curriculum curriculum;

    @Temporal(TemporalType.DATE)
    private Date dataCad;



    @Size(min=5, max=50)
    @NotNull
    @NotEmpty   
    private String nome;

    @NotEmpty
    private String sexo;

    @Email
    @NotEmpty
    @NotNull
    @Size(max=250)
    @Column(unique=true)
    private String email;

    @NotNull
    @NotEmpty
    @Size(min=14, max=14)
    @Column(unique=true)
    private String cpf;

    @NotEmpty
    @NotNull
    @Size(min=8, max=8)
    private String senha;

    @OneToOne//(cascade = CascadeType.ALL)
    @NotNull
    @JoinColumn(name="idCargo")
    private Cargo cargo;

    private String ativado = "N";
}


@Entity
@Table(name="curriculum2")
public class Curriculum2 implements Serializable{
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue
    private Long idC2; //NOT NULL AUTO_INCREMENT,

    @NotNull @OneToOne(cascade=CascadeType.ALL,orphanRemoval=true) 
    @JoinColumn(name="idCurriculum")    
    private Curriculum curriculum; //NOT NULL,

    @NotNull @Temporal(TemporalType.DATE)
    private Date dataNasceu; //NOT NULL,

    @NotNull @NotEmpty @Size(min=5, max=50)
    private String cidade; //` varchar(50) NOT NULL,

    @NotNull @NotEmpty @Size(min=5, max=50)
    private String endereco; //` varchar(50) NOT NULL,

    @NotNull 
    private int numero; //` int(11) NOT NULL,

    @NotNull @NotEmpty @Size(min=5, max=50)
    private String bairro; //` varchar(50) NOT NULL,


    private String complemento;

    //14.790-000
    private String cep;

    private String estado;


    private String fone; //varchar(15)


    private String celular;


    private String pai;

    @NotNull @NotEmpty @Size(min=5, max=50)
    private String mae;//NOT NULL,

    @NotNull @NotEmpty @Size(min=5, max=50)
    private String nacionalidade; // NOT NULL,

    @NotNull @NotEmpty @Size(min=5, max=50)
    private String naturalidade; //NOT NULL,

    @NotNull @NotEmpty @Size(min=5, max=50)
    private String estcivil; //NOT NULL,


    private String rg;

    @NotNull @NotEmpty @Size(min=5, max=20)
    private String cartprof; //NOT NULL,

    @NotNull @NotEmpty @Size(min=2, max=20)
    private String serie; //NOT NULL,


    private String reservista; 
    private String titeleitor;
    private String zona;
    private String carthabilita;
    private String categoria;
    private BigDecimal ultimosalario;

    @NotNull @NotEmpty @Size(min=5, max=50)
    private String salariopretendido; //not null

    private String observacoes;    
}


@Entity
@Table(name="escolaridade")
public class Escolaridade implements Serializable{
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue
    private Integer idEscola;

    @NotNull @NotEmpty @Size(min=5,max=50)
    private String escola;

    @NotNull @NotEmpty @Size(min=5,max=50)
    private String cidade;

    @NotNull
    private String estado;

    @NotNull @Temporal(TemporalType.DATE)
    private Date inicio;

    @Temporal(TemporalType.DATE)
    private Date conclusao;

    @NotNull 
    private String ensino;

    @NotNull @OneToOne @JoinColumn(name="idCurriculum")
    private Curriculum curriculum;
}


@Entity
@Table(name="experiencia")
public class Experiencia implements Serializable{
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue
    private Integer idExperiencia;  
    @NotNull @NotEmpty @Size(min=5,max=50)
    private String empresa; 
    @NotNull @NotEmpty @Size(min=5,max=50)
    private String endereco;    
    @NotNull @NotEmpty @Size(min=5,max=50)
    private String cidade;  
    @NotNull @NotEmpty @Size(min=5,max=50)
    private String bairro;  
    @NotNull @NotEmpty
    private String estado;      
    @NotNull @Temporal(TemporalType.DATE) 
    private Date entrada;   
    @Temporal(TemporalType.DATE)
    private Date saida; 
    @NotNull @NotEmpty @Size(min=5,max=50)
    private String cargo;   
    private String funcoesDesemp;   
    private String fone;
    private String nomeEncarregado; 
    @NotNull @OneToOne @JoinColumn(name="idCurriculum")
    private Curriculum curriculum;
}

@Entity
@Table(name="aperfeicoamento")
public class Aperfeicoamento implements Serializable{
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue
    private Integer idAperfeicoamento;

    @NotNull @NotEmpty @Size(min=5, max=50)
    private String aperfeicoamento;

    @NotNull @NotEmpty @Size(min=5, max=50)
    private String entidadeensino;

    @NotNull @NotEmpty @Size(min=5, max=50)
    private String cidade;

    @NotNull 
    private String estado;

    @NotNull @Temporal(TemporalType.DATE)
    private Date inicio;

    @Temporal(TemporalType.DATE)
    private Date conclusao;

    @OneToOne 
    @JoinColumn(name="idCurriculum")    
    private Curriculum curriculum;
}


//return List<Curriculum>
public List<Curriculum> getCurriculumReport(Long id){
        List<Curriculum> lista = new ArrayList<Curriculum>();

        EntityManager em = datasource.getEntityProvider().getEntityManager();       
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Curriculum> c = cb.createQuery(Curriculum.class);        
        Root<Curriculum> root = c.from(Curriculum.class);
        Join<Curriculum, Cargo> joinCCargo = root.join("curriculum");
        Join<Curriculum, Curriculum2> joinCC2 = root.join("curriculum");
        Join<Curriculum, Escolaridade> joinCE = root.join("curriculum");
        Join<Curriculum, Aperfeicoamento> joinCA = root.join("curriculum");
        Join<Curriculum, Experiencia> joinCExp = root.join("curriculum");

        c.where(cb.equal(joinCCargo.get("idCurriculum"), cb.parameter(Long.class, "id")));
        TypedQuery q = em.createQuery(c);
        q.setParameter("id", id);

        lista = q.getResultList();

        return lista;
    }


//Exception returns
Caused by: java.lang.IllegalArgumentException: Unable to resolve attribute [curriculum] against path
    at org.hibernate.ejb.criteria.path.AbstractPathImpl.unknownAttribute(AbstractPathImpl.java:120)
    at org.hibernate.ejb.criteria.path.AbstractPathImpl.locateAttribute(AbstractPathImpl.java:229)
    at org.hibernate.ejb.criteria.path.AbstractFromImpl.join(AbstractFromImpl.java:411)
    at org.hibernate.ejb.criteria.path.AbstractFromImpl.join(AbstractFromImpl.java:397)
    at br.ind.ibg.dao.RelatorioDAO.getCurriculumReport(RelatorioDAO.java:33)
    at br.ind.ibg.reports.Report.<init>(Report.java:23)
    at br.ind.ibg.views.ViewCurriculum.buttonClick(ViewCurriculum.java:413)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at com.vaadin.event.ListenerMethod.receiveEvent(ListenerMethod.java:508)
    ... 37 more

Any idea ??

On this line `Join joinCCargo = root.join("curriculum");` you are trying to join Curriculum with Cargo but cargo does not have a Curriculum attribute (with join anottations). So check it and tell me so I can add a proper answer. Plus when you anottate an attribute with `@NotEmpty` it doesn't need a `@NotNull` because `@NotEmpty` already do a `@NotNull` validation. — Jorge Campos, May 26 '14 at 12:22
@JorgeCampos ...the relationship Curriculum with Cargo is OneToOne. Did you say to add the attribute curriculum in Cargo bean ? — FernandoPaiva, May 26 '14 at 13:47
@JorgeCampos but Curriculum have a Foreign Key to Cargo, using annotation OneToOne and JoinColumn(name="idCargo"), using SELECT * FROM curriculum INNER JOIN cargo ON (curriculum.idCargo = cargo.idCargo) its work, using CriteriaQuery I can't — FernandoPaiva, May 26 '14 at 19:48
Oooh, sorry I miss that. So then I suggest you to put one join at time to see which one is broke. Then analise the entities of that join. Another thing, i didn't read the specification but everytime I need to create entities I put the getters and setter method. I don't know if it is required. — Jorge Campos, May 26 '14 at 20:07

carbontax · Accepted Answer · 2014-05-28T12:14:34.420

0

This does not make sense based on your entity classes.

    Join<Curriculum, Cargo> joinCCargo = root.join("curriculum");

The type of root is Curriculum, which does indeed have a curriculum property, but the type of that property is not Cargo, it is Curriculum.

This looks like what you want.

    Join<Curriculum, Cargo> joinCCargo = root.join("cargo");

-- EDIT -- And so on for the other associations. If you need to join an entity that isn't mapped then your best solution is to map the entity. Map a collection of Experiencia objects to your Curriculum class with a property name of 'experienciaList' and then join it in your query by doing

   root.join("experienciaList");

Hibernate will know how to join the table because your mapping will define it.

Also, you really only need to create these named Join objects when you want to use them to create restrictions on your query something. If you just want to tell Hibernate to fetch the associations just do this.

   root.join("cargo").join("foo").join("bar");

edited May 28 '14 at 12:14

answered May 28 '14 at 03:43

carbontax

2,164
23
37

yep, I changed and works...But now I can't JOIN with others Entities(Curriculum2, Escolaridade, Experiencia, Aperfeicoamento). How I do this ? – FernandoPaiva May 28 '14 at 11:30
you said use mappedBy in annotation relationship ? example: OneToOne(mappedBy="curriculum") ? Looks like I did doesn't work ? – FernandoPaiva May 28 '14 at 12:49
I'm trying JOIN others Entities and returns this exception: Caused by: java.lang.IllegalArgumentException: Unable to resolve attribute [curriculum] against path [null] – FernandoPaiva May 28 '14 at 12:52
The path strings must match property names on your mapped entities. Cargo is mapped as a property named `cargo` on your Curriculum object, so the string "cargo" works. – carbontax May 28 '14 at 21:41
yeah, I understand. Entity(Escolaridade) have a attribute "curriculum" which make reference to Curriculum entity. On data base table Escolaridade have a Foreign Key to table Curriculum using field idCurriculum. So, Entity(Escolaridade) is OneToOne to Curriculum. When I do SELECT manually, I do SELECT * FROM curriculum JOIN escolaridade ON (curriculum.idCurriculum = escolaridade.idCurriculum) and returns results, but using CriteriaQuery I don't know how I do this. Can you understand me ? – FernandoPaiva May 29 '14 at 11:31
This is when you want "mappedBy". On Curriculum add a Escolaridade property named "escolaridade" and annotate it with @OneToOne(mappedBy="curriculum"). See discussions like http://stackoverflow.com/questions/787698/jpa-hibernate-one-to-one-relationship – carbontax May 29 '14 at 11:47

JOIN with CriteriaQuery?

1 Answers1

Linked