1:n relationship / ejbCreate()

[mlange]

Hi,

serious problem: I definded a 1:n relationship between a table TRANSACTION_TYPES and a table TRANSACTIONS.

TRANSACTION contains a foreign key field for the transaction type (TRANS_TRAT_FK). Here is my ejb-jar.xml for this:

<abstract-schema-name>Transaction</abstract-schema-name>
<cmp-field>
<field-name>transactionId</field-name>
</cmp-field>
<cmp-field>
<field-name>transactionType</field-name>
</cmp-field>
[ ... ]
<ejb-relation>
<ejb-relation-name>Transaction-TransactionType</ejb-relation-name>
<ejb-relationship-role>
<ejb-relationship-role-name>transType-has-trans</ejb-relationship-role-name>
One
<relationship-role-source>
<ejb-name>AccountTransactionType</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>transactions</cmr-field-name>
<cmr-field-type>java.util.Set</cmr-field-type>
</cmr-field>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>trans-belongs-to-transType</ejb-relationship-role-name>
Many
<cascade-delete/>
<relationship-role-source>
<ejb-name>AccountTransaction</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>relTransactionType</cmr-field-name>
</cmr-field>
</ejb-relationship-role>
</ejb-relation>

My jbosscmp-jdbc.xml looks like this:

<ejb-relation>
<ejb-relation-name>Transaction-TransactionType</ejb-relation-name>
<foreign-key-mapping/>
<ejb-relationship-role>
<ejb-relationship-role-name>transType-has-trans</ejb-relationship-role-name>
<key-fields>
<key-field>
<field-name>transactionTypeId</field-name>
<column-name>TRANS_TRAT_FK</column-name>
<key-field>
</key-fields>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>trans-belongs-to-transType</ejb-relationship-role-name>
<key-fields/>
</ejb-relationship-role>
</ejb-relation>

This is my ejbCreate() for a TRANSACTION:

public Long ejbCreate(
Long transactionId,
Long transactionType,
Long transactionAccountNumber,
Timestamp transactionDate,
BigDecimal transactionAmount,
Long foreignAccountNumber,
Long foreignBankIdentificationCode,
String subject,
Timestamp transactionDateCreated)
throws CreateException, EJBException, SQLException
{
log.info(
"Creating Transaction "
+ transactionId
+ ", "
+ transactionAccountNumber
+ ", "
+ transactionAmount
+ ", "
+ subject);
setTransactionId(transactionId);
setTransactionType(transactionType);
setTransactionAccountNumber(transactionAccountNumber);
setTransactionDate(transactionDate);
setTransactionAmount(transactionAmount);
setForeignAccountNumber(foreignAccountNumber);
setForeignBankIdentificationCode(foreignBankIdentificationCode);
setSubject(subject);
setTransactionDateCreated(transactionDateCreated);
return null;
}

My Question: if I try to call ejbCreate() of the AccountTransactionBean the container always wants to insert TRANS_TRAT_FK twice: "javax.ejb.CreateException: Could not create entity:java.sql.SQLException..."

So, is it necessary to set the cmr-field as a <cmp-field> in the descriptors? If not, how should I define the cmr-field in ejbCreate()? If I insert a transaction, it always has to take a row from TRANSACTION_TYPES!

So, I don't catch that!

Thanks,
Marek

[h2o_polo]

1. It looks to me that your type of realtion is not 1:n but rather 1:1 unidirectional. Since one transaction is only of one type. So you would need to fix that first.

2. If you have CMRs you do not need to declare a CMP field if you want that field to be the relational link. Jboss will figure that part out, simply from the cmr-field-name element of your ejb-jar.

Play around with it and try to let JBoss create your tables. You will see how it wants things to be done on the database end. It seems to be particular about it; Not too much flexibility there (at least not yet :-))

[mlange]

Ok, that was my thought not to declare the field as <cmp-field>. The relationship is working, I can assign a transaction type to a transaction if there is a data set in the db. Great.

But how can I fill the foreign key field when I create a data set ????

[h2o_polo]

Just read your post closer and realized that my first point is not valid.

The second one is still true.

The way to provide the transaction type is to pass a TransactionTypeLocal interface to the ejbCreate and ejbPostCreate method. Use the passed reference in the ejbPostCreate to set the relation. So your ejbPostCreate method will look something like:

public Long ejbPostCreate(
Long transactionId,
Long transactionType, // this should bereplaced with TransactionTypeLocal tType should repalce it
Long transactionAccountNumber,
Timestamp transactionDate,
BigDecimal transactionAmount,
Long foreignAccountNumber,
Long foreignBankIdentificationCode,
String subject,
Timestamp transactionDateCreated) {
setRelTransactionType (tType);
}

[mlange]

Ok, I have tried it this way. Now I provide the correct interface type and call the setsetRelTransactionType() in ejbPostCreate().

What I don't understand: the container always wants to insert null in the foreign key field:

"ERROR [AccountTransaction] Could not create entity
java.sql.SQLException: Insert of NULL...."

What is strange here: the logs show correct insert values with a correct key (accountalarm/AccountTransactionTypeEntity:1).

14:18:45,702 INFO [SessionFacadeBean] createTransaction 111
14:18:45,707 DEBUG [LogInterceptor] InvokeHome: create(111,accountalarm/AccountTransactionTypeEntity:1,1111,2003-01-16 14:18:44.494,10,45678901,12096597,Money for you!,2003-01-16 14:18:44.494)
14:18:45,712 INFO [AccountTransactionBean$Proxy] Creating Transaction 111, 1111, 10, Money for you!
14:18:45,715 DEBUG [AccountTransaction] Create: pk=111
14:18:45,721 DEBUG [AccountTransaction] Executing SQL: SELECT COUNT(*) FROM KW_TRANSACTION WHERE TRANS_ID=?
14:18:45,730 DEBUG [AccountTransaction] Executing SQL: INSERT INTO KW_TRANSACTION (TRANS_ID, TRANS_ACC_ACCOUNT_NUMBER, TRANS_DATE_TRANSACTION, TRANS_AMOUNT, TRANS_FOREIGN_ACCOUNT, TRANS_FOREIGN_BANK_CODE, TRANS_SUBJECT, TRANS_DATE_CREATED, TRANS_TRAT_FK) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)

Very strange. Thanks for your help!

[mlange]

The SQL Exception is always thrown before ejbPostCreate(). So ejbPostCreate() and the relationship setter is never called. How can I handle this? Is this perhaps db-related (Oracle)?

[h2o_polo]

This is a known limitation of the older versions of JBoss, it does not like non-null foreign keys.

There was an announcment by loubyanski somewhere on the list that the limitation is fixed in some of the versions, but I never tried to create the non null fk, so I really can not tell you the exact version of JBoss that eliminates the problem.

[mlange]

Yes, I have read about that problem as well. Unfortunately it is still not fixed in 3.2.0RC1....