CODEWITHSUNDEEP
javahibernatepersistencecriteria
AHungerArtist
AHungerArtist

Reputation: 9579

Creating a Hibernate Criteria that can filter by referenced entity

I have an entity (PersonQuestionsEntity) that has a PersonEntity and QuestionEntity as its primary key. I use a composite key to reflect this relationship.

Now, I want to create a Criteria object that can do the following: Find all PersonQuestion entities for a given questionId and a person's age.

Here is my attempt at creating a Criteria for this:

    Session session = getHibernateTemplate().getSessionFactory().openSession();
    Criteria criteria = session.createCriteria(PersonQuestionsEntity.class);
    criteria.add(Restrictions.eq("question.questionId", "87"));
    criteria = criteria.createCriteria("person");
    criteria.add(Restrictions.eq("age", 23));
    criteria.setMaxResults(100);
    List l = criteria.list();

The problem is that I get this error: 

Caused by: java.sql.SQLException: ORA-00904: "PERSONENTI1_"."AGE": invalid identifier

In the generated SQL, it seems the person is referenced as PERSONENTI4, not PERSONENTI1. If I copy the SQL and run it with PERSONENTIT4 instead of PERSONENTI4, it works (sort of -- it seems to be doing a cartesian join of some sort).

Any clues as to what I might be doing wrong? I'm very new to using Hibernate.

PersonQuestionsEntity

@Entity
@IdClass(com.anonymous.model.PersonQuestionsKey.class)
@Table(name = "PERSON_QUESTIONS")
public class PersonQuestionsEntity implements Serializable
{
    private static final long serialVersionUID = -8254277382097937813L;

    @Id
    @ManyToOne
    @JoinColumn(name = "USER_NAME", nullable = false)
    private PersonEntity person;

    @Id
    @ManyToOne
    @JoinColumn(name = "QUESTION_ID", nullable = false)
    private QuestionEntity question;

    @Column(name = "THEIR_ANSWER")
    private int theirAnswer;
}

PersonEntity

@Entity
@Table(name = "PERSON")

public class PersonEntity implements Serializable
{
    private static final long serialVersionUID = -1699435979266209440L;

    @Id
    @Column(name = "USER_NAME", length = 20, nullable = false)
    private String userName;

    @Column(name = "USER_NAME_REAL", length = 20, nullable = false)
    private String userNameReal;

    @Column(name = "AGE", nullable = false)
    private int age;
}

PersonQuestionsKey

   @Embeddable
    public class PersonQuestionsKey implements Serializable
    {
        private static final long serialVersionUID = -264160855961369405L;

        @Id
        @ManyToOne
        @JoinColumn(name = "USER_NAME", nullable = false)
        private PersonEntity person;

        @Id
        @ManyToOne
        @JoinColumn(name = "QUESTION_ID", nullable = false)
        private QuestionEntity question;

}

Upvotes: 2

Views: 8018

Answers (1)

Bivas
Bivas

Reputation: 1494

First of all, you don't really need the inner criteria, simply use: 

    Criteria criteria = session.createCriteria(PersonQuestionsEntity.class);
    criteria.add(Restrictions.eq("question.questionId", "87"));
    criteria.add(Restrictions.eq("person.age", 23));
    criteria.setMaxResults(100);
    List l = criteria.list();

Second (regarding the join type), in such cases, I usually go with HQL that produces inner joins. The HQL might look as follows:

from PersonQeustionEntity where question.questionId = :questionId 
                                            and person.age = :age

in the result Query object you can set the parameters questionId and age to your desired inputs.

Upvotes: 1

Related Questions