I have a problem understanding the operation of composite-element in queries. I have an object MRWObject that references parents and children that are collections of the same MRWObject. In order to record the date of association between a parent and child, I have used composite-element - this works fine until I come to write queries where I wish to restrict the results to just those objects with a certain parent. I am trying:
'from article a inner join a.parents parent where parent.id='
where article is a subclass of MRWObject. I get an exception 'parent_1.obj_id' doesn't exist.
Can anyone help me with the format of this type of query? Full details below.
Thanks
Neil
Hibernate version: 3.0
Mapping documents:
<hibernate-mapping package="com.wiley.d3301.els.dbobj">
<class name="MRWObject" table="mrwobject" lazy="false">
<id name="id" column="obj_id">
<generator class="hilo">
<param name="table">mrwobject_unique_key</param>
<param name="column">next_hi</param>
<param name="max_lo">1</param>
</generator>
</id>
<discriminator column="obj_type"/>
<version name="versionId" column="obj_version_id" unsaved-value="negative"/>
<property name="type" column="obj_type"/>
<property name="notes" column="obj_notes"/>
<property name="submission" column="obj_submission"/>
<property name="deleted" column="obj_deleted"/>
<property name="versionText" column="obj_version_text"/>
<bag name="roles" lazy="true" order-by="rol_type, rol_status">
<key column="rol_obj_id"/>
<one-to-many class="Role"/>
</bag>
<bag name="earlierVersions" table="object_version" lazy="true">
<key column="ver_later_version"/>
<many-to-many class="MRWObject" column="ver_earlier_version"/>
</bag>
<bag name="laterVersions" table="object_version" lazy="true">
<key column="ver_earlier_version"/>
<many-to-many class="MRWObject" column="ver_later_version"/>
</bag>
<one-to-one name="workflow" property-ref="object" cascade="delete"/>
<!-- one-to-one name="copyright" class="Copyright" property-ref="object" cascade="all" /-->
<bag name="copyrights" lazy="false" order-by="cpy_id">
<key column="cpy_object_id"/>
<one-to-many class="Copyright"/>
</bag>
<bag name="parents" lazy="true" table="object_contains">
<key column="ocon_child_id"/>
<composite-element class="MRWObjDatedAssociation">
<parent name="child"/>
<many-to-one name="parent" class="MRWObject" column="ocon_parent_id"/>
<property name="dateAdded" column="ocon_date_added"/>
</composite-element>
</bag>
<bag name="children" lazy="true" table="object_contains">
<key column="ocon_parent_id"/>
<composite-element class="MRWObjDatedAssociation">
<parent name="parent"/>
<many-to-one name="child" class="MRWObject" column="ocon_child_id"/>
<property name="dateAdded" column="ocon_date_added"/>
</composite-element>
</bag>
<joined-subclass name="Article" table="article" lazy="false">
<key column="art_id"/>
<property name="actualLength" column="art_actual_length"/>
<property name="commissionedLength" column="art_commissioned_length"/>
<property name="headword" column="art_headword"/>
<property name="illustrationCount" column="art_illustration_count"/>
<property name="level" column="art_level"/>
<property name="tableCount" column="art_table_count"/>
<property name="otherCount" column="art_other_count"/>
<property name="subjectAreaId" column="art_subject"/>
<property name="conceptId" column="art_concept"/>
<property name="source" column="art_source"/>
<property name="sourceDate" column="art_source_date"/>
<many-to-one name="subjectArea" column="art_subject" update="false" insert="false"/>
<many-to-one name="concept" column="art_concept" update="false" insert="false"/>
<bag name="lockedAHeads" lazy="true" where="ahd_locked=1" order-by="ahd_order" inverse="true">
<key column="ahd_article_id"/>
<one-to-many class="AHead"/>
</bag>
<bag name="unlockedAHeads" lazy="true" where="ahd_locked=0" order-by="ahd_order" inverse="true">
<key column="ahd_article_id"/>
<one-to-many class="AHead"/>
</bag>
</joined-subclass>
<joined-subclass name="Illustration" table="illustration" lazy="false">
<key column="ill_id"/>
<property name="description" column="ill_description"/>
<property name="illustrationType" column="ill_type"/>
</joined-subclass>
<joined-subclass name="Publication" table="publication" lazy="false">
<key column="pub_id"/>
<property name="description" column="pub_description"/>
<property name="name" column="pub_name"/>
</joined-subclass>
</class>
</hibernate-mapping>
Code between sessionFactory.openSession() and session.close():
public FoundThings getArticles(FindArticleCommand c) {
StringBuffer query = new StringBuffer(" from Article a ");
query.append("inner join a.parents as parent ");
query.append(" where parent.id = '" + c.getDestinationId() + "'");
List items = (List)getHibernateTemplate().find(query.toString());
Full stack trace of any exception that occurs:
org.springframework.jdbc.BadSqlGrammarException: Bad SQL grammar [] in task 'Hibernate operation'; nested exception is java.sql.SQLException: Unknown column 'parents1_.obj_id' in 'where clause'
org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:223)
org.springframework.orm.hibernate3.HibernateAccessor.convertJdbcAccessException(HibernateAccessor.java:243)
org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:227)
org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:316)
org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:740)
org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:732)
com.wiley.d3301.els.dao.hibernate.ArticleDaoHibernate.getArticles(ArticleDaoHibernate.java:229)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
java.lang.reflect.Method.invoke(Method.java:585)
org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:287)
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:163)
$Proxy14.getArticles(Unknown Source)
com.wiley.d3301.els.web.controller.FindArticleController.onSubmit(FindArticleController.java:47)
org.springframework.web.servlet.mvc.SimpleFormController.onSubmit(SimpleFormController.java:332)
org.springframework.web.servlet.mvc.SimpleFormController.onSubmit(SimpleFormController.java:307)
org.springframework.web.servlet.mvc.SimpleFormController.processFormSubmission(SimpleFormController.java:248)
org.springframework.web.servlet.mvc.AbstractFormController.handleRequestInternal(AbstractFormController.java:243)
org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:128)
org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:44)
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:684)
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:625)
org.springframework.web.servlet.FrameworkServlet.serviceWrapper(FrameworkServlet.java:386)
org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:355)
javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
com.wiley.d3301.els.web.utils.CharsetFilter.doFilter(CharsetFilter.java:26)
Name and version of the database you are using:MySql 5
The generated SQL (show_sql=true):
Hibernate: select article0_.art_id as obj1_, article0_1_.obj_version_id as obj3_42_, article0_1_.obj_type as obj2_42_, article0_1_.obj_notes as obj4_42_, article0_1_.obj_submission as obj5_42_, article0_1_.obj_deleted as obj6_42_, article0_1_.obj_version_text as obj7_42_, article0_.art_actual_length as art2_45_, article0_.art_commissioned_length as art3_45_, article0_.art_headword as art4_45_, article0_.art_illustration_count as art5_45_, article0_.art_level as art6_45_, article0_.art_table_count as art7_45_, article0_.art_other_count as art8_45_, article0_.art_subject as art9_45_, article0_.art_concept as art10_45_, article0_.art_source as art11_45_, article0_.art_source_date as art12_45_ from article article0_ inner join mrwobject article0_1_ on article0_.art_id=article0_1_.obj_id inner join object_contains parents1_ on article0_.art_id=parents1_.ocon_child_id where (parents1_.obj_id='35')
Debug level Hibernate log excerpt:
_________________ Those who can frequently don't
|