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.”.
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:- the select clause
- the from clause. The order of the tables is currently not guaranteed
- 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- Primary key constraint name; which is always
pk_{table-name}
- 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:
- Table name
- Foreign key constraint name; which is always
fk_{table-name}_{cmr-field-name}
- Comma separated list of foreign key column names
- References table name
- 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 isALTER TABLE ?1 ADD ?2 ?3
. The parameters are:- the table name
- the column name
- 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 isALTER TABLE ?1 ALTER ?2 TYPE ?3
. The parameters are:- the table name
- the column name
- 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 isALTER TABLE ?1 DROP ?2
. The parameters are:- the table name
- 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. Ifsubquery-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.