0 Replies Latest reply on Apr 15, 2004 8:40 AM by ahardy66

    unexpected SQL from entity beans

    ahardy66 Novice

      I am calling up some entity beans via a 1..n relationship method on my parent entity bean, and I notice that the SQL that JBoss CMP generates in the logging is surprisingly 1 SQL per field.

      For instance, I have this output from JDBCLoadEntityCommand:

      SELECT question_option_id, answer_id FROM answer WHERE (question_option_id=?) OR (question_option_id=?) OR (question_option_id=?)
      SELECT question_option_id, owner_id FROM question_option WHERE (question_option_id=?) OR (question_option_id=?) OR (question_option_id=?)
      SELECT question_option_id, question_option_text FROM question_option WHERE (question_option_id=?) OR (question_option_id=?) OR (question_option_id=?)
      SELECT question_option_id, question_type_id FROM question_option WHERE (question_option_id=?) OR (question_option_id=?) OR (question_option_id=?)
      SELECT question_option_id, position FROM question_option WHERE (question_option_id=?) OR (question_option_id=?) OR (question_option_id=?)
      SELECT question_option_id, total_answers FROM question_option WHERE (question_option_id=?) OR (question_option_id=?) OR (question_option_id=?)
      SELECT question_option_id, date_created FROM question_option WHERE (question_option_id=?) OR (question_option_id=?) OR (question_option_id=?)
      SELECT question_option_id, date_modified FROM question_option WHERE (question_option_id=?) OR (question_option_id=?) OR (question_option_id=?)
      


      Can I configure my entity beans or JBoss somehow to call up the question_option dataset in one SQL, for efficiency? Have I configured something wrong?

      The entity declaration in jbosscmp-jdbc.xml is basic - just ejb-name, table-name and cmp-fields. I suspect the ejb-relation declaration, because it comes out of xdoclet and I'm not sure about it:

      <ejb-relation>
       <ejb-relation-name>Question-Options</ejb-relation-name>
      
       <foreign-key-mapping/>
      
       <ejb-relationship-role>
       <ejb-relationship-role-name>Question-Has-Options</ejb-relationship-role-name>
       <key-fields>
       <key-field>
       <field-name>questionId</field-name>
       <column-name>question_id</column-name>
       </key-field>
       </key-fields>
      
       </ejb-relationship-role>
       <ejb-relationship-role>
       <ejb-relationship-role-name>Options-belong-to-Question</ejb-relationship-role-name>
       <key-fields/>
      
       </ejb-relationship-role>
       </ejb-relation>
      


      Thanks,
      Adam