Instructor has many Courses, courses have many Students. I'd like to use the Criteria API to search for all Instructors with a given Student. I don't know if it's legal to do something like someCriteria.createCriteria("x.y.z"); it doesn't give me an exception when I create the criteria but it does when it tries to actually do the search.
What is the best way to accomplish this type of search? My real search is considerably more complicated than this isolated test case, so I would really prefer to use the Criteria API instead of Query.
Hibernate version: 3.05
Mapping documents:
Code:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.prosc.test" default-cascade="lock" default-access="field">
<class lazy="false" name="Instructor">
<id name="id" type="java.lang.Long" unsaved-value="null">
<generator class="native"/>
</id>
<property name="firstName" type="java.lang.String"/>
<property name="lastName" type="java.lang.String"/>
<property name="title" type="java.lang.String"/>
<many-to-one name="primaryCourse" column="primaryCourseId"/>
<list name="courses" lazy="true" inverse="false">
<key column="instructorId" not-null="true"/>
<index column="sortOrderForinstructor"/>
<one-to-many class="Course"/>
</list>
</class>
</hibernate-mapping>
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.prosc.test" default-cascade="lock" default-access="field">
<class lazy="false" name="Course">
<id name="id" type="java.lang.Long" unsaved-value="null">
<generator class="native"/>
</id>
<property name="name" type="java.lang.String"/>
<property name="courseNumber" type="java.lang.String"/>
<property name="courseDescription" type="java.lang.String"/>
<many-to-one name="instructor" column="instructorId" not-null="true" update="false" insert="false"/>
<many-to-one name="classroom" column="classroomId"/>
<set inverse="false" name="students" lazy="true" table="JoinCourseStudent">
<key column="coursesId"/>
<many-to-many column="studentsId" class="Student"/>
</set>
</class>
</hibernate-mapping>
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.prosc.test" default-cascade="lock" default-access="field">
<class lazy="false" name="Student">
<id name="id" type="java.lang.Long" unsaved-value="null">
<generator class="native"/>
</id>
<property name="firstName" type="java.lang.String"/>
<property name="lastName" type="java.lang.String"/>
<property name="dormitory" type="java.lang.String"/>
<property name="enrollmentDate" type="java.util.Date"/>
<set inverse="true" name="courses" lazy="true" table="JoinCourseStudent">
<key column="studentsId"/>
<many-to-many column="coursesId" class="Course"/>
</set>
</class>
</hibernate-mapping>
Code:
List allStudents = session.createCriteria( Student.class ).list();
assertTrue( allStudents.size() >= 1 );
Student whichStudent = (Student)allStudents.get( 0 );
Criteria search = session.createCriteria( Instructor.class);
search.createCriteria( "courses.students").add( Restrictions.idEq(whichStudent.getId() ) );
search.list();
Full stack trace of any exception that occurs:org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:92)
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:80)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1596)
at org.hibernate.loader.Loader.list(Loader.java:1577)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:111)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1322)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:300)
at com.prosc.test.TestCriteriaSearch.testCriteriaDotNotationSearch(TestCriteriaSearch.java:68)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at com.intellij.rt.execution.junit2.JUnitStarter.main(JUnitStarter.java:31)
Caused by: java.sql.SQLException: Base table or view not found, message from server: "Unknown table 'student1_' in where clause"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1905)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1109)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1203)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2090)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1496)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1272)
at org.hibernate.loader.Loader.doQuery(Loader.java:391)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.doList(Loader.java:1593)
... 23 more
Name and version of the database you are using:
The generated SQL (show_sql=true):
Hibernate: select this_.id as id3_, this_.firstName as firstName3_3_, this_.lastName as lastName3_3_, this_.title as title3_3_, this_.primaryCourseId as primaryC5_3_3_, course3_.id as id0_, course3_.name as name0_0_, course3_.courseNumber as courseNu3_0_0_, course3_.courseDescription as courseDe4_0_0_, course3_.instructorId as instruct5_0_0_, course3_.classroomId as classroo6_0_0_, instructor4_.id as id1_, instructor4_.firstName as firstName3_1_, instructor4_.lastName as lastName3_1_, instructor4_.title as title3_1_, instructor4_.primaryCourseId as primaryC5_3_1_, classroom5_.id as id2_, classroom5_.floor as floor4_2_, classroom5_.building as building4_2_, classroom5_.campusId as campusId4_2_ from Instructor this_ left outer join Course course3_ on this_.primaryCourseId=course3_.id left outer join Instructor instructor4_ on course3_.instructorId=instructor4_.id left outer join Classroom classroom5_ on course3_.classroomId=classroom5_.id where student1_.id = ?
Debug level Hibernate log excerpt:
Aug 19, 2005 10:54:02 AM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 1109, SQLState: 42S02
Aug 19, 2005 10:54:02 AM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: Base table or view not found, message from server: "Unknown table 'student1_' in where clause"
Login
FAQ
Search
