Problem with DetachedCriteria and Subqueries

Axel Wilhelm Berle · **Posted:** Mon Oct 02, 2006 4:38 pm

Hibernate version: 3.1.3

Please, I could use some help with DetachedCriteria and Subqueries.

I would like to use Subqueries with the Criteria API, but I am getting a java.lang.ClassCastException: org.hibernate.impl.CriteriaImpl$Subcriteria.

I have the following joined entities:

User.hbm.xml: Main Table "users"

Lernender.hbm.xml: secondary table: "lernender": linked to the first with foreign key "user_id" (one-to-one)

BestellterKurs.hbm.xml: secondary table: "bestellterKurs": linked to second by foreign key "lernender_id" (many-to-one). Has attribute "status".

A "User" can have at most one "Lernender" which can have many "BestellteKurse".

I need to get all the Users that have at least one associated "BestellteKurse" with status = 1.

Mapping documents:
User:

Code:

<hibernate-mapping package="rechtschnell.business.model.user">

<class name="User" 
       table="users"
       lazy="true">
   <id name="id"
       column="id"
            type="long"
            unsaved-value="null">
       <generator class="increment"/>
   </id>
        ...
        <one-to-one
            name="lernender"
            class="rechtschnell.business.model.user.Lernender"
            cascade="all"
            property-ref="user"/>
</class>
</hibernate-mapping>

Lernender:

Code:

<hibernate-mapping package="rechtschnell.business.model.user">

<class name="Lernender" 
       table="lernender"
       lazy="true">
   <id name="id"
       column="id"
            type="long"
            unsaved-value="null">
       <generator class="increment"/>
   </id>
   <many-to-one
            name="user"
            class="User"
            foreign-key="fk_lernender_user"
            cascade="none">
            <column 
                name="user_id"
                not-null="true"
                unique="true"
                index="idx_lernender_user"/>
        </many-to-one>

        <map name="bestellteKurse"
             inverse="true"
             cascade="all-delete-orphan"
             lazy="true">
             <key>
                <column
                    name="lernender_id"
                    not-null="true"/>
             </key>
             <index type="long" column="lernprofil_id"/>
             <one-to-many class="rechtschnell.business.model.lernprofil.BestellterKurs"/>
        </map>
</class>
</hibernate-mapping>

BestellterKurs:

Code:

<hibernate-mapping package="rechtschnell.business.model.lernprofil">

<class name="BestellterKurs" 
       table="lernprofil_lernender"
       lazy="true">
   <id name="id"
       column="id"
            type="long"
            unsaved-value="null">
       <generator class="increment"/>
   </id>
        <many-to-one
            name="lernProfil"
            class="LernProfil"
            foreign-key="fk_lernprofil_lernender_lernprofil"
            cascade="none">
            <column 
                name="lernprofil_id"
                not-null="true"
                index="idx_lernprofil_lernender_lernprofil_id"/>
        </many-to-one>
        <many-to-one
            name="lernender"
            update="false"
            class="rechtschnell.business.model.user.Lernender"
            foreign-key="fk_lernprofil_lernender_lernender"
            cascade="none">
            <column 
                name="lernender_id"
                not-null="true"
                index="idx_lernprofil_lernender_lernender_id"/>
        </many-to-one>
        <property   
            name="status"
type="rechtschnell.business.model.lernprofil.BestellungsStatusType"
            update="true">
            <column
                name="status"
                not-null="false"
                index="idx_lernprofil_lernender_status"/>
        </property>

</class>

</hibernate-mapping>

Code:

I figured that the best way would be to use a subquery:

Code:

Criteria criteria = HibernateUtil.getSession().createCriteria(User.class);
Criteria lernenderCriteria = criteria.createCriteria("lernender");
Criteria bestellteKurseCriteria = lernenderCriteria.createCriteria("bestellteKurse");

DetachedCriteria countSubquery = DetachedCriteria.forClass(BestellterKurs.class);
countSubquery.add(Restrictions.eq("status", 1));
countSubquery.add(Subqueries.exists(countSubquery));
lernenderCriteria.add(Property.forName("bestellteKurse").in(countSubquery));

Full stack trace of any exception that occurs:

Code:

java.lang.ClassCastException: org.hibernate.impl.CriteriaImpl$Subcriteria
        at org.hibernate.criterion.SubqueryExpression.toSqlString(SubqueryExpression.java:43)
        at org.hibernate.loader.criteria.CriteriaQueryTranslator.getWhereCondition(CriteriaQueryTranslator.java:333)
        at org.hibernate.loader.criteria.CriteriaJoinWalker.<init>(CriteriaJoinWalker.java:82)
        at org.hibernate.loader.criteria.CriteriaLoader.<init>(CriteriaLoader.java:67)
        at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1514)
        at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
        at rechtschnell.business.dao.user.UserDAO.getPage(UserDAO.java:444)

If anybody could point me in the right direction, I would be very grateful.

Axel