Problem using criteria with parent-child relationship

mrflippy · **Joined:** Tue Feb 03, 2004 12:39 pm **Posts:** 8

Hello,

We have an application that needs to search through records in our database. Everything is working great with Criteria *except* we have a parent-child relationship, and Hibernate doesn't like us trying to filter on values in the child objects.

The important pieces are: 2 .hbm files: Movement and InventoryTransaction. These correspond to 4 database tables: Movement, TruckMovement, MovementInventoryTransaction, and InventoryTransaction. (Domain object classes are the same name as the database tables)

TruckMovement is subclassed from Movement.

What we are trying to do is search for TruckMovements based on data in the InventoryTransaction.

First of all, here are the .hbm files:

Movement:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >
    
<hibernate-mapping>

<class 
    name="com.savage.chubs.domain.Movement" 
    table="Movement"
>
    <id
        name="movementId"
        type="java.lang.Integer"
        column="MovementID"
        unsaved-value="0"
    >
        <generator class="native" />
    </id>
    
    <!-- associations -->
    <!-- bi-directional many-to-one association to InventoryTransaction -->
    <many-to-one
        name="movementType"
        class="com.savage.chubs.domain.MovementType"
        not-null="true"
    >
        <column name="MovementTypeID" />
    </many-to-one>
    <many-to-one
        name="shipper"
        class="com.savage.chubs.domain.Agent"
        not-null="false"
    >
        <column name="ShipperID" />
    </many-to-one>
    <many-to-one
        name="broker"
        class="com.savage.chubs.domain.Agent"
        not-null="false"
    >
        <column name="BrokerID" />
    </many-to-one>
    <many-to-one
        name="carrier"
        class="com.savage.chubs.domain.Agent"
        not-null="false"
    >
        <column name="CarrierID" />
    </many-to-one>
    <many-to-one
        name="movementOriginLocation"
        class="com.savage.chubs.domain.Location"
        not-null="true"
    >
        <column name="MovementOriginLocationID" />
    </many-to-one>
    <many-to-one
        name="movementDestinationLocation"
        class="com.savage.chubs.domain.Location"
        not-null="true"
    >
        <column name="MovementDestinationLocationID" />
    </many-to-one>
    
    <many-to-one
        name="comment"
        cascade="all"
        class="com.savage.chubs.domain.Comment"
        not-null="false"
    >
        <column name="CommentId"/>
    </many-to-one>
    
    <set
        name="movementInventoryTransactions"
        lazy="false"
        cascade="all"
        inverse="true"
    >
        <key>
            <column name="MovementID" />
        </key>
        <one-to-many 
            class="com.savage.chubs.domain.MovementInventoryTransaction"
        />
    </set>

    <joined-subclass name="com.savage.chubs.domain.TruckMovement" table="TruckMovement">
        <key column="MovementID"/>
        
        <property
            name="ticketNumber"
            type="java.lang.String"
            column="TicketNumber"
            not-null="true"
            length="50"
        />
        <property
            name="scaleGross"
            type="int"
            column="ScaleGross"
            length="53"
        />
        <property
            name="scaleTare"
            type="int"
            column="ScaleTare"
            length="53"
        />
        <property
            name="scaleNet"
            type="int"
            column="ScaleNet"
            not-null="true"
            length="53"
        />
        <property
            name="bolnet"
            type="int"
            column="BOLNet"
            length="53"
        />
        <property
            name="shippersBOL"
            type="int"
            column="ShippersBOL"
            length="53"
        />
        <property
            name="carrierNumber"
            type="java.lang.String"
            column="CarrierNumber"
            not-null="false"
            length="50"
        />
        <property
            name="truckTrailerNumber"
            type="java.lang.String"
            column="TruckTrailerNumber"
            not-null="false"
            length="50"
        />
    </joined-subclass>
</class>
</hibernate-mapping>

InventoryTransaction:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >
    
<hibernate-mapping>

<class 
    name="com.savage.chubs.domain.InventoryTransaction" 
    table="InventoryTransaction"
>
    <id
        name="inventoryTransactionId"
        type="java.lang.Integer"
        column="TransactionID"
        unsaved-value="0"
    >
        <generator class="native" />
    </id>
    <property
        name="transactionDate"
        type="java.sql.Timestamp"
        column="TransactionDate"
        not-null="true"
        length="23"
    />
    <property
        name="quantity"
        type="int"
        column="Quantity"
        not-null="true"
    />
    <property
        name="transactionTypeId"
        type="int"
        column="TransactionTypeID"
        not-null="true"
    />

    <many-to-one
        name="originCustomer"
        class="com.savage.chubs.domain.Agent"
        not-null="true"
    >
        <column name="OriginCustomerID" />
    </many-to-one>
    <many-to-one
        name="destinationCustomer"
        class="com.savage.chubs.domain.Agent"
        not-null="true"
    >
        <column name="DestinationCustomerID" />
    </many-to-one>

    <many-to-one
        name="originLocation"
        class="com.savage.chubs.domain.Location"
        not-null="true"
    >
        <column name="OriginLocationID" />
    </many-to-one>
    <many-to-one
        name="destinationLocation"
        class="com.savage.chubs.domain.Location"
        not-null="true"
    >
        <column name="DestinationLocationID" />
    </many-to-one>

    <many-to-one
        name="comment"
        class="com.savage.chubs.domain.Comment"
        column="CommentId"
        unique="true"    
        not-null="false"
        cascade="all"
    />
    
    <many-to-one
        name="fileAttachment"
        class="com.savage.chubs.domain.FileAttachment"
        column="FileAttachmentId"
        unique="true"    
        not-null="false"
        cascade="all"
    />

    <joined-subclass name="com.savage.chubs.domain.MovementInventoryTransaction" table="MovementInventoryTransaction">
        <key column="TransactionID"/>
        
        <many-to-one
            name="movement"
            class="com.savage.chubs.domain.Movement"
            not-null="true"
        >
            <column name="MovementID" />
        </many-to-one>
    </joined-subclass>
</class>
</hibernate-mapping>

This is how we are building the search criteria:

Code:

Criteria crit = session.createCriteria(TruckMovement.class);

...other criteria added here...

tmpId = consigneeId;
if( tmpId.intValue() != 0 ) {          
            crit.createCriteria("movementInventoryTransactions")
                 .createCriteria("destinationCustomer")
                 .add(Expression.eq("agentId", tmpId));
}

List list = crit.list();

Creating the criteria succeeds, but an exception is thrown by crit.list(): "java.sql.SQLException: The column prefix 'x0__1_' does not match with a table name or alias name used in the query."

We're also running into errors on any other child properties such as TransactionDate.

Here is the full debug log:

Code:

11:04:33,415 DEBUG [SessionImpl]() opened session
11:04:33,415 DEBUG [JDBCTransaction]() begin
11:04:33,415 DEBUG [JDBCTransaction]() current autocommit status:true
11:04:33,415 DEBUG [JDBCTransaction]() disabling autocommit
11:04:38,696 DEBUG [SessionImpl]() find by criteria: [net.sf.hibernate.impl.CriteriaImpl$CriterionEntry@e47e99]
11:04:38,930 DEBUG [SessionImpl]() flushing session
11:04:38,930 DEBUG [SessionImpl]() Flushing entities and processing referenced collections
11:04:38,930 DEBUG [SessionImpl]() Processing unreferenced collections
11:04:38,930 DEBUG [SessionImpl]() Scheduling collection removes/(re)creates/updates
11:04:38,930 DEBUG [SessionImpl]() Flushed: 0 insertions, 0 updates, 0 deletions to 0 objects
11:04:38,930 DEBUG [SessionImpl]() Flushed: 0 (re)creations, 0 updates, 0 removals to 0 collections
11:04:38,946 DEBUG [SessionImpl]() Dont need to execute flush
11:04:38,946 DEBUG [BatcherImpl]() about to open: 0 open PreparedStatements, 0 open ResultSets
11:04:38,946 DEBUG [SQL]() select this.MovementID as MovementID23_, this.TicketNumber as TicketNu2_14_23_, this.ScaleGross as ScaleGross14_23_, this.ScaleTare as ScaleTare14_23_, this.ScaleNet as ScaleNet14_23_, this.BOLNet as BOLNet14_23_, this.ShippersBOL as Shippers7_14_23_, this.CarrierNumber as CarrierN8_14_23_, this.TruckTrailerNumber as TruckTra9_14_23_, this_1_.MovementTypeID as Movement2_11_23_, this_1_.ShipperID as ShipperID11_23_, this_1_.BrokerID as BrokerID11_23_, this_1_.CarrierID as CarrierID11_23_, this_1_.MovementOriginLocationID as Movement6_11_23_, this_1_.MovementDestinationLocationID as Movement7_11_23_, this_1_.CommentId as CommentId11_23_, movementty1_.MovementTypeID as Movement1_0_, movementty1_.MovementName as Movement2_0_, movementty1_.MovementNameShort as Movement3_0_, agent2_.AgentID as AgentID1_, agent2_.AgentName as AgentName1_, agent2_.IsCustomer as IsCustomer1_, agent2_.IsConsignee as IsConsig4_1_, agent2_.IsShipper as IsShipper1_, agent2_.IsBroker as IsBroker1_, agent2_.IsGSS as IsGSS1_, agent2_.IsActive as IsActive1_, agent2_.IsCarrier as IsCarrier1_, agent2_.AgentNameShort as AgentNa10_1_, agent3_.AgentID as AgentID2_, agent3_.AgentName as AgentName2_, agent3_.IsCustomer as IsCustomer2_, agent3_.IsConsignee as IsConsig4_2_, agent3_.IsShipper as IsShipper2_, agent3_.IsBroker as IsBroker2_, agent3_.IsGSS as IsGSS2_, agent3_.IsActive as IsActive2_, agent3_.IsCarrier as IsCarrier2_, agent3_.AgentNameShort as AgentNa10_2_, agent4_.AgentID as AgentID3_, agent4_.AgentName as AgentName3_, agent4_.IsCustomer as IsCustomer3_, agent4_.IsConsignee as IsConsig4_3_, agent4_.IsShipper as IsShipper3_, agent4_.IsBroker as IsBroker3_, agent4_.IsGSS as IsGSS3_, agent4_.IsActive as IsActive3_, agent4_.IsCarrier as IsCarrier3_, agent4_.AgentNameShort as AgentNa10_3_, location5_.LocationID as LocationID4_, case when location5__1_.LocationID is not null then 1 when location5_.LocationID is not null then 0 end as clazz_4_, location5_.LocationName as Location2_8_4_, location5_.IsOrigin as IsOrigin8_4_, location5_.IsDestination as IsDestin4_8_4_, location5_.IsGSS as IsGSS8_4_, location5_.IsMarine as IsMarine8_4_, location5_.IsActive as IsActive8_4_, location5_.LocationNameShort as Location8_8_4_, location5__1_.DefaultMarineTypeID as DefaultM2_9_4_, marinetype6_.MarineTypeID as MarineTy1_5_, marinetype6_.Description as Descript2_5_, marinetype6_.MarineDescriptionShort as MarineDe3_5_, comment7_.CommentId as CommentId6_, comment7_.Comment as Comment6_, comment7_.PrintIndicator as PrintInd3_6_, comment7_.InternalComment as Internal4_6_, x0_.TransactionID as Transact1___, x0_.MovementID as MovementID__, x0_.TransactionID as Transact1_7_, x0_.MovementID as MovementID6_7_, x0__1_.TransactionDate as Transact2_5_7_, x0__1_.Quantity as Quantity5_7_, x0__1_.TransactionTypeID as Transact4_5_7_, x0__1_.OriginCustomerID as OriginCu5_5_7_, x0__1_.DestinationCustomerID as Destinat6_5_7_, x0__1_.OriginLocationID as OriginLo7_5_7_, x0__1_.DestinationLocationID as Destinat8_5_7_, x0__1_.CommentId as CommentId5_7_, x0__1_.FileAttachmentId as FileAtt10_5_7_, movement9_.MovementID as MovementID8_, case when movement9__1_.MovementID is not null then 1 when movement9__2_.MovementID is not null then 2 when movement9__3_.MovementID is not null then 3 when movement9_.MovementID is not null then 0 end as clazz_8_, movement9_.MovementTypeID as Movement2_11_8_, movement9_.ShipperID as ShipperID11_8_, movement9_.BrokerID as BrokerID11_8_, movement9_.CarrierID as CarrierID11_8_, movement9_.MovementOriginLocationID as Movement6_11_8_, movement9_.MovementDestinationLocationID as Movement7_11_8_, movement9_.CommentId as CommentId11_8_, movement9__1_.VoyageNumber as VoyageNu2_12_8_, movement9__1_.SurveyNumber as SurveyNu3_12_8_, movement9__1_.BillOfLading as BillOfLa4_12_8_, movement9__1_.LoadPortArrivalDate as LoadPort5_12_8_, movement9__1_.LoadPortConnectDate as LoadPort6_12_8_, movement9__1_.LoadPortDisconnectDate as LoadPort7_12_8_, movement9__1_.LoadPortDepartDate as LoadPort8_12_8_, movement9__1_.DischargePortArrivalDate as Discharg9_12_8_, movement9__1_.DischargePortConnectDate as Dischar10_12_8_, movement9__1_.DischargePortDisconnectDate as Dischar11_12_8_, movement9__1_.DischargePortDepartDate as Dischar12_12_8_, movement9__1_.ArriveGalvastonBuoy as ArriveG13_12_8_, movement9__1_.ArriveTampaBuoy as ArriveT14_12_8_, movement9__1_.StartPointDate as StartPo15_12_8_, movement9__1_.EndPointDate as EndPoin16_12_8_, movement9__1_.LocationID as LocationID12_8_, movement9__1_.MarineTypeID as MarineT18_12_8_, movement9__1_.StartPointID as StartPo19_12_8_, movement9__1_.EndPointID as EndPointID12_8_, movement9__1_.BilledCustomerID as BilledC21_12_8_, movement9__2_.RailcarNumber as RailcarN2_13_8_, movement9__2_.InYardDate as InYardDate13_8_, movement9__2_.InInventoryDate as InInvent4_13_8_, movement9__2_.ShipDate as ShipDate13_8_, movement9__2_.ReleaseDate as ReleaseD6_13_8_, movement9__2_.SteamStart as SteamStart13_8_, movement9__2_.SteamStop as SteamStop13_8_, movement9__2_.UnitTrain as UnitTrain13_8_, movement9__2_.RoadMarkID as RoadMarkID13_8_, movement9__3_.TicketNumber as TicketNu2_14_8_, movement9__3_.ScaleGross as ScaleGross14_8_, movement9__3_.ScaleTare as ScaleTare14_8_, movement9__3_.ScaleNet as ScaleNet14_8_, movement9__3_.BOLNet as BOLNet14_8_, movement9__3_.ShippersBOL as Shippers7_14_8_, movement9__3_.CarrierNumber as CarrierN8_14_8_, movement9__3_.TruckTrailerNumber as TruckTra9_14_8_, movementty10_.MovementTypeID as Movement1_9_, movementty10_.MovementName as Movement2_9_, movementty10_.MovementNameShort as Movement3_9_, agent11_.AgentID as AgentID10_, agent11_.AgentName as AgentName10_, agent11_.IsCustomer as IsCustomer10_, agent11_.IsConsignee as IsConsig4_10_, agent11_.IsShipper as IsShipper10_, agent11_.IsBroker as IsBroker10_, agent11_.IsGSS as IsGSS10_, agent11_.IsActive as IsActive10_, agent11_.IsCarrier as IsCarrier10_, agent11_.AgentNameShort as AgentNa10_10_, agent12_.AgentID as AgentID11_, agent12_.AgentName as AgentName11_, agent12_.IsCustomer as IsCustomer11_, agent12_.IsConsignee as IsConsig4_11_, agent12_.IsShipper as IsShipper11_, agent12_.IsBroker as IsBroker11_, agent12_.IsGSS as IsGSS11_, agent12_.IsActive as IsActive11_, agent12_.IsCarrier as IsCarrier11_, agent12_.AgentNameShort as AgentNa10_11_, agent13_.AgentID as AgentID12_, agent13_.AgentName as AgentName12_, agent13_.IsCustomer as IsCustomer12_, agent13_.IsConsignee as IsConsig4_12_, agent13_.IsShipper as IsShipper12_, agent13_.IsBroker as IsBroker12_, agent13_.IsGSS as IsGSS12_, agent13_.IsActive as IsActive12_, agent13_.IsCarrier as IsCarrier12_, agent13_.AgentNameShort as AgentNa10_12_, comment14_.CommentId as CommentId13_, comment14_.Comment as Comment13_, comment14_.PrintIndicator as PrintInd3_13_, comment14_.InternalComment as Internal4_13_, marine15_.LocationID as LocationID14_, marine15_.DefaultMarineTypeID as DefaultM2_9_14_, marine15__1_.LocationName as Location2_8_14_, marine15__1_.IsOrigin as IsOrigin8_14_, marine15__1_.IsDestination as IsDestin4_8_14_, marine15__1_.IsGSS as IsGSS8_14_, marine15__1_.IsMarine as IsMarine8_14_, marine15__1_.IsActive as IsActive8_14_, marine15__1_.LocationNameShort as Location8_8_14_, marinetype16_.MarineTypeID as MarineTy1_15_, marinetype16_.Description as Descript2_15_, marinetype16_.MarineDescriptionShort as MarineDe3_15_, marinetype17_.MarineTypeID as MarineTy1_16_, marinetype17_.Description as Descript2_16_, marinetype17_.MarineDescriptionShort as MarineDe3_16_, agent18_.AgentID as AgentID17_, agent18_.AgentName as AgentName17_, agent18_.IsCustomer as IsCustomer17_, agent18_.IsConsignee as IsConsig4_17_, agent18_.IsShipper as IsShipper17_, agent18_.IsBroker as IsBroker17_, agent18_.IsGSS as IsGSS17_, agent18_.IsActive as IsActive17_, agent18_.IsCarrier as IsCarrier17_, agent18_.AgentNameShort as AgentNa10_17_, roadmark19_.RoadMarkID as RoadMarkID18_, roadmark19_.Name as Name18_, roadmark19_.IsActive as IsActive18_, agent20_.AgentID as AgentID19_, agent20_.AgentName as AgentName19_, agent20_.IsCustomer as IsCustomer19_, agent20_.IsConsignee as IsConsig4_19_, agent20_.IsShipper as IsShipper19_, agent20_.IsBroker as IsBroker19_, agent20_.IsGSS as IsGSS19_, agent20_.IsActive as IsActive19_, agent20_.IsCarrier as IsCarrier19_, agent20_.AgentNameShort as AgentNa10_19_, x1_.AgentID as AgentID20_, x1_.AgentName as AgentName20_, x1_.IsCustomer as IsCustomer20_, x1_.IsConsignee as IsConsig4_20_, x1_.IsShipper as IsShipper20_, x1_.IsBroker as IsBroker20_, x1_.IsGSS as IsGSS20_, x1_.IsActive as IsActive20_, x1_.IsCarrier as IsCarrier20_, x1_.AgentNameShort as AgentNa10_20_, comment22_.CommentId as CommentId21_, comment22_.Comment as Comment21_, comment22_.PrintIndicator as PrintInd3_21_, comment22_.InternalComment as Internal4_21_, fileattach23_.AttachmentID as Attachme1_22_, fileattach23_.FileName as FileName22_, fileattach23_.Data as Data22_ from TruckMovement this inner join Movement this_1_ on this.MovementID=this_1_.MovementID left outer join MovementType movementty1_ on this_1_.MovementTypeID=movementty1_.MovementTypeID left outer join Agent agent2_ on this_1_.ShipperID=agent2_.AgentID left outer join Agent agent3_ on this_1_.BrokerID=agent3_.AgentID left outer join Agent agent4_ on this_1_.CarrierID=agent4_.AgentID left outer join Location location5_ on this_1_.MovementOriginLocationID=location5_.LocationID left outer join Marine location5__1_ on location5_.LocationID=location5__1_.LocationID left outer join MarineType marinetype6_ on location5__1_.DefaultMarineTypeID=marinetype6_.MarineTypeID left outer join Comments comment7_ on this_1_.CommentId=comment7_.CommentId inner join MovementInventoryTransaction x0_ on this.MovementID=x0_.MovementID left outer join Movement movement9_ on x0_.MovementID=movement9_.MovementID left outer join MarineMovement movement9__1_ on movement9_.MovementID=movement9__1_.MovementID left outer join RailcarMovement movement9__2_ on movement9_.MovementID=movement9__2_.MovementID left outer join TruckMovement movement9__3_ on movement9_.MovementID=movement9__3_.MovementID left outer join MovementType movementty10_ on movement9_.MovementTypeID=movementty10_.MovementTypeID left outer join Agent agent11_ on movement9_.ShipperID=agent11_.AgentID left outer join Agent agent12_ on movement9_.BrokerID=agent12_.AgentID left outer join Agent agent13_ on movement9_.CarrierID=agent13_.AgentID left outer join Comments comment14_ on movement9_.CommentId=comment14_.CommentId left outer join Marine marine15_ on movement9__1_.LocationID=marine15_.LocationID left outer join Location marine15__1_ on marine15_.LocationID=marine15__1_.LocationID left outer join MarineType marinetype16_ on marine15_.DefaultMarineTypeID=marinetype16_.MarineTypeID left outer join MarineType marinetype17_ on movement9__1_.MarineTypeID=marinetype17_.MarineTypeID left outer join Agent agent18_ on movement9__1_.BilledCustomerID=agent18_.AgentID left outer join RoadMark roadmark19_ on movement9__2_.RoadMarkID=roadmark19_.RoadMarkID left outer join Agent agent20_ on x0__1_.OriginCustomerID=agent20_.AgentID inner join Agent x1_ on x0__1_.DestinationCustomerID=x1_.AgentID left outer join Comments comment22_ on x0__1_.CommentId=comment22_.CommentId left outer join FileAttachment fileattach23_ on x0__1_.FileAttachmentId=fileattach23_.AttachmentID where x1_.AgentID=?
11:04:39,227 DEBUG [BatcherImpl]() preparing statement
11:04:39,352 DEBUG [IntegerType]() binding '3' to parameter: 1
11:04:39,446 DEBUG [JDBCExceptionReporter]() SQL Exception
java.sql.SQLException: The column prefix 'x0__1_' does not match with a table name or alias name used in the query.
   at net.sourceforge.jtds.jdbc.SqlMessage.toSQLException(Unknown Source)
   at net.sourceforge.jtds.jdbc.SQLWarningChain.addOrReturn(Unknown Source)
   at net.sourceforge.jtds.jdbc.Tds.submitProcedure(Unknown Source)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.submitProcedure(Unknown Source)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.execute(Unknown Source)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.executeQuery(Unknown Source)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:205)
   at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:83)
   at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:794)
   at net.sf.hibernate.loader.Loader.doQuery(Loader.java:188)
   at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:132)
   at net.sf.hibernate.loader.Loader.doList(Loader.java:949)
   at net.sf.hibernate.loader.CriteriaLoader.list(CriteriaLoader.java:109)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:3440)
   at net.sf.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:177)
   at net.sf.hibernate.impl.CriteriaImpl$Subcriteria.list(CriteriaImpl.java:79)
   at com.savage.chubs.struts.SearchTruckMovement.process(SearchTruckMovement.java:144)
   at com.savage.chubs.struts.BaseAction.execute(BaseAction.java:77)
   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:763)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:284)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:204)
   at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
   at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
   at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:563)
   at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:245)
   at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:199)
   at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
   at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:594)
   at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:149)
   at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:563)
   at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:195)
   at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
   at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:164)
   at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:149)
   at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:563)
   at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:156)
   at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
   at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:563)
   at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:972)
   at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:209)
   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:534)
11:04:39,477 WARN  [JDBCExceptionReporter]() SQL Error: 107, SQLState: S1000
11:04:39,477 ERROR [JDBCExceptionReporter]() The column prefix 'x0__1_' does not match with a table name or alias name used in the query.
11:04:39,477 WARN  [JDBCExceptionReporter]() SQL Error: 107, SQLState: S1000
11:04:39,477 ERROR [JDBCExceptionReporter]() The column prefix 'x0__1_' does not match with a table name or alias name used in the query.
11:04:39,477 WARN  [JDBCExceptionReporter]() SQL Error: 107, SQLState: S1000
11:04:39,477 ERROR [JDBCExceptionReporter]() The column prefix 'x0__1_' does not match with a table name or alias name used in the query.
11:04:39,477 WARN  [JDBCExceptionReporter]() SQL Error: 107, SQLState: S1000
11:04:39,477 ERROR [JDBCExceptionReporter]() The column prefix 'x0__1_' does not match with a table name or alias name used in the query.
11:04:39,477 WARN  [JDBCExceptionReporter]() SQL Error: 107, SQLState: S1000
11:04:39,477 ERROR [JDBCExceptionReporter]() The column prefix 'x0__1_' does not match with a table name or alias name used in the query.
11:04:39,477 WARN  [JDBCExceptionReporter]() SQL Error: 107, SQLState: S1000
11:04:39,477 ERROR [JDBCExceptionReporter]() The column prefix 'x0__1_' does not match with a table name or alias name used in the query.
11:04:39,493 WARN  [JDBCExceptionReporter]() SQL Error: 107, SQLState: S1000
11:04:39,493 ERROR [JDBCExceptionReporter]() The column prefix 'x0__1_' does not match with a table name or alias name used in the query.
11:04:39,493 WARN  [JDBCExceptionReporter]() SQL Error: 107, SQLState: S1000
11:04:39,493 ERROR [JDBCExceptionReporter]() The column prefix 'x0__1_' does not match with a table name or alias name used in the query.
11:04:39,493 WARN  [JDBCExceptionReporter]() SQL Error: 107, SQLState: S1000
11:04:39,493 ERROR [JDBCExceptionReporter]() The column prefix 'x0__1_' does not match with a table name or alias name used in the query.
11:04:39,493 WARN  [JDBCExceptionReporter]() SQL Error: 107, SQLState: S1000
11:04:39,493 ERROR [JDBCExceptionReporter]() The column prefix 'x0__1_' does not match with a table name or alias name used in the query.
11:04:39,493 WARN  [JDBCExceptionReporter]() SQL Error: 107, SQLState: S1000
11:04:39,493 ERROR [JDBCExceptionReporter]() The column prefix 'x0__1_' does not match with a table name or alias name used in the query.
11:04:39,493 WARN  [JDBCExceptionReporter]() SQL Error: 107, SQLState: S1000
11:04:39,493 ERROR [JDBCExceptionReporter]() The column prefix 'x0__1_' does not match with a table name or alias name used in the query.
11:04:39,493 WARN  [JDBCExceptionReporter]() SQL Error: 107, SQLState: S1000
11:04:39,493 ERROR [JDBCExceptionReporter]() The column prefix 'x0__1_' does not match with a table name or alias name used in the query.
11:04:39,493 DEBUG [BatcherImpl]() done closing: 0 open PreparedStatements, 0 open ResultSets
11:04:39,493 DEBUG [BatcherImpl]() closing statement
11:04:39,493 DEBUG [JDBCExceptionReporter]() SQL Exception
java.sql.SQLException: The column prefix 'x0__1_' does not match with a table name or alias name used in the query.
   at net.sourceforge.jtds.jdbc.SqlMessage.toSQLException(Unknown Source)
   at net.sourceforge.jtds.jdbc.SQLWarningChain.addOrReturn(Unknown Source)
   at net.sourceforge.jtds.jdbc.Tds.submitProcedure(Unknown Source)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.submitProcedure(Unknown Source)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.execute(Unknown Source)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.executeQuery(Unknown Source)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:205)
   at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:83)
   at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:794)
   at net.sf.hibernate.loader.Loader.doQuery(Loader.java:188)
   at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:132)
   at net.sf.hibernate.loader.Loader.doList(Loader.java:949)
   at net.sf.hibernate.loader.CriteriaLoader.list(CriteriaLoader.java:109)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:3440)
   at net.sf.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:177)
   at net.sf.hibernate.impl.CriteriaImpl$Subcriteria.list(CriteriaImpl.java:79)
   at com.savage.chubs.struts.SearchTruckMovement.process(SearchTruckMovement.java:144)
   at com.savage.chubs.struts.BaseAction.execute(BaseAction.java:77)
   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:763)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:284)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:204)
   at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
   at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
   at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:563)
   at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:245)
   at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:199)
   at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
   at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:594)
   at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:149)
   at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:563)
   at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:195)
   at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
   at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:164)
   at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:149)
   at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:563)
   at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:156)
   at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
   at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:563)
   at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:972)
   at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:209)
   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:534)
11:04:39,524 WARN  [JDBCExceptionReporter]() SQL Error: 107, SQLState: S1000
11:04:39,524 ERROR [JDBCExceptionReporter]() The column prefix 'x0__1_' does not match with a table name or alias name used in the query.
11:04:39,524 WARN  [JDBCExceptionReporter]() SQL Error: 107, SQLState: S1000
11:04:39,524 ERROR [JDBCExceptionReporter]() The column prefix 'x0__1_' does not match with a table name or alias name used in the query.
11:04:39,524 WARN  [JDBCExceptionReporter]() SQL Error: 107, SQLState: S1000
11:04:39,540 ERROR [JDBCExceptionReporter]() The column prefix 'x0__1_' does not match with a table name or alias name used in the query.
11:04:39,540 WARN  [JDBCExceptionReporter]() SQL Error: 107, SQLState: S1000
11:04:39,540 ERROR [JDBCExceptionReporter]() The column prefix 'x0__1_' does not match with a table name or alias name used in the query.
11:04:39,540 WARN  [JDBCExceptionReporter]() SQL Error: 107, SQLState: S1000
11:04:39,540 ERROR [JDBCExceptionReporter]() The column prefix 'x0__1_' does not match with a table name or alias name used in the query.
11:04:39,540 WARN  [JDBCExceptionReporter]() SQL Error: 107, SQLState: S1000
11:04:39,540 ERROR [JDBCExceptionReporter]() The column prefix 'x0__1_' does not match with a table name or alias name used in the query.
11:04:39,540 WARN  [JDBCExceptionReporter]() SQL Error: 107, SQLState: S1000
11:04:39,540 ERROR [JDBCExceptionReporter]() The column prefix 'x0__1_' does not match with a table name or alias name used in the query.
11:04:39,540 WARN  [JDBCExceptionReporter]() SQL Error: 107, SQLState: S1000
11:04:39,540 ERROR [JDBCExceptionReporter]() The column prefix 'x0__1_' does not match with a table name or alias name used in the query.
11:04:39,540 WARN  [JDBCExceptionReporter]() SQL Error: 107, SQLState: S1000
11:04:39,540 ERROR [JDBCExceptionReporter]() The column prefix 'x0__1_' does not match with a table name or alias name used in the query.
11:04:39,540 WARN  [JDBCExceptionReporter]() SQL Error: 107, SQLState: S1000
11:04:39,540 ERROR [JDBCExceptionReporter]() The column prefix 'x0__1_' does not match with a table name or alias name used in the query.
11:04:39,540 WARN  [JDBCExceptionReporter]() SQL Error: 107, SQLState: S1000
11:04:39,540 ERROR [JDBCExceptionReporter]() The column prefix 'x0__1_' does not match with a table name or alias name used in the query.
11:04:39,540 WARN  [JDBCExceptionReporter]() SQL Error: 107, SQLState: S1000
11:04:39,555 ERROR [JDBCExceptionReporter]() The column prefix 'x0__1_' does not match with a table name or alias name used in the query.
11:04:39,555 WARN  [JDBCExceptionReporter]() SQL Error: 107, SQLState: S1000
11:04:39,555 ERROR [JDBCExceptionReporter]() The column prefix 'x0__1_' does not match with a table name or alias name used in the query.
11:04:39,555 ERROR [JDBCExceptionReporter]() SQLException occurred
java.sql.SQLException: The column prefix 'x0__1_' does not match with a table name or alias name used in the query.
   at net.sourceforge.jtds.jdbc.SqlMessage.toSQLException(Unknown Source)
   at net.sourceforge.jtds.jdbc.SQLWarningChain.addOrReturn(Unknown Source)
   at net.sourceforge.jtds.jdbc.Tds.submitProcedure(Unknown Source)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.submitProcedure(Unknown Source)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.execute(Unknown Source)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.executeQuery(Unknown Source)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:205)
   at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:83)
   at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:794)
   at net.sf.hibernate.loader.Loader.doQuery(Loader.java:188)
   at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:132)
   at net.sf.hibernate.loader.Loader.doList(Loader.java:949)
   at net.sf.hibernate.loader.CriteriaLoader.list(CriteriaLoader.java:109)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:3440)
   at net.sf.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:177)
   at net.sf.hibernate.impl.CriteriaImpl$Subcriteria.list(CriteriaImpl.java:79)
   at com.savage.chubs.struts.SearchTruckMovement.process(SearchTruckMovement.java:144)
   at com.savage.chubs.struts.BaseAction.execute(BaseAction.java:77)
   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:763)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:284)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:204)
   at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
   at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
   at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:563)
   at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:245)
   at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:199)
   at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
   at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:594)
   at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:149)
   at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:563)
   at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:195)
   at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
   at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:164)
   at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:149)
   at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:563)
   at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:156)
   at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
   at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:563)
   at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:972)
   at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:209)
   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:534)
11:04:47,117 DEBUG [JDBCTransaction]() rollback
11:04:47,133 DEBUG [SessionImpl]() transaction completion
11:04:47,149 DEBUG [JDBCTransaction]() re-enabling autocommit
11:04:47,180 DEBUG [SessionImpl]() closing session
11:04:47,196 DEBUG [SessionImpl]() disconnecting session
11:04:47,211 DEBUG [SessionImpl]() transaction completion

Using Criteria works wonderfully for everything else we are doing -- the only problems we have are with these children objects.

Does anyone know what might be the problem? I've looked at a lot of other posts and through the docs, and I'm doing what has worked for similar problems, but it doesn't seem to be working for us. Any help would be appreciated!

Thanks,
Andy
[/code]

emmanuel · **Posted:** Wed Feb 04, 2004 6:47 am

Is there a way to render the code as simple as possible ?

mrflippy · **Joined:** Tue Feb 03, 2004 12:39 pm **Posts:** 8

Which part of the code? I don't think I can simplify the Criteria part of the code any more. I could cut out parts of the mapping files though.

emmanuel · **Posted:** Wed Feb 04, 2004 12:23 pm

The mapping (joined-subclass, useless proeprties, useless associations etc)

mrflippy · **Joined:** Tue Feb 03, 2004 12:39 pm **Posts:** 8

Ok, here are the stripped-down mappings:

Movement:

Code:

<hibernate-mapping>

<class 
    name="com.savage.chubs.domain.Movement" 
    table="Movement"
>
    
    <!-- associations -->
    <set
        name="movementInventoryTransactions"
        lazy="false"
        cascade="all"
        inverse="true"
    >
        <key>
            <column name="MovementID" />
        </key>
        <one-to-many 
            class="com.savage.chubs.domain.MovementInventoryTransaction"
        />
    </set>
</class>
</hibernate-mapping>

InventoryTransaction:

Code:

<hibernate-mapping>

<class 
    name="com.savage.chubs.domain.InventoryTransaction" 
    table="InventoryTransaction"
>

    <many-to-one
        name="destinationCustomer"
        class="com.savage.chubs.domain.Agent"
        not-null="true"
    >
        <column name="DestinationCustomerID" />
    </many-to-one>

    <joined-subclass name="com.savage.chubs.domain.MovementInventoryTransaction" table="MovementInventoryTransaction">
        <key column="TransactionID"/>
        
        <many-to-one
            name="movement"
            class="com.savage.chubs.domain.Movement"
            not-null="true"
        >
            <column name="MovementID" />
        </many-to-one>
    </joined-subclass>
</class>
</hibernate-mapping>

gavin · **Posted:** Thu Feb 05, 2004 1:38 pm

PLease simplify not only the mapping but also the query.

I can't help unless you get something *really* simple.

mrflippy · **Joined:** Tue Feb 03, 2004 12:39 pm **Posts:** 8

gavin wrote:

PLease simplify not only the mapping but also the query.

I can't help unless you get something *really* simple.

Unfortunately, I think my lack of understanding of database terms may be getting in the way. I'll try to simplify and explain better this time :)

This is what we want to do:

Code:

Criteria criteria = session.createCriteria(TruckMovement.class);
criteria.add(Expression.eq("movementInventoryTransaction.destinationCustomer.agentId", 12345));

or, maybe simpler:

Code:

Criteria criteria = session.createCriteria(TruckShipment.class);
criteria.add(Expression.eq("transaction.receivingCustomer.customerId", 12345));

I hope this makes more sense.

Thanks for your time,
Andy

gavin · **Posted:** Thu Feb 05, 2004 3:16 pm

You must use:

Code:

Criteria criteria = session.createCriteria(TruckMovement.class);
criteria = criteria.createCriteria("movementInventoryTransactions");
criteria = criteria.createCriteria("destinationCustomer");
criteria.add( Expression.eq("agentId", 12345) );

mrflippy · **Joined:** Tue Feb 03, 2004 12:39 pm **Posts:** 8

I'm sorry Gavin, but that doesn't work for us. The code you posted was almost exactly what I had before I posted here, and I just tested your code as well. We are getting this exception:

Code:

The column prefix 'x0__1_' does not match with a table name or alias name used in the query.

Could we perhaps achieve the same result using Expression.sql?

Thanks,
Andy

mrflippy · **Joined:** Tue Feb 03, 2004 12:39 pm **Posts:** 8

Sorry, can't figure out how to edit the post... that exception is being thrown by criteria.list(), not by the createCriteria or add.

gavin · **Posted:** Thu Feb 05, 2004 3:44 pm

Show the SQL.

Also, make sure that you are using 2.1.2 release.

mrflippy · **Joined:** Tue Feb 03, 2004 12:39 pm **Posts:** 8

Here's the SQL:

It looks as though the subclassed table is included in the joins, but the parent table is not.

Code:

select this.MovementID as MovementID23_, this.TicketNumber as TicketNu2_14_23_, this.ScaleGross as ScaleGross14_23_, this.ScaleTare as ScaleTare14_23_, this.ScaleNet as ScaleNet14_23_, this.BOLNet as BOLNet14_23_, this.ShippersBOL as Shippers7_14_23_, this.CarrierNumber as CarrierN8_14_23_, this.TruckTrailerNumber as TruckTra9_14_23_, this_1_.MovementTypeID as Movement2_11_23_, this_1_.ShipperID as ShipperID11_23_, this_1_.BrokerID as BrokerID11_23_, this_1_.CarrierID as CarrierID11_23_, this_1_.MovementOriginLocationID as Movement6_11_23_, this_1_.MovementDestinationLocationID as Movement7_11_23_, this_1_.CommentId as CommentId11_23_, movementty1_.MovementTypeID as Movement1_0_, movementty1_.MovementName as Movement2_0_, movementty1_.MovementNameShort as Movement3_0_, agent2_.AgentID as AgentID1_, agent2_.AgentName as AgentName1_, agent2_.IsCustomer as IsCustomer1_, agent2_.IsConsignee as IsConsig4_1_, agent2_.IsShipper as IsShipper1_, agent2_.IsBroker as IsBroker1_, agent2_.IsGSS as IsGSS1_, agent2_.IsActive as IsActive1_, agent2_.IsCarrier as IsCarrier1_, agent2_.AgentNameShort as AgentNa10_1_, agent3_.AgentID as AgentID2_, agent3_.AgentName as AgentName2_, agent3_.IsCustomer as IsCustomer2_, agent3_.IsConsignee as IsConsig4_2_, agent3_.IsShipper as IsShipper2_, agent3_.IsBroker as IsBroker2_, agent3_.IsGSS as IsGSS2_, agent3_.IsActive as IsActive2_, agent3_.IsCarrier as IsCarrier2_, agent3_.AgentNameShort as AgentNa10_2_, agent4_.AgentID as AgentID3_, agent4_.AgentName as AgentName3_, agent4_.IsCustomer as IsCustomer3_, agent4_.IsConsignee as IsConsig4_3_, agent4_.IsShipper as IsShipper3_, agent4_.IsBroker as IsBroker3_, agent4_.IsGSS as IsGSS3_, agent4_.IsActive as IsActive3_, agent4_.IsCarrier as IsCarrier3_, agent4_.AgentNameShort as AgentNa10_3_, location5_.LocationID as LocationID4_, case when location5__1_.LocationID is not null then 1 when location5_.LocationID is not null then 0 end as clazz_4_, location5_.LocationName as Location2_8_4_, location5_.IsOrigin as IsOrigin8_4_, location5_.IsDestination as IsDestin4_8_4_, location5_.IsGSS as IsGSS8_4_, location5_.IsMarine as IsMarine8_4_, location5_.IsActive as IsActive8_4_, location5_.LocationNameShort as Location8_8_4_, location5__1_.DefaultMarineTypeID as DefaultM2_9_4_, marinetype6_.MarineTypeID as MarineTy1_5_, marinetype6_.Description as Descript2_5_, marinetype6_.MarineDescriptionShort as MarineDe3_5_, comment7_.CommentId as CommentId6_, comment7_.Comment as Comment6_, comment7_.PrintIndicator as PrintInd3_6_, comment7_.InternalComment as Internal4_6_, x0_.TransactionID as Transact1___, x0_.MovementID as MovementID__, x0_.TransactionID as Transact1_7_, x0_.MovementID as MovementID6_7_, x0__1_.TransactionDate as Transact2_5_7_, x0__1_.Quantity as Quantity5_7_, x0__1_.TransactionTypeID as Transact4_5_7_, x0__1_.OriginCustomerID as OriginCu5_5_7_, x0__1_.DestinationCustomerID as Destinat6_5_7_, x0__1_.OriginLocationID as OriginLo7_5_7_, x0__1_.DestinationLocationID as Destinat8_5_7_, x0__1_.CommentId as CommentId5_7_, x0__1_.FileAttachmentId as FileAtt10_5_7_, movement9_.MovementID as MovementID8_, case when movement9__1_.MovementID is not null then 1 when movement9__2_.MovementID is not null then 2 when movement9__3_.MovementID is not null then 3 when movement9_.MovementID is not null then 0 end as clazz_8_, movement9_.MovementTypeID as Movement2_11_8_, movement9_.ShipperID as ShipperID11_8_, movement9_.BrokerID as BrokerID11_8_, movement9_.CarrierID as CarrierID11_8_, movement9_.MovementOriginLocationID as Movement6_11_8_, movement9_.MovementDestinationLocationID as Movement7_11_8_, movement9_.CommentId as CommentId11_8_, movement9__1_.VoyageNumber as VoyageNu2_12_8_, movement9__1_.SurveyNumber as SurveyNu3_12_8_, movement9__1_.BillOfLading as BillOfLa4_12_8_, movement9__1_.LoadPortArrivalDate as LoadPort5_12_8_, movement9__1_.LoadPortConnectDate as LoadPort6_12_8_, movement9__1_.LoadPortDisconnectDate as LoadPort7_12_8_, movement9__1_.LoadPortDepartDate as LoadPort8_12_8_, movement9__1_.DischargePortArrivalDate as Discharg9_12_8_, movement9__1_.DischargePortConnectDate as Dischar10_12_8_, movement9__1_.DischargePortDisconnectDate as Dischar11_12_8_, movement9__1_.DischargePortDepartDate as Dischar12_12_8_, movement9__1_.ArriveGalvastonBuoy as ArriveG13_12_8_, movement9__1_.ArriveTampaBuoy as ArriveT14_12_8_, movement9__1_.StartPointDate as StartPo15_12_8_, movement9__1_.EndPointDate as EndPoin16_12_8_, movement9__1_.LocationID as LocationID12_8_, movement9__1_.MarineTypeID as MarineT18_12_8_, movement9__1_.StartPointID as StartPo19_12_8_, movement9__1_.EndPointID as EndPointID12_8_, movement9__1_.BilledCustomerID as BilledC21_12_8_, movement9__2_.RailcarNumber as RailcarN2_13_8_, movement9__2_.InYardDate as InYardDate13_8_, movement9__2_.InInventoryDate as InInvent4_13_8_, movement9__2_.ShipDate as ShipDate13_8_, movement9__2_.ReleaseDate as ReleaseD6_13_8_, movement9__2_.SteamStart as SteamStart13_8_, movement9__2_.SteamStop as SteamStop13_8_, movement9__2_.UnitTrain as UnitTrain13_8_, movement9__2_.RoadMarkID as RoadMarkID13_8_, movement9__3_.TicketNumber as TicketNu2_14_8_, movement9__3_.ScaleGross as ScaleGross14_8_, movement9__3_.ScaleTare as ScaleTare14_8_, movement9__3_.ScaleNet as ScaleNet14_8_, movement9__3_.BOLNet as BOLNet14_8_, movement9__3_.ShippersBOL as Shippers7_14_8_, movement9__3_.CarrierNumber as CarrierN8_14_8_, movement9__3_.TruckTrailerNumber as TruckTra9_14_8_, movementty10_.MovementTypeID as Movement1_9_, movementty10_.MovementName as Movement2_9_, movementty10_.MovementNameShort as Movement3_9_, agent11_.AgentID as AgentID10_, agent11_.AgentName as AgentName10_, agent11_.IsCustomer as IsCustomer10_, agent11_.IsConsignee as IsConsig4_10_, agent11_.IsShipper as IsShipper10_, agent11_.IsBroker as IsBroker10_, agent11_.IsGSS as IsGSS10_, agent11_.IsActive as IsActive10_, agent11_.IsCarrier as IsCarrier10_, agent11_.AgentNameShort as AgentNa10_10_, agent12_.AgentID as AgentID11_, agent12_.AgentName as AgentName11_, agent12_.IsCustomer as IsCustomer11_, agent12_.IsConsignee as IsConsig4_11_, agent12_.IsShipper as IsShipper11_, agent12_.IsBroker as IsBroker11_, agent12_.IsGSS as IsGSS11_, agent12_.IsActive as IsActive11_, agent12_.IsCarrier as IsCarrier11_, agent12_.AgentNameShort as AgentNa10_11_, agent13_.AgentID as AgentID12_, agent13_.AgentName as AgentName12_, agent13_.IsCustomer as IsCustomer12_, agent13_.IsConsignee as IsConsig4_12_, agent13_.IsShipper as IsShipper12_, agent13_.IsBroker as IsBroker12_, agent13_.IsGSS as IsGSS12_, agent13_.IsActive as IsActive12_, agent13_.IsCarrier as IsCarrier12_, agent13_.AgentNameShort as AgentNa10_12_, comment14_.CommentId as CommentId13_, comment14_.Comment as Comment13_, comment14_.PrintIndicator as PrintInd3_13_, comment14_.InternalComment as Internal4_13_, marine15_.LocationID as LocationID14_, marine15_.DefaultMarineTypeID as DefaultM2_9_14_, marine15__1_.LocationName as Location2_8_14_, marine15__1_.IsOrigin as IsOrigin8_14_, marine15__1_.IsDestination as IsDestin4_8_14_, marine15__1_.IsGSS as IsGSS8_14_, marine15__1_.IsMarine as IsMarine8_14_, marine15__1_.IsActive as IsActive8_14_, marine15__1_.LocationNameShort as Location8_8_14_, marinetype16_.MarineTypeID as MarineTy1_15_, marinetype16_.Description as Descript2_15_, marinetype16_.MarineDescriptionShort as MarineDe3_15_, marinetype17_.MarineTypeID as MarineTy1_16_, marinetype17_.Description as Descript2_16_, marinetype17_.MarineDescriptionShort as MarineDe3_16_, agent18_.AgentID as AgentID17_, agent18_.AgentName as AgentName17_, agent18_.IsCustomer as IsCustomer17_, agent18_.IsConsignee as IsConsig4_17_, agent18_.IsShipper as IsShipper17_, agent18_.IsBroker as IsBroker17_, agent18_.IsGSS as IsGSS17_, agent18_.IsActive as IsActive17_, agent18_.IsCarrier as IsCarrier17_, agent18_.AgentNameShort as AgentNa10_17_, roadmark19_.RoadMarkID as RoadMarkID18_, roadmark19_.Name as Name18_, roadmark19_.IsActive as IsActive18_, agent20_.AgentID as AgentID19_, agent20_.AgentName as AgentName19_, agent20_.IsCustomer as IsCustomer19_, agent20_.IsConsignee as IsConsig4_19_, agent20_.IsShipper as IsShipper19_, agent20_.IsBroker as IsBroker19_, agent20_.IsGSS as IsGSS19_, agent20_.IsActive as IsActive19_, agent20_.IsCarrier as IsCarrier19_, agent20_.AgentNameShort as AgentNa10_19_, x1_.AgentID as AgentID20_, x1_.AgentName as AgentName20_, x1_.IsCustomer as IsCustomer20_, x1_.IsConsignee as IsConsig4_20_, x1_.IsShipper as IsShipper20_, x1_.IsBroker as IsBroker20_, x1_.IsGSS as IsGSS20_, x1_.IsActive as IsActive20_, x1_.IsCarrier as IsCarrier20_, x1_.AgentNameShort as AgentNa10_20_, comment22_.CommentId as CommentId21_, comment22_.Comment as Comment21_, comment22_.PrintIndicator as PrintInd3_21_, comment22_.InternalComment as Internal4_21_, fileattach23_.AttachmentID as Attachme1_22_, fileattach23_.FileName as FileName22_, fileattach23_.Data as Data22_ from TruckMovement this inner join Movement this_1_ on this.MovementID=this_1_.MovementID left outer join MovementType movementty1_ on this_1_.MovementTypeID=movementty1_.MovementTypeID left outer join Agent agent2_ on this_1_.ShipperID=agent2_.AgentID left outer join Agent agent3_ on this_1_.BrokerID=agent3_.AgentID left outer join Agent agent4_ on this_1_.CarrierID=agent4_.AgentID left outer join Location location5_ on this_1_.MovementOriginLocationID=location5_.LocationID left outer join Marine location5__1_ on location5_.LocationID=location5__1_.LocationID left outer join MarineType marinetype6_ on location5__1_.DefaultMarineTypeID=marinetype6_.MarineTypeID left outer join Comments comment7_ on this_1_.CommentId=comment7_.CommentId inner join MovementInventoryTransaction x0_ on this.MovementID=x0_.MovementID left outer join Movement movement9_ on x0_.MovementID=movement9_.MovementID left outer join MarineMovement movement9__1_ on movement9_.MovementID=movement9__1_.MovementID left outer join RailcarMovement movement9__2_ on movement9_.MovementID=movement9__2_.MovementID left outer join TruckMovement movement9__3_ on movement9_.MovementID=movement9__3_.MovementID left outer join MovementType movementty10_ on movement9_.MovementTypeID=movementty10_.MovementTypeID left outer join Agent agent11_ on movement9_.ShipperID=agent11_.AgentID left outer join Agent agent12_ on movement9_.BrokerID=agent12_.AgentID left outer join Agent agent13_ on movement9_.CarrierID=agent13_.AgentID left outer join Comments comment14_ on movement9_.CommentId=comment14_.CommentId left outer join Marine marine15_ on movement9__1_.LocationID=marine15_.LocationID left outer join Location marine15__1_ on marine15_.LocationID=marine15__1_.LocationID left outer join MarineType marinetype16_ on marine15_.DefaultMarineTypeID=marinetype16_.MarineTypeID left outer join MarineType marinetype17_ on movement9__1_.MarineTypeID=marinetype17_.MarineTypeID left outer join Agent agent18_ on movement9__1_.BilledCustomerID=agent18_.AgentID left outer join RoadMark roadmark19_ on movement9__2_.RoadMarkID=roadmark19_.RoadMarkID left outer join Agent agent20_ on x0__1_.OriginCustomerID=agent20_.AgentID inner join Agent x1_ on x0__1_.DestinationCustomerID=x1_.AgentID left outer join Comments comment22_ on x0__1_.CommentId=comment22_.CommentId left outer join FileAttachment fileattach23_ on x0__1_.FileAttachmentId=fileattach23_.AttachmentID where x1_.AgentID=?

(Replace the ? at the end with an integer)

emmanuel · **Posted:** Fri Feb 06, 2004 4:53 am

Please !
Make it as simple as possible, We cannot spend half an hour reading an SQL requst of 20 lines. This forum is around 115 topics a day and probably far more for the last 3 months.
Or use commercial support for customize, answer.

MrLoud · **Joined:** Fri Feb 06, 2004 6:59 pm **Posts:** 4

Hi all, I'm working with Andy on this. Here's a simple version of the problem:

Parent class mapping:

Code:

<hibernate-mapping>
<class 
    name="criteriatest.Parent" 
    table="Parent"
>
    <id
        name="id"
        type="java.lang.Integer"
        column="parentId"
        unsaved-value="0"
    >
        <generator class="hilo" />
    </id>
    
    <property
        name="name"
        type="java.lang.String"
        column="name"
        not-null="true"
    />
    
    <set
        name="subChildren"
        lazy="false"
        cascade="all"
        inverse="true"
    >
        <key>
            <column name="parentId" />
        </key>
        <one-to-many 
            class="criteriatest.SubChild"
        />
    </set>
</class>
</hibernate-mapping>

Child and subclassed child mapping:

Code:

<hibernate-mapping>
    <class 
        name="criteriatest.Child" 
        table="Child"
    >
        <id
            name="id"
            type="java.lang.Integer"
            column="childId"
            unsaved-value="0"
        >
            <generator class="hilo" />
        </id>
        <property
            name="quantity"
            type="int"
            column="qty"
            not-null="true"
        />

        <joined-subclass
            name="criteriatest.SubChild"
            table="SubChild"
        >
            <key column="childId"/>

            <many-to-one
                name="parent"
                class="criteriatest.Parent"
                not-null="true"
            >
                <column name="parentId" />
            </many-to-one>
        </joined-subclass>
    </class>
</hibernate-mapping>

The search code:

Code:

        Criteria criteria = session.createCriteria(Parent.class);
        criteria = criteria.createCriteria("subChildren");
        criteria.add( Expression.eq("quantity", new Integer(12)) );

The SQL:

Code:

select this.parentId as parentId1_, this.name as name1_, x0_.childId as childId__, x0_.parentId as parentId__, x0_.childId as childId0_, x0_.parentId as parentId1_0_, x0__1_.qty as qty0_0_ from Parent this inner join SubChild x0_ on this.parentId=x0_.parentId where x0__1_.qty=?

The stacktrace:

Code:

java.sql.SQLException: The column prefix 'x0__1_' does not match with a table name or alias name used in the query.
   at net.sourceforge.jtds.jdbc.SqlMessage.toSQLException(Unknown Source)
   at net.sourceforge.jtds.jdbc.SQLWarningChain.addOrReturn(Unknown Source)
   at net.sourceforge.jtds.jdbc.Tds.submitProcedure(Unknown Source)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.submitProcedure(Unknown Source)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.execute(Unknown Source)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.executeQuery(Unknown Source)
   at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
   at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:795)
   at net.sf.hibernate.loader.Loader.doQuery(Loader.java:189)
   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.loader.CriteriaLoader.list(CriteriaLoader.java:118)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:3571)
   at net.sf.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:223)
   at net.sf.hibernate.impl.CriteriaImpl$Subcriteria.list(CriteriaImpl.java:84)
   at criteriatest.CriteriaTest.search(CriteriaTest.java:36)
   at criteriatest.CriteriaTest.main(CriteriaTest.java:46)

Sorry about being so verbose before, usually everyone asks for more information. Thanks for your help!

drj · **Posted:** Sun Feb 08, 2004 8:20 am

Quote:

Criteria criteria = session.createCriteria(Parent.class);
criteria = criteria.createCriteria("subChildren");
criteria.add( Expression.eq("quantity", new Integer(12)) );

Why are you re-assigning (and overwriting) your criteria reference ?

Shouldn't this be:

Code:

Criteria criteria = session.createCriteria(Parent.class);
Criteria childrenCriteria = criteria.createCriteria("subChildren");
childrenCriteria.add( Expression.eq("quantity", new Integer(12)) ); 

List results = criteria.list();