Kodo JPA/JDO can take advantage of any JDBC 2.x compliant driver, making almost any major database a candidate for use. See our officially supported database list in Appendix 3, Supported Databases for more information. Typically, Kodo auto-configures its JDBC behavior and SQL dialect for your database, based on the values of your connection-related configuration properties.
If Kodo cannot detect what type of database you are using, or if
you are using an unsupported database, you will have to tell Kodo
what org.apache.openjpa.jdbc.sql.DBDictionary to use.
The DBDictionary abstracts away the differences
between databases. You can plug a dictionary into Kodo using the
kodo.jdbc.DBDictionary
configuration property. The built-in dictionaries
are listed below. If you are using an unsupported database, you may
have to write your own DBDictionary subclass,
a simple process.
access: Dictionary for Microsoft
Access. This is an alias for the
org.apache.openjpa.jdbc.sql.AccessDictionary
class.
db2: Dictionary for IBM's DB2 database.
This is an alias for the
org.apache.openjpa.jdbc.sql.DB2Dictionary
class.
derby: Dictionary for the Apache Derby
database. This is an alias for the
org.apache.openjpa.jdbc.sql.DerbyDictionary class.
empress: Dictionary for Empress database
This is an alias for the
org.apache.openjpa.jdbc.sql.EmpressDictionary class.
foxpro: Dictionary for Microsoft
Visual FoxPro. This is an alias for the
org.apache.openjpa.jdbc.sql.FoxProDictionary class.
hsql: Dictionary for the Hypersonic SQL
database. This is an alias for the
org.apache.openjpa.jdbc.sql.HSQLDictionary class.
informix: Dictionary for the Informix
database. This is an alias for the
org.apache.openjpa.jdbc.sql.InformixDictionary class.
jdatastore: Dictionary for Borland
JDataStore. This is an alias for the
org.apache.openjpa.jdbc.sql.JDataStoreDictionary class.
mysql: Dictionary for the MySQL
database. This is an alias for the
org.apache.openjpa.jdbc.sql.MySQLDictionary class.
oracle: Dictionary for Oracle.
This is an alias for the
org.apache.openjpa.jdbc.sql.OracleDictionary class.
pointbase: Dictionary for Pointbase Embedded
database. This is an alias for the
org.apache.openjpa.jdbc.sql.PointbaseDictionary class.
postgres: Dictionary for PostgreSQL.
This is an alias for the
org.apache.openjpa.jdbc.sql.PostgresDictionary class.
sqlserver: Dictionary for Microsoft's
SQLServer database. This is an alias for the
org.apache.openjpa.jdbc.sql.SQLServerDictionary class.
sybase: Dictionary for Sybase.
This is an alias for the
org.apache.openjpa.jdbc.sql.SybaseDictionary class.
The example below demonstrates how to set a dictionary and configure
its properties in your configuration file. The DBDictionary
property uses Kodo's
plugin syntax.
Example 4.7. Specifying a DBDictionary
JPA XML format:
<property name="kodo.jdbc.DBDictionary" value="hsql(SimulateLocking=true)"/>
JDO properties format:
kodo.jdbc.DBDictionary: hsql(SimulateLocking=true)
The standard dictionaries all recognize the following properties.
These properties will usually not need to be overridden, since the
dictionary implementation should use the appropriate default values
for your database. You typically won't use these properties
unless you are designing your own DBDictionary
for an unsupported database.
DriverVendor: The vendor of the particular
JDBC driver you are using. Some dictionaries must alter their
behavior depending on the driver vendor. See the
VENDOR_XXX constants defined in your
dictionary's Javadoc for available options.
CatalogSeparator:
The string the database uses to delimit between
the schema name and the table name. This is typically
".", which is the default.
CreatePrimaryKeys: If
false, then do not create database
primary keys for identifiers. Defaults to
true.
ConstraintNameMode: When
creating constraints, whether to put the constraint name
before the definition (before), just after
the constraint type name (mid), or after the
constraint definition (after).
Defaults to before.
MaxTableNameLength: The maximum number
of characters in a table name. Defaults to 128.
MaxColumnNameLength: The maximum number
of characters in a column name. Defaults to 128.
MaxConstraintNameLength: The maximum number
of characters in a constraint name. Defaults to 128.
MaxIndexNameLength: The maximum number
of characters in an index name. Defaults to 128.
MaxAutoAssignNameLength: Set
this property to the maximum length of name for
sequences used for auto-increment columns. Names
longer than this value are truncated.
Defaults to 31.
MaxIndexesPerTable: The maximum number
of indexes that can be placed on a single table. Defaults to
no limit.
SupportsForeignKeys: Whether the database
supports foreign keys. Defaults to true.
SupportsTimestampNanos: Whether the JDBC
driver supports nanoseconds with TIMESTAMP columns. Defaults
to true.
SupportsUniqueConstraints: Whether the
database supports unique constraints. Defaults to true.
SupportsDeferredConstraints: Whether the
database supports deferred constraints. Defaults to true.
SupportsRestrictDeleteAction: Whether the
database supports the RESTRICT foreign key delete action.
Defaults to true.
SupportsCascadeDeleteAction: Whether the
database supports the CASCADE foreign key delete action.
Defaults to true.
SupportsNullDeleteAction: Whether the
database supports the SET NULL foreign key delete action.
Defaults to true.
SupportsDefaultDeleteAction: Whether the
database supports the SET DEFAULT foreign key delete action.
Defaults to true.
SupportsAlterTableWithAddColumn: Whether the
database supports adding a new column in an ALTER TABLE
statement. Defaults to true.
SupportsAlterTableWithDropColumn: Whether
the database supports dropping a column in an ALTER TABLE
statement. Defaults to true.
ReservedWords: A comma-separated list of
reserved words for this database, beyond the standard SQL92
keywords.
SystemTables: A comma-separated list of
table names that should be ignored.
SystemSchemas: A comma-separated list of
schema names that should be ignored.
SchemaCase: The case to use when querying
the database metadata about schema components. Defaults to
making all names upper case. Available values are:
upper, lower, preserve.
ValidationSQL: The SQL used to
validate that a connection is still in a valid state. For
example, "SELECT SYSDATE FROM DUAL" for
Oracle.
InitializationSQL: A piece of SQL to issue
against the database whenever a connection is retrieved from
the DataSource.
JoinSyntax: The SQL join syntax
to use in select statements. See
Section 4.7, “Setting the SQL Join Syntax”.
CrossJoinClause: The clause to use for
a cross join (cartesian product).
Defaults to CROSS JOIN.
InnerJoinClause: The clause to use for
an inner join. Defaults to INNER JOIN.
OuterJoinClause: The clause to use for
an left outer join.
Defaults to LEFT OUTER JOIN.
RequiresConditionForCrossJoin: Some
databases require that there always be a conditional
statement for a cross join. If set, this parameter ensures
that there will always be some condition to the join clause.
ToUpperCaseFunction: SQL function call for
for converting a string to upper case. Use the token
{0} to represent the argument.
ToLowerCaseFunction: Name of the SQL function
for converting a string to lower case. Use the token
{0} to represent the argument.
StringLengthFunction: Name of the SQL
function for getting the length of a string. Use the token
{0} to represent the argument.
SubstringFunctionName: Name of the SQL
function for getting the substring of a string.
DistinctCountColumnSeparator:
The string the database uses to delimit between column
expressions in a SELECT COUNT(DISTINCT column-list)
clause. Defaults to null for most databases,
meaning that multiple columns in a distinct COUNT clause are
not supported.
ForUpdateClause: The clause to append to
SELECT statements to issue queries that
obtain pessimistic locks. Defaults to
FOR UPDATE.
TableForUpdateClause: The clause to append
to the end of each table alias in queries that obtain
pessimistic locks. Defaults to null.
SupportsSelectForUpdate: If true, then the
database supports SELECT statements with a
pessimistic locking clause. Defaults to true.
SupportsLockingWithDistinctClause: If true,
then the database supports FOR UPDATE
select clauses with DISTINCT clauses.
SupportsLockingWithOuterJoin: If true, then
the database supports FOR UPDATE select
clauses with outer join queries.
SupportsLockingWithInnerJoin: If true, then
the database supports FOR UPDATE select
clauses with inner join queries.
SupportsLockingWithMultipleTables: If true,
then the database supports FOR UPDATE select
clauses that select from multiple tables.
SupportsLockingWithOrderClause: If true,
then the database supports FOR UPDATE
select clauses with ORDER BY clauses.
SupportsLockingWithSelectRange: If true,
then the database supports FOR UPDATE
select clauses with queries that select a range of data using
LIMIT, TOP or the
database equivalent. Defaults to true.
SimulateLocking: Some databases
do not support pessimistic locking, which will result in
an exception when you attempt a pessimistic
transaction. Setting this property to
true bypasses the locking check to
allow pessimistic transactions even on databases that
do not support locking. Defaults to false.
SupportsQueryTimeout: If true, then the
JDBC driver supports calls to
java.sql.Statement.setQueryTimeout.
SupportsHaving: Whether this database
supports HAVING clauses in selects.
SupportsSelectStartIndex: Whether this
database can create a select that skips the first N results.
SupportsSelectEndIndex: Whether this
database can create a select that is limited to the first N
results.
SupportsSubselect: Whether this database
supports subselects in queries.
RequiresAliasForSubselect: If true, then
the database requires that subselects in a FROM clause be
assigned an alias.
SupportsMultipleNontransactionalResultSets:
If true, then a nontransactional connection is capable
of having multiple open ResultSet
instances.
StorageLimitationsFatal: If true, then any
data truncation/rounding that is performed by the dictionary
in order to store a value in the database will be treated as
a fatal error, rather than just issuing a warning.
StoreLargeNumbersAsStrings: Many
databases have limitations on the number of digits that can
be stored in a numeric field (for example, Oracle can only
store 38 digits). For applications that operate
on very large BigInteger and
BigDecimal values, it may be
necessary to store these objects as string fields rather
than the database's numeric type. Note that this may
prevent meaningful numeric queries from being executed
against the database. Defaults to
false.
StoreCharsAsNumbers: Set this property
to false to store Java
char fields as CHAR
values rather than numbers. Defaults to true.
UseGetBytesForBlobs: If true, then
ResultSet.getBytes will be used to
obtain blob data rather than
ResultSet.getBinaryStream.
UseGetObjectForBlobs: If true, then
ResultSet.getObject will be used to
obtain blob data rather than
ResultSet.getBinaryStream.
UseSetBytesForBlobs: If true, then
PreparedStatement.setBytes
will be used to set blob data, rather than
PreparedStatement.setBinaryStream.
UseGetStringForClobs: If true, then
ResultSet.getString will be used to
obtain clob data rather than
ResultSet.getCharacterStream.
UseSetStringForClobs: If true, then
PreparedStatement.setString
will be used to set clob data, rather than
PreparedStatement.setCharacterStream.
CharacterColumnSize: The default size of
varchar and char columns.
Typically 255.
ArrayTypeName: The overridden default
column type for java.sql.Types.ARRAY.
This is only used when the schema is generated by
the mappingtool.
BigintTypeName: The overridden default
column type for java.sql.Types.BIGINT.
This is only used when the schema is generated by
the mappingtool.
BinaryTypeName: The overridden default
column type for java.sql.Types.BINARY.
This is only used when the schema is generated by
the mappingtool.
BitTypeName: The overridden default
column type for java.sql.Types.BIT.
This is only used when the schema is generated by
the mappingtool.
BlobTypeName: The overridden default
column type for java.sql.Types.BLOB.
This is only used when the schema is generated by
the mappingtool.
CharTypeName: The overridden default
column type for java.sql.Types.CHAR.
This is only used when the schema is generated by
the mappingtool.
ClobTypeName: The overridden default
column type for java.sql.Types.CLOB.
This is only used when the schema is generated by
the mappingtool.
DateTypeName: The overridden default
column type for java.sql.Types.DATE.
This is only used when the schema is generated by
the mappingtool.
DecimalTypeName: The overridden default
column type for java.sql.Types.DECIMAL.
This is only used when the schema is generated by
the mappingtool.
DistinctTypeName: The overridden default
column type for java.sql.Types.DISTINCT.
This is only used when the schema is generated by
the mappingtool.
DoubleTypeName: The overridden default
column type for java.sql.Types.DOUBLE.
This is only used when the schema is generated by
the mappingtool.
FloatTypeName: The overridden default
column type for java.sql.Types.FLOAT.
This is only used when the schema is generated by
the mappingtool.
IntegerTypeName: The overridden default
column type for java.sql.Types.INTEGER.
This is only used when the schema is generated by
the mappingtool.
JavaObjectTypeName: The overridden default
column type for java.sql.Types.JAVAOBJECT.
This is only used when the schema is generated by
the mappingtool.
LongVarbinaryTypeName: The overridden default
column type for java.sql.Types.LONGVARBINARY.
This is only used when the schema is generated by
the mappingtool.
LongVarcharTypeName: The overridden default
column type for java.sql.Types.LONGVARCHAR.
This is only used when the schema is generated by
the mappingtool.
NullTypeName: The overridden default
column type for java.sql.Types.NULL.
This is only used when the schema is generated by
the mappingtool.
NumericTypeName: The overridden default
column type for java.sql.Types.NUMERIC.
This is only used when the schema is generated by
the mappingtool.
OtherTypeName: The overridden default
column type for java.sql.Types.OTHER.
This is only used when the schema is generated by
the mappingtool.
RealTypeName: The overridden default
column type for java.sql.Types.REAL.
This is only used when the schema is generated by
the mappingtool.
RefTypeName: The overridden default
column type for java.sql.Types.REF.
This is only used when the schema is generated by
the mappingtool.
SmallintTypeName: The overridden default
column type for java.sql.Types.SMALLINT.
This is only used when the schema is generated by
the mappingtool.
StructTypeName: The overridden default
column type for java.sql.Types.STRUCT.
This is only used when the schema is generated by
the mappingtool.
TimeTypeName: The overridden default
column type for java.sql.Types.TIME.
This is only used when the schema is generated by
the mappingtool.
TimestampTypeName: The overridden default
column type for java.sql.Types.TIMESTAMP.
This is only used when the schema is generated by
the mappingtool.
TinyintTypeName: The overridden default
column type for java.sql.Types.TINYINT.
This is only used when the schema is generated by
the mappingtool.
VarbinaryTypeName: The overridden default
column type for java.sql.Types.VARBINARY.
This is only used when the schema is generated by
the mappingtool.
VarcharTypeName: The overridden default
column type for java.sql.Types.VARCHAR.
This is only used when the schema is generated by
the mappingtool.
UseSchemaName: If false,
then avoid including the schema name in table name references.
Defaults to true.
TableTypes: Comma-separated list of table
types to use when looking for tables during schema reflection,
as defined in the
java.sql.DatabaseMetaData.getTableInfo
JDBC method. An example is:
"TABLE,VIEW,ALIAS". Defaults to
"TABLE".
SupportsSchemaForGetTables: If false, then
the database driver does not support using
the schema name for schema reflection on table names.
SupportsSchemaForGetColumns: If false, then
the database driver does not support using
the schema name for schema reflection on column names.
SupportsNullTableForGetColumns:
If true, then the database supports passing a
null parameter to
DatabaseMetaData.getColumns
as an optimization to get information about all the tables.
Defaults to true.
SupportsNullTableForGetPrimaryKeys:
If true, then the database supports passing a
null parameter to
DatabaseMetaData.getPrimaryKeys
as an optimization to get information about all the tables.
Defaults to false.
SupportsNullTableForGetIndexInfo:
If true, then the database supports passing a
null parameter to
DatabaseMetaData.getIndexInfo
as an optimization to get information about all the tables.
Defaults to false.
SupportsNullTableForGetImportedKeys:
If true, then the database supports passing a
null parameter to
DatabaseMetaData.getImportedKeys
as an optimization to get information about all the tables.
Defaults to false.
UseGetBestRowIdentifierForPrimaryKeys:
If true, then metadata queries will use
DatabaseMetaData.getBestRowIdentifier
to obtain information about primary keys, rather than
DatabaseMetaData.getPrimaryKeys.
RequiresAutoCommitForMetadata:
If true, then the JDBC driver requires that autocommit
be enabled before any schema interrogation operations can
take place.
AutoAssignClause: The column definition
clause to append to a creation statement. For example,
"AUTO_INCREMENT" for MySQL. This property
is set automatically in the dictionary, and should not need to
be overridden, and is only used when the schema is generated
using the mappingtool.
AutoAssignTypeName: The column type name
for auto-increment columns. For example,
"SERIAL" for PostgreSQL. This property is
set automatically in the dictionary, and should not need to be
overridden, and is only used when the schema is generated
using the mappingtool.
LastGeneratedKeyQuery: The query to issue
to obtain the last automatically generated key for an
auto-increment column. For example,
"select @@identity" for Sybase.
This property is set automatically in the
dictionary, and should not need to be overridden.
NextSequenceQuery: A SQL string for
obtaining a native sequence value. May use a placeholder of
{0} for the variable sequence name. Defaults
to a database-appropriate value.
The mysql dictionary also understands the
following properties:
DriverDeserializesBlobs: Many MySQL
drivers automatically deserialize BLOBs on calls to
ResultSet.getObject.
The MySQLDictionary overrides the
standard DBDictionary.getBlobObject
method to take this into account. If your driver does not
deserialize automatically, set this property to
false.
TableType: The MySQL table type to
use when creating tables. Defaults to
innodb.
UseClobs: Some older versions of
MySQL do not handle clobs correctly. To enable clob
functionality, set this to true. Defaults to
false.
The oracle dictionary understands the
following additional properties:
UseTriggersForAutoAssign:
If true, then Kodo will allow simulation of auto-increment
columns by the use of Oracle triggers. Kodo will
assume that the current sequence value from the sequence
specified in the
AutoAssignSequenceName parameter
will hold the value of the new primary key for rows that
have been inserted.
For more details on auto-increment support, see
Section 5.3.3, “Autoassign / Identity Strategy Caveats”.
AutoAssignSequenceName:
The global name of the sequence that Kodo will
assume to hold the value of primary key value
for rows that use auto-increment. If left unset, Kodo will
use a the sequence named
"SEQ_<table name>".
MaxEmbeddedBlobSize:
Oracle is unable to persist BLOBs using the embedded update
method when BLOBs get over a certain size. The size depends
on database configuration, e.g. encoding. This property
defines the maximum size BLOB to persist with the embedded
method. Defaults to 4000 bytes.
MaxEmbeddedClobSize:
Oracle is unable to persist CLOBs using the embedded update
method when Clobs get over a certain size. The size depends
on database configuration, e.g. encoding. This property
defines the maximum size CLOB to persist with the embedded
method. Defaults to 4000 characters.
UseSetFormOfUseForUnicode:
Prior to Oracle 10i, statements executed against
unicode capable columns (the NCHAR,
NVARCHAR, NCLOB
Oracle types) required special handling to be able to
store unicode values. Setting this property
to true (the default) will cause Kodo to attempt
to detect when the column of one of these types, and
if so, will attempt to correctly configure the
statement using the
OraclePreparedStatement.setFormOfUse.
Note that this can only work if Kodo is able to
access the underlying
OraclePreparedStatement instance,
which may not be possible when using some third-party
datasources. If Kodo detects that this is the case,
a warning will be logged.
|
|
