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.
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 OpenJPA's plugin syntax
.
Example 4.6. Specifying a DBDictionary
<property name="openjpa.jdbc.DBDictionary" value="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 database 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 6, “
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
.
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 12, “
Schema Tool
” for
more info about deleteTableContents
.) Defaults to
false
, since the statement may fail if using InnoDB
tables and delete constraints.
The oracle
dictionary understands the following additional
properties:
UseTriggersForAutoAssign
: If true, then 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 3.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 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 OpenJPA 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 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.