Creating index for mysql with Jboss configuration 1

[Themuppeteer]

Hello guys,

I want to set a foreign key constraint in Jboss (3.2.3).
I'm using a mySQL DB (version MySQL 4.0.17). In order to do this, I've to add an index
to my foreign key field. The problem is I have no idea where to set this in the deployment
descriptors of my ejb. One solution is to create the db tables by myself, but there are
to many tables to do this.

Is there somebody that knows how to create an index on a field by specifying it in the
deployment descriptors of the ejb?

(If anyone can help me, of course I'd be happy to give a star.)

Any help is welcome, cause I'm already stuck on this for a week...

Greetz,

NOSPAM_themuppeteer@hotmail.com (for mails, remove the NOSPAM_)

http://www.themuppeteer.tk

"Those who say they understand chess, understand nothing"

-- Robert HUBNER

 

[sedj]

Do you mean an index on the primary key ?

 

[Themuppeteer]

No, I want to create an index on a foreign key field. I think the primary key field is already indexed.

Greetz,

NOSPAM_themuppeteer@hotmail.com (for mails, remove the NOSPAM_)

http://www.themuppeteer.tk

"Those who say they understand chess, understand nothing"

-- Robert HUBNER

 

[sedj]

in the jbosscmp-jdbc.xml file, a foreign key mapping would look something like this (sorry about the formatting) :

<jbosscmp-jdbc> <relationships> <ejb-relation> <ejb-relation-name>Organization-Gangster</ejb-relation-name> <foreign-key-mapping/> <ejb-relationship-role> <ejb-relationship-role-name>org-has-gangsters </ejb-relationship-role-name> <key-fields> <key-field> <field-name>name</field-name> <column-name>organization</column-name> </key-field> </key-fields> </ejb-relationship-role> <ejb-relationship-role> <ejb-relationship-role-name>gangster-belongs-to-org </ejb-relationship-role-name> <key-fields/> </ejb-relationship-role> </ejb-relation>

 

[sedj]

Here is a better example :

<?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?>

<!DOCTYPE jbosscmp-jdbc PUBLIC
   &quot;-//JBoss//DTD JBOSSCMP-JDBC 3.0//EN&quot;
   &quot;[URL unfurl="true"]http://www.jboss.org/j2ee/dtd/jbosscmp-jdbc_3_0.dtd&quot;>[/URL]

<jbosscmp-jdbc>

   <defaults>
      <datasource>java:/DefaultDS</datasource>
      <datasource-mapping>Hypersonic SQL</datasource-mapping>
      <create-table>true</create-table>
      <remove-table>true</remove-table>
   </defaults>

   <enterprise-beans>
      <entity>
         <ejb-name>CustomerEJB</ejb-name>
         <table-name>Customer</table-name>
         <cmp-field>
            <field-name>id</field-name>
            <column-name>ID</column-name>
         </cmp-field>
         <cmp-field>
            <field-name>lastName</field-name>
            <column-name>LAST_NAME</column-name>
         </cmp-field>
         <cmp-field>
            <field-name>firstName</field-name>
            <column-name>FIRST_NAME</column-name>
         </cmp-field>
         <cmp-field>
            <field-name>hasGoodCredit</field-name>
            <column-name>HAS_GOOD_CREDIT</column-name>
         </cmp-field>
      </entity>
      <entity>
         <ejb-name>AddressEJB</ejb-name>
         <table-name>Address</table-name>
         <cmp-field>
            <field-name>id</field-name>
            <column-name>ID</column-name>
         </cmp-field>
         <cmp-field>
            <field-name>street</field-name>
            <column-name>STREET</column-name>
         </cmp-field>
         <cmp-field>
            <field-name>city</field-name>
            <column-name>CITY</column-name>
         </cmp-field>
         <cmp-field>
            <field-name>state</field-name>
            <column-name>STATE</column-name>
         </cmp-field>
         <cmp-field>
            <field-name>zip</field-name>
            <column-name>ZIP</column-name>
         </cmp-field>
      </entity>
      <entity>
         <ejb-name>CreditCardEJB</ejb-name>
         <table-name>CreditCard</table-name>
         <cmp-field>
            <field-name>id</field-name>
            <column-name>ID</column-name>
         </cmp-field>
         <cmp-field>
            <field-name>expirationDate</field-name>
            <column-name>EXP_DATE</column-name>
         </cmp-field>
         <cmp-field>
            <field-name>number</field-name>
            <column-name>NUMBER</column-name>
         </cmp-field>
         <cmp-field>
            <field-name>nameOnCard</field-name>
            <column-name>NAME</column-name>
         </cmp-field>
         <cmp-field>
            <field-name>creditOrganization</field-name>
            <column-name>ORGANIZATION</column-name>
         </cmp-field>
      </entity>
      <entity>
         <ejb-name>PhoneEJB</ejb-name>
         <table-name>Phone</table-name>
         <cmp-field>
            <field-name>id</field-name>
            <column-name>ID</column-name>
         </cmp-field>
         <cmp-field>
            <field-name>number</field-name>
            <column-name>NUMBER</column-name>
         </cmp-field>
         <cmp-field>
            <field-name>type</field-name>
            <column-name>TYPE</column-name>
         </cmp-field>
      </entity>
      <entity>
         <ejb-name>CabinEJB</ejb-name>
         <table-name>Cabin</table-name>
         <cmp-field>
            <field-name>id</field-name>
            <column-name>ID</column-name>
         </cmp-field>
         <cmp-field>
            <field-name>bedCount</field-name>
            <column-name>BED_COUNT</column-name>
         </cmp-field>
         <cmp-field>
            <field-name>deckLevel</field-name>
            <column-name>DECK_LEVEL</column-name>
         </cmp-field>
      </entity>
      <entity>
         <ejb-name>CruiseEJB</ejb-name>
         <table-name>Cruise</table-name>
         <cmp-field>
            <field-name>id</field-name>
            <column-name>ID</column-name>
         </cmp-field>
         <cmp-field>
            <field-name>name</field-name>
            <column-name>NAME</column-name>
         </cmp-field>
      </entity>
      <entity>
         <ejb-name>ShipEJB</ejb-name>
         <table-name>Ship</table-name>
         <cmp-field>
            <field-name>id</field-name>
            <column-name>ID</column-name>
         </cmp-field>
         <cmp-field>
            <field-name>name</field-name>
            <column-name>NAME</column-name>
         </cmp-field>
         <cmp-field>
            <field-name>tonnage</field-name>
            <column-name>TONNAGE</column-name>
         </cmp-field>
      </entity>
      <entity>
         <ejb-name>ReservationEJB</ejb-name>
         <table-name>Reservation</table-name>
         <cmp-field>
            <field-name>id</field-name>
            <column-name>ID</column-name>
         </cmp-field>
         <cmp-field>
            <field-name>date</field-name>
            <column-name>DATE</column-name>
         </cmp-field>
         <cmp-field>
            <field-name>amountPaid</field-name>
            <column-name>AMOUNT_PAID</column-name>
         </cmp-field>
      </entity>
   </enterprise-beans>

   <!-- as a direct SELECT will be issued by the TravelAgent bean, the name of the 
         relationship table and columns has to be define in advance -->
   <relationships>
      <ejb-relation>
         <ejb-relation-name>Cabin-Reservation</ejb-relation-name>
         <relation-table-mapping>
            <table-name>RESERVATION_CABIN_LINK</table-name>
            <create-table>true</create-table>
            <remove-table>true</remove-table>
         </relation-table-mapping>
         <ejb-relationship-role>
            <ejb-relationship-role-name>Cabin-has-many-Reservations</ejb-relationship-role-name>
            <key-fields>
               <key-field>
                  <field-name>id</field-name>
                  <column-name>CABIN_ID</column-name>
               </key-field>
            </key-fields>
         </ejb-relationship-role>
         <ejb-relationship-role>
            <ejb-relationship-role-name>Reservation-has-many-Cabins</ejb-relationship-role-name>
            <key-fields>
               <key-field>
                  <field-name>id</field-name>
                  <column-name>RESERVATION_ID</column-name>
               </key-field>
            </key-fields>
         </ejb-relationship-role>
      </ejb-relation>

      <ejb-relation>
         <ejb-relation-name>Cabin-Ship</ejb-relation-name>
         <foreign-key-mapping/>      
         <ejb-relationship-role>
            <ejb-relationship-role-name>Ship-has-many-Cabins</ejb-relationship-role-name>
            <key-fields>
               <key-field>
                  <field-name>id</field-name>
                  <column-name>SHIP_ID</column-name>
               </key-field>
            </key-fields>
         </ejb-relationship-role>
         <ejb-relationship-role>
            <ejb-relationship-role-name>Cabin-has-a-Ship</ejb-relationship-role-name>
            <key-fields/>
         </ejb-relationship-role>
      </ejb-relation>
      
      <ejb-relation>
         <ejb-relation-name>Cruise-Reservation</ejb-relation-name>
         <foreign-key-mapping/>      
         <ejb-relationship-role>
            <ejb-relationship-role-name>Cruise-has-many-Reservations</ejb-relationship-role-name>
            <key-fields>
               <key-field>
                  <field-name>id</field-name>
                  <column-name>CRUISE_ID</column-name>
               </key-field>
            </key-fields>
         </ejb-relationship-role>
         <ejb-relationship-role>
            <ejb-relationship-role-name>Reservation-has-a-Cruise</ejb-relationship-role-name>
            <key-fields/>
         </ejb-relationship-role>
      </ejb-relation>

   </relationships>
   
</jbosscmp-jdbc>