1 to N mapping problems

[Stephen Ting <st...@shinyang.com.my>]

The relationship of the classes are

LogReceiving ----> 1:N LogReceivingItem ---> Log

The LogReceivingItem object actually reference to Log object. If I don't
reference it to Log object. It's run perfectly. Whereas if I include the
<reference-descriptor> to Log object it will failed because of multiple
insert at LOG table with the same primary key. From the SQL generated
trace I found that OJB generate the following SQL command.

1. INSERT INTO LOG_RECEIVING (with attributes of LogReceiving)
2. INSERT INTO LOG (with attributes of LOG)
3. UPDATE LOG_RECEIVING  (with attributes of LogReceiving)
4. UPDATE LOG (with attributes of LogReceiving)
5. INSERT LOG (with attributes of LogReceivingItem)

The java code I used to persist the LogReceiving object are as follow

Implementation odmg = OJB.getInstance();
Database db = odmg.newDatabase();
try{	
	LogReceiving document = createDoc();	
	db.open("site1", Database.OPEN_READ_WRITE);
	Transaction tx = odmg.newTransaction();
	tx.begin();
	tx.lock(document, Database.OPEN_READ_WRITE);
	tx.commit();
			
}catch(ODMGException e){
	throw new BDException(e.getMessage());			
}

<class-descriptor
	class="model.Log" table="LOG" >
	<field-descriptor 
		name="logId"
		column="LOG_ID"
		jdbc-type="INTEGER" 
		primarykey="true" 
		autoincrement="true" 
	/>
</class-descriptor>

<class-descriptor
	class="model.LogReceiving"
	table="LOG_RECEIVING">
	<field-descriptor 
		name="logReceivingId"
		column="LOG_RECEIVING_ID"
		jdbc-type="INTEGER"
		primarykey="true" 
		autoincrement="true" 
	/>

	<collection-descriptor
		name="lineItem"
		element-class-ref="model.LogReceivingItem"
		auto-retrieve="true"
		auto-update="false"
		auto-delete="true">
		<inverse-foreignkey field-ref="logReceivingId"/>
	</collection-descriptor>
</class-descriptor>

<class-descriptor
	class="model.LogReceivingItem" table="LOG">
	<field-descriptor 
		name="logReceivingId"
		column="LOG_RECEIVING_ID"
		jdbc-type="INTEGER" 
	/>
	<field-descriptor 
		name="logId"
		column="LOG_ID"
		jdbc-type="INTEGER"
	/> 
	<reference-descriptor
		name="logReceiving"
		class-ref="model.LogReceiving"
		auto-retrieve="true"
		auto-update="true"
		auto-delete="true">
	<foreignkey field-ref="logReceivingId"/>
	</reference-descriptor> 
	<reference-descriptor
		name="log"
		class-ref="model.Log"
		auto-retrieve="true"
		auto-update="true"
		auto-delete="true">
	<foreignkey field-ref="logId"/>
	</reference-descriptor> 
</class-descriptor>

Any helps are very much appreciated..

Thanks

Regards,
Stephen

Re: 1 to N mapping problems

[kr...@webit.de]

Hi Stephen,

see comments below...

On Wed, May 28, 2003 at 05:13:28PM +0800, Stephen Ting wrote:
> The relationship of the classes are
> 
> LogReceiving ----> 1:N LogReceivingItem ---> Log
> 
> The LogReceivingItem object actually reference to Log object. If I don't
> reference it to Log object. It's run perfectly. Whereas if I include the
> <reference-descriptor> to Log object it will failed because of multiple
> insert at LOG table with the same primary key. From the SQL generated
> trace I found that OJB generate the following SQL command.
> 
> 1. INSERT INTO LOG_RECEIVING (with attributes of LogReceiving)
> 2. INSERT INTO LOG (with attributes of LOG)
> 3. UPDATE LOG_RECEIVING  (with attributes of LogReceiving)
> 4. UPDATE LOG (with attributes of LogReceiving)
> 5. INSERT LOG (with attributes of LogReceivingItem)
> 
> The java code I used to persist the LogReceiving object are as follow
> 
> Implementation odmg = OJB.getInstance();
> Database db = odmg.newDatabase();
> try{	
> 	LogReceiving document = createDoc();	
> 	db.open("site1", Database.OPEN_READ_WRITE);
> 	Transaction tx = odmg.newTransaction();
> 	tx.begin();
> 	tx.lock(document, Database.OPEN_READ_WRITE);
> 	tx.commit();
> 			
> }catch(ODMGException e){
> 	throw new BDException(e.getMessage());			
> }
> 
> <class-descriptor
> 	class="model.Log" table="LOG" >
> 	<field-descriptor 
> 		name="logId"
> 		column="LOG_ID"
> 		jdbc-type="INTEGER" 
> 		primarykey="true" 
> 		autoincrement="true" 
> 	/>
> </class-descriptor>
> 
> <class-descriptor
> 	class="model.LogReceiving"
> 	table="LOG_RECEIVING">
> 	<field-descriptor 
> 		name="logReceivingId"
> 		column="LOG_RECEIVING_ID"
> 		jdbc-type="INTEGER"
> 		primarykey="true" 
> 		autoincrement="true" 
> 	/>
> 
> 	<collection-descriptor
> 		name="lineItem"
> 		element-class-ref="model.LogReceivingItem"
> 		auto-retrieve="true"
> 		auto-update="false"
> 		auto-delete="true">
> 		<inverse-foreignkey field-ref="logReceivingId"/>
> 	</collection-descriptor>
> </class-descriptor>
> 
> <class-descriptor
> 	class="model.LogReceivingItem" table="LOG">
> 	<field-descriptor 
> 		name="logReceivingId"
> 		column="LOG_RECEIVING_ID"
> 		jdbc-type="INTEGER" 
> 	/>
> 	<field-descriptor 
> 		name="logId"
> 		column="LOG_ID"
> 		jdbc-type="INTEGER"
> 	/> 

I think you should declare logId as primary key here, too (just as you
did for model.Log above). 

> 	<reference-descriptor
> 		name="logReceiving"
> 		class-ref="model.LogReceiving"
> 		auto-retrieve="true"
> 		auto-update="true"
> 		auto-delete="true">
> 	<foreignkey field-ref="logReceivingId"/>
> 	</reference-descriptor> 
> 	<reference-descriptor
> 		name="log"
> 		class-ref="model.Log"
> 		auto-retrieve="true"
> 		auto-update="true"
> 		auto-delete="true">
> 	<foreignkey field-ref="logId"/>
> 	</reference-descriptor> 

Imho this reference won't work.
Imagine you create an instance of LogReceivingItem associated with an
instance of Log. 
log.logId then is equal to logReceivingItem.logId.
Now what happens when you try to insert both items into db ? Two
insert-Statements are generated, both for table LOG, and both with the
same value for LOG_ID, which I suppose to be the primary key in table
LOG.


hth,
Jens