4.4. Database Support
Prev | Chapter 4. JDBC | Next

## 4.4. Database Support

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 4, 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)

### 4.4.1. DBDictionary Properties

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.

### 4.4.2. MySQLDictionary Properties

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`.

### 4.4.3. OracleDictionary Properties

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`. For more details, see the Oracle Readme For NChar. 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.

Prev | Up | Next
4.3. Runtime Access to DataSource | Home | 4.5. SQLFactory Properties