I have a 1-n relation between an entity bean called 'ProductionPart' and one called ProductionPartNotes. ProductionPartNotes has a FK of 'pp_id' and a primary key of 'pkey'. ProductionPart (the 1-side of the relation) has a String PK called 'partNumber'. Here is the realtion excerpt from the jbosscmp-jdbc.xml:
<ejb-relation>
<ejb-relation-name>ProductionPart-Notes</ejb-relation-name>
<foreign-key-mapping/>
<ejb-relationship-role>
<ejb-relationship-role-name>ProductionPart-Has-Many-Notes</ejb-relationship-role-name>
<key-fields>
<key-field>
<field-name>partNumber</field-name>
<column-name>pp_id</column-name>
</key-field>
</key-fields>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>Note-Has-One-ProductionPart</ejb-relationship-role-name>
<key-fields/>
</ejb-relationship-role>
</ejb-relation>
When I invoke 'getNotes' on the parent bean, the CMP container tries to invoke some very strange SQL:
SELECT pp_id, key FROM ProductionPartNote WHERE (pp_id=?) OR (pp_id=?) OR (pp_id=?) OR (pp_id=?) OR (pp_id=?)
which causes a SQLException (of course).
I would have expected to see this for the SQL:
SELECT pkey from ProductionPartNote WHERE (pp_id=?)
Anyone run into this before?
Many Thanks in Advance
This "strange" sql is born by read-ahead to preload the data. Check Dain's JBossCMP book (link documentation).
What the exception is? You probably have a wrong mapping "key" -> "pkey"?