CMR 1:1 Unidirectional relationship : JBOSS tries to insert

[ajacob]

Hello, I'm trying to get a simple unidirectional relationship working. In the code below, TDNSBean references the appl_ref_id field of ApplicationBean. I was originally trying to reference the primary key appl_id field of ApplicationBean, but read somewhere in here that CMR doesn't work when the Foreign Key being referred to is the Primary Key of the referred table.

When I try to create an ApplicationBean, I get the following error:

12:12:00,946 ERROR [ApplicationBean] Could not create entity
java.sql.SQLException: [Solid JDBC 02.10.0043] SOLID SQL Error 13: illegal column name 'TDNSBEAN_APP
L_REF_ID'

This column doesnt exist and shouldn't exist, because the 1:1 unidirectional relationship I am trying to implement has TDNSBean referring to ApplicationBean (ApplicationBean doesn't know about TDNSBean), and also because I've explicitly overridden table mappings in jbosscmp-jdbc.xml.


Any help/pointers would be greatly appreciated. My basic questions as a result of this problem:

(1) is it true I can't reference primary key fields in a CMR relationship?
(2) in a 1:1 unidirectional relationship, I thought that the referenced field of the referenced table would be defined with a <cmp-field> tag (standard cmp definition), and that the referenced field of the referring table would be defined with a <cmr-field> tag.

I've included the table structures, ejb-jar.xml listing, and jbosscmp-jdbc.xml listing.

Here is the SQL definition for the Application table:

TABLE application (
appl_id INTEGER NOT NULL,
server_id INTEGER, -- WARNING: relax it for now
appl_name_id INTEGER NOT NULL,
version VARCHAR(64),
userid VARCHAR(64),
password VARCHAR(128),
hostname VARCHAR(32),
appl_ref_id INTEGER UNIQUE,
PRIMARY KEY (appl_id),
FOREIGN KEY (hostname, server_id) REFERENCES location(hostname, server_id),
FOREIGN KEY (server_id) REFERENCES server(server_id),
FOREIGN KEY (appl_name_id) REFERENCES ApplType(appl_name_id)
);

and the SQL definition for the TDNS table:

TABLE tdns (
tdns_id INTEGER NOT NULL,
appl_ref_id INTEGER NOT NULL,
PRIMARY KEY (tdns_id),
FOREIGN KEY (appl_ref_id) REFERENCES application(appl_ref_id)
);

The relevant sections of my ejb-jar.xml:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE ejb-jar PUBLIC
"-//Sun Microsystems, Inc.//DTD Enterprise JavaBeans 2.0//EN"
"http://java.sun.com/dtd/ejb-jar_2_0.dtd">


<ejb-jar>
<enterprise beans>

<display-name>Application Bean</display-name>
<ejb-name>ApplicationBean</ejb-name>
demo.ApplicationLocal
<local-home>demo.ApplicationLocalHome</local-home>
demo.ApplicationHome
demo.Application
<ejb-class>demo.ApplicationBean</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>java.lang.Integer</prim-key-class>
false
<cmp-version>2.x</cmp-version>

<abstract-schema-name>Application</abstract-schema-name >
<cmp-field><field-name>appl_id</field-name></cmp-field>
<cmp-field><field-name>Server_id</field-name></cmp-field>
<cmp-field><field-name>Appl_name_id</field-name></cmp-field>
<cmp-field><field-name>Userid</field-name></cmp-field>
<cmp-field><field-name>Version</field-name></cmp-field>
<cmp-field><field-name>Password</field-name></cmp-field>
<cmp-field><field-name>Hostname</field-name></cmp-field>
<cmp-field><field-name>appl_ref_id</field-name></cmp-field>
<primkey-field>appl_id</primkey-field>

Override the find all
<query-method>
<method-name>findAll</method-name>
<method-params/>
</query-method>
<ejb-ql>
SELECT DISTINCT OBJECT(a)
FROM Application a
</ejb-ql>






<display-name>TDNS Bean</display-name>
<ejb-name>TDNSBean</ejb-name>
demo.TDNSLocal
<local-home>demo.TDNSLocalHome</local-home>
demo.TDNSHome
demo.TDNS
<ejb-class>demo.TDNSBean</ejb-class>

<persistence-type>Container</persistence-type>
<prim-key-class>java.lang.Integer</prim-key-class>
false
<cmp-version>2.x</cmp-version>

<abstract-schema-name>TDNS</abstract-schema-name>
<cmp-field><field-name>TDNS_id</field-name></cmp-field>
<primkey-field>TDNS_id</primkey-field>

</enterprise beans>

<ejb-relation>
<ejb-relation-name>Application-TDNS</ejb-relation-name>
<ejb-relationship-role>
<ejb-relationship-role-name>TDNS-is-an-Application</ejb-relationship-role-name>
One
<relationship-role-source>
<ejb-name>TDNSBean</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>appl_ref_id</cmr-field-name>
</cmr-field>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>Application-can-be-TDNS</ejb-relationship-role-name>
One
<relationship-role-source>
<ejb-name>ApplicationBean</ejb-name>
</relationship-role-source>
</ejb-relationship-role>
</ejb-relation>

</ejb-jar>

and the relevant sections of my jbosscmp-jdbc.xml file:


<?xml version="1.0" encoding="UTF-8"?>


<jbosscmp-jdbc>

java:/SolidDS
<create-table>true</create-table>
<remove-table>false</remove-table>
<pk-constraint>false</pk-constraint>
<relation-mapping-style>foreign-key</relation-mapping-style>
true


<enterprise-beans>

<ejb-name>ApplicationBean</ejb-name>
<table-name>Application</table-name>
<cmp-field><field-name>appl_id</field-name><column-name>APPL_ID</column-name></cmp-field>
<cmp-field><field-name>Server_id</field-name><column-name>Server_id</column-name></cmp-field>
<cmp-field><field-name>Appl_name_id</field-name><column-name>Appl_name_id</column-name></cmp-field>
<cmp-field><field-name>Userid</field-name><column-name>Userid</column-name></cmp-field>
<cmp-field><field-name>Version</field-name><column-name>Version</column-name></cmp-field>
<cmp-field><field-name>Password</field-name><column-name>Password</column-name></cmp-field>
<cmp-field><field-name>Hostname</field-name><column-name>Hostname</column-name></cmp-field>
<cmp-field><field-name>appl_ref_id</field-name><column-name>APPL_REF_ID</column-name></cmp-field>


**Placeholder for jbosscmp-jdbc.xml declaration**
<query-method>
<method-name>findAll</method-name>
<method-params/>
</query-method>

<declared-sql>


as o, (server INNER JOIN device ON server.server_id = device.server_id) LEFT JOIN application ON server.server_id = application.server_id
</declared-sql>




<ejb-name>TDNSBean</ejb-name>

<table-name>TDNS</table-name>
<cmp-field>
<field-name>TDNS_id</field-name>
<column-name>TDNS_ID</column-name>
</cmp-field>

</enterprise beans>


<relation-mapping-style>foreign-key</relation-mapping-style>
<ejb-relation>
<ejb-relation-name>Application-TDNS</ejb-relation-name>
<foreign-key-mapping>
<ejb-relationship-role>
<ejb-relationship-role-name>TDNS-is-an-Application</ejb-relationship-role-name>
<foreign-key-fields>
<foreign-key-field>
<field-name>appl_ref_id</field-name>
<column-name>APPL_REF_ID</column-name>
</foreign-key-field>
</foreign-key-fields>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>Application-can-be-TDNS</ejb-relationship-role-name>
<foreign-key-fields/>
</ejb-relationship-role>
</foreign-key-mapping>
</ejb-relation>


</jbosscmp-jdbc>

[ajacob]

I was able to get this working, but with some serious drawbacks:

(1) removed all constraints of any type on the cmr field in the underlying database.
(2) referenced the cmr field in both beans, not just the referring bean. This is different from what the EJB2.0 spec says on page 131:
"A unidirectional relationship is implemented with a cmr-field on the entity bean instance from which
navigation can take place, and no related cmr-field on the entity bean instance that is the target of the
relationship. Unidirectional relationships are typically used when the Bean Provider wishes to restrict
the visibility of a relationship."

CMR won't work for me because I need to keep the relationships defined by the primary key and foreign key constraints in place in the database so that other non EJB apps can still use it. Are there any plans to implement CMR so that it can be used with primary key/foreign key fields?

[dsundstrom]

Yes there are plans for this type of mapping. It should be in JBoss 3.1.

[ajacob]

when is the estimated release date of 3.1?

thanks,
-Arun

[ajacob]

I'm assuming that in the case where one table references the primary key of another table via a foreign key (1:1 unidirectional), that the referencing table would list the foreign key as a cmr-field, and the primary key of the referenced table would be listed as a cmp-field element.

do you know if that is the way it is going to work?

Thanks,

-Arun

[dsundstrom]

> I'm assuming that in the case where one table
> references the primary key of another table via a
> foreign key (1:1 unidirectional), that the
> referencing table would list the foreign key as a
> cmr-field, and the primary key of the referenced
> table would be listed as a cmp-field element.

I have no idea what you mean? It will work by allowing a CMR foreign key field to be mapped to a CMP field. If you do a get on the CMP field you will get the primary key of the related object.

[ajacob]

That's exactly what I meant, sorry for the confusion, and thanks for the reply :)

-Arun