31.13. Datasource Customization

JBoss includes predefined type-mappings for many databases including: Cloudscape, DB2, DB2/400, Hypersonic SQL, InformixDB, InterBase, MS SQLSERVER, MS SQLSERVER2000, mySQL, Oracle7, Oracle8, Oracle9i, PointBase, PostgreSQL, PostgreSQL 7.2, SapDB, SOLID, and Sybase. If you do not like the supplied mapping, or a mapping is not supplied for your database, you will have to define a new mapping. If you find an error in one of the supplied mappings, or if you create a new mapping for a new database, please consider posting a patch at the JBoss project page on SourceForge.

31.13.1. Type Mapping

Customization of a database is done through the type-mapping section of the jbosscmp-jdbc.xml descriptor. The content model for the type-mapping element is given in Figure 31.17, “The jbosscmp-jdbc type-mapping element content model.”.
The jbosscmp-jdbc type-mapping element content model.

Figure 31.17. The jbosscmp-jdbc type-mapping element content model.

The elements are:
  • name: This required element provides the name identifying the database customization. It is used to refer to the mapping by the datasource-mapping elements found in defaults and entity.
  • row-locking-template: This required element gives the PreparedStatement template used to create a row lock on the selected rows. The template must support three arguments:
    1. the select clause
    2. the from clause. The order of the tables is currently not guaranteed
    3. the where clause
    If row locking is not supported in select statement this element should be empty. The most common form of row locking is select for update as in: SELECT ?1 FROM ?2 WHERE ?3 FOR UPDATE.
  • pk-constraint-template: This required element gives the PreparedStatement template used to create a primary key constraint in the create table statement. The template must support two arguments
    1. Primary key constraint name; which is always pk_{table-name}
    2. Comma separated list of primary key column names
    If a primary key constraint clause is not supported in a create table statement this element should be empty. The most common form of a primary key constraint is: CONSTRAINT ?1 PRIMARY KEY (?2)
  • fk-constraint-template: This is the template used to create a foreign key constraint in separate statement. The template must support five arguments:
    1. Table name
    2. Foreign key constraint name; which is always fk_{table-name}_{cmr-field-name}
    3. Comma separated list of foreign key column names
    4. References table name
    5. Comma separated list of the referenced primary key column names
    If the datasource does not support foreign key constraints this element should be empty. The most common form of a foreign key constraint is: ALTER TABLE ?1 ADD CONSTRAINT ?2 FOREIGN KEY (?3) REFERENCES ?4 (?5).
  • auto-increment-template: This declares the SQL template for specifying auto increment columns.
  • add-column-template: When alter-table is true, this SQL template specifies the syntax for adding a column to an existing table. The default value is ALTER TABLE ?1 ADD ?2 ?3. The parameters are:
    1. the table name
    2. the column name
    3. the column type
  • alter-column-template: When alter-table is true, this SQL template specifies the syntax for dropping a column to from an existing table. The default value is ALTER TABLE ?1 ALTER ?2 TYPE ?3. The parameters are:
    1. the table name
    2. the column name
    3. the column type
  • drop-column-template: When alter-table is true, this SQL template specifies the syntax for dropping a column to from an existing table. The default value is ALTER TABLE ?1 DROP ?2. The parameters are:
    1. the table name
    2. the column name
  • alias-header-prefix: This required element gives the prefix used in creating the alias header. An alias header is prepended to a generated table alias by the EJB-QL compiler to prevent name collisions. The alias header is constructed as follows: alias-header-prefix + int_counter + alias-header-suffix. An example alias header would be t0_ for an alias-header-prefix of "t" and an alias-header-suffix of "_".
  • alias-header-suffix: This required element gives the suffix portion of the generated alias header.
  • alias-max-length: This required element gives the maximum allowed length for the generated alias header.
  • subquery-supported: This required element specifies if this type-mapping subqueries as either true or false. Some EJB-QL operators are mapped to exists subqueries. If subquery-supported is false, the EJB-QL compiler will use a left join and is null.
  • true-mapping: This required element defines true identity in EJB-QL queries. Examples include TRUE, 1, and (1=1).
  • false-mapping: This required element defines false identity in EJB-QL queries. Examples include FALSE, 0, and (1=0).
  • function-mapping: This optional element specifies one or more the mappings from an EJB-QL function to an SQL implementation. See Section 31.13.2, “Function Mapping” for the details.
  • mapping: This required element specifies the mappings from a Java type to the corresponding JDBC and SQL type. See Section 31.13.3, “Mapping” for the details.