JBossCMP Mappings to Physical Databases

Not all databases are created equal. Although the JDBC API provides mechanisms that hide the different physical characteristics of different database systems, there are some issues that it does not address which require JBossCMP to be configured for each platform. Default mappings are contained in standardjbossmp-jdbc.xml for the following databases:

These default configurations can be overridden in jbosscmp-jdbc.xml and new ones can be defined; if you produce a definition for a database not listed here, please submit the mapping to the JBoss Project.

Using a Specific Type Mapping

Which type mapping JBossCMP should use is specified in jbosscmp-jdbc.xml in conjunction with which DataSource to use. The following snippet from standardjbosscmp-jdbc.xml illustrates the default configuration for the embedded hsqldb instance:

  
    java:/DefaultDS
    Hypersonic SQL
  

Rather than re-map the DefaultDS, the Deployer would typically configure the application to use a specific DataSource using the datasource element. The mapping for that datasource must also be specified using one of the names listed above, or the name for a custom mapping declared in the jbosscmp-jdbc.xml file itself.

It is possible to specify the datasource and datasource-mapping for each CMP Entity individually.

  
    
      Order
      java:/DefaultDS
      Hypersonic SQL
    
  

JBossCMP, however, cannot manage relationships or execute queries that involve Entities from different datasources.

Defining a Custom Type Mapping

A type mapping comprises fours parts:

1. a header
2. mapping of EJB-QL functions
3. mappings of Java classes to database types
4. datasource specific entity commands

The header has the following elements

Mapping Name

Element:

The name used to identify the mapping in a datasource-mapping reference.

Row Locking Template

Element:

The row locking template is used to generate the SQL to be executed when row locking has been enabled. The template has three parameters:

?1	the SELECT columns
?2	the FROM clause
?3	the WHERE clause

This template should generate SQL that takes an exclusive lock on a row whenever it is read. Many databases support an additional FOR UPDATE clause, for which the tempate would be:

  SELECT ?1 FROM ?2 WHERE ?3 FOR UPDATE

Primary Key Template

Element:

When JBossCMP is creating a table, this template is used to construct the SQL used to add a PRIMARY KEY constraint on the key columns. The template has two parameters

?1	the name of the constraint, of the form "pk_{table-name}"
?2	the list of columns that comprise the primary key

The template:

  CONSTRAINT ?1 PRIMARY KEY (?2)

would result in the following SQL when creating the table for Order entity:

  CREATE TABLE ORDER_DATA (
      ORDER_ID INTEGER,
      CONSTRAINT pk_ORDER_DATA PRIMARY KEY (ORDER_ID)
  )

Foreign Key Template

Element:

When JBossCMP creates a relationship table for a ejb-relation, this template is used to construct the SQL statement used to add constraints back to the tables for the entities. The template has 5 parameters:

?1	the name of the table which will be altered to create the constraint
?2	the name of the constraint, of the form "fk_{table-name}_{cmr-field-name}"
?3	the list of columns that comprise the foreign key
?4	the table whose key is referenced
?5	the list of key fields in the referenced table

The template would typically look like:

  ALTER TABLE ?1 ADD CONSTRAINT ?2 FOREIGN KEY (?3) REFERENCES ?4 (?5)

As an example, if JBoss created the relation table for the Product-ProductCategory relationships, it would issue the following SQL statements

  ALTER TABLE PRODUCT_TO_CATEGORY ADD CONSTRAINT fk_PRODUCT_PRODUCT_ID
    FOREIGN KEY (RT_PRODUCT_ID) REFERENCES PRODUCT (PRODUCT_ID)
  ALTER TABLE PRODUCT_TO_CATEGORY ADD CONSTRAINT fk_CATEGORY_CATEGORY_ID
    FOREIGN KEY (RT_CATEGORY_ID) REFERENCES CATEGORY (CATEGORY_ID)

JBossCMP does not create constraints for foreign key relationships.

Auto Increment Template

Element:

When JBossCMP is creating a table, this template is used to generate the column definition for a column whose value is automatically generated by the datebase during an insert; different databases have different names for these columns such as identity or serial. The template has one parameter:

?1	the column definitions, for example: "ORDER_ID INTEGER"

For example, the template for MySQL is:

  ?1 auto_increment

So if the Order EJB was defined with an auto-increment primary key, the SQL used to create the table would be:

  CREATE TABLE ORDER_DATA (
      ORDER_ID INTEGER auto_increment,
  )

This template should not be used when the database uses a different datatype for these columns. Instead, the datatype for the column should be explicitly set in the entity definition. For example, Informix uses a special SERIAL datatype

    
      Order
      
        orderId
        INTEGER
        SERIAL
      
    

Alias Generation

Elements:

When generating the SQL for a queyr, JBossCMP uses aliases to identify the tables used to avoid potential name conflicts with other objects in the database. The alias is derived from an internal counter and the path_expression used in the query as follows:

  prefix + counter + suffix + path_with_dots_replaced

Because some databases restrict the length of identifiers, the generated name is truncated at alias-max-length characters.

If the mapping defined:

  t
  _
  18

Then the actual alias names used would be:

Path Used	Logical Alias	Physical Alias
o.orderId	o	t0_o
l.itemId	l	t1_l
o.customer.firstName	o.customer	t2_o_customer
o.customer.homeAddress.city	o.customer.homeAddress	t3_o_customer_home

Subquery Support

Element:

Some databases, notably MySQL, do not support SQL sub-queries. For such platforms, setting this element to true causes JBossCMP to generate SQL for queries that uses left joins rather tan subqueries for operators such as MEMBER OF and IS EMPTY. This is not a true conversion and may cause duplicate rows to be returned which would not be present if a true sub-query was used; to avoid this, JBossCMP will automatically add a DISTINCT clause to the query.

For example, if we consider the EJB-QL query

  SELECT OBJECT(o) FROM Order o WHERE o.lineItems IS EMPTY

With sub-queries enabled, then JBossCMP would use the following SQL:

  SELECT t0_o.ORDER_ID
  FROM ORDER_DATA t0_o
  WHERE NOT EXISTS(
      SELECT t1_o_lineItem.ITEM_ID
      FROM LINEITEM t1_o_lineItem
      WHERE t1_o_lineitem.FK_ORDER_ID = t0_o.ORDER_ID
      )

If sub-queries are disabled, then the SQL would become:

  SELECT DISTINCT t0_o.ORDER_ID
  FROM ORDER_DATA t0_o 
  LEFT JOIN LINEITEM t1_o_lineItems ON (t0_o.ORDER_ID = t1_o_lineItems.FK_ORDER_ID)
  WHERE t1_o_lineItems.ITEM_ID IS NULL

Boolean mappings

Element:

SQL-92 did not define a Boolean data type and different database vendors have implemented this differently. Some databases provide their own boolean type, others use numeric or character columns with defined data values (such as 0,1 or 'true','false').

These two elements define the SQL that will be generated when the EJB-QL boolean literals TRUE and FALSE are used in a query and should corespond to the values used by the JDBC driver for Boolean.TRUE and Boolean.FALSE.

Function Mappings

By default, JBossCMP will translation EJB-QL and JBoss-QL functions like SUBSTRING and LENGTH directly into their JDBC equivalent:

Not all database drivers support all these function escapes. Instead, JBossCMP can be configured to issue database specific SQL. For example, the PostgreSQL definition defines LOCATE as:

  
    locate
    
      (CASE position(?1 in substring(?2 from ?3)) 
       WHEN 0 THEN 0 
       ELSE position(?1 in substring(?2 from ?3)) + ?3 - 1 
       END)
    
  

Datatype Mappings

Datatype mappings are used to specify which JDBC type (as defined in java.sql.Types) is used for a specific Java class. This can be used to override the default mapping performed by the driver. Each mapping takes the form:

    
      
      java.lang.Integer
 
      
      INTEGER
 
      
      INTEGER
    

The sql-type is only used when JBossCMP creates a table.

The mapping between java-type and jdbc-type defined in standardjbosscmp-jdbc.xml is usually adequate. However, if JBoss is being used to create database tables, then it is common to override the sql-type for each cmp-field which uses a variable length column. For example:

    
      Customer
 
      
      
        firstName
        VARCHAR
        VARCHAR(20)
      
    

Both jdbc-type and sql-type must be provided; one on its own is not valid.