Criteria - where clause on joined table

Question

I have two tables in a MySQL database: COURSE and COURSE_SESSION.

COURSE has two fields : code(primary key) and title.

CourseSession has a field called course_code which is a foreign key linked to an element of Course.

I want to do somthing like this but using the Criteria API in Java :

SELECT * FROM COURSE_SESSION join COURSE 
WHERE course_code = code AND title like "%substring%";

Both tables are mapped correctly with hibernate.

I tried this :

Criteria criter = s.createCriteria(CourseSession.class);
        criter.add(Restrictions.like("courseCode.title", "%substring%"));

But I end up with an error :

HTTP 500 - could not resolve property: courseCode.title of: org.xxx.core.entity.CourseSession

message could not resolve property: courseCode.title of: org.xxx.core.entity.CourseSession

exception

org.hibernate.QueryException: could not resolve property: courseCode.title of: fr.utbm.lo54.core.entity.CourseSession
    org.hibernate.persister.entity.AbstractPropertyMapping.propertyException(AbstractPropertyMapping.java:81)
    org.hibernate.persister.entity.AbstractPropertyMapping.toColumns(AbstractPropertyMapping.java:96)
    org.hibernate.persister.entity.BasicEntityPropertyMapping.toColumns(BasicEntityPropertyMapping.java:62)
    org.hibernate.persister.entity.AbstractEntityPersister.toColumns(AbstractEntityPersister.java:1443)
    org.hibernate.loader.criteria.CriteriaQueryTranslator.getColumns(CriteriaQueryTranslator.java:483)
    org.hibernate.loader.criteria.CriteriaQueryTranslator.getColumnsUsingProjection(CriteriaQueryTranslator.java:443)
    org.hibernate.criterion.SimpleExpression.toSqlString(SimpleExpression.java:68)
    org.hibernate.loader.criteria.CriteriaQueryTranslator.getWhereCondition(CriteriaQueryTranslator.java:380)
    org.hibernate.loader.criteria.CriteriaJoinWalker.(CriteriaJoinWalker.java:114)
    org.hibernate.loader.criteria.CriteriaJoinWalker.(CriteriaJoinWalker.java:83)
    org.hibernate.loader.criteria.CriteriaLoader.(CriteriaLoader.java:92)
    org.hibernate.impl.SessionImpl.list(SessionImpl.java:1687)
    org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:347)
    fr.utbm.lo54.core.servlet.SearchData.doGet(SearchData.java:52)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

The strange thing is that if I do a Restriction on courseCode.code with Restrictions.like("courseCode.code", "%substring%"), it works fine.

EDIT

Course.hbm.xml :

<!DOCTYPE hibernate-mapping PUBLIC
      "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
          "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="org.xxx.core.entity">
    <class name="Course" table="COURSE">
        <id name="code" column="CODE">
            <generator class ="identity"/>
        </id>
    <property name="title" column="TITLE" not-null="true"/>
    </class>
</hibernate-mapping>

CourseSession.hbm.xml :

<!DOCTYPE hibernate-mapping PUBLIC
      "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
          "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="org.xxx.core.entity">
    <class name="CourseSession" table="COURSE_SESSION">
        <id name="id" column="ID">
            <generator class="identity" />
        </id>

        <property name="startDate" column="START_DATE" not-null="true" />
        <property name="endDate" column="END_DATE" not-null="true" />
        <many-to-one name="courseCode" column="COURSE_CODE" />
        <many-to-one name="locationId" column="LOCATION_ID" />
    </class>
</hibernate-mapping>

*Both tables are mapped correctly with hibernate*: how? That matters, since a Criteria query uses fields and associations from the entities. Show us their code. — JB Nizet, Oct 29 '15 at 17:36

score 0 · Accepted Answer · answered Oct 29 '15 at 17:37

0

create an alias for the field first to achieve this.

try the below code, it should help

criter.createAlias("courseCode","courseCode");//course code is the association variable in your pojo class.

answered Oct 29 '15 at 17:37

Vivek Singh

2,047
11
24

Thank you, it's working ! Could you explain me a bit more why this is needed and why the courseCode.code worked before ? – Nono Oct 29 '15 at 18:17
this happens because whenever we have a relation in our class we need to set an alias for them through which the associated fields can be called. Since the alias was not present hibernate was unable to determine the relation among the object. – Vivek Singh Oct 29 '15 at 18:21

Criteria - where clause on joined table

1 Answers1