CODEWITHSUNDEEP
javahibernate
micdcar
micdcar

Reputation: 171

Hibernate generates ambiguous SQL query

Consider the following ParentClass entity:

@Entity
@Table(schema = "iwrs")
@Inheritance(strategy=InheritanceType.TABLE_PER_CLASS)
public class ParentClass {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "PK_IWRS")
    public int id;

    public String name;

    public ParentClass(String name) {
        this.name = name;
    }
}

And the following ChildClass entity:

@Entity
@Table(schema = "iwrs")
public class ChildClass extends ParentClass {

    @ManyToOne(targetEntity=ParentClass.class)
    @JoinColumn(name="parent_id")
    private ParentClass parent;

    public ChildClass(String name) {
        super(name);
    }
}

As you can see, this ChildClass extends from ParentClass. Moreover, it contains a reference for ParentClass mapped in parent field.

There is a point where I want to get all instances of ParentClass, but not the instances that are ChildClass.

I searched around and found that could be achieved with this criteria:

Criteria criteria = sessionFactory.getCurrentSession()
                .createCriteria(ParentClass.class, "parentClass")
                .add(Restrictions.eq("class", ParentClass.class));

However, when I try to list it, I get the following error:

ERROR SqlExceptionHelper:147 - ERROR: column reference "clazz_" is ambiguous

If I remove the last line of the criteria, the query is successfully executed. But the ChildClass instances are also returned, which is not what I want.

Here is the query generated by hibernate when I have all the restrictions:

select this_.id as id1_29_1_, this_.name as name2_29_1_, this_.parent_id as parent_i1_14_1_, this_.clazz_ as clazz_1_, parentclas2_.id as id1_29_0_, parentclas2_.name as name2_29_0_, parentclas2_.parent_id as parent_i1_14_0_, parentclas2_.clazz_ as clazz_0_ from ( select id, name, null::int4 as parent_id, 0 as clazz_ from iwrs.parent_class union all select id, name, parent_id, 1 as clazz_ from iwrs.child_class ) this_ left outer join ( select id, name, null::int4 as parent_id, 0 as clazz_ from iwrs.parent_class union all select id, name, parent_id, 1 as clazz_ from iwrs.child_class ) parentclas2_ on this_.parent_id=parentclas2_.id where clazz_=?

Working example available here: https://github.com/mmalmeida/hibernateTest , just run the test RetrieveParentTest.java.

Do you know how I can work around this problem?

Thanks in advance!

Upvotes: 1

Views: 1597

Answers (1)

malvarez
malvarez

Reputation: 286

Try using the alias you defined:

Criteria criteria = sessionFactory.getCurrentSession()
                    .createCriteria(ParentClass.class, "parentClass")
                    .add(Restrictions.eq("parentClass.class", ParentClass.class));

Upvotes: 2

Related Questions