JPA : Schema Mapping

You saw in our basic class mapping guide how you define a classes basic persistence, notating which fields are persisted. The next step is to define how it maps to the datastore. Fields of a class are mapped to columns of a table (note that with some datastores it is not called a 'table' or 'column', but the concept is similar and we use 'table' and 'column' here to represent the mapping). If you don't specify the table and column names, then DataNucleus will generate table and column names for you. You should specify your table and column names if you have an existing schema. Failure to do so will mean that DataNucleus uses its own names and these will almost certainly not match what you have in the datastore. There are several aspects to cover here

Tables and Column names

The main thing that developers want to do when they set up the persistence of their data is to control the names of the tables and columns used for storing the classes and fields. This is an essential step when mapping to an existing schema, because it is necessary to map the classes onto the existing database entities. Let's take an example

public class Hotel
{
    private String name;
    private String address;
    private String telephoneNumber;
    private int numberOfRooms;
    ...
}

In our case we want to map this class to a table called ESTABLISHMENT, and has columns NAME, DIRECTION, PHONE and NUMBER_OF_ROOMS (amongst other things). So we define our Meta-Data like this

<entity class="Hotel">
    <table name="ESTABLISHMENT"/>
    <attributes>
        <basic name="name">
            <column name="NAME"/>
        </basic>
        <basic name="address">
            <column name="DIRECTION"/>
        </basic>
        <basic name="telephoneNumber">
            <column name="PHONE"/>
        </basic>
        <basic name="numberOfRooms">
            <column name="NUMBER_OF_ROOMS"/>
        </basic>
    </attributes>
</entity>

Alternatively, if you really want to embody schema info in your class, you can use annotations

@Table(name="ESTABLISHMENT")
public class Hotel
{
    @Column(name="NAME")
    private String name;
    @Column(name="DIRECTION")
    private String address;
    @Column(name="PHONE")
    private String telephoneNumber;
    @Column(name="NUMBER_OF_ROOMS")
    private int numberOfRooms;

So we have defined the table and the column names. It should be mentioned that if you don't specify the table and column names then DataNucleus will generate names for the datastore identifiers consistent with the JPA specification. The table name will be based on the class name, and the column names will be based on the field names and the role of the field (if part of a relationship).

See also :-


Column nullability and default values

So we've seen how to specify the basic structure of a table, naming the table and its columns, and how to control the types of the columns. We can extend this further to control whether the columns are allowed to contain nulls. Let's take a related class for our hotel. Here we have a class to model the payments made to the hotel.

public class Payment
{
    Customer customer;
    String bankTransferReference;
    String currency;
    double amount;
}

In this class we can model payments from a customer of an amount. Where the customer pays by bank transfer we can save the reference number. Since the bank transfer reference is optional we want that column to be nullable. So let's specify the MetaData for the class.

<entity class="Payment">
    <attributes>
        <one-to-one name="customer">
            <primary-key-join-column name="CUSTOMER_ID"/>
        </one-to-one>
        <basic name="bankTransferReference">
            <column name="TRANSFER_REF" nullable="true"/>
        </basic>
        <basic name="currency">
            <column name="CURRENCY" default-value="GBP"/>
        </basic>
        <basic name="amount">
            <column name="AMOUNT"/>
        </basic>
    </attributes>
</entity>

Alternatively, you can specify these using annotations should you so wish.

So we make use of the nullable attribute. The table, when created by DataNucleus, will then provide the nullability that we require. Unfortunately with JPA there is no way to specify a default value for a field when it hasn't been set (unlike JDO where you can do that).

See also :-


Column types

DataNucleus will provide a default type for any columns that it creates, but it will allow users to override this default. The default that DataNucleus chooses is always based on the Java type for the field being mapped. For example a Java field of type "int" will be mapped to a column type of INTEGER in RDBMS datastores. Similarly String will be mapped to VARCHAR. JPA does NOT allow detailed control over the JDBC type as such, with the exception of distinguishing BLOB/CLOB/TIME/TIMESTAMP types. Fortunately DataNucleus (from v3.0.2) provides an extension to overcome this flaw in the JPA spec. Here we make use of a DataNucleus annotation @JdbcType

public class Payment
{
    @JdbcType("CHAR")
    String currency;

    ...
}

So we defined the JDBC type that this field will use (rather than the default of VARCHAR).

JPA does allow permit control over the length/precision/scale of columns. So we define this as follows

<entity name="Payment">
    <attributes>
        <one-to-one name="customer">
            <primary-key-join-column name="CUSTOMER_ID"/>
        </one-to-one>
        <basic name="bankTransferReference">
            <column name="TRANSFER_REF" nullable="true" length="255"/>
        </basic>
        <basic name="currency">
            <column name="CURRENCY" default-value="GBP" length="3"/>
        </basic>
        <basic name="amount">
            <column name="AMOUNT" precision="10" scale="2"/>
        </basic>
    </attributes>
</entity>

Alternatively, you can specify these using annotations should you so wish.

So we have defined TRANSFER_REF to use VARCHAR(255) column type, CURRENCY to use (VAR)CHAR(3) column type, and AMOUNT to use DECIMAL(10,2) column type.

See also :-

columnposition

With some datastores it is desirable to be able to specify the relative position of a column in the table schema. The default (for DataNucleus) is just to put them in ascending alphabetical order. DataNucleus allows an extension to JPA providing definition of this using the position of a column. See fields/properties column positioning docs for details.

RDBMS : Views

The standard situation with an RDBMS datastore is to map classes to Tables. The majority of RDBMS also provide support for Views, providing the equivalent of a read-only SELECT across various tables. DataNucleus also provides support for querying such Views. This provides more flexibility to the user where they have data and need to display it in their application. Support for Views is described below.

When you want to access data according to a View, you are required to provide a class that will accept the values from the View when queried, and Meta-Data for the class that defines the View and how it maps onto the provided class. Let's take an example. We have a View SALEABLE_PRODUCT in our database as follows, defined based on data in a PRODUCT table.

CREATE VIEW SALEABLE_PRODUCT (ID, NAME, PRICE, CURRENCY) AS
    SELECT ID, NAME, CURRENT_PRICE AS PRICE, CURRENCY FROM PRODUCT
    WHERE PRODUCT.STATUS_ID = 1

So we define a class to receive the values from this View, and define how it is mapped to the view.

package mydomain.views;

@Entity
@Table("SALEABLE_PRODUCT")
@Extension(vendorName="datanucleus", key="view-definition", value="CREATE VIEW SALEABLE_PRODUCT
(
    {this.id},
    {this.name},
    {this.price},
    {this.currency}
) AS
SELECT ID, NAME, CURRENT_PRICE AS PRICE, CURRENCY FROM PRODUCT
WHERE PRODUCT.STATUS_ID = 1")
public class SaleableProduct
{
    String id;
    String name;
    double price;
    String currency;

    public String getId()
    {
        return id;
    }

    public String getName()
    {
        return name;
    }

    public double getPrice()
    {
        return price;
    }

    public String getCurrency()
    {
        return currency;
    }
}

Please note the following

  • We would typically define our class as using "nondurable" identity (@NonDurableId). This is an important step since rows of the View typically don't operate in the same way as rows of a Table, not mapping onto a persisted updateable object as such
  • We've specified the "table", which in this case is the view name - otherwise DataNucleus would create a name for the view based on the class name.
  • We've defined a DataNucleus extension view-definition that defines the view for this class. If the view doesn't already exist it doesn't matter since DataNucleus (when used with autoCreateSchema) will execute this construction definition.
  • The view-definition can contain macros utilising the names of the fields in the class, and hence borrowing their column names (if we had defined column names for the fields of the class).
  • You can also utilise other classes in the macros, and include them via a DataNucleus MetaData extension view-imports (not shown here)
  • If your View already exists you are still required to provide a view-definition even though DataNucleus will not be utilising it, since it also uses this attribute as the flag for whether it is a View or a Table - just make sure that you specify the "table" also in the MetaData.

We can now utilise this class within normal DataNucleus JPA querying operation.

Query<MyViewClass> q = em.createQuery("SELECT p FROM SaleableProduct p", SaleableProduct.class);
List<MyViewClass> results = q.getResultList();

Hopefully that has given enough detail on how to create and access views from with a DataNucleus-enabled application.

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.

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
Entity 1 [embedded]
Entity[] 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.

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


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

JDBC Type Supported Restrictions
ARRAY Only for PostgreSQL array type
BIGINT
BINARY Only for geospatial 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 geospatial types on Oracle
TIME
TIMESTAMP
TINYINT
VARBINARY
VARCHAR