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:
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.
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:
The header has the following elements
The name used to identify the mapping in a datasource-mapping reference.
Row Locking Template
The row locking template is used to generate the SQL to be executed when row locking has been enabled. The template has three parameters:
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
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
CONSTRAINT ?1 PRIMARY KEY (?2)
would result in the following SQL when creating the table for Order entity:
CREATE TABLE ORDER_DATA (
Foreign Key Template
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:
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
JBossCMP does not create constraints for foreign key relationships.
Auto Increment Template
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:
For example, the template for MySQL is:
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 (
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
Elements: <alias-header-prefix> <alias-header-suffix> <alias-max-length>
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
If the mapping defined:
Then the actual alias names used would be:
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:
If sub-queries are disabled, then the SQL would become:
SELECT DISTINCT t0_o.ORDER_ID
Element: <true-mapping> <false-mapping>
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.
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:
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:
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:
Both jdbc-type and sql-type must be provided; one on its own is not valid.
© 2003 Core Developers Network Ltd
"Core Developers Network", the stylized apple logo and "Core Associates" are trademarks of Core Developers Network Ltd. All other trademarks are held by their respective owners. Core Developers Network Ltd is not affiliated with any of the respective trademark owners.