In order to get meaningful feedback from the chapter tests, you will want to increase the log level of the CMP subsystem before running running the test. To enable debug logging add the following category to your log4j.xml file:

<category name="org.jboss.ejb.plugins.cmp">
    <priority value="DEBUG"/>
</category>

In addition to this, it is necessary to decrease the threshold on the CONSOLE appender to allow debug level messages to be logged to the console. The following changes also need to be applied to the log4j.xml file.

<appender name="CONSOLE" class="org.apache.log4j.ConsoleAppender">
    <errorHandler class="org.jboss.logging.util.OnlyOnceErrorHandler"/>
    <param name="Target"    value="System.out"/>
    <param name="Threshold" value="DEBUG" />

    <layout class="org.apache.log4j.PatternLayout">
        <!-- The default pattern: Date Priority [Category] Message\n -->
        <param name="ConversionPattern" value="%d{ABSOLUTE} %-5p [%c{1}] %m%n"/>
    </layout>
</appender>

To see the full workings of the CMP engine you would need to enable the custom TRACE level priority on the org.jboss.ejb.plugins.cmp category as shown here:

<category name="org.jboss.ejb.plugins.cmp">
    <priority value="TRACE" class="org.jboss.logging.XLevel"/>
</category>

The first test target illustrates a number of the customization features that will be discussed throughout this chapter. To run these tests execute the following ant target:

[examples]$ ant -Dchap=cmp2 -Dex=test run-example

22:30:09,862 DEBUG [OrganizationEJB#findByPrimaryKey] Executing SQL: SELECT t0_OrganizationEJB.name FROM ORGANIZATION t0_OrganizationEJB WHERE t0_OrganizationEJB.name=?
22:30:09,927 DEBUG [OrganizationEJB] Executing SQL: SELECT desc, the_boss FROM ORGANIZATION WHERE (name=?)
22:30:09,931 DEBUG [OrganizationEJB] load relation SQL: SELECT id FROM GANGSTER WHERE (organization=?)
22:30:09,947 DEBUG [StatelessSessionContainer] Useless invocation of remove() for stateless session bean
22:30:10,086 DEBUG [GangsterEJB#findBadDudes_ejbql] Executing SQL: SELECT t0_g.id FROM GANGSTER t0_g WHERE (t0_g.badness > ?)
22:30:10,097 DEBUG [GangsterEJB#findByPrimaryKey] Executing SQL: SELECT t0_GangsterEJB.id FROM GANGSTER t0_GangsterEJB WHERE t0_GangsterEJB.id=?
22:30:10,102 DEBUG [GangsterEJB#findByPrimaryKey] Executing SQL: SELECT t0_GangsterEJB.id FROM GANGSTER t0_GangsterEJB WHERE t0_GangsterEJB.id=?

These tests exercise various finders, selectors and object to table mapping issues. We will refer to the tests throughout the chapter.

The other main target runs a set of tests to demonstrate the optimized loading configurations presented in Section 11.7, “Optimized Loading”. Now that the logging is setup correctly, the read-ahead tests will display useful information about the queries performed. Note that you do not have to restart the JBoss server for it to recognize the changes to the log4j.xml file, but it may take a minute or so. The following shows the actual execution of the readahead client:

[examples]$ ant -Dchap=cmp2 -Dex=readahead run-example

When the readahead client is executed, all of the SQL queries executed during the test are displayed in the JBoss server console. The important items of note when analyzing the output are the number of queries executed, the columns selected, and the number of rows loaded. The following shows the read-ahead none portion of the JBoss server console output from readahead:

22:44:31,570 INFO  [ReadAheadTest] 
########################################################
### read-ahead none
###
22:44:31,582 DEBUG [GangsterEJB#findAll_none] Executing SQL: SELECT t0_g.id FROM GANGSTER t0_g ORDER BY t0_g.id ASC
22:44:31,604 DEBUG [GangsterEJB] Executing SQL: SELECT name, nick_name, badness, organization, hangout FROM GANGSTER WHERE (id=?)
22:44:31,615 DEBUG [GangsterEJB] Executing SQL: SELECT name, nick_name, badness, organization, hangout FROM GANGSTER WHERE (id=?)
22:44:31,622 DEBUG [GangsterEJB] Executing SQL: SELECT name, nick_name, badness, organization, hangout FROM GANGSTER WHERE (id=?)
22:44:31,628 DEBUG [GangsterEJB] Executing SQL: SELECT name, nick_name, badness, organization, hangout FROM GANGSTER WHERE (id=?)
22:44:31,635 DEBUG [GangsterEJB] Executing SQL: SELECT name, nick_name, badness, organization, hangout FROM GANGSTER WHERE (id=?)
22:44:31,644 DEBUG [GangsterEJB] Executing SQL: SELECT name, nick_name, badness, organization, hangout FROM GANGSTER WHERE (id=?)
22:44:31,649 DEBUG [GangsterEJB] Executing SQL: SELECT name, nick_name, badness, organization, hangout FROM GANGSTER WHERE (id=?)
22:44:31,658 DEBUG [GangsterEJB] Executing SQL: SELECT name, nick_name, badness, organization, hangout FROM GANGSTER WHERE (id=?)
22:44:31,670 INFO  [ReadAheadTest] 
###
########################################################
...

We will revisit this example and explore the output when we discuss the settings for optimized loading.

The JBoss configuration for the entity is declared with an entity element in the jbosscmp-jdbc.xml file. This file is located in the META-INF directory of the EJB JAR and contains all of the optional configuration information for configuring the CMP mapping. The entity elements for each entity bean are grouped together in the enterprise-beans element under the top level jbosscmp-jdbc element. A stubbed out entity configuration is shown below.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE jbosscmp-jdbc PUBLIC
     "-//JBoss//DTD JBOSSCMP-JDBC 3.2//EN"
     "http://www.jboss.org/j2ee/dtd/jbosscmp-jdbc_3_2.dtd">
<jbosscmp-jdbc>
    <defaults>
        <!-- application-wide CMP defaults -->
    </defaults>
    <enterprise-beans>
        <entity>
            <ejb-name>GangsterEJB</ejb-name>
            <!-- overrides to defaults section -->
            <table-name>gangster</table-name>            
            <!-- CMP Fields (see CMP-Fields) -->
            <!-- Load Groups (see Load Groups)-->
            <!-- Queries (see Queries) -->
        </entity>
    </enterprise-beans>
</jbosscmp-jdbc>

The ejb-name element is required to match the entity specification here with the one in the ejb-jar.xml file. The remainder of the elements specify either overrides the global or application-wide CMP defaults and CMP mapping details specific to the bean. The application defaults come from the defaults section of the jbosscmp-jdbc.xml file and the global defaults come from the defaults section of the standardjbosscmp-jdbc.xml file in the conf directory for the current server configuration file set. The defaults section is discussed in Section 11.12, “Defaults”. Figure 11.3, “The entity element content model ” shows the full entity content model.

Figure 11.3. The entity element content model

A detailed description of each entity element follows:

The declaration of a CMP field starts in the ejb-jar.xml file. On the gangster bean, for example, the gangsterId, name, nickName and badness would be declared in the ejb-jar.xml file as follows:

<ejb-jar>
  <enterprise-beans>
    <entity>
        <ejb-name>GangsterEJB</ejb-name>
        <cmp-field><field-name>gangsterId</field-name></cmp-field>
        <cmp-field><field-name>name</field-name></cmp-field>
        <cmp-field><field-name>nickName</field-name></cmp-field>
        <cmp-field><field-name>badness</field-name></cmp-field>
    </entity>
  </enterprise-beans>
            </ejb-jar>

Note that the J2EE deployment descriptor doesn't declare any object-relational mapping details or other configuration. It is nothing more than a simple declaration of the CMP fields.

The relational mapping configuration of a CMP field is done in the jbosscmp-jdbc.xml file. The structure is similar to the ejb-jar.xml with an entity element that has cmp-field elements under it with the additional configuration details.

The following is shows the basic column name and data type mappings for the gangster bean.

<jbosscmp-jdbc>
  <enterprise-beans>
    <entity>
      <ejb-name>GangsterEJB</ejb-name>
      <table-name>gangster</table-name>
                 
      <cmp-field>
        <field-name>gangsterId</field-name>
        <column-name>id</column-name>
      </cmp-field>
      <cmp-field>
        <field-name>name</field-name>
        <column-name>name</column-name>
        <not-null/>
      </cmp-field>
      <cmp-field>
        <field-name>nickName</field-name>
        <column-name>nick_name</column-name>
        <jdbc-type>VARCHAR</jdbc-type>
        <sql-type>VARCHAR(64)</sql-type>
      </cmp-field>
      <cmp-field>
        <field-name>badness</field-name>
        <column-name>badness</column-name>
      </cmp-field>
    </entity>
  </enterprise-beans>
            </jbosscmp-jdbc>

The full content model of the cmp-field element of the jbosscmp-jdbc.xml is shown below.

Figure 11.4. The JBoss entity element content model

A detailed description of each element follows:

JBoss allows for read-only CMP fields by setting the read-only and read-time-out elements in the cmp-field declaration. These elements work the same way as they do at the entity level. If a field is read-only, it will never be used in an INSERT or UPDATE statement. If a primary key field is read-only, the create method will throw a CreateException. If a set accessor is called for a read-only field, it throws an EJBException. Read-only fields are useful for fields that are filled in by database triggers, such as last update. A read-only CMP field declaration example follows:

<jbosscmp-jdbc>
    <enterprise-beans>
        <entity>
            <ejb-name>GangsterEJB</ejb-name>
            <cmp-field>
                <field-name>lastUpdated</field-name>
                <read-only>true</read-only>
                <read-time-out>1000</read-time-out>
            </cmp-field>
        </entity>
    </enterprise-beans>
</jbosscmp-jdbc>

The audit element of the entity section allows one to specify how access to and entity bean is audited. This is only allowed when an entity bean is accessed under a security domain so that this is a caller identity established. The content model of the audit element is given Figure 11.5, “The jbosscmp-jdbc.xml audit element content model ”.

Figure 11.5. The jbosscmp-jdbc.xml audit element content model

For each element, if a field-name is given, the corresponding audit information should be stored in the specified CMP field of the entity bean being accessed. Note that there does not have to be an corresponding CMP field declared on the entity. In case there are matching field names, you will be able to access audit fields in the application using the corresponding CMP field abstract getters and setters. Otherwise, the audit fields will be created and added to the entity internally. You will be able to access audit information in EJB-QL queries using the audit field names, but not directly through the entity accessors.

If, on the other hand, a column-name is specified, the corresponding audit information should be stored in the indicated column of the entity table. If JBoss is creating the table the jdbc-type and sql-type element can then be used to define the storage type.

The declaration of audit information with given column names is shown below.

<jbosscmp-jdbc>
    <enterprise-beans>
        <entity>
            <ejb-name>AuditChangedNamesEJB</ejb-name>
            <table-name>cmp2_audit_changednames</table-name>
            <audit>
                <created-by>
                    <column-name>createdby</column-name>
                </created-by>
                <created-time>
                    <column-name>createdtime</column-name>
                </created-time>
                <updated-by>
                    <column-name>updatedby</column-name></updated-by>
                <updated-time>
                    <column-name>updatedtime</column-name>
                </updated-time>
            </audit>
        </entity>
    </enterprise-beans>
</jbosscmp-jdbc>

A dependent value class (DVC) is a fancy term used to identity any Java class that is the type of a cmp-field other than the automatically recognized types core types such as strings and number values. By default, a DVC is serialized, and the serialized form is stored in a single database column. Although not discussed here, there are several known issues with the long-term storage of classes in serialized form.

JBoss also supports the storage of the internal data of a DVC into one or more columns. This is useful for supporting legacy JavaBeans and database structures. It is not uncommon to find a database with a highly flattened structure (e.g., a PURCHASE_ORDER table with the fields SHIP_LINE1, SHIP_LINE2, SHIP_CITY, etc. and an additional set of fields for the billing address). Other common database structures include telephone numbers with separate fields for area code, exchange, and extension, or a person's name spread across several fields. With a DVC, multiple columns can be mapped to one logical field.

JBoss requires that a DVC to be mapped must follow the JavaBeans naming specification for simple properties, and that each property to be stored in the database must have both a getter and a setter method. Furthermore, the bean must be serializable and must have a no argument constructor. A property can be any simple type, an unmapped DVC or a mapped DVC, but cannot be an EJB. A DVC mapping is specified in a dependent-value-class element within the dependent-value-classes element.

Figure 11.6. The jbosscmp-jdbc dependent-value-class element model.

Here is an example of a simple ContactInfo DVC class.

public class ContactInfo 
    implements Serializable 
{
    /** The cell phone number. */
    private PhoneNumber cell;
    
    /** The pager number. */
    private PhoneNumber pager;
    
    /** The email address */
    private String email;

    
    /**
     * Creates empty contact info.
     */
    public ContactInfo() {
    }

    public PhoneNumber getCell() {
        return cell;
    }
    
    public void setCell(PhoneNumber cell) {
        this.cell = cell;
    }
    
    public PhoneNumber getPager() {
        return pager;
    }
    
    public void setPager(PhoneNumber pager) {
      this.pager = pager;
    }
    
    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email.toLowerCase();
    }
                
    // ... equals, hashCode, toString 
}

The contact info includes a phone number, which is represented by another DVC class.

public class PhoneNumber
    implements Serializable 
{
    /** The first three digits of the phone number. */
    private short areaCode;

    /** The middle three digits of the phone number. */
	private short exchange;

    /** The last four digits of the phone number. */
	private short extension;

    // ... getters and setters 
                
    // ... equals, hashCode, toString
} 

The DVC mappings for these two classes are relatively straight forward.

<dependent-value-classes>
    <dependent-value-class>
        <description>A phone number</description>
        <class>org.jboss.cmp2.crimeportal.PhoneNumber</class>
        <property>
            <property-name>areaCode</property-name>
            <column-name>area_code</column-name>
        </property>
        <property>
            <property-name>exchange</property-name>
            <column-name>exchange</column-name>
        </property>
        <property>
            <property-name>extension</property-name>
            <column-name>extension</column-name>
        </property>
    </dependent-value-class>
                 
    <dependent-value-class>
        <description>General contact info</description>
        <class>org.jboss.cmp2.crimeportal.ContactInfo</class>
        <property>
            <property-name>cell</property-name>
            <column-name>cell</column-name>
        </property>
        <property>
            <property-name>pager</property-name>
            <column-name>pager</column-name>
        </property>
        <property>
            <property-name>email</property-name>
            <column-name>email</column-name>
            <jdbc-type>VARCHAR</jdbc-type>
            <sql-type>VARCHAR(128)</sql-type>
        </property>
    </dependent-value-class>
</dependent-value-classes>

Each DVC is declared with a dependent-value-class element. A DVC is identified by the Java class type declared in the class element. Each property to be persisted is declared with a property element. This specification is based on the cmp-field element, so it should be self-explanatory. This restriction will also be removed in a future release. The current proposal involves storing the primary key fields in the case of a local entity and the entity handle in the case of a remote entity.

The dependent-value-classes section defines the internal structure and default mapping of the classes. When JBoss encounters a field that has an unknown type, it searches the list of registered DVCs, and if a DVC is found, it persists this field into a set of columns, otherwise the field is stored in serialized form in a single column. JBoss does not support inheritance of DVCs; therefore, this search is only based on the declared type of the field. A DVC can be constructed from other DVCs, so when JBoss runs into a DVC, it flattens the DVC tree structure into a set of columns. If JBoss finds a DVC circuit during startup, it will throw an EJBException. The default column name of a property is the column name of the base cmp-field followed by an underscore and then the column name of the property. If the property is a DVC, the process is repeated. For example, a cmp-field named info that uses the ContactInfo DVC would have the following columns:

info_cell_area_code
info_cell_exchange
info_cell_extension
info_pager_area_code
info_pager_exchange
info_pager_extension
info_email

The automatically generated column names can quickly become excessively long and awkward. The default mappings of columns can be overridden in the entity element as follows:

<jbosscmp-jdbc>
    <enterprise-beans>
        <entity>
            <ejb-name>GangsterEJB</ejb-name>
            <cmp-field>
                <field-name>contactInfo</field-name>
                <property>
                    <property-name>cell.areaCode</property-name>
                    <column-name>cell_area</column-name>
                </property>
                <property>
                    <property-name>cell.exchange</property-name>
                    <column-name>cell_exch</column-name>
                </property>
                <property>
                    <property-name>cell.extension</property-name>
                    <column-name>cell_ext</column-name>
                </property>
                
                <property>
                    <property-name>pager.areaCode</property-name>
                    <column-name>page_area</column-name>
                </property>
                <property>
                    <property-name>pager.exchange</property-name>
                    <column-name>page_exch</column-name>
                </property>
                <property>
                    <property-name>pager.extension</property-name>
                    <column-name>page_ext</column-name>
                </property>
                 
                <property>
                    <property-name>email</property-name>
                    <column-name>email</column-name>
                    <jdbc-type>VARCHAR</jdbc-type>
                    <sql-type>VARCHAR(128)</sql-type>
                </property>
            </cmp-field>
        </entity>
    </enterprise-beans>
</jbosscmp-jdbc>

When overriding property info for the entity, you need to refer to the property from a flat perspective as in cell.areaCode.

CMR-Field abstract accessors have the same signatures as cmp-fields, except that single-valued relationships must return the local interface of the related entity, and multi-valued relationships can only return a java.util.Collection (or java.util.Set) object. For example, to declare a one-to-many relationship between organization and gangster, we declare the relationship from organization to gangster in the OrganizationBean class:

public abstract class OrganizationBean
    implements EntityBean 
{
    public abstract Set getMemberGangsters();
    public abstract void setMemberGangsters(Set gangsters);
} 

We also can declare the relationship from gangster to organization in the GangsterBean class:

public abstract class GangsterBean
    implements EntityBean 
{
    public abstract Organization getOrganization();
    public abstract void setOrganization(Organization org);
}

Although each bean declared a CMR field, only one of the two beans in a relationship must have a set of accessors. As with CMP fields, a CMR field is required to have both a getter and a setter method.

The declaration of relationships in the ejb-jar.xml file is complicated and error prone. Although we recommend using a tool like XDoclet to manage the deployment descriptors for CMR fields, it's still important to understand how the descriptor works. The following illustrates the declaration of the organization/gangster relationship:

<ejb-jar>
    <relationships>
        <ejb-relation>
            <ejb-relation-name>Organization-Gangster</ejb-relation-name>
            <ejb-relationship-role>
                <ejb-relationship-role-name>org-has-gangsters </ejb-relationship-role-name>
                <multiplicity>One</multiplicity>
                <relationship-role-source>
                    <ejb-name>OrganizationEJB</ejb-name>
                </relationship-role-source>
                <cmr-field>
                    <cmr-field-name>memberGangsters</cmr-field-name>
                    <cmr-field-type>java.util.Set</cmr-field-type>
                </cmr-field>
            </ejb-relationship-role>
            <ejb-relationship-role>
                <ejb-relationship-role-name>gangster-belongs-to-org </ejb-relationship-role-name>
                <multiplicity>Many</multiplicity>
                <cascade-delete/>
                <relationship-role-source>
                    <ejb-name>GangsterEJB</ejb-name>
                </relationship-role-source>
                <cmr-field>
                    <cmr-field-name>organization</cmr-field-name>
                </cmr-field>
            </ejb-relationship-role>
        </ejb-relation>
    </relationships>
</ejb-jar>

As you can see, each relationship is declared with an ejb-relation element within the top level relationships element. The relation is given a name in the ejb-relation-name element. This is important because we will need to refer to the role by name in the jbosscmp-jdbc.xml file. Each ejb-relation contains two ejb-relationship-role elements (one for each side of the relationship). The ejb-relationship-role tags are as follows:

After adding the CMR field abstract accessors and declaring the relationship, the relationship should be functional. The next section discusses the database mapping of the relationship.

Relationships can be mapped using either a foreign key or a separate relation table. One-to-one and one-to-many relationships use the foreign key mapping style by default, and many-to-many relationships use only the relation table mapping style. The mapping of a relationship is declared in the relationships section of the jbosscmp-jdbc.xml descriptor via ejb-relation elements. Relationships are identified by the ejb-relation-name from the ejb-jar.xml file. The jbosscmp-jdbc.xml ejb-relation element content model is shown in Figure 11.7, “The jbosscmp-jdbc.xml ejb-relation element content model”.

Figure 11.7. The jbosscmp-jdbc.xml ejb-relation element content model

The basic template of the relationship mapping declaration for Organization-Gangster relationship follows:

<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>
    </relationships>
</jbosscmp-jdbc>

After the ejb-relation-name of the relationship being mapped is declared, the relationship can be declared as read only using the read-only and read-time-out elements. They have the same semantics as their counterparts in the entity element.

The ejb-relation element must contain either a foreign-key-mapping element or a relation-table-mapping element, which are described in Section 11.5.3.2, “Foreign Key Mapping” and Section 11.5.3.3, “Relation table Mapping”. This element may also contain a pair of ejb-relationship-role elements as described in the following section.

11.5.3.1. Relationship Role Mapping

Each of the two ejb-relationship-role elements contains mapping information specific to an entity in the relationship. The content model of the ejb-relationship-role element is shown in Figure 11.8, “The jbosscmp-jdbc ejb-relationship-role element content model” .

Figure 11.8. The jbosscmp-jdbc ejb-relationship-role element content model

A detailed description of the main elements follows:

• ejb-relationship-role-name: This required element gives the name of the role to which this configuration applies. It must match the name of one of the roles declared for this relationship in the ejb-jar.xml file.

• fk-constraint: This optional element is a true/false value that indicates whether JBoss should add a foreign key constraint to the tables for this side of the relationship. JBoss will only add generate the constraint if both the primary table and the related table were created by JBoss during deployment.

• key-fields: This optional element specifies the mapping of the primary key fields of the current entity, whether it is mapped in the relation table or in the related object. The key-fields element must contain a key-field element for each primary key field of the current entity. The key-fields element can be empty if no foreign key mapping is needed for this side of the relation. An example of this would be the many side of a one-to-many relationship. The details of this element are described below.

• read-ahead: This optional element controls the caching of this relationship. This option is discussed in Section 11.8.3.1, “Relationships”.

• batch-cascade-delete: This indicates that a cascade delete on this relationship should be performed with a single SQL statement. This requires that the relationship be marked as batch-delete in the ejb-jar.xml.

As noted above, the key-fields element contains a key-field for each primary key field of the current entity. The key-field element uses the same syntax as the cmp-field element of the entity, except that key-field does not support the not-null option. Key fields of a relation-table are automatically not null, because they are the primary key of the table. On the other hand, foreign key fields must be nullable by default. This is because the CMP specification requires an insert into the database after the ejbCreate method and an update to it after to pick up CMR changes made in ejbPostCreate. Since the EJB specification does not allow a relationship to be modified until ejbPostCreate, a foreign key will be initially set to null. There is a similar problem with removal. You can change this insert behavior using the jboss.xml insert-after-ejb-post-create container configuration flag. The following example illustrates the creation of a new bean configuration that uses insert-after-ejb-post-create by default.

<jboss>
    <!-- ... -->
    <container-configurations>
        <container-configuration extends="Standard CMP 2.x EntityBean">
            <container-name>INSERT after ejbPostCreate Container</container-name>
            <insert-after-ejb-post-create>true</insert-after-ejb-post-create>
        </container-configuration>
    </container-configurations>                     
</jboss>

An alternate means of working around the non-null foreign key issue is to map the foreign key elements onto non-null CMP fields. In this case you simply populate the foreign key fields in ejbCreate using the associated CMP field setters.

The content model of the key-fields element is Figure 11.9, “The jbosscmp-jdbc key-fields element content model”.

Figure 11.9. The jbosscmp-jdbc key-fields element content model

A detailed description of the elements contained in the key-field element follows:

• field-name: This required element identifies the field to which this mapping applies. This name must match a primary key field of the current entity.

• column-name: Use this element to specify the column name in which this primary key field will be stored. If this is relationship uses foreign-key-mapping, this column will be added to the table for the related entity. If this relationship uses relation-table-mapping, this column is added to the relation-table. This element is not allowed for mapped dependent value class; instead use the property element.

• jdbc-type: This is the JDBC type that is used when setting parameters in a JDBC PreparedStatement or loading data from a JDBC ResultSet. The valid types are defined in java.sql.Types.

• sql-type: This is the SQL type that is used in create table statements for this field. Valid types are only limited by your database vendor.

• property: Use this element for to specify the mapping of a primary key field which is a dependent value class.

• dbindex: The presence of this optional field indicates that the server should create an index on the corresponding column in the database, and the index name will be fieldname_index.

<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>
    </relationships>
</jbosscmp-jdbc>

11.5.3.3. Relation table Mapping

Relation table mapping is less common for one-to-one and one-to-many relationships, but is the only mapping style allowed for many-to-many relationships. Relation table mapping is defined using the relation-table-mapping element, the content model of which is shown below.

Figure 11.10. The jbosscmp-jdbc relation-table-mapping element content model

The relation-table-mapping for the Gangster-Job relationship is shown in with table mapping elements highlighted in bold:

Example 11.1. The jbosscmp-jdbc.xml Relation-table Mapping

<jbosscmp-jdbc>
    <relationships>
        <ejb-relation>
            <ejb-relation-name>Gangster-Jobs</ejb-relation-name>
            <relation-table-mapping>
                <table-name>gangster_job</table-name>
            </relation-table-mapping>
            <ejb-relationship-role>
                <ejb-relationship-role-name>gangster-has-jobs</ejb-relationship-role-name>
                <key-fields>
                    <key-field>
                        <field-name>gangsterId</field-name>
                        <column-name>gangster</column-name>
                    </key-field>
                </key-fields>
            </ejb-relationship-role>   
            <ejb-relationship-role>
                <ejb-relationship-role-name>job-has-gangsters</ejb-relationship-role-name>
                <key-fields>
                    <key-field>
                        <field-name>name</field-name>
                        <column-name>job</column-name>
                    </key-field>
                </key-fields>
            </ejb-relationship-role>
        </ejb-relation>
    </relationships>
</jbosscmp-jdbc>

The relation-table-mapping element contains a subset of the options available in the entity element. A detailed description of these elements is reproduced here for convenience:

• table-name: This optional element gives the name of the table that will hold data for this relationship. The default table name is based on the entity and cmr-field names.

• datasource: This optional element gives the jndi-name used to look up the datasource. All database connections are obtained from the datasource. Having different datasources for entities is not recommended, as it vastly constrains the domain over which finders and ejbSelects can query.

• datasourcemapping: This optional element allows one to specify the name of the type-mapping to use.

• create-table: This optional element if true indicates JBoss should attempt to create a table for the relationship. When the application is deployed, JBoss checks if a table already exists before creating the table. If a table is found, it is logged, and the table is not created. This option is very useful during the early stages of development when the table structure changes often.

• post-table-create: This optional element specifies an arbitrary SQL statement that should be executed immediately after the database table is created. This command is only executed if create-table is true and the table did not previously exist.

• remove-table: This optional element if true indicates JBoss should attempt to drop the relation-table when the application is undeployed. This option is very useful during the early stages of development when the table structure changes often.

• row-locking: This optional element if true indicates JBoss should lock all rows loaded in a transaction. Most databases implement this by using the SELECT FOR UPDATE syntax when loading the entity, but the actual syntax is determined by the row-locking-template in the datasource-mapping used by this entity.

• pk-constraint: This optional element if true indicates JBoss should add a primary key constraint when creating tables.

The declaration of finders has not changed in CMP 2.0. Finders are still declared in the home interface (local or remote) of the entity. Finders defined on the local home interface do not throw a RemoteException. The following code declares the findBadDudes_ejbql finder on the GangsterHome interface. The ejbql suffix here is not required. It is simply a naming convention used here to differentiate the different types of query specifications we will be looking at.

public interface GangsterHome 
    extends EJBLocalHome 
{
    Collection findBadDudes_ejbql(int badness) throws FinderException;
}

Select methods are declared in the entity implementation class, and must be public and abstract just like CMP and CMR field abstract accessors and must throw a FinderException. The following code declares an select method:

public abstract class GangsterBean 
    implements EntityBean 
{
    public abstract Set ejbSelectBoss_ejbql(String name)
        throws FinderException;
}

Every select or finder method (except findByPrimaryKey) must have an EJB-QL query defined in the ejb-jar.xml file. The EJB-QL query is declared in a query element, which is contained in the entity element. The following are the declarations for findBadDudes_ejbql and ejbSelectBoss_ejbql queries:

<ejb-jar>
    <enterprise-beans>
        <entity>
            <ejb-name>GangsterEJB</ejb-name> 
            <!-- ... -->
            <query>
                <query-method>
                    <method-name>findBadDudes_ejbql</method-name>
                    <method-params>
                        <method-param>int</method-param>
                    </method-params>
                </query-method>
                <ejb-ql><![CDATA[
                 SELECT OBJECT(g)
                 FROM gangster g
                 WHERE g.badness > ?1
                 ]]></ejb-ql>
            </query>
            <query>
                <query-method>
                    <method-name>ejbSelectBoss_ejbql</method-name>
                    <method-params>
                        <method-param>java.lang.String</method-param>
                    </method-params>
                </query-method>
                <ejb-ql><![CDATA[
                 SELECT DISTINCT underling.organization.theBoss
                 FROM gangster underling
                 WHERE underling.name = ?1 OR underling.nickName = ?1
                 ]]></ejb-ql>
            </query>
        </entity>
    </enterprise-beans>
</ejb-jar>

EJB-QL is similar to SQL but has some surprising differences. The following are some important things to note about EJB-QL:

g.hangout.state = 'CA' Legal
'CA' = g.shippingAddress.state NOT Legal
'CA' = 'CA' NOT Legal
(r.amountPaid * .01) > 300 NOT Legal
r.amountPaid > (300 / .01) Legal

gangster.hangout.state = ?1 Legal
?1 = gangster.hangout.state NOT Legal

The EJB-QL query can be overridden in the jbosscmp-jdbc.xml file. The finder or select is still required to have an EJB-QL declaration, but the ejb-ql element can be left empty. Currently the SQL can be overridden with JBossQL, DynamicQL, DeclaredSQL or a BMP style custom ejbFind method. All EJB-QL overrides are non-standard extensions to the EJB specification, so use of these extensions will limit portability of your application. All of the EJB-QL overrides, except for BMP custom finders, are declared using a query element in the jbosscmp-jdbc.xml file. Tthe content model is shown in Figure 11.11, “The jbosscmp-jdbc query element content model”.

Figure 11.11. The jbosscmp-jdbc query element content model

JBossQL is a superset of EJB-QL that is designed to address some of the inadequacies of EJB-QL. In addition to a more flexible syntax, new functions, key words, and clauses have been added to JBossQL. At the time of this writing, JBossQL includes support for an ORDER BY, OFFSET and LIMIT clauses, parameters in the IN and LIKE operators, the COUNT, MAX, MIN, AVG, SUM, UCASE and LCASE functions. Queries can also include functions in the SELECT clause for select methods.

JBossQL is declared in the jbosscmp-jdbc.xml file with a jboss-ql element containing the JBossQL query. The following example provides an example JBossQL declaration.

<jbosscmp-jdbc>
    <enterprise-beans>
        <entity>
            <ejb-name>GangsterEJB</ejb-name>
            <query>
                <query-method>
                    <method-name>findBadDudes_jbossql</method-name>
                    <method-params>
                        <method-param>int</method-param>
                    </method-params>
                </query-method>
                <jboss-ql><![CDATA[
                 SELECT OBJECT(g)
                 FROM gangster g
                 WHERE g.badness > ?1
                 ORDER BY g.badness DESC
                 ]]></jboss-ql>
            </query>
        </entity>
    </enterprise-beans>
</jbosscmp-jdbc>

The corresponding generated SQL is straightforward.

SELECT t0_g.id
    FROM gangster t0_g
    WHERE t0_g.badness > ?
    ORDER BY t0_g.badness DESC

Another capability of JBossQL is the ability to retrieve finder results in blocks using the LIMIT and OFFSET functions. For example, to iterate through the large number of jobs performed, the following findManyJobs_jbossql finder may be defined.

<jbosscmp-jdbc>
    <enterprise-beans>
        <entity>
            <ejb-name>GangsterEJB</ejb-name>
            <query>
                <query-method>
                    <method-name>findManyJobs_jbossql</method-name>
                    <method-params>
                        <method-param>int</method-param>
                    </method-params>
                    <method-params>
                        <method-param>int</method-param>
                    </method-params>
                </query-method>
                <jboss-ql><![CDATA[
                 SELECT OBJECT(j)
                 FROM jobs j
                 OFFSET ?1 LIMIT ?2
                 ]]></jboss-ql>
            </query>
        </entity>
    </enterprise-beans>
</jbosscmp-jdbc>

DynamicQL allows the runtime generation and execution of JBossQL queries. A DynamicQL query method is an abstract method that takes a JBossQL query and the query arguments as parameters. JBoss compiles the JBossQL and executes the generated SQL. The following generates a JBossQL query that selects all the gangsters that have a hangout in any state in the states set:

public abstract class GangsterBean 
    implements EntityBean 
{
    public Set ejbHomeSelectInStates(Set states)
        throws FinderException
    {
        // generate JBossQL query
        StringBuffer jbossQl = new StringBuffer();
        jbossQl.append("SELECT OBJECT(g) ");
        jbossQl.append("FROM gangster g ");
        jbossQl.append("WHERE g.hangout.state IN (");

        for (int i = 0; i < states.size(); i++) {
            if (i > 0) {
                jbossQl.append(", ");
            }

            jbossQl.append("?").append(i+1);
        }

	    jbossQl.append(") ORDER BY g.name");

        // pack arguments into an Object[]
        Object[] args = states.toArray(new Object[states.size()]);
 
        // call dynamic-ql query
        return ejbSelectGeneric(jbossQl.toString(), args);
    }
}

The DynamicQL select method may have any valid select method name, but the method must always take a string and an object array as parameters. DynamicQL is declared in the jbosscmp-jdbc.xml file with an empty dynamic-ql element. The following is the declaration for ejbSelectGeneric.

<jbosscmp-jdbc>
    <enterprise-beans>
        <entity>
            <ejb-name>GangsterEJB</ejb-name>
            <query>
                <query-method>
                    <method-name>ejbSelectGeneric</method-name>
                    <method-params>
                        <method-param>java.lang.String</method-param>
                        <method-param>java.lang.Object[]</method-param>
                    </method-params>
                </query-method>
                <dynamic-ql/>
            </query>
        </entity>
    </enterprise-beans>
</jbosscmp-jdbc>

DeclaredSQL is based on the legacy JAWS CMP 1.1 engine finder declaration, but has been updated for CMP 2.0. Commonly this declaration is used to limit a query with a WHERE clause that cannot be represented in q EJB-QL or JBossQL. The content model for the declared-sql element is given in Figure 11.12, “The jbosscmp-jdbc declared-sql element content model.>”.

Figure 11.12. The jbosscmp-jdbc declared-sql element content model.>

The following is an example DeclaredSQL declaration.

<jbosscmp-jdbc>
    <enterprise-beans>
        <entity>
            <ejb-name>GangsterEJB</ejb-name>
            <query>
                <query-method>
                    <method-name>findBadDudes_declaredsql</method-name>
                    <method-params>
                        <method-param>int</method-param>
                    </method-params>
                </query-method>
                <declared-sql>
                    <where><![CDATA[ badness > {0} ]]></where>
                    <order><![CDATA[ badness DESC ]]></order>
                </declared-sql>
            </query>
        </entity>
    </enterprise-beans>
</jbosscmp-jdbc>

The generated SQL would be:

SELECT id
FROM gangster
WHERE badness > ?
ORDER BY badness DESC

As you can see, JBoss generates the SELECT and FROM clauses necessary to select the primary key for this entity. If desired an additional FROM clause can be specified that is appended to the end of the automatically generated FROM clause. The following is example DeclaredSQL declaration with an additional FROM clause.

<jbosscmp-jdbc>
    <enterprise-beans>
        <entity>
            <ejb-name>GangsterEJB</ejb-name>
            <query>
                <query-method>
                    <method-name>ejbSelectBoss_declaredsql</method-name>
                    <method-params>
                        <method-param>java.lang.String</method-param>
                    </method-params>
                </query-method>
                <declared-sql>
                    <select>
                        <distinct/>
                        <ejb-name>GangsterEJB</ejb-name>
                        <alias>boss</alias>
                    </select>
                    <from><![CDATA[, gangster g, organization o]]></from>
                    <where><![CDATA[
                     (LCASE(g.name) = {0} OR LCASE(g.nick_name) = {0}) AND
                     g.organization = o.name AND o.the_boss = boss.id
                     ]]></where>
                </declared-sql>
            </query>
        </entity>
    </enterprise-beans>
</jbosscmp-jdbc>

The generated SQL would be:

SELECT DISTINCT boss.id
    FROM gangster boss, gangster g, organization o
    WHERE (LCASE(g.name) = ? OR LCASE(g.nick_name) = ?) AND
          g.organization = o.name AND o.the_boss = boss.id

Notice that the FROM clause starts with a comma. This is because the container appends the declared FROM clause to the end of the generated FROM clause. It is also possible for the FROM clause to start with a SQL JOIN statement. Since this is a select method, it must have a select element to declare the entity that will be selected. Note that an alias is also declared for the query. If an alias is not declared, the table-name is used as the alias, resulting in a SELECT clause with the table_name.field_name style column declarations. Not all database vendors support the that syntax, so the declaration of an alias is preferred. The optional empty distinct element causes the SELECT clause to use the SELECT DISTINCT declaration. The DeclaredSQL declaration can also be used in select methods to select a CMP field.

Now we well see an example which overrides a select to return all of the zip codes an Organization operates in.

<jbosscmp-jdbc>
    <enterprise-beans>
        <entity>
            <ejb-name>OrganizationEJB</ejb-name>
            <query>
                <query-method>
                    <method-name>ejbSelectOperatingZipCodes_declaredsql</method-name>
                    <method-params>
                        <method-param>java.lang.String</method-param>
                    </method-params>
                </query-method>
                <declared-sql>
                    <select>
                        <distinct/>
                        <ejb-name>LocationEJB</ejb-name>
                        <field-name>zipCode</field-name>
                        <alias>hangout</alias>
                    </select>
                    <from><![CDATA[ , organization o, gangster g ]]></from>
                    <where><![CDATA[
                     LCASE(o.name) = {0} AND o.name = g.organization AND
                     g.hangout = hangout.id
                     ]]></where>
                    <order><![CDATA[ hangout.zip ]]></order>
                </declared-sql>
            </query>
        </entity>
    </enterprise-beans>
</jbosscmp-jdbc>

The corresponding SQL would be:

SELECT DISTINCT hangout.zip
    FROM location hangout, organization o, gangster g
    WHERE LCASE(o.name) = ? AND o.name = g.organization AND g.hangout = hangout.id
                ORDER BY hangout.zip

The default query compiler doesn't fully support EJB-QL 2.1