Mapping a List : collection index problem

koma · **Posted:** Fri Jan 09, 2004 7:55 am

Hi,

I have a parent child relationship

1 Allocation -> Many AllocationDays

mapped as a List with dayOfWeek column in the table AllocationDays as the index column for sorting the list.

extract from the Allocation.hbm.xml :
-------------------------------------------
<list
name="allocationDays"
lazy="true"
inverse="false"
cascade="all-delete-orphan"
>
<key column="allocationId" />
<index column="dayOfWeek" />
<one-to-many class="package.AllocationDay"/>
</list>

I create a new Allocation and add allocationDays to the List (index 0..6) :

AllocationDay monday = new AllocationDay();
AllocationDay tuesday = new AllocationDay();
AllocationDay wednesday = new AllocationDay();
AllocationDay thursday = new AllocationDay();
AllocationDay friday = new AllocationDay();
AllocationDay saturday = new AllocationDay();
AllocationDay sunday = new AllocationDay();
allocationDays.add(Weekday.MONDAY.toInt(), monday);
allocationDays.add(Weekday.TUESDAY.toInt(), tuesday);
allocationDays.add(Weekday.WEDNESDAY.toInt(), wednesday);
allocationDays.add(Weekday.THURSDAY.toInt(), thursday);
allocationDays.add(Weekday.FRIDAY.toInt(), friday);
allocationDays.add(Weekday.SATURDAY.toInt(), saturday);
allocationDays.add(Weekday.SUNDAY.toInt(), sunday);

The allocationDay object does NOT have a bidirectional relationship with allocation (not allowed for List), and it does NOT have the collection-index column mapped. I suppose I'm not allowed to map the collection index column in the child object... hibernate uses this solely for sorting the collection/List (right?).

But ... the collection-index column 'dayOfWeek' in the table of the child object is not filled out by Hibernate and I dont see why. This results in a NOT NULL constraint violation :

This is the generated SQL :
Hibernate: insert into capacity.dbo.allocation (version, accountid, allocatedFrom, allocatedUntil, creationDt, destinationId, goodsdescriptionId, originId, reference, remark, scheduledFrom, scheduledMovementNr, scheduledUntil, ScheduledVehicleTypeId, username, volumeUnitId, weightUnitId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: select @@identity
Hibernate: insert into capacity.dbo.AllocationDays (version, maxVolume, maxWeight, sta, std) values (?, ?, ?, ?, ?) <<----------- TROUBLES !

I expected Hibernate to generate :
Hibernate: insert into capacity.dbo.AllocationDays (version, maxVolume, maxWeight, sta, std, dayOfWeek) values (?, ?, ?, ?, ?, ?)

Instead I end up with :

org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:167)
Caused by: net.sf.hibernate.JDBCException: Could not insert: [BRUAVIW2SV20006]Cannot insert the value NULL into column 'DayOfWeek', table 'capacity.dbo.AllocationDays'; column does not allow nulls. INSERT fails.
at net.sf.hibernate.impl.SessionImpl.doSave(SessionImpl.java:720)
at net.sf.hibernate.impl.SessionImpl.save(SessionImpl.java:605)
....

Anybody to give me a working example of a List mapping - or point me to my mistake...

thx in advance.

koma · **Posted:** Fri Jan 09, 2004 10:59 am

Hello world,

Got it to work - my conclusions :

1) You still have to fully map the parent object in the child object; i thought this would not be necessary/allowed, because getting/setting the parent in the child object has no effect and Hibernate knows from the <List> mapping which column to set as foreign key.

2) You still have to fully map the index field in the child object; i thought this would not be necessary/allowed, because it is determined by the order of the List accessible from the parent.

So I changed my child object adding the following :

public class AllocationDay {

private Long allocationDayId;
private Integer weekday = null;

+ getters & setters
........
}

Ok so far.

Next problem is that in my DB, there was a NOT-NULL constraint for DayOfWeek (the index column) and AllocationId (the parent object) in the child table AllocationDays.... I added the NOT-NULL attribute for the mappings of AllocationId and DayOfWeek but the delete strategy of Hibernate seems to be:
-- first disassociate the children from the parent (update to NULL for index+parent columns)
-- next delete the children

So i HAVE TO remove the NOT-NULL constraints - right ?? Any suggestions ?

Thx,

Koen

Here's the SQL for the create - and delete again - cylce :

Hibernate: insert into capacity.dbo.allocation (version, accountid, allocatedFrom, allocatedUntil, creationDt, destinationId, goodsdescriptionId, originId, reference, remark, scheduledFrom, scheduledMovementNr, scheduledUntil, ScheduledVehicleTypeId, username, volumeUnitId, weightUnitId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: select @@identity
Hibernate: insert into capacity.dbo.AllocationDays (version, dayOfWeek, maxVolume, maxWeight, sta, std, allocationId) values (?, ?, ?, ?, ?, ?, ?)
Hibernate: select @@identity
Hibernate: insert into capacity.dbo.AllocationDays (version, dayOfWeek, maxVolume, maxWeight, sta, std, allocationId) values (?, ?, ?, ?, ?, ?, ?)
Hibernate: select @@identity
Hibernate: insert into capacity.dbo.AllocationDays (version, dayOfWeek, maxVolume, maxWeight, sta, std, allocationId) values (?, ?, ?, ?, ?, ?, ?)
Hibernate: select @@identity
Hibernate: insert into capacity.dbo.AllocationDays (version, dayOfWeek, maxVolume, maxWeight, sta, std, allocationId) values (?, ?, ?, ?, ?, ?, ?)
Hibernate: select @@identity
Hibernate: insert into capacity.dbo.AllocationDays (version, dayOfWeek, maxVolume, maxWeight, sta, std, allocationId) values (?, ?, ?, ?, ?, ?, ?)
Hibernate: select @@identity
Hibernate: insert into capacity.dbo.AllocationDays (version, dayOfWeek, maxVolume, maxWeight, sta, std, allocationId) values (?, ?, ?, ?, ?, ?, ?)
Hibernate: select @@identity
Hibernate: insert into capacity.dbo.AllocationDays (version, dayOfWeek, maxVolume, maxWeight, sta, std, allocationId) values (?, ?, ?, ?, ?, ?, ?)
Hibernate: select @@identity
Hibernate: update capacity.dbo.AllocationDays set allocationId=?, dayOfWeek=? where allocationDayId=?
Hibernate: insert into capacity.dbo.allocationProducts (allocationId, productId) values (?, ?)
Hibernate: select allocati0_.allocationId as allocationId, allocati0_.version as version, allocati0_.accountid as accountid, allocati0_.allocatedFrom as allocate4_, allocati0_.allocatedUntil as allocate5_, allocati0_.creationDt as creationDt, allocati0_.destinationId as destinat7_, allocati0_.goodsdescriptionId as goodsdes8_, allocati0_.originId as originId, allocati0_.reference as reference, allocati0_.remark as remark, allocati0_.scheduledFrom as schedul12_, allocati0_.scheduledMovementNr as schedul13_, allocati0_.scheduledUntil as schedul14_, allocati0_.ScheduledVehicleTypeId as Schedul15_, allocati0_.username as username, allocati0_.volumeUnitId as volumeU17_, allocati0_.weightUnitId as weightU18_ from capacity.dbo.allocation allocati0_ where (allocati0_.reference='TESTALLOCATION:1073660103484' )
Hibernate: select allocati0_.allocationId as allocationId, allocati0_.version as version, allocati0_.accountid as accountid, allocati0_.allocatedFrom as allocate4_, allocati0_.allocatedUntil as allocate5_, allocati0_.creationDt as creationDt, allocati0_.destinationId as destinat7_, allocati0_.goodsdescriptionId as goodsdes8_, allocati0_.originId as originId, allocati0_.reference as reference, allocati0_.remark as remark, allocati0_.scheduledFrom as schedul12_, allocati0_.scheduledMovementNr as schedul13_, allocati0_.scheduledUntil as schedul14_, allocati0_.ScheduledVehicleTypeId as Schedul15_, allocati0_.username as username, allocati0_.volumeUnitId as volumeU17_, allocati0_.weightUnitId as weightU18_ from capacity.dbo.allocation allocati0_ where (allocati0_.reference='TESTALLOCATION:1073660103484' )
Hibernate: select allocati0_.allocationId as allocationId, allocati0_.version as version, allocati0_.accountid as accountid, allocati0_.allocatedFrom as allocate4_, allocati0_.allocatedUntil as allocate5_, allocati0_.creationDt as creationDt, allocati0_.destinationId as destinat7_, allocati0_.goodsdescriptionId as goodsdes8_, allocati0_.originId as originId, allocati0_.reference as reference, allocati0_.remark as remark, allocati0_.scheduledFrom as schedul12_, allocati0_.scheduledMovementNr as schedul13_, allocati0_.scheduledUntil as schedul14_, allocati0_.ScheduledVehicleTypeId as Schedul15_, allocati0_.username as username, allocati0_.volumeUnitId as volumeU17_, allocati0_.weightUnitId as weightU18_ from capacity.dbo.allocation allocati0_ where (allocati0_.reference='TESTALLOCATION:1073660103484' )
Hibernate: select allocati0_.allocationId as allocationId, allocati0_.version as version, allocati0_.accountid as accountid, allocati0_.allocatedFrom as allocate4_, allocati0_.allocatedUntil as allocate5_, allocati0_.creationDt as creationDt, allocati0_.destinationId as destinat7_, allocati0_.goodsdescriptionId as goodsdes8_, allocati0_.originId as originId, allocati0_.reference as reference, allocati0_.remark as remark, allocati0_.scheduledFrom as schedul12_, allocati0_.scheduledMovementNr as schedul13_, allocati0_.scheduledUntil as schedul14_, allocati0_.ScheduledVehicleTypeId as Schedul15_, allocati0_.username as username, allocati0_.volumeUnitId as volumeU17_, allocati0_.weightUnitId as weightU18_ from capacity.dbo.allocation allocati0_ where (allocati0_.reference='TESTALLOCATION:1073660103484' )
Hibernate: select allocati0_.allocationId as allocationId, allocati0_.version as version, allocati0_.accountid as accountid, allocati0_.allocatedFrom as allocate4_, allocati0_.allocatedUntil as allocate5_, allocati0_.creationDt as creationDt, allocati0_.destinationId as destinat7_, allocati0_.goodsdescriptionId as goodsdes8_, allocati0_.originId as originId, allocati0_.reference as reference, allocati0_.remark as remark, allocati0_.scheduledFrom as schedul12_, allocati0_.scheduledMovementNr as schedul13_, allocati0_.scheduledUntil as schedul14_, allocati0_.ScheduledVehicleTypeId as Schedul15_, allocati0_.username as username, allocati0_.volumeUnitId as volumeU17_, allocati0_.weightUnitId as weightU18_ from capacity.dbo.allocation allocati0_ where (allocati0_.allocationId=109 )
Hibernate: select allocati0_.allocationDayId as allocati1___, allocati0_.dayOfWeek as dayOfWeek__, allocati0_.allocationDayId as allocationDayId, allocati0_.version as version, allocati0_.dayOfWeek as dayOfWeek, allocati0_.maxVolume as maxVolume, allocati0_.maxWeight as maxWeight, allocati0_.sta as sta, allocati0_.std as std, allocati0_.allocationId as allocati8_ from capacity.dbo.AllocationDays allocati0_ where allocati0_.allocationId=?
Hibernate: update capacity.dbo.AllocationDays set allocationId=null, dayOfWeek=null where allocationId=?
Hibernate: delete from capacity.dbo.allocationProducts where allocationId=?
Hibernate: delete from capacity.dbo.AllocationDays where allocationDayId=? and version=?
Hibernate: delete from capacity.dbo.AllocationDays where allocationDayId=? and version=?
Hibernate: delete from capacity.dbo.AllocationDays where allocationDayId=? and version=?
Hibernate: delete from capacity.dbo.AllocationDays where allocationDayId=? and version=?
Hibernate: delete from capacity.dbo.AllocationDays where allocationDayId=? and version=?
Hibernate: delete from capacity.dbo.AllocationDays where allocationDayId=? and version=?
Hibernate: delete from capacity.dbo.AllocationDays where allocationDayId=? and version=?
Hibernate: delete from capacity.dbo.allocation where allocationId=? and version=?

michael · **Posted:** Fri Jan 09, 2004 11:21 am

Use inverse=true. See http://www.hibernate.org/hib_docs/reference/html/parent-child.html

koma · **Posted:** Fri Jan 09, 2004 11:27 am

From the manual :

" Please note that Hibernate does not support bidirectional one-to-many associations with an indexed collection (list, map or array) as the "many" end.

You may specify a bidirectional many-to-many association simply by mapping two many-to-many associations to the same database table and declaring one end as inverse. "

Since I'm mapping a List (with an index !) I cannot use "inverse=true", not ?

michael · **Posted:** Fri Jan 09, 2004 11:42 am

You can, this is however not supported very clean - you need to map the index column in the child element, and keep it manually up to date to the position of the element in the List.

koma · **Posted:** Fri Jan 09, 2004 11:56 am

That's a very confusing explanation to me.... the doc states clearly it is not supported for a List.

I'm just looking for some clarifications :

1- Do I have to explicitly map the parent in my child objects ?
In my experience=yes because otherwise no parent Id is saved along with the child.

2 Do I have to explicitly map the index in my child objects ?
In my experience=yes because otherwise no index is saved along with the child.

3 Can I have NOT-NULL constraints for the parent Id or index in the child table ?

In my experience=no, because the child delete strategy is to first de-couple the children from the parent by setting the index and parentId to NULL and afterwards calling a delete statement.

What is most strange to me is that I have the index and parent mapped in my children but updating the index and/or parent in my code would break consistency. I most consider them READ ONLY at all ... Hibernate takes ownership.

Do i get this right ?

thx.

michael · **Posted:** Fri Jan 09, 2004 12:26 pm

Here is an example, of a Page class, where each Page contains a List of sub-Pages:

Code:

public class Page implements Serializable {
    private int position; // The index Property
    private List subPages; // The child objects
    private Page parent; // The parent Page

    public List getSubPages() {
        return subPages;
    }

    // let only hibernate set this explicitly
    private void setSubPages(List subPages) {
        this.subPages = subPages;
    }

    // add a new child page
    public void addPage(Page newPage) {
        subPages.add(newPage);
        newPage.setParent(this);
    }    

    public void setPosition(int ind) {
        position = ind;
    }

    public int getPosition() {
        if (getParent() != null
            && Hibernate.isInitialized(getParent().getSubPages())) {
            position = parent.getSubPages().indexOf(this);
        }
        return position;
    }

    public Page getParent() {
        return parent;
    }

    public void setParent(Page parent) {
        this.parent = parent;
    }
}

And the according mapping:

Code:

        <list
            name="subPages"
            lazy="true"
            inverse="true"
            cascade="all"
        >
              <key
                  column="PARENT_PAGE_ID"
              />
              <index
                  column="PAGE_POSITION"
              />
              <one-to-many
                  class="data.Page"
              />
        </list>

        <property
            name="position"
            type="int"
            update="true"
            insert="true"
            column="PAGE_POSITION"
            not-null="true"
        />

        <many-to-one
            name="parent"
            class="net.oktiron.tint.common.data.Page"
            cascade="none"
            outer-join="true"
            update="true"
            insert="true"
            column="PARENT_PAGE_ID"
        />

Most likely not a perfect example (the position part of the class is a bit shady) but it works for me.

koma · **Posted:** Fri Jan 09, 2004 12:41 pm

thx gloeglm for your replies.

In your example position is merely a helper var keeping track of the current position you're at, rather than the mapped index column because you posted the parent class while the index property is part of the child object.

My problems are illustrated by my init code for the parent-child. Here you see that :
- I have to set child.setParent (in my case allocationDay.setAllocation)
- I have to set child.setIndex (in my case allocationDay.setWeekday)
- Add the children to the parent in the right order (conform the index i set)

This is so much overhead and easy to make a mistake :

AllocationDay monday = new AllocationDay();
AllocationDay tuesday = new AllocationDay();
AllocationDay wednesday = new AllocationDay();
AllocationDay thursday = new AllocationDay();
AllocationDay friday = new AllocationDay();
AllocationDay saturday = new AllocationDay();
AllocationDay sunday = new AllocationDay();

monday.setAllocation(this); // set parent
monday.setWeekday(Weekday.MONDAY); // set order index
tuesday.setAllocation(this); // and so on and on ...
tuesday.setWeekday(Weekday.TUESDAY);
wednesday.setAllocation(this);
wednesday.setWeekday(Weekday.WEDNESDAY);
thursday.setAllocation(this);
thursday.setWeekday(Weekday.THURSDAY);
friday.setAllocation(this);
friday.setWeekday(Weekday.FRIDAY);
saturday.setAllocation(this);
saturday.setWeekday(Weekday.SATURDAY);
sunday.setAllocation(this);
sunday.setWeekday(Weekday.SUNDAY);

// and then make sure you add the children in the right order !!!

allocationDays.add(Weekday.MONDAY.toInt(), monday);
allocationDays.add(Weekday.TUESDAY.toInt(), tuesday);
allocationDays.add(Weekday.WEDNESDAY.toInt(), wednesday);
allocationDays.add(Weekday.THURSDAY.toInt(), thursday);
allocationDays.add(Weekday.FRIDAY.toInt(), friday);
allocationDays.add(Weekday.SATURDAY.toInt(), saturday);
allocationDays.add(Weekday.SUNDAY.toInt(), sunday);

But :
- not setting the child.setParent -> no parentId is saved along
- not setting the index -> the index column is not populated

While I really expect Hibernate to :
- know about the parent because of the mapping
- take care of the index known from the ordering of the List

And finally :
- parentId and index must allow for NULL in the child table because of the delete strategy ...

any feedback on this ?

michael · **Posted:** Fri Jan 09, 2004 1:20 pm

Parent and Child classes are identical in my example, this is a self-referential relationship

greg_barton · **Posted:** Fri Jan 09, 2004 2:44 pm

Quote:

While I really expect Hibernate to :
- know about the parent because of the mapping
- take care of the index known from the ordering of the List

Both of these are relatively simple to do on your own, and might not necessarily be desired behavior for everyone. I'm sure that's why it was left out.

mitchellch · **Joined:** Wed Sep 03, 2003 9:56 pm **Posts:** 58

Works perfectly.

Thanks so much.
-Mitch