DataNucleus supports persisting objects to RDBMS datastores (using the
datanucleus-rdbms plugin).
It supports the vast majority of RDBMS products available today.
DataNucleus communicates with the RDBMS datastore using JDBC. RDBMS systems accept varying standards
of SQL and so DataNucleus will support particular RDBMS/JDBC combinations only, though clearly we try
to support as many as possible.
By default when you create a PersistenceManagerFactory (PMF) to
connect to a particular datastore DataNucleus will automatically detect the
datastore adapter
to use and will use its own internal adapter for that type of datastore. If you find that either
DataNucleus has incorrectly detected the adapter to use, or that there is some issue with the
internal adapter, you can override the default behaviour. Please refer to the
Database Adapter Extension Guide for details.
The table below shows the versions of RDBMS and JDBC driver that DataNucleus has been tested with
To specify DB2 as your datastore, you will need something like the following specifying
(where "mydb1" is the name of the database)
datanucleus.ConnectionDriverName=com.ibm.db2.jcc.DB2Driver
datanucleus.ConnectionURL=jdbc:db2://localhost:50002/mydb1
datanucleus.ConnectionUserName='username' (e.g db2inst1)
datanucleus.ConnectionPassword='password'
With DB2 Express-C v9.7 you need to have db2jcc.jar and db2jcc_license_cu.jar in the CLASSPATH.
MySQL is supported as an RDBMS datastore by DataNucleus with the following provisos
-
INNODB
tables must be used since it is the only table type that allows foreign keys etc at
the moment. You can however define what type your table uses by setting the <class>
extension "mysql-engine-type" to be MyISAM or whatever for the class being persisted.
-
JDOQL.isEmpty()/contains() will not work in MySQL 4.0 (or earlier) since the query uses EXISTS
and that is only available from MySQL 4.1
-
MySQL on Windows MUST specify
datanucleus.identifier.case
as "LowerCase" since the MySQL server
stores all identifiers in lowercase BUT the mysql-connector-java JDBC driver has a bug (in versions
up to and including 3.1.10) where it claims that the MySQL server stores things in mixed case when
it doesnt
-
MySQL 3.* will not work reliably with inheritance cases since DataNucleus requires UNION and this doesn't
exist in MySQL 3.*
-
MySQL before version 4.1 will not work correctly on JDOQL Collection.size(), Map.size() operations
since this requires subqueries, which are not supported before MySQL 4.1.
-
If you receive an error "Incorrect arguments to mysql_stmt_execute" then this is a bug in MySQL and
you need to update your JDBC URL to append "?useServerPrepStmts=false".
-
MySQL throws away the milliseconds on a Date and so cannot be used reliably for Optimistic locking
using strategy "date-time" (use "version" instead)
-
You can specify "BLOB", "CLOB" JDBC types when using MySQL with DataNucleus but you must turn validation
of columns OFF. This is because these types are not supported by the MySQL JDBC driver and
it returns them as LONGVARBINARY/LONGVARCHAR when querying the column type
To specify MySQL as your datastore, you will need something like the following specifying (replacing 'db-name'
with name of your database etc)
datanucleus.ConnectionDriverName=com.mysql.jdbc.Driver
datanucleus.ConnectionURL=jdbc:mysql://'host':'port'/'db-name'
datanucleus.ConnectionUserName='user-name'
datanucleus.ConnectionPassword='password'
MS SQL Server is supported as an RDBMS datastore by DataNucleus with the following proviso
-
MS SQL 2000 does not keep accuracy on
datetime
datatypes. This is an MS SQL 2000 issue.
In order to keep the accuracy when storing
java.util.Date
java types, use
int
datatype.
To specify MS SQL as your datastore, you will need something like the following specifying (replacing 'db-name'
with name of your database etc)
Microsoft SqlServer 2005 JDBC Driver (Recommended)
datanucleus.ConnectionDriverName=com.microsoft.sqlserver.jdbc.SQLServerDriver
datanucleus.ConnectionURL=jdbc:sqlserver://'host':'port';DatabaseName='db-name'
;SelectMethod=cursor
datanucleus.ConnectionUserName='user-name'
datanucleus.ConnectionPassword='password'
Microsoft SqlServer 2000 JDBC Driver
datanucleus.ConnectionDriverName=com.microsoft.jdbc.sqlserver.SQLServerDriver
datanucleus.ConnectionURL=jdbc:microsoft:sqlserver://'host':'port';DatabaseName='db-name'
;SelectMethod=cursor
datanucleus.ConnectionUserName='user-name'
datanucleus.ConnectionPassword='password'
To specify Oracle as your datastore, you will need something like the following specifying (replacing 'db-name' with
name of your database etc) ... you can also use 'oci' instead of 'thin' depending on your driver.
datanucleus.ConnectionDriverName=oracle.jdbc.driver.OracleDriver
datanucleus.ConnectionURL=jdbc:oracle:thin:@'host':'port':'db-name'
datanucleus.ConnectionUserName='user-name'
datanucleus.ConnectionPassword='password'
To specify Sybase as your datastore, you will need something like the following specifying (replacing 'db-name' with
name of your database etc)
datanucleus.ConnectionDriverName=com.sybase.jdbc2.jdbc.SybDriver
datanucleus.ConnectionURL=jdbc:sybase:Tds:'host':'port'/'db-name'
datanucleus.ConnectionUserName='user-name'
datanucleus.ConnectionPassword='password'
HSQLDB is supported as an RDBMS datastore by DataNucleus with the following proviso
-
Use of batched statements is disabled since HSQLDB has a bug where it throws exceptions
"batch failed" (really informative). Still waiting for this to be fixed in HSQLDB
-
Use of JDOQL/JPQL subqueries cannot be used where you want to refer back to the parent query
since HSQLDB up to and including version 1.8 don't support this.
To specify HSQL as your datastore, you will need something like the following specifying (replacing 'db-name' with
name of your database etc)
datanucleus.ConnectionDriverName=org.hsqldb.jdbcDriver
datanucleus.ConnectionURL=jdbc:hsqldb:hsql://'host':'port'/'db-name'
datanucleus.ConnectionUserName='user-name'
datanucleus.ConnectionPassword='password'
H2 is supported as an RDBMS datastore by DataNucleus
To specify H2 as your datastore, you will need something like the following specifying (replacing 'db-name' with
name of your database etc)
datanucleus.ConnectionDriverName=org.h2.Driver
datanucleus.ConnectionURL=jdbc:h2:'db-name'
datanucleus.ConnectionUserName=sa
datanucleus.ConnectionPassword=
Informix is supported as an RDBMS datastore by DataNucleus
To specify Informix as your datastore, you will need something like the following specifying (replacing 'db-name' with
name of your database etc)
datanucleus.ConnectionDriverName=com.informix.jdbc.IfxDriver
datanucleus.ConnectionURL=jdbc:informix-sqli://[{ip|host}:port][/dbname]:
INFORMIXSERVER=servername[;name=value[;name=value]...]
datanucleus.ConnectionUserName=informix
datanucleus.ConnectionPassword=password
datanucleus.ConnectionDriverName=com.informix.jdbc.IfxDriver
datanucleus.ConnectionURL=jdbc:informix-sqli://192.168.254.129:9088:
informixserver=demo_on;database=buf_log_db
datanucleus.ConnectionUserName=informix
datanucleus.ConnectionPassword=password
Note that some database logging options in Informix do not allow changing autoCommit dinamically. You need to rebuild the database
to support it. To rebuild the database refer to Informix documention, but as example,
run $INFORMIXDIR\bin\dbaccess and execute the command "CREATE DATABASE mydb WITH BUFFERED LOG".
INDEXOF
: Informix 11.x does not have a function to search a string in another string.
DataNucleus defines a user defined function, DATANUCLEUS_STRPOS, which is automatically created
on startup. The SQL for the UDF function is:
create function DATANUCLEUS_STRPOS(str char(40),search char(40),from smallint) returning smallint
define i,pos,lenstr,lensearch smallint;
let lensearch = length(search);
let lenstr = length(str);
if lenstr=0 or lensearch=0 then return 0; end if;
let pos=-1;
for i=1+from to lenstr
if substr(str,i,lensearch)=search then
let pos=i;
exit for;
end if;
end for;
return pos;
end function;
McKoi is supported as an RDBMS datastore by DataNucleus with the following proviso
-
McKoi doesn't provide full information to allow correct validation of tables/constraints.
To specify McKoi as your datastore, you will need something like the following specifying
(replacing 'db-name' with name of your database etc)
datanucleus.ConnectionDriverName=com.mckoi.JDBCDriver
datanucleus.ConnectionURL=jdbc:mckoi://'host':'port'/'db-name'
datanucleus.ConnectionUserName='user-name'
datanucleus.ConnectionPassword='password'
To specify PostgreSQL as your datastore, you will need something like the following specifying
(replacing 'db-name' with name of your database etc)
datanucleus.ConnectionDriverName=org.postgresql.Driver
datanucleus.ConnectionURL=jdbc:postgresql://'host':'port'/'db-name'
datanucleus.ConnectionUserName='user-name'
datanucleus.ConnectionPassword='password'
To specify PostGIS as your datastore, you will need to decide first which geometry library you
want to use and then set the connection url accordingly.
For the PostGIS JDBC geometries you will need something like the following specifying
(replacing 'db-name' with name of your database etc)
datanucleus.ConnectionDriverName=org.postgresql.Driver
datanucleus.ConnectionURL=jdbc:postgresql://'host':'port'/'db-name'
datanucleus.ConnectionUserName='user-name'
datanucleus.ConnectionPassword='password'
For Oracle's JGeometry you will need something like the following specifying
(replacing 'db-name' with name of your database etc)
datanucleus.ConnectionDriverName=org.postgresql.Driver
datanucleus.ConnectionURL=jdbc:postgres_jgeom://'host':'port'/'db-name'
datanucleus.ConnectionUserName='user-name'
datanucleus.ConnectionPassword='password'
For the JTS (Java Topology Suite) geometries you will need something like the following
specifying (replacing 'db-name' with name of your database etc)
datanucleus.ConnectionDriverName=org.postgresql.Driver
datanucleus.ConnectionURL=jdbc:postgres_jts://'host':'port'/'db-name'
datanucleus.ConnectionUserName='user-name'
datanucleus.ConnectionPassword='password'
Apache Derby is supported as an RDBMS datastore by DataNucleus
To specify Apache Derby/Cloudscape as your datastore, you will need something like the following specifying
(replacing 'db-name' with filename of your database etc)
datanucleus.ConnectionDriverName=org.apache.derby.jdbc.EmbeddedDriver
datanucleus.ConnectionURL=jdbc:derby:'db-name';create=true
datanucleus.ConnectionUserName='user-name'
datanucleus.ConnectionPassword='password'
Above settings are used together with the Apache Derby in embedded mode. The below settings are used in network mode, where
the default port number is 1527.
datanucleus.ConnectionDriverName=org.apache.derby.jdbc.ClientDriver
datanucleus.ConnectionURL=jdbc:derby://'hostname':'portnumber'/'db-name';create=true
datanucleus.ConnectionUserName='user-name'
datanucleus.ConnectionPassword='password'
ASCII
: Derby 10.1 does not have a function to convert a char into ascii code. DataNucleus needs
such function to converts chars to int values when performing queries converting chars to ints.
DataNucleus defines a user defined function, DataNucleus_ASCII, which is automatically created on startup.
The SQL for the UDF function is:
DROP FUNCTION NUCLEUS_ASCII;
CREATE FUNCTION NUCLEUS_ASCII(C CHAR(1)) RETURNS INTEGER
EXTERNAL NAME 'org.datanucleus.store.rdbms.adapter.DerbySQLFunction.ascii'
CALLED ON NULL INPUT
LANGUAGE JAVA PARAMETER STYLE JAVA;
String.matches(pattern)
: When pattern argument is a column, DataNucleus defines a function that
allows Derby 10.1 to perform the matches function.
The SQL for the UDF function is:
DROP FUNCTION NUCLEUS_MATCHES;
CREATE FUNCTION NUCLEUS_MATCHES(TEXT VARCHAR(8000), PATTERN VARCHAR(8000)) RETURNS INTEGER
EXTERNAL NAME 'org.datanucleus.store.rdbms.adapter.DerbySQLFunction.matches'
CALLED ON NULL INPUT
LANGUAGE JAVA PARAMETER STYLE JAVA;
Firebird is supported as an RDBMS datastore by DataNucleus with the proviso that
-
Auto-table creation is severely limited with Firebird. In Firebird, DDL statements are not
auto-committed and are executed at the end of a transaction, after any DML statements. This makes
"on the fly" table creation in the middle of a DML transaction not work. You must make sure that
"autoStartMechanism" is NOT set to "SchemaTable" since this will use DML. You must also make sure
that nobody else is connected to the database at the same time. Don't ask us why such
limitations are in a RDBMS, but then it was you that chose to use it ;-)
To specify Firebird as your datastore, you will need something like the following specifying
(replacing 'db-name' with filename of your database etc)
datanucleus.ConnectionDriverName=org.firebirdsql.jdbc.FBDriver
datanucleus.ConnectionURL=jdbc:firebirdsql://localhost/'db-name'
datanucleus.ConnectionUserName='user-name'
datanucleus.ConnectionPassword='password'
To specify SAPDB/MaxDB as your datastore, you will need something like the following specifying
(replacing 'db-name' with filename of your database etc)
datanucleus.ConnectionDriverName=com.sap.dbtech.jdbc.DriverSapDB
datanucleus.ConnectionURL=jdbc:sapdb://localhost/'db-name'
datanucleus.ConnectionUserName='user-name'
datanucleus.ConnectionPassword='password'
If you need to pass additional parameters to the JDBC driver you can append these to the end of the
datanucleus.ConnectionURL
. For example,
datanucleus.ConnectionURL=jdbc:mysql://localhost?useUnicode=true&characterEncoding=UTF-8
Firebird