<one-to-one> throws SQL Exception

brian_wainwright · **Posted:** Tue Mar 23, 2004 5:57 pm

Hey Everyone,

I have a one-to-one mapping between two classes, ProgramImpl and LgyRightsProfileImpl, mapped thusly in the mapping file:

Code:

<!-- for program -->
<joined-subclass name="org.wgbh.scape.domain.ProgramImpl" table="program">
            <meta attribute="class-description"> @author Brian R. Wainwright @version 1.0 </meta>
            <meta attribute="implements">org.wgbh.scape.domain.Program</meta>
   <key column="program_id"/>
   
            <many-to-one class="org.wgbh.scape.domain.SeriesImpl" column="series_id" name="series">
                <meta attribute="finder">findBySeries</meta>
            </many-to-one>
            
             <set batch-size="30" inverse="true" lazy="false" name="title" outer-join="false" cascade="all">
                  <key column="associated_object_id"/>
                  <one-to-many class="org.wgbh.scape.domain.TitleImpl"/>
             </set>
             
             <set batch-size="30" inverse="true" lazy="false" name="programs" outer-join="false" cascade="all">
                  <key column="program_id"/>
                  <one-to-many class="org.wgbh.scape.domain.ProgramUnderContractImpl"/>
             </set>
             
             <set batch-size="30" inverse="true" lazy="false" name="dateOfAir" outer-join="false" cascade="all">
                  <key column="program_id"/>
                  <one-to-many class="org.wgbh.scape.domain.DateOfAirImpl"/>
             </set>
             
            <set batch-size="30" lazy="true" name="screener_requests" outer-join="false" table="screener_program_requests" cascade="all">
                <key column="program_id"/>
                <many-to-many class="org.wgbh.scape.domain.ScreenerRequestImpl" column="screener_request_id"/>
            </set>
            
             <set batch-size="30" inverse="true" lazy="false" name="lgyRightsGrid" outer-join="false" cascade="all">
                  <key column="program_id"/>
                  <one-to-many class="org.wgbh.scape.domain.LgyRightsGridImpl"/>
             </set>
             
        <one-to-one name="lgyRightsProfile" class="org.wgbh.scape.domain.LgyRightsProfileImpl" property-ref="program" constrained="
        true" outer-join="false" />
             
             <property name="awards" column="awards" type="text"/>
             
             <property column="lgy_program_code" name="lgcyProgramCode" type="string">
                       <meta attribute="finder">findByProgramCode</meta>
             </property>
             
             <property column="lgy_series_code" name="lgcySeriesCode" type="string">
                       <meta attribute="finder">findBySeriesCode</meta>
             </property>
             
             <property column="season" name="season" type="text">
                       <meta attribute="finder">findBySeason</meta>
             </property>
             
             <property column="activity_code_id" name="activityCodeId" type="string">
                       <meta attribute="finder">findByCodeId</meta>
             </property>
             
             <property column="short_description" name="shortDescription" type="string">
                       <meta attribute="finder">findByDescription</meta>
             </property>
             
             <property column="long_description" name="longDescription" type="text">                 
             </property>
             
             <property column="treatment_url" name="treatmentUrl" type="string">
                       <meta attribute="finder">findByUrl</meta>
             </property>
             
</joined-subclass>

<joined-subclass name="org.wgbh.scape.domain.LgyRightsProfileImpl" table="lgy_rights_profile">
            <meta attribute="class-description"> @author Brian R. Wainwright @version 1.0 </meta>

   <meta attribute="implements">org.wgbh.scape.domain.LgyRightsProfile</meta>
   <key column="lgy_rights_profile_id"/>
   
   <one-to-one name="program" class="org.wgbh.scape.domain.ProgramImpl" property-ref="lgyRightsProfile" constrained="true" cascade="all" outer-join="false"/>
   
           <property column="program_origin" name="programOrigin" type="string">
                <meta attribute="finder">findByProgramOrigin</meta>
           </property>
           
            <property column="lgy_rights_summary" name="legacyRightsSummary" type="text">
                <meta attribute="finder">findByLegacyRightsSummary</meta>
            </property>
</joined-subclass>

Each table in the database looks like this:

Code:

mysql> desc program;                                   
+-----------------------+--------------+------+-----+---------+-------+
| Field                 | Type         | Null | Key | Default | Extra |
+-----------------------+--------------+------+-----+---------+-------+
| lgy_program_code      | varchar(32)  | YES  |     | NULL    |       |
| lgy_series_code       | varchar(32)  | YES  |     | NULL    |       |
| series_id             | varchar(32)  | YES  | MUL | NULL    |       |
| season                | varchar(32)  | YES  |     | NULL    |       |
| activity_code_id      | varchar(32)  | YES  |     | NULL    |       |
| short_description     | varchar(255) | YES  | MUL | NULL    |       |
| long_description      | text         | YES  | MUL | NULL    |       |
| awards                | text         | YES  |     | NULL    |       |
| treatment_url         | varchar(80)  | YES  |     | NULL    |       |
| program_id            | varchar(32)  |      | PRI |         |       |
| lgy_rights_profile_id | varchar(32)  | YES  | MUL | NULL    |       |
+-----------------------+--------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

mysql> desc lgy_rights_profile;
+-----------------------+--------------+------+-----+---------+-------+
| Field                 | Type         | Null | Key | Default | Extra |
+-----------------------+--------------+------+-----+---------+-------+
| program_id            | varchar(32)  | YES  | MUL | NULL    |       |
| program_origin        | varchar(255) |      | MUL |         |       |
| lgy_rights_summary    | varchar(255) |      | MUL |         |       |
| lgy_rights_profile_id | varchar(32)  |      | PRI |         |       |
+-----------------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Everything APPEARS to be mapped correctly however, when attempting to fetch a program object from the DB I get a SQL exception:

Code:

SEVERE: SQLException occurred
java.sql.SQLException: Parameter index out of range (1 > 0).
   at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:1940)
   at com.mysql.jdbc.PreparedStatement.setString(PreparedStatement.java:1075)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.setString(DelegatingPreparedStatement.java:243)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.setString(DelegatingPreparedStatement.java:243)
   at net.sf.hibernate.type.StringType.set(StringType.java:26)
   at net.sf.hibernate.type.NullableType.nullSafeSet(NullableType.java:48)
   at net.sf.hibernate.type.NullableType.nullSafeSet(NullableType.java:35)
   at net.sf.hibernate.loader.Loader.bindPositionalParameters(Loader.java:674)
   at net.sf.hibernate.loader.Loader.prepareQueryStatement(Loader.java:713)
   at net.sf.hibernate.loader.Loader.doQuery(Loader.java:185)
   at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
   at net.sf.hibernate.loader.Loader.loadEntity(Loader.java:831)
   at net.sf.hibernate.loader.Loader.loadEntity(Loader.java:851)
   at net.sf.hibernate.loader.EntityLoader.load(EntityLoader.java:57)
   at net.sf.hibernate.loader.EntityLoader.loadByUniqueKey(EntityLoader.java:53)
   at net.sf.hibernate.persister.AbstractEntityPersister.loadByUniqueKey(AbstractEntityPersister.java:1104)
   at net.sf.hibernate.impl.SessionImpl.loadByUniqueKey(SessionImpl.java:3806)
   at net.sf.hibernate.type.EntityType.resolveIdentifier(EntityType.java:218)
   at net.sf.hibernate.impl.SessionImpl.initializeEntity(SessionImpl.java:2169)
   at net.sf.hibernate.loader.Loader.doQuery(Loader.java:240)
   at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
   at net.sf.hibernate.loader.Loader.doList(Loader.java:950)
   at net.sf.hibernate.loader.Loader.list(Loader.java:941)
   at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:834)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1512)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1491)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1483)
   at org.wgbh.scape.util.QueryBuilderImpl.executeQuery(QueryBuilderImpl.java:133)
   at org.wgbh.scape.action.ScapeSearchAction.execute(ScapeSearchAction.java:63)
   at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
   at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
   at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
   at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
   at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
   at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
   at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
   at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
   at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
   at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
   at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
   at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
   at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2417)
   at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
   at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
   at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:171)
   at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
   at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172)
   at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
   at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
   at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
   at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
   at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
   at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
   at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
   at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:193)
   at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:781)
   at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:549)
   at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:589)
   at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:666)
   at java.lang.Thread.run(Thread.java:552)

Here's the query and resultant SQL:

Code:

*****

THE QUERY: 

 select  program from org.wgbh.scape.domain.Program as program right join program.title as programTitle where programTitle.title = 'HOWYA?'

*****
LOGGING: Getting SessionFactory from Application Properties...
Retrieved SessionFactory: net.sf.hibernate.impl.SessionFactoryImpl@212ab1
Hibernate: select programimp0_.program_id as object_id, programimp0_.series_id as series_id19_, programimp0_.awards as awards19_, programimp0_.lgy_program_code as lgy_prog4_19_, programimp0_.lgy_series_code as lgy_seri5_19_, programimp0_.season as season19_, programimp0_.activity_code_id as activity7_19_, programimp0_.short_description as short_de8_19_, programimp0_.long_description as long_des9_19_, programimp0_.treatment_url as treatme10_19_, programimp0__1_.access_privilege as access_p2_0_, programimp0__1_.object_type_id as object_t3_0_ from program programimp0_ inner join object programimp0__1_ on programimp0_.program_id=programimp0__1_.object_id right outer join title title1_ on programimp0_.program_id=title1_.associated_object_id left outer join object title1__1_ on title1_.title_id=title1__1_.object_id where (title1_.title='HOWYA?' )
Hibernate: select lgyrightsp0_.lgy_rights_profile_id as object_id0_, lgyrightsp0_.program_origin as program_2_37_0_, lgyrightsp0_.lgy_rights_summary as lgy_righ3_37_0_, lgyrightsp0__1_.access_privilege as access_p2_0_0_, lgyrightsp0__1_.object_type_id as object_t3_0_0_ from lgy_rights_profile lgyrightsp0_ inner join object lgyrightsp0__1_ on lgyrightsp0_.lgy_rights_profile_id=lgyrightsp0__1_.object_id where 

My guess is that this is an issue with the mapping and the database... it was all working fine, until I added the columns = "program.lgy_rights_profile_id" and "lgy_rights_profile.program_id"....

Any ideas?

Thanks!

---BW

gavin · **Posted:** Tue Mar 23, 2004 7:19 pm

Why are you passing a query parameter to a query with no parameters?

brian_wainwright · **Posted:** Wed Mar 24, 2004 1:49 pm

Hey Gavin,

Thanks for the reply. I'm not sure what you mean. Can you clarify? The sql is what was generated as a result of the following HQL query:

[code]
select