Lists gaining null element after query.

adcworks · **Joined:** Fri Jun 18, 2004 10:17 am **Posts:** 140

Hi,

I am getting some strange behaviour when I load instances of an object called POF. A POF has 2 List collections lineItems and actionHistory. I have 1 POF persisted in the database which has 1 LineItem in the lineItem collection and 2 Actions in the actionHistory list.

When I load the POF, the lineItems and actionHistory lists are 1 element longer than they should be. Debugging this, I found each had a NULL entry. The lists are initialised in the object constructor as 0 length.

Here is what is printed when I iterate over lineItems:

null element
com.qas.newmedia.intranet.pof.dto.LineItem

As you can see, only the 2nd should be in the list.

I have run the HQL that Hibernate is sending to the DB and the queries are correct in the number of rows they get, so quite where these NULL entries are coming from is unknown to me!

Any ideas really helpful!

Hibernate version:

2.1.6

Mapping documents:

N/A

Code between sessionFactory.openSession() and session.close():

Code:

         Query q = session.createQuery(
                "from POF as p " +
                "   left outer join fetch p.lineItems " +
                "where p.id = :pofId"
            );
            q.setParameter("pofId", id);
            pof  = (POF) q.list().get(0);

Full stack trace of any exception that occurs:

N/A

Name and version of the database you are using:

SQLS2K

The generated SQL (show_sql=true):

Code:

Hibernate: select pof0_.pof_id as pof_id0_, lineitems1_.line_item_id as line_ite1_1_, pof0_.created as created0_, pof0_.status as status0_, pof0_.reference as reference0_, pof0_.external_id as external5_0_, pof0_.originator as originator0_, pof0_.originator_full_name as originat7_0_, pof0_.department as department0_, pof0_.department_code as departme9_0_, pof0_.currency_code as currenc10_0_, pof0_.tax_rate as tax_rate0_, pof0_.authoriser as authoriser0_, pof0_.authoriser_full_name as authori13_0_, pof0_.applies_to_min as applies14_0_, pof0_.applies_to_max as applies15_0_, pof0_.net_cost as net_cost0_, pof0_.discount_cost as discoun17_0_, pof0_.carriage_cost as carriag18_0_, pof0_.tax_cost as tax_cost0_, pof0_.gross_cost as gross_cost0_, pof0_.notes as notes0_, pof0_.supplier_id as supplie22_0_, pof0_.delivery_address_id as deliver23_0_, lineitems1_.code as code1_, lineitems1_.description as descript3_1_, lineitems1_.fn as fn1_, lineitems1_.quantity as quantity1_, lineitems1_.unit_price as unit_price1_, lineitems1_.tax_type as tax_type1_, lineitems1_.net_cost as net_cost1_, lineitems1_.notes as notes1_, lineitems1_.pof_id as pof_id1_, lineitems1_.line_item_id as line_ite1___, lineitems1_.pof_id as pof_id__ from dbPOF.dbo.tbl_POFs pof0_ left outer join dbPOF.dbo.tbl_LineItems lineitems1_ on pof0_.pof_id=lineitems1_.pof_id where (pof0_.pof_id=? )

Debug level Hibernate log excerpt:

N/A

adcworks · **Joined:** Fri Jun 18, 2004 10:17 am **Posts:** 140

does nobody have any idea about this oddity?

adcworks · **Joined:** Fri Jun 18, 2004 10:17 am **Posts:** 140

here is some more logging. Note that i output the list size at the start which is 0 and then at the end of the various HQL calls is 2. If I run the first HQL statement, the main record comes back, 1 row, fine. The I run the other 2 collection HQL calls, the list comes back as 1 row only. The other list comes back as 2 rows. Yet each list has +1 null element. This has to be Hibernate?

Code:

[b]1lisize=0[/b]
Hibernate: select pof0_.pof_id as pof_id3_, pof0_.created as created3_, pof0_.status as status3_, pof0_.reference as reference3_, pof0_.external_id as external5_3_, pof0_.originator as originator3_, pof0_.originator_full_name as originat7_3_, pof0_.department as department3_, pof0_.department_code as departme9_3_, pof0_.currency_code as currenc10_3_, pof0_.tax_rate as tax_rate3_, pof0_.authoriser as authoriser3_, pof0_.authoriser_full_name as authori13_3_, pof0_.applies_to_min as applies14_3_, pof0_.applies_to_max as applies15_3_, pof0_.net_cost as net_cost3_, pof0_.discount_cost as discoun17_3_, pof0_.carriage_cost as carriag18_3_, pof0_.tax_cost as tax_cost3_, pof0_.gross_cost as gross_cost3_, pof0_.notes as notes3_, pof0_.supplier_id as supplie22_3_, pof0_.delivery_address_id as deliver23_3_, supplier1_.supplier_id as supplier1_0_, supplier1_.code as code0_, supplier1_.address_id as address_id0_, intlcompan2_.intl_comp_det_id as intl_com1_1_, intlcompan2_.company as company1_, intlcompan2_.contact as contact1_, intlcompan2_.telephone as telephone1_, intlcompan2_.fax as fax1_, intlcompan2_.email as email1_, intlcompan2_.web as web1_, intlcompan2_.address_line_1 as address_8_1_, intlcompan2_.address_line_2 as address_9_1_, intlcompan2_.address_line_3 as address10_1_, intlcompan2_.address_line_4 as address11_1_, intlcompan2_.address_line_5 as address12_1_, intlcompan2_.address_line_6 as address13_1_, intlcompan2_.address_line_7 as address14_1_, intlcompan3_.intl_comp_det_id as intl_com1_2_, intlcompan3_.company as company2_, intlcompan3_.contact as contact2_, intlcompan3_.telephone as telephone2_, intlcompan3_.fax as fax2_, intlcompan3_.email as email2_, intlcompan3_.web as web2_, intlcompan3_.address_line_1 as address_8_2_, intlcompan3_.address_line_2 as address_9_2_, intlcompan3_.address_line_3 as address10_2_, intlcompan3_.address_line_4 as address11_2_, intlcompan3_.address_line_5 as address12_2_, intlcompan3_.address_line_6 as address13_2_, intlcompan3_.address_line_7 as address14_2_ from dbPOF.dbo.tbl_POFs pof0_ left outer join dbPOF.dbo.tbl_Suppliers supplier1_ on pof0_.supplier_id=supplier1_.supplier_id left outer join dbPOF.dbo.tbl_IntlCompanyDetails intlcompan2_ on supplier1_.address_id=intlcompan2_.intl_comp_det_id left outer join dbPOF.dbo.tbl_IntlCompanyDetails intlcompan3_ on pof0_.delivery_address_id=intlcompan3_.intl_comp_det_id where pof0_.pof_id=?
Hibernate: select actionhist0_.action_id as action_id__, actionhist0_.pof_id as pof_id__, actionhist0_.action_id as action_id0_, actionhist0_.created as created0_, actionhist0_.username as username0_, actionhist0_.fullName as fullName0_, actionhist0_.department as department0_, actionhist0_.action as action0_, actionhist0_.pof_id as pof_id0_ from dbPOF.dbo.tbl_Actions actionhist0_ where actionhist0_.pof_id=?
Hibernate: select lineitems0_.line_item_id as line_ite1___, lineitems0_.pof_id as pof_id__, lineitems0_.line_item_id as line_ite1_0_, lineitems0_.code as code0_, lineitems0_.description as descript3_0_, lineitems0_.fn as fn0_, lineitems0_.quantity as quantity0_, lineitems0_.unit_price as unit_price0_, lineitems0_.tax_type as tax_type0_, lineitems0_.net_cost as net_cost0_, lineitems0_.notes as notes0_, lineitems0_.pof_id as pof_id0_ from dbPOF.dbo.tbl_LineItems lineitems0_ where lineitems0_.pof_id=?
[b]2lisize=2[/b]

Reto · **Posted:** Mon Oct 25, 2004 3:49 pm

Hi

What is the result when your query looks like this:

Code:

Query q = session.createQuery(
                "from POF as p where p.id = :pofId" );
            q.setParameter("pofId", id);
            pof  = (POF) q.list().get(0);

Reto

adcworks · **Joined:** Fri Jun 18, 2004 10:17 am **Posts:** 140

but this query returns only a POF. I am not having trouble loading the single POF. It is when I eagerly fetch the pof.lineItems and pof.actionHistory list collection, that each collection comes back with 1 extra null element.

thanks!

anthony · **Posted:** Tue Oct 26, 2004 4:24 am

you didn't show mapping file....

adcworks · **Joined:** Fri Jun 18, 2004 10:17 am **Posts:** 140

ok, you asked for it.

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 schema="dbPOF.dbo">

    <class name="com.qas.newmedia.intranet.pof.dto.POF" 
      table="tbl_POFs">

        <id name="id" type="int" column="pof_id" unsaved-value="0">
           <generator class="identity" />
       </id>
                        
        <property name="created" column="created" type="calendar" not-null="true" />
      <property name="status" column="status" not-null="true" />
      <property name="reference" column="reference" not-null="true" />
      <property name="externalId" column="external_id" not-null="false" />
      <property name="originator" column="originator" not-null="true" />
      <property name="originatorFullName" column="originator_full_name" not-null="true" />
      <property name="department" column="department" not-null="true" />
      <property name="departmentCode" column="department_code" not-null="true" />
      <property name="currencyCode" column="currency_code" not-null="true" />
      
      <property name="taxRate">
         <column name="tax_rate" not-null="true" sql-type="money" />
      </property>
      
      <property name="authoriser" column="authoriser" not-null="true" />
      <property name="authoriserFullName" column="authoriser_full_name" not-null="true" />
      <property name="appliesToMin" column="applies_to_min" type="calendar" not-null="true" />
      <property name="appliesToMax" column="applies_to_max" type="calendar" not-null="true" />

      <property name="netCost">
         <column name="net_cost" not-null="true" sql-type="money" />
      </property>
      
      <property name="discountCost">
         <column name="discount_cost" not-null="true" sql-type="money" />
      </property>
      
      <property name="carriageCost">
         <column name="carriage_cost" not-null="true" sql-type="money" />
      </property>            

      <property name="taxCost">
         <column name="tax_cost" not-null="true" sql-type="money" />
      </property>

      <property name="grossCost">
         <column name="gross_cost" not-null="true" sql-type="money" />
      </property>

      <property name="notes">
         <column name="notes" not-null="false" sql-type="varchar(4000)" />
      </property>
 
       <many-to-one name="supplier" column="supplier_id" cascade="all" unique="true" />
      <many-to-one name="deliveryAddress" column="delivery_address_id" cascade="all" unique="true" />

      <list name="lineItems" inverse="true" lazy="true" cascade="all">
            <key column="pof_id"/>
         <index column="line_item_id" />
            <one-to-many class="com.qas.newmedia.intranet.pof.dto.LineItem" />  
        </list>

      <list name="actionHistory" inverse="true" lazy="true" cascade="all">
            <key column="pof_id"/>
         <index column="action_id" />
            <one-to-many class="com.qas.newmedia.intranet.pof.dto.Action" />  
        </list>

    </class>

   <class name="com.qas.newmedia.intranet.pof.dto.LineItem" 
      table="tbl_LineItems">

        <id name="id" type="int" column="line_item_id" unsaved-value="0">
           <generator class="identity" />
       </id>

      <property name="code" column="code" not-null="false" />
      <property name="description" column="description" not-null="true" />
      <property name="function" column="fn" not-null="false" />
      <property name="quantity" column="quantity" type="int" not-null="true" />

      <property name="unitPrice">
         <column name="unit_price" not-null="true" sql-type="money" />
      </property>

      <property name="taxType" column="tax_type" not-null="true" />

      <property name="netCost">
         <column name="net_cost" not-null="true" sql-type="money" />
      </property>

      <property name="notes">
         <column name="notes" not-null="false" sql-type="varchar(4000)" />
      </property>

      <many-to-one name="purchaseOrderForm" column="pof_id" not-null="true" />

   </class>
   
   <class name="com.qas.newmedia.intranet.pof.dto.Supplier" 
      table="tbl_Suppliers">

        <id name="id" type="int" column="supplier_id" unsaved-value="0">
           <generator class="identity" />
       </id>

      <property name="code" column="code" not-null="true" />
      <many-to-one name="address" column="address_id" cascade="all" unique="true" />

   </class>

   <class name="com.qas.newmedia.intranet.pof.dto.IntlCompanyDetail" 
      table="tbl_IntlCompanyDetails">

        <id name="id" type="int" column="intl_comp_det_id" unsaved-value="0">
           <generator class="identity" />
       </id>
      
      <property name="company" column="company" not-null="false" />
      <property name="contact" column="contact" not-null="false" />
      <property name="telephone" column="telephone" not-null="false" />
      <property name="fax" column="fax" not-null="false" />
      <property name="email" column="email" not-null="false" />
      <property name="web" column="web" not-null="false" />
      <property name="addressLine1" column="address_line_1" not-null="false" />
      <property name="addressLine2" column="address_line_2" not-null="false" />
      <property name="addressLine3" column="address_line_3" not-null="false" />
      <property name="addressLine4" column="address_line_4" not-null="false" />
      <property name="addressLine5" column="address_line_5" not-null="false" />
      <property name="addressLine6" column="address_line_6" not-null="false" />
      <property name="addressLine7" column="address_line_7" not-null="false" />

   </class>

   <class name="com.qas.newmedia.intranet.pof.dto.Action" 
      table="tbl_Actions">

        <id name="id" type="int" column="action_id" unsaved-value="0">
           <generator class="identity" />
       </id>

      <property name="created" column="created" type="calendar" not-null="false" />
      <property name="username" column="username" not-null="false" />
      <property name="fullName" column="fullName" not-null="false" />
      <property name="department" column="department" not-null="false" />
      <property name="action" column="action" not-null="false" />

      <many-to-one name="purchaseOrderForm" column="pof_id" not-null="true" />
   </class>
   
</hibernate-mapping> 

anthony · **Posted:** Tue Oct 26, 2004 4:32 am

<index column="line_item_id" />

if you don't have a row with line_item_id=0 then it is the expected behaviour (of a list) to have the first element = null

adcworks · **Joined:** Fri Jun 18, 2004 10:17 am **Posts:** 140

i see :( so the index actually stores the position of an element in the list and this must start with 0 for each pof_id in that table else i get a null element?

i had the line_item_id as an incremental primary key so that column could never be 0 more than once. i have removed the primary key but i notice that the <index..> element has made the line_item_id a non-null insert.

Yet when i try to insert using Hibernate, it does not appear to be trying to save the line_item_id at all.

Code:

Hibernate: insert into dbPOF.dbo.tbl_LineItems (code, description, fn, quantity, unit_price, tax_type, net_cost, notes, pof_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?)

Code:

SEVERE: could not insert: [com.qas.newmedia.intranet.pof.dto.LineItem]
java.sql.SQLException: Cannot insert the value NULL into column 'line_item_id', table 'dbPOF.dbo.tbl_LineItems'; column does not allow nulls. INSERT fails.

Code:

<list name="lineItems" inverse="true" lazy="true" cascade="all">
            <key column="pof_id"/>
         <index column="line_item_id" />
            <one-to-many class="com.qas.newmedia.intranet.pof.dto.LineItem" />  
        </list>

adcworks · **Joined:** Fri Jun 18, 2004 10:17 am **Posts:** 140

ok, i have altered my mapping to be

Code:

      <list name="lineItems" inverse="true" lazy="true" cascade="all">
            <key column="pof_id"/>
         <index column="list_pos" />
            <one-to-many class="com.qas.newmedia.intranet.pof.dto.LineItem" />  
        </list>

following the example in the online documentation. I notice however that the index column list_pos is being inserted as NULL rather than a sequential int starting at 0.

Do I need to do some additional mapping or configuration??

Thanks!

gavin · **Posted:** Tue Oct 26, 2004 5:38 am

This is a FAQ. inverse="true" does not work well with lists.

adcworks · **Joined:** Fri Jun 18, 2004 10:17 am **Posts:** 140

is there any chance at all that you could point me in a general direction where I can help myself find the solution.

I checked the FAQ as you say but it's not there. Searching this forum on "Lists + inverse="true"" brings back funnily enough this very post! :)

Are you saying i should remove inverse="true" or switch to using a bag or something else?

adcworks · **Joined:** Fri Jun 18, 2004 10:17 am **Posts:** 140

This issue has been solved using the following document:

http://www.hibernate.org/193.html

anthony · **Posted:** Tue Oct 26, 2004 8:40 am

what about mapping a list with a bag in the mapping file?

adcworks · **Joined:** Fri Jun 18, 2004 10:17 am **Posts:** 140

the line items and action history collections should be ordered. i suppose i could use an order-by on a bag, but since i now have the list working :) i think i will stick with it, unless of course there is a reason why i really should not use a list :)

thanks very much for your advice.