4.  Database Support

4.1. DBDictionary Properties
4.2. FirebirdDictionary Properties
4.3. MySQLDictionary Properties
4.4. OracleDictionary Properties
4.5. SybaseDictionary Properties
4.6. DB2 Properties
4.7. Delimited Identifiers Support

OpenJPA 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 2, Supported Databases for more information. Typically, OpenJPA auto-configures its JDBC behavior and SQL dialect for your database, based on the values of your connection-related configuration properties.

If OpenJPA cannot detect what type of database you are using, or if you are using an unsupported database, you will have to tell OpenJPA what org.apache.openjpa.jdbc.sql.DBDictionary to use. The DBDictionary abstracts away the differences between databases. You can plug a dictionary into OpenJPA using the openjpa.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.

The example below demonstrates how to set a dictionary and configure its properties in your configuration file. The DBDictionary property uses OpenJPA's plugin syntax .

Example 4.7.  Specifying a DBDictionary

<property name="openjpa.jdbc.DBDictionary" value="hsql(SimulateLocking=true)"/>

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.

  • AllowsAliasInBulkClause: When true, SQL delete and update statements may use table aliases.

  • ArrayTypeName: The overridden default column type for java.sql.Types.ARRAY. This is used only when the schema is generated by the mappingtool.

  • 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, "BIGSERIAL" for PostgreSQL. This property is set automatically in the dictionary and should not need to be overridden. It is used only when the schema is generated using the mappingtool.

  • BatchLimit: The default batch limit for sending multiple SQL statements at once to the database. A value of -1 indicates unlimited batching, and any positive integer indicates the maximum number of SQL statements to batch together. Defaults to 0 which disables batching.

  • BigintTypeName: The overridden default column type for java.sql.Types.BIGINT. This is used only when the schema is generated by the mappingtool.

  • BinaryTypeName: The overridden default column type for java.sql.Types.BINARY. This is used only when the schema is generated by the mappingtool.

  • BitTypeName: The overridden default column type for java.sql.Types.BIT. This is used only when the schema is generated by the mappingtool.

  • BlobBufferSize: This property establishes the buffer size in the INSERT/UPDATE operations with an java.io.InputStream. This is only used with OpenJPA's Section 7.11, “ LOB Streaming ”. Defaults to 50000.

  • BlobTypeName: The overridden default column type for java.sql.Types.BLOB. This is used only when the schema is generated by the mappingtool.

  • BooleanTypeName: The overridden default column type for java.sql.Types.BOOLEAN. This is used only when the schema is generated by the mappingtool.

  • CastFunction: The SQL function call to cast a value to another SQL type. Use the tokens {0} and {1} to represent the two arguments. The result of the function is convert the {0} value to a {1} type. The default is "CAST({0} AS {1})".

  • CatalogSeparator: The string the database uses to delimit between the schema name and the table name. This is typically "." , which is the default.

  • CharTypeName: The overridden default column type for java.sql.Types.CHAR. This is used only when the schema is generated by the mappingtool.

  • CharacterColumnSize: The default size of varchar and char columns. Typically 255.

  • ClobBufferSize: This property establish the buffer size in the INSERT/UPDATE operations with a java.io.Reader. This is only used with OpenJPA's Section 7.11, “ LOB Streaming ”. Defaults to 50000.

  • ClobTypeName: The overridden default column type for java.sql.Types.CLOB. This is used only when the schema is generated by the mappingtool.

  • ClosePoolSQL: A special command to issue to the database when shutting down the pool. Usually the pool of connections to the database is closed when the application is ending. For embedded databases, whose lifecycle is coterminous with the application, there may be a special command, usually "SHUTDOWN", that will cause the embedded database to close cleanly. Defaults to null.

  • ConcatenateFunction: The SQL function call or operation to concatenate two strings. Use the tokens {0} and {1} to represent the two arguments. The result of the function or operation is to concatenate the {1} string to the end of the {0} string. Defaults to "({0}||{1})".

  • 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".

  • CreatePrimaryKeys: When false, do not create database primary keys for identifiers. Defaults to true .

  • CrossJoinClause: The clause to use for a cross join (cartesian product). Defaults to "CROSS JOIN".

  • CurrentDateFunction: The SQL function call to obtain the current date from the database. Defaults to "CURRENT_DATE".

  • CurrentTimeFunction: The SQL function call to obtain the current time from the database. Defaults to "CURRENT_TIME".

  • CurrentTimestampFunction: The SQL function call to obtain the current timestamp from the database. Defaults to "CURRENT_TIMESTAMP".

  • DatePrecision: The database is able to store time values to this degree of precision, which is expressed in nanoseconds. This value is usually one million, meaning that the database is able to store time values with a precision of one millisecond. Particular databases may have more or less precision. OpenJPA will round all time values to this degree of precision before storing them in the database. Defaults to 1000000.

  • DateTypeName: The overridden default column type for java.sql.Types.DATE. This is used only when the schema is generated by the mappingtool.

  • DecimalTypeName: The overridden default column type for java.sql.Types.DECIMAL. This is used only when the schema is generated by the mappingtool.

  • DelimitedCase: The case to use when querying the database about identifiers that have been delimited. It defaults to preserving the case of the originally specified name. Available values are: upper, lower, preserve.

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

  • DistinctTypeName: The overridden default column type for java.sql.Types.DISTINCT. This is used only when the schema is generated by the mappingtool.

  • DoubleTypeName: The overridden default column type for java.sql.Types.DOUBLE. This is used only when the schema is generated by the mappingtool.

  • DriverVendor: The vendor of the particular JDBC driver you are using. Some dictionaries must alter their behavior depending on the driver vendor. Dictionaries usually detect the driver vendor and set this property themselves. See the VENDOR_XXX constants defined in the DBDictionary Javadoc for available options.

  • DropTableSQL: The SQL statement used to drop a table. Use the token {0} as the argument for the table name. Defaults to "DROP TABLE {0}".

  • FixedSizeTypeNames: A comma separated list of additional database types that have a size defined by the database. In other words, when a column of a fixed size type is declared, its size cannot be defined by the user. Common examples would be DATE, FLOAT, and INTEGER. Each database dictionary has its own internal set of fixed size type names that include the names mentioned here and many others. Names added to this property are added to the dictionary's internal set. Defaults to null.

  • FloatTypeName: The overridden default column type for java.sql.Types.FLOAT. This is used only when the schema is generated by the mappingtool.

  • ForUpdateClause: The clause to append to SELECT statements to issue queries that obtain pessimistic locks. Defaults to "FOR UPDATE".

  • GetStringVal: A special function to return the value of an XML column in a select statement. For example, Oracle uses ".getClobVal()", as in "SELECT t0.xmlcol.getClobVal() FROM xmltab t0". Defaults to the empty string.

  • InClauseLimit: The maximum number of elements in an IN clause. OpenJPA works around cases where the limit is exceeded. Defaults to -1 meaning no limit.

  • InitializationSQL: A piece of SQL to issue against the database whenever a connection is retrieved from the DataSource .

  • InnerJoinClause: The clause to use for an inner join. Defaults to "INNER JOIN".

  • IntegerTypeName: The overridden default column type for java.sql.Types.INTEGER. This is used only when the schema is generated by the mappingtool.

  • JavaObjectTypeName: The overridden default column type for java.sql.Types.JAVAOBJECT. This is used only when the schema is generated by the mappingtool.

  • JoinSyntax: The SQL join syntax to use in select statements. See Section 6, “ Setting the SQL Join Syntax ”.

  • LastGeneratedKeyQuery: The query to issue to obtain the last automatically generated key for an auto-increment column. For example, "SELECT LAST_INSERT_ID()" for MySQL. This property is set automatically in the dictionary, and should not need to be overridden. If SupportsGetGeneratedKeys is true, the query will not be issued but a more efficient JDBC 3.0 mechanism for obtaining generated keys will be used instead.

  • LeadingDelimiter: The characters to use as the leading delimiter for a delimited identifier. The default value is a double quote, ("). See Section 4.7, “ Delimited Identifiers Support ” for the default value for some specific databases.

  • LongVarbinaryTypeName: The overridden default column type for java.sql.Types.LONGVARBINARY. This is used only when the schema is generated by the mappingtool.

  • LongVarcharTypeName: The overridden default column type for java.sql.Types.LONGVARCHAR. This is used only when the schema is generated by the mappingtool.

  • MaxAutoAssignNameLength: Set this property to the maximum length of the sequence name used for auto-increment columns. Names longer than this value are truncated. Defaults to 31.

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

  • MaxEmbeddedBlobSize: When greater than -1, the maximum size of a BLOB value that can be sent directly to the database within an insert or update statement. Values whose size is greater than MaxEmbeddedBlobSize force OpenJPA to work around this limitation. A value of -1 means that there is no limitation. Defaults to -1.

  • MaxEmbeddedClobSize: When greater than -1, the maximum size of a CLOB value that can be sent directly to the database within an insert or update statement. Values whose size is greater than MaxEmbeddedClobSize force OpenJPA to work around this limitation. A value of -1 means that there is no limitation. Defaults to -1.

  • MaxIndexNameLength: The maximum number of characters in an index name. Defaults to 128.

  • MaxIndexesPerTable: The maximum number of indexes that can be placed on a single table. Defaults to no limit.

  • MaxTableNameLength: The maximum number of characters in a table name. Defaults to 128.

  • NameConcatenator: The value used when names are concatenated to create a generated name. The default value is the underscore "_".

  • 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. For example, "SELECT {0}.NEXTVAL FROM DUAL" for Oracle.

  • NullTypeName: The overridden default column type for java.sql.Types.NULL. This is used only when the schema is generated by the mappingtool.

  • NumericTypeName: The overridden default column type for java.sql.Types.NUMERIC. This is used only when the schema is generated by the mappingtool.

  • OtherTypeName: The overridden default column type for java.sql.Types.OTHER. This is used only when the schema is generated by the mappingtool.

  • OuterJoinClause: The clause to use for an left outer join. Defaults to "LEFT OUTER JOIN".

  • Platform: The name of the database that this dictionary targets. Defaults to "Generic", but all dictionaries override this value.

  • RangePosition: Indicates where to specify in the SQL select statement the range, if any, of the result rows to be returned. When limiting the number of returned result rows to a subset of all those that satisfy the query's conditions, the position of the range clause varies by database. Defaults to 0, meaning that the range is expressed at the end of the select statement but before any locking clause. See the RANGE_XXX constants defined in DBDictionary.

  • RealTypeName: The overridden default column type for java.sql.Types.REAL. This is used only when the schema is generated by the mappingtool.

  • RefTypeName: The overridden default column type for java.sql.Types.REF. This is used only when the schema is generated by the mappingtool.

  • RequiresAliasForSubselect: When true, the database requires that subselects in a FROM clause be assigned an alias.

  • RequiresAutoCommitForMetadata: When true, the JDBC driver requires that autocommit be enabled before any schema interrogation operations can take place.

  • RequiresCastForComparisons: When true, comparisons of two values of different types or of two literals requires a cast in the generated SQL. Defaults to false.

  • RequiresCastForMathFunctions: When true, math operations on two values of different types or on two literals requires a cast in the generated SQL. Defaults to false.

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

  • RequiresTargetForDelete: When true, the database requires a target for delete statements. Defaults to false.

  • ReservedWords: A comma-separated list of reserved words for this database, beyond the standard SQL92 keywords.

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

  • SearchStringEscape: The default escape character used when generating SQL LIKE clauses. The escape character is used to escape the wildcard meaning of the _ and % characters. Note: since JPQL provides the ability to define the escape character in the query, this setting is primarily used when translating other query languages, such as JDOQL. Defaults to "\\" (a single backslash in Java speak).

  • RequiresSearchStringEscapeForLike: When true, the database requires an escape string for queries that use LIKE. The escape string can be specified using searchStringEscape. Defaults to true.

  • SelectWords: A comma-separated list of keywords which may be used to start a SELECT statement for this database. If an application executes a native SQL statement which begins with SelectWords OpenJPA will treat the statement as a SELECT statement rather than an UPDATE statement.

  • SequenceNameSQL: Additional phrasing to use with SequenceSQL. Defaults to null.

  • SequenceSQL: General structure of the SQL query to use when interrogating the database for sequence names. As there is no standard way to obtain sequence names, it defaults to null.

  • SequenceSchemaSQL: Additional phrasing to use with SequenceSQL. Defaults to null.

  • SimulateLocking: Some databases do not support pessimistic locking, which will result in an exception when you attempt a transaction while using the pessimistic lock manager. Setting this property to true suppresses the locking of rows in the database, thereby allowing pessimistic transactions even on databases that do not support locking. At the same time, setting this property to true means that you do not obtain the semantics of a pessimistic transaction with the database. Defaults to false.

  • SmallintTypeName: The overridden default column type for java.sql.Types.SMALLINT. This is used only when the schema is generated by the mappingtool.

  • StorageLimitationsFatal: When true, 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.

  • StoreCharsAsNumbers: Set this property to false to store Java char fields as CHAR values rather than numbers. Defaults to true.

  • StoreLargeNumbersAsStrings: When true, the dictionary prefers to store Java fields of type BigInteger and BigDecimal as string values in the database. Likewise, the dictionary will instruct the mapping tool to map these Java types to character columns. Because some databases have limitations on the number of digits that can be stored in a numeric column (for example, Oracle can only store 38 digits), this option may be necessary for some applications. Note that this option may prevent OpenJPA from executing meaningful numeric queries against the columns. Defaults to false.

  • StringLengthFunction: Name of the SQL function for getting the length of a string. Use the token {0} to represent the argument.

  • StructTypeName: The overridden default column type for java.sql.Types.STRUCT. This is used only when the schema is generated by the mappingtool.

  • SubstringFunctionName: Name of the SQL function for getting the substring of a string.

  • SupportsAlterTableWithAddColumn: When true, the database supports adding a new column in an ALTER TABLE statement. Defaults to true.

  • SupportsAlterTableWithDropColumn: When true, the database supports dropping a column in an ALTER TABLE statement. Defaults to true.

  • SupportsAutoAssign: When true, the database supports auto-assign columns, where the value of column is assigned upon insertion of the row into the database. Defaults to false.

  • SupportsCascadeDeleteAction: When true, the database supports the CASCADE delete action on foreign keys. Defaults to true.

  • SupportsCascadeUpdateAction: When true, the database supports the CASCADE update action on foreign keys. Defaults to true.

  • SupportsComments: When true, comments can be associated with the table in the table creation statement. Defaults to false.

  • SupportsCorrelatedSubselect: When true, the database supports correlated subselects. Correlated subselects are select statements nested within select statements that refers to a column in the outer select statement. For performance reasons, correlated subselects are generally a last resort. Defaults to true.

  • SupportsDefaultDeleteAction: When true, the database supports the SET DEFAULT delete action on foreign keys. Defaults to true.

  • SupportsDefaultUpdateAction: When true, the database supports the SET DEFAULT update action on foreign keys. Defaults to true.

  • SupportsDeferredConstraints: When true, the database supports deferred constraints. The database supports deferred constraints by checking for constraint violations when the transaction commits, rather than checking for violations immediately after receiving each SQL statement within the transaction. Defaults to true.

  • SupportsDelimitedIdentifiers: When true, the database supports delimited identifiers. It defaults to true.

  • SupportsForeignKeys: When true, the database supports foreign keys. Defaults to true.

  • SupportsForeignKeysComposite: When true, the database supports composite foreign keys. Defaults to true.

  • SupportsGetGeneratedKeys: When true, OpenJPA will use java.sql.Statement.getGeneratedKeys method to obtain values of auto-increment columns. When false, a query specified by LastGeneratedKeyQuery will be used for that purpose. If not set, the value will be auto-detected by querying the JDBC driver. Setting the value to true requires that the JDBC driver supports version 3.0 or higher of the JDBC specification and supports the java.sql.Statement.getGeneratedKeys method.

  • SupportsHaving: When true, the database supports HAVING clauses in selects.

  • SupportsLockingWithDistinctClause: When true, the database supports FOR UPDATE select clauses with DISTINCT clauses.

  • SupportsLockingWithInnerJoin: When true, the database supports FOR UPDATE select clauses with inner join queries.

  • SupportsLockingWithMultipleTables: When true, the database supports FOR UPDATE select clauses that select from multiple tables.

  • SupportsLockingWithOrderClause: When true, the database supports FOR UPDATE select clauses with ORDER BY clauses.

  • SupportsLockingWithOuterJoin: When true, the database supports FOR UPDATE select clauses with outer join queries.

  • SupportsLockingWithSelectRange: When true, 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.

  • SupportsModOperator: When true, the database supports the modulus operator (%) instead of the MOD function. Defaults to false.

  • SupportsMultipleNontransactionalResultSets: When true, a nontransactional connection is capable of having multiple open ResultSet instances.

  • SupportsNullDeleteAction: When true, the database supports the SET NULL delete action on foreign keys. Defaults to true.

  • SupportsNullTableForGetColumns: When true, the database supports passing a null parameter to DatabaseMetaData.getColumns as an optimization to get information about all the tables. Defaults to true.

  • SupportsNullTableForGetImportedKeys: When true, the database supports passing a null parameter to DatabaseMetaData.getImportedKeys as an optimization to get information about all the tables. Defaults to false.

  • SupportsNullTableForGetIndexInfo: When true, the database supports passing a null parameter to DatabaseMetaData.getIndexInfo as an optimization to get information about all the tables. Defaults to false.

  • SupportsNullTableForGetPrimaryKeys: When true, the database supports passing a null parameter to DatabaseMetaData.getPrimaryKeys as an optimization to get information about all the tables. Defaults to false.

  • SupportsNullUpdateAction: When true, the database supports the SET NULL update action on foreign keys. Defaults to true.

  • SupportsQueryTimeout: When true, the JDBC driver supports calls to java.sql.Statement.setQueryTimeout.

  • SupportsRestrictDeleteAction: When true, the database supports the RESTRICT delete action on foreign keys. Defaults to true.

  • SupportsRestrictUpdateAction: When true, the database supports the RESTRICT update action on foreign keys. Defaults to true.

  • SupportsSchemaForGetColumns: When false, the database driver does not support using the schema name for schema reflection on column names.

  • SupportsSchemaForGetTables: If false, then the database driver does not support using the schema name for schema reflection on table names.

  • SupportsSelectEndIndex: When true, the database can create a select that is limited to the first N results.

  • SupportsSelectForUpdate: When true, the database supports SELECT statements with a pessimistic locking (FOR UPDATE) clause. Defaults to true.

  • SupportsSelectStartIndex: When true, the database can create a select that skips the first N results.

  • SupportsSimpleCaseExpression: When true, the database supports the simple form of CASE expression: CASE <a> WHEN <b> THEN <c> WHEN <d> THEN <e> ELSE <f> END. When false, the general form of CASE expression will be used: CASE WHEN <a> = <b> THEN <c> WHEN <a> = <d> THEN <e> ELSE <f> END. Defaults to true.

  • SupportsSubselect: When true, the database supports subselects in queries.

  • SupportsTimestampNanos: When true, the database supports nanoseconds with TIMESTAMP columns. Defaults to true.

  • SupportsUniqueConstraints: When true, the database supports unique constraints. Defaults to true.

  • SupportsXMLColumn: When true, the database supports an XML column type. See Section 7.10, “ XML Column Mapping ” for information on using this capability. Defaults to false.

  • SystemSchemas: A comma-separated list of schema names that should be ignored.

  • SystemTables: A comma-separated list of table names that should be ignored.

  • TableForUpdateClause: The clause to append to the end of each table alias in queries that obtain pessimistic locks. Defaults to null.

  • tableLengthIncludesSchema: Whether the max length for a table name includes the table's schema. Defaults to true for backwards compatibility. In future releases this will be set to false.

  • 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".

  • TimeTypeName: The overridden default column type for java.sql.Types.TIME. This is used only when the schema is generated by the mappingtool.

  • TimestampTypeName: The overridden default column type for java.sql.Types.TIMESTAMP. This is used only when the schema is generated by the mappingtool.

  • TinyintTypeName: The overridden default column type for java.sql.Types.TINYINT. This is used only when the schema is generated by the mappingtool.

  • ToLowerCaseFunction: Name of the SQL function for converting a string to lower case. Use the token {0} to represent the argument.

  • ToUpperCaseFunction: SQL function call for converting a string to upper case. Use the token {0} to represent the argument.

  • TrailingDelimiter: The characters to use as the trailing delimiter for a delimited identifier. The default value is a double quote, ("). See Section 4.7, “ Delimited Identifiers Support ” for the default value for some specific databases.

  • TrimBothFunction: The SQL function call to trim any number of a particular character from both the start and end of a string. Note: some databases do not support specifying the character in which case only spaces or whitespace can be trimmed. Use the token {1} when possible to represent the character, and the token {0} to represent the string. Defaults to "TRIM(BOTH {1} FROM {0})".

  • TrimLeadingFunction: The SQL function call to trim any number of a particular character from the start of a string. Note: some databases do not support specifying the character in which case only spaces or whitespace can be trimmed. Use the token {1} when possible to represent the character, and the token {0} to represent the string. Defaults to "TRIM(LEADING {1} FROM {0})".

  • TrimTrailingFunction: The SQL function call to trim any number of a particular character from the end of a string. Note: some databases do not support specifying the character in which case only spaces or whitespace can be trimmed. Use the token {1} when possible to represent the character, and the token {0} to represent the string. Defaults to "TRIM(TRAILING {1} FROM {0})".

  • UseGetBestRowIdentifierForPrimaryKeys: When true, metadata queries will use DatabaseMetaData.getBestRowIdentifier to obtain information about primary keys, rather than DatabaseMetaData.getPrimaryKeys.

  • UseGetBytesForBlobs: When true, ResultSet.getBytes will be used to obtain blob data rather than ResultSet.getBinaryStream.

  • UseGetObjectForBlobs: When true, ResultSet.getObject will be used to obtain blob data rather than ResultSet.getBinaryStream.

  • UseGetStringForClobs: When true, ResultSet.getString will be used to obtain clob data rather than ResultSet.getCharacterStream.

  • UseSchemaName: If false, then avoid including the schema name in table name references. Defaults to true .

  • UseSetBytesForBlobs: When true, PreparedStatement.setBytes will be used to set blob data, rather than PreparedStatement.setBinaryStream.

  • UseSetStringForClobs: When true, PreparedStatement.setString will be used to set clob data, rather than PreparedStatement.setCharacterStream.

  • UseWildCardForCount: When true, the JPQL COUNT aggregate function will be translated into SQL COUNT(*) expression if the SQL query does not involve joins. Defaults to false.

  • ValidationSQL: The SQL used to validate that a connection is still in a valid state. For example, "SELECT SYSDATE FROM DUAL" for Oracle.

  • VarbinaryTypeName: The overridden default column type for java.sql.Types.VARBINARY. This is used only when the schema is generated by the mappingtool.

  • VarcharTypeName: The overridden default column type for java.sql.Types.VARCHAR. This is used only when the schema is generated by the mappingtool.

  • XmlTypeName: The column type name for XML columns. This property is set automatically in the dictionary and should not need to be overridden. It is used only when the schema is generated using the mappingtool. Defaults to "XML".

4.2.  FirebirdDictionary Properties

The firebird dictionary understands the following additional properties:

  • FirebirdVersion: The database version OpenJPA connects to. This property affects the SQL statements executed by OpenJPA. Available values are: 15, 20 and 21 - they indicate Firebird versions 1.5, 2.0 and 2.1 respectively. If not set, the value will be auto-detected.

  • IndexedVarcharMaxSizeFB15: Firebird 1.5 imposes tight limits on index size. In particular, an indexed VARCHAR column size cannot exceed 252. When schema is created, OpenJPA will use this property to reduce the size of indexed VARCHAR columns. Defaults to 252 but you might want to decrease this value if multi-column indexes are used. If the Firebird version is 2.0 or later or schema creation is not used, this property does not matter.

  • RangeSyntax: Firebird 2.0 and later support two ways of handling queries that select a range of data: "FIRST <p> SKIP <q>" and "ROWS <m> TO <n>". Earlier versions support only "FIRST <p> SKIP <q>" syntax. This property determines the syntax to be used. Available values are: "firstskip" and "rows". Defaults to using "ROWS <m> TO <n>" if the Firebird version is 2.0 or later, and "FIRST <p> SKIP <q>" otherwise.

4.3.  MySQLDictionary Properties

The mysql dictionary also understands the following properties:

  • DriverDeserializesBlobs: Many older MySQL drivers automatically deserialize BLOBs on calls to ResultSet.getObject. The MySQLDictionary overrides the standard DBDictionary.getBlobObject method to take this into account. Defaults to true if driver version is less than 5.0, false otherwise. If your driver deserializes automatically, you may want to set this property to true.

  • 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 disable CLOB functionality, set this to false. Defaults to true.

  • OptimizeMultiTableDeletes: MySQL as of version 4.0.0 supports multiple tables in DELETE statements. When this option is set, OpenJPA will use that syntax when doing bulk deletes from multiple tables. This can happen when the deleteTableContents SchemaTool action is used. (See Section 13, “ Schema Tool ” for more info about deleteTableContents.) Defaults to false, since the statement may fail if using InnoDB tables and delete constraints.

Starting with Connector/J 3.1.7, MySQL supports a variant of the driver com.mysql.jdbc.ReplicationDriver that automatically sends queries to a read/write master, or a failover or round-robin load balanced set of slaves based on the state of read-only status of the connection. See MySQL Reference for more details.

This replication feature can be used transparently with OpenJPA application by following configuration:

  • openjpa.ConnectionDriverName: com.mysql.jdbc.ReplicationDriver

  • openjpa.ConnectionFactoryProperties: autoReconnect=true,roundRobinLoadBalance=true

    OpenJPA will use a read-only connection with replicated database configuration and will automatically switch the connection to a non-readonly mode if the transaction is writing to the database.

4.4.  OracleDictionary Properties

The oracle dictionary understands the following additional properties:

  • UseTriggersForAutoAssign: When true, OpenJPA will allow simulation of auto-increment columns by the use of Oracle triggers. OpenJPA 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 4.4, “ Autoassign / Identity Strategy Caveats ” .

  • AutoAssignSequenceName: The global name of the sequence that OpenJPA will assume to hold the value of primary key value for rows that use auto-increment. If left unset, OpenJPA will use a sequence named "<table name>_<column name>_SEQ".

  • 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 OpenJPA to attempt to detect when the column is 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 OpenJPA is able to access the underlying OraclePreparedStatement instance, which may not be possible when using some third-party datasources. If OpenJPA detects that this is the case, a warning will be logged.

4.5.  SybaseDictionary Properties

The sybase dictionary understands the following additional properties:

  • IgnoreNumericTruncation: If true, Sybase will ignore numeric truncation on SQL operations. Otherwise, if numeric truncation is detected, the operation will fail.

4.6.  DB2 Properties

The db2 dictionary understands the following additional properties:

  • AppendExtendedExceptionText: If false, OpenJPA will not call back to the database to get extended exception text.

4.7.  Delimited Identifiers Support

OpenJPA provides support for delimited identifiers as defined in the 2.0 JPA specification. Identifiers can either be automatically delimited or individually manually delimited. To have OpenJPA automatically delimit identifiers, add the <delimited-identifiers/> tag to the mapping file as documented in the JPA specification.

You can manually delimit individual identifiers either in the annotations or in the definitions in the mapping file. To delimit an identifier element in an annotation, surround it with double quotes. In a mapping file, add &quote; to both the beginning and end of the element.

When delimited identifiers has been specified, OpenJPA will delimit SQL identifiers in the generated SQL statements. It will use database-specific delimiters as defined in the appropriate database dictionary. By default, the leading and trailing delimiters are both double quotes, ("). Different defaults for other dictionaries provided by OpenJPA are in the following table.

Table 4.1.  Default delimiters for delimited identifiers

Dictionary Leading Delimiter Trailing Delimiter
MySQLDictionary ` `
AccessDictionary [ ]

Some databases support more than one set of delimiters, often based on configuration. If you need values different than the default values, you can set the LeadingDelimiter and the TrailingDelimiter dictionary properties.

You can specify whether or not the particular database that you are using supports delimited identifiers by setting the SupportsDelimitedIdentifiers dictionary property. If this value is set to false, identifiers will not be automatically delimited, even if the <delimited-identifiers/> tag is specified in the mapping file.

Limitation: The columnDefinition elements in identifiers are not automatically delimited by OpenJPA when using the <delimited-identifiers/> tag in the mapping file. If you want these to be delimited, you must manually delimit them in the annotation or mapping file definitions.