RDBMS : Datastore Types

As we saw in the Types Guide DataNucleus supports the persistence of a large range of Java field types. With RDBMS datastores, we have the notion of tables/columns in the datastore and so each Java type is mapped across to a column or a set of columns in a table. It is important to understand this mapping when mapping to an existing schema for example. In RDBMS datastores a java type is stored using JDBC types. DataNucleus supports the use of the vast majority of the available JDBC types.

JDBC types used when persisting Java types

When persisting a Java type in general it is persisted into a single column. For example a String will be persisted into a VARCHAR column by default. Some types (e.g Color) have more information to store than we can conveniently persist into a single column and so use multiple columns. Other types (e.g Collection) store their information in other ways, such as foreign keys.

This table shows the Java types we saw earlier and whether they can be queried using JDOQL queries, and what JDBC types can be used to store them in your RDBMS datastore. Not all RDBMS datastores support all of these options. While DataNucleus always tries to provide a complete list sometimes this is impossible due to limitations in the underlying JDBC driver

Java Type Number
Columns
Queryable JDBC Type(s)
boolean 1 BIT, CHAR ('Y','N'), BOOLEAN, TINYINT, SMALLINT, NUMERIC
byte 1 TINYINT, SMALLINT, NUMERIC
char 1 CHAR, INTEGER, NUMERIC
double 1 DOUBLE, DECIMAL, FLOAT
float 1 FLOAT, REAL, DOUBLE, DECIMAL
int 1 INTEGER, BIGINT, NUMERIC
long 1 BIGINT, NUMERIC, DOUBLE, DECIMAL, INTEGER
short 1 SMALLINT, INTEGER, NUMERIC
boolean[] 1 [5] LONGVARBINARY, BLOB
byte[] 1 [5] LONGVARBINARY, BLOB
char[] 1 [5] LONGVARBINARY, BLOB
double[] 1 [5] LONGVARBINARY, BLOB
float[] 1 [5] LONGVARBINARY, BLOB
int[] 1 [5] LONGVARBINARY, BLOB
long[] 1 [5] LONGVARBINARY, BLOB
short[] 1 [5] LONGVARBINARY, BLOB
java.lang.Boolean 1 BIT, CHAR('Y','N'), BOOLEAN, TINYINT, SMALLINT
java.lang.Byte 1 TINYINT, SMALLINT, NUMERIC
java.lang.Character 1 CHAR, INTEGER, NUMERIC
java.lang.Double 1 DOUBLE, DECIMAL, FLOAT
java.lang.Float 1 FLOAT, REAL, DOUBLE, DECIMAL
java.lang.Integer 1 INTEGER, BIGINT, NUMERIC
java.lang.Long 1 BIGINT, NUMERIC, DOUBLE, DECIMAL, INTEGER
java.lang.Short 1 SMALLINT, INTEGER, NUMERIC
java.lang.Boolean[] 1 [5] LONGVARBINARY, BLOB
java.lang.Byte[] 1 [5] LONGVARBINARY, BLOB
java.lang.Character[] 1 [5] LONGVARBINARY, BLOB
java.lang.Double[] 1 [5] LONGVARBINARY, BLOB
java.lang.Float[] 1 [5] LONGVARBINARY, BLOB
java.lang.Integer[] 1 [5] LONGVARBINARY, BLOB
java.lang.Long[] 1 [5] LONGVARBINARY, BLOB
java.lang.Short[] 1 [5] LONGVARBINARY, BLOB
java.lang.Number 1
java.lang.Object 1 LONGVARBINARY, BLOB
java.lang.String [8] 1 VARCHAR, CHAR, LONGVARCHAR, CLOB, BLOB, DATALINK [6], UNIQUEIDENTIFIER [7], XMLTYPE [9]
java.lang.StringBuffer [8] 1 VARCHAR, CHAR, LONGVARCHAR, CLOB, BLOB, DATALINK [6], UNIQUEIDENTIFIER [7], XMLTYPE [9]
java.lang.String[] 1 [5] LONGVARBINARY, BLOB
java.lang.Enum 1 LONGVARBINARY, BLOB, VARCHAR, INTEGER
java.lang.Enum[] 1 [5] LONGVARBINARY, BLOB
java.math.BigDecimal 1 DECIMAL, NUMERIC
java.math.BigInteger 1 NUMERIC, DECIMAL
java.math.BigDecimal[] 1 [5] LONGVARBINARY, BLOB
java.math.BigInteger[] 1 [5] LONGVARBINARY, BLOB
java.sql.Date 1 DATE, TIMESTAMP
java.sql.Time 1 TIME, TIMESTAMP
java.sql.Timestamp 1 TIMESTAMP
java.util.ArrayList 0
java.util.BitSet 0 LONGVARBINARY, BLOB
java.util.Calendar [3] 1 or 2 INTEGER, VARCHAR, CHAR
java.util.Collection 0
java.util.Currency 1 VARCHAR, CHAR
java.util.Date 1 TIMESTAMP, DATE, CHAR, BIGINT
java.util.Date[] 1 [5] LONGVARBINARY, BLOB
java.util.GregorianCalendar [2] 1 or 2 INTEGER, VARCHAR, CHAR
java.util.HashMap 0
java.util.HashSet 0
java.util.Hashtable 0
java.util.LinkedHashMap 0
java.util.LinkedHashSet 0
java.util.LinkedList 0
java.util.List 0
java.util.Locale [8] 1 VARCHAR, CHAR, LONGVARCHAR, CLOB, BLOB, DATALINK [6], UNIQUEIDENTIFIER [7], XMLTYPE [9]
java.util.Locale[] 1 [5] LONGVARBINARY, BLOB
java.util.Map 0
java.util.Properties 0
java.util.PriorityQueue 0
java.util.Queue 0
java.util.Set 0
java.util.SortedMap 0
java.util.SortedSet 0
java.util.Stack 0
java.util.TimeZone [8] 1 VARCHAR, CHAR, LONGVARCHAR, CLOB, BLOB, DATALINK [7], UNIQUEIDENTIFIER [8], XMLTYPE [9]
java.util.TreeMap 0
java.util.TreeSet 0
java.util.UUID [8] 1 VARCHAR, CHAR, LONGVARCHAR, CLOB, BLOB, DATALINK [7], UNIQUEIDENTIFIER [8], XMLTYPE [9]
java.util.Vector 0
java.awt.Color [1] 4 INTEGER
java.awt.Point [2] 2 INTEGER
java.awt.image.BufferedImage [4] 1 LONGVARBINARY, BLOB
java.net.URI [8] 1 VARCHAR, CHAR, LONGVARCHAR, CLOB, BLOB, DATALINK [7], UNIQUEIDENTIFIER [8], XMLTYPE [9]
java.net.URL [8] 1 VARCHAR, CHAR, LONGVARCHAR, CLOB, BLOB, DATALINK [7], UNIQUEIDENTIFIER [8], XMLTYPE [9]
java.io.Serializable 1 LONGVARBINARY, BLOB
javax.jdo.spi.PersistenceCapable 1 [embedded]
javax.jdo.spi.PersistenceCapable[] 1 [5]
  • [1] - java.awt.Color - stored in 4 columns (red, green, blue, alpha). ColorSpace is not persisted.
  • [2] - java.awt.Point - stored in 2 columns (x and y).
  • [3] - java.util.Calendar - stored in 2 columns (milliseconds and timezone).
  • [4] - java.awt.image.BufferedImage is stored using JPG image format
  • [5] - Array types are queryable if not serialised, but stored to many rows
  • [6] - DATALINK JDBC type supported on DB2 only. Uses the SQL function DLURLCOMPLETEONLY to fetch from the datastore. You can override this using the select-function extension. See the JDO MetaData reference.
  • [7] - UNIQUEIDENTIFIER JDBC type supported on MSSQL only.
  • [8] - Oracle treats an empty string as the same as NULL. To workaround this limitation DataNucleus replaces the empty string with the character \u0001.
  • [9] - XMLTYPE JDBC type supported on Oracle only, and is included in the "datanucleus-rdbms" plugin.

If you need to extend the provided DataNucleus capabilities in terms of its datastore types support you can utilise a plugin point.


Supported JDBC types

DataNucleus provides support for the majority of the JDBC types. The support is shown below.

JDBC Type Supported Restrictions
ARRAY
BIGINT
BINARY Only for spatial types on MySQL
BIT
BLOB
BOOLEAN
CHAR
CLOB
DATALINK Only on DB2
DATE
DECIMAL
DISTINCT
DOUBLE
FLOAT
INTEGER
JAVA_OBJECT
LONGVARBINARY
LONGVARCHAR
NCHAR
NULL
NUMERIC
NVARCHAR
OTHER
REAL
REF
SMALLINT
STRUCT Only for spatial types on Oracle
TIME
TIMESTAMP
TINYINT
VARBINARY
VARCHAR