Tables

A table is the basic persist structure of a relational database. A table contains a list of columns which define the table's structure, and a list of rows that define the table's data. Each column has a specific type and generally size. The standard set of relational types are limited to basic types including numeric, character, date-time, and binary (although most modern databases have additional types and typing systems). Tables can also have constraints that define the rules which restrict the row data, such as primary key, foreign key, and unique constraints. Tables also have other artifacts such as indexes, partitions and triggers.

A typical mapping of a persist class will map the class to a single table. In JPA this is defined through the @Table annotation or <table> XML element. If no table annotation is present, the JPA implementation will auto assign a table for the class. The JPA default table name is the name of the class (minus the package) with the first letter capitalized. Each attribute of the class will be stored in a column in the table.

Example mapping annotations for an entity with a single table

...
@Entity
@Table(name="EMPLOYEE")
public class Employee {
    ...
}

Example mapping XML for an entity with a single table

<entity name="Employee" class="org.acme.Employee" access="FIELD">
    <table name="EMPLOYEE"/>
</entity>

Advanced

Although in the ideal case each class would map to a single table, this is not always possible. Other scenarios include:

Multiple tables : One class maps to 2 or multiple tables.
Sharing tables : 2 or multiple classes are stored in the same table.
Inheritance : A class is involved in inheritance and has an inherited and local table.
Views : A class maps to a view.
Stored procedures : A class maps to a set of stored procedures.
Partitioning : Some instances of a class map to one table, and other instances to another table.
Replication : A class's data is replicated to multiple tables.
History : A class has historical data.

These are all advanced cases, some are handled by the JPA Spec and many are not. The following sections investigate each of these scenarios further and include what is supported by the JPA spec, what can be done to workaround the issue within the spec, and how to use some JPA implementations extensions to handle the scenario.

Multiple tables

Sometimes a class maps to multiple tables. This typically occurs on legacy or existing data models where the object model and data model do not match. It can also occur in inheritance when subclass data is stored in additional tables. Multiple tables may also be used for performance, partitioning or security reasons.

JPA allows multiple tables to be assigned to a single class. The @SecondaryTable and SecondaryTables annotations or <secondary-table> elements can be used. By default the @Id column(s) are assumed to be in both tables, such that the secondary table's @Id column(s) are the primary key of the secondary table and a foreign key to the first table. If the first table's @Id column(s) are not named the same the @PrimaryKeyJoinColumn or <primary-key-join-column> can be used to define the foreign key join condition.

In a multiple table entity, each mapping must define which table the mapping's columns are from. This is done using the table attribute of the @Column or @JoinColumn annotations or XML elements. By default the primary table of the class is used, so you only need to set the table for secondary tables. For inheritance the default table is the primary table of the subclass being mapped.

Example mapping annotations for an entity with multiple tables

...
@Entity
@Table(name="EMPLOYEE")
@SecondaryTable(name="EMP_DATA",
                pkJoinColumns = @PrimaryKeyJoinColumn(name="EMP_ID", referencedColumnName="ID")
               )
public class Employee {
    ...
    @Column(name="YEAR_OF_SERV", table="EMP_DATA")
    private int yearsOfService;

    @OneToOne
    @JoinColumn(name="MGR_ID", table="EMP_DATA", referencedColumnName="ID")
    private Employee manager;
    ...
}

Example mapping XML for an entity with multiple tables

<entity name="Employee" class="org.acme.Employee" access="FIELD">
    <table name="EMPLOYEE"/>
    <secondary-table name="EMP_DATA">
        <primary-key-join-column name="EMP_ID" referenced-column-name="ID"/>
    </secondary-table>
    <attributes>
        ...
        <basic name="yearsOfService">
            <column name="YEAR_OF_SERV" table="EMP_DATA"/>
        </basic>
        <one-to-one name="manager">
            <join-column name="MGR_ID" table="EMP_DATA" referenced-column-name="ID"/>
        </one-to-one>
    </attributes>
</entity>

With the @PrimaryKeyJoinColumn the name refers to the foreign key column in the secondary table and the referencedColumnName refers to the primary key column in the first table. If you have multiple secondary tables, they must always refer to the first table. When defining the table's schema typically you will define the join columns in the secondary table as the primary key of the table, and a foreign key to the first table. Depending how you have defined your foreign key constraints, the order of the tables can be important, the order will typically match the order that the JPA implementation will insert into the tables, so ensure the table order matches your constraint dependencies.

For relationships to a class that has multiple tables the foreign key (join column) always maps to the primary table of the target. JPA does not allow having a foreign key map to a table other than the target object's primary table. Normally this is not an issue as foreign keys almost always map to the id/primary key of the primary table, but in some advanced scenarios this may be an issue. Some JPA products allow the column or join column to use the qualified name of the column (i.e. @JoinColumn(referenceColumnName="EMP_DATA.EMP_NUM"), to allow this type of relationship. Some JPA products may also support this through their own API, annotations or XML.

Multiple tables with foreign keys

Sometimes you may have a secondary table that is referenced through a foreign key from the primary table to the secondary table instead of a foreign key from the secondary table to the primary table. You may even have a foreign key between two of the secondary tables. Consider having an EMPLOYEE and ADDRESS table where EMPLOYEE refers to ADDRESS through an ADDRESS_ID foreign key, and (for some strange reason) you only want a single Employee class that has the data from both tables. The JPA spec does not cover this directly, so if you have this scenario the first thing to consider, if you have the flexibility, is to change your data model to stay within the confines of the spec. You could also change your object model to define a class for each table, in this case an Employee class and an Address class, which is typically the best solution. You should also check with you JPA implementation to see what extensions it supports in this area.

One way to solve the issue is simply to swap your primary and secondary tables. This will result in having the secondary table referencing the primary tables primary key and is within the spec. This however will have side-effects, one being that you now changed the primary key of your object from EMP_ID to ADDRESS_ID, and may have other mapping and querying implications. If you have more than 2 tables this also may not work.

Another option is to just use the foreign key column in the @PrimaryKeyJoinColumn, this will technically be backward, and perhaps not supported by the spec, but may work for some JPA implementations. However this will result in the table insert order not matching the foreign key constraints, so the constraints will need to be removed, or deferred.

It is also possible to map the scenario through a database view. A view could be defined joining the two tables and the class could be mapped to the view instead of the tables. Views are read-only on some databases, but many also allow writes, or allow triggers to be used to handle writes.

Some JPA implementations provide extensions to handle this scenarios.

TopLink, EclipseLink : Provides a proprietary API for its mapping model ClassDescriptor.addForeignKeyFieldNameForMultipleTable() that allows for arbitrary complex foreign keys relationships to be defined among the secondary tables. This can be configured through using a @DescriptorCustomizer annotation and DescriptorCustomizer class.

Multiple table joins

Occasionally the data model and object model do not get along very well at all. The database could be a legacy model and not fit very well with the new application model, or the DBA or object architect may be a little crazy. In these cases you may require advanced multiple table joins.

Examples of these include having two tables related not by their primary or foreign keys, but through some constant or computation. Consider having an EMPLOYEE table and an ADDRESS table, the ADDRESS table has an EMP_ID foreign key to the EMPLOYEE table, but there are several addresses for each employee and only the address with the TYPE of "HOME" is desired. In this case data from both of the tables is desired to be mapped in the Employee object. A join expression is required where the foreign key matches and the constant matches.

Again this scenario could be handled through redesigning the data or object model, or through using a view. Some JPA implementations provide extensions to handle this scenarios.

TopLink, EclipseLink : Provides a proprietary API for its mapping model DescriptorQueryManager.setMultipleTableJoinExpression() that allows for arbitrary complex multiple table joins to be defined. This can be configured through using a @DescriptorCustomizer annotation and DescriptorCustomizer class.

Multiple table outer joins

Another perversion of multiple table mapping is to desire to outer join the secondary table. This may be desired if the secondary may or may not have a row defined for the object. Typically the object should be read-only if this is to be attempted, as writing to a row that may or may not be there can be tricky.

This is not directly supported by JPA, and it is best to reconsider the data model or object model design if faced with this scenario. Again it is possible to map this through a database view, where an outer join is used to join the tables in the view.

Some JPA implementation support using outer joins for multiple tables.

Hibernate : This can be accomplished through using the Hibernate @Table annotation and set its optional attribute to true. This will configure Hibernate to use an outer join to read the table, and will not write to the table if all of the attributes mapping to the table are null.

TopLink, EclipseLink : If the database supports usage of outer join syntax in the where clause (Oracle, Sybase, SQL Server), then the multiple table join expression could be used to configure an outer join to be used to read the table.

Tables with special characters and mixed case

Some JPA providers may have issues with table and column names with special characters, such as spaces. In general it is best to use standard characters, no spaces, and all uppercase names. International languages should be ok, as long as the database and JDBC driver supports the character set.

It may be required to "quote" table and column names with special characters or in some cases with mixed case. For example if the table name had a space it could be defined as the following:

  @Table("\"Employee Data\"")

Some databases support mixed case table and column names, and others are case insensitive. If your database is case insensitive, or you wish your data model to be portable, it is best to use all uppercase names. This is normally not a big deal with JPA where you rarely use the table and column names directly from your application, but can be an issue in certain cases if using native SQL queries.

Table qualifiers, schemas, or creators

A database table may require to be prefixed with a table qualifier, such as the table's creator, or its' namespace, schema, or catalog. Some databases also support linking a table on other database, so the link name can also be a table qualifier.

In JPA a table qualifier can be set on a table through the schema or catalog attribute. Generally it does not matter which attribute is used as both just result in prefixing the table name. Technically you could even include the full name "schema.table" as the table's name and it would work. The benefit of setting the prefix in the schema or catalog is a default table qualifier can be set for the entire persistence unit, also not setting the real table name may impact native SQL queries.

If all of your tables require the same table qualifier, you can set the default in the orm.xml.

Example mapping annotations for an entity with a qualified table

...
@Entity
@Table(name="EMPLOYEE", schema="ACME")
public class Employee {
    ...
}

Example mapping XML for default (entire persistence unit) table qualifier

<entity-mappings>
    <persistence-unit-metadata>
        <persistence-unit-defaults>
            <schema name="ACME"/>
        </persistence-unit-defaults>
    </persistence-unit-metadata>
    ....
</entity-mappings>

Example mapping XML for default (orm file) table qualifier

<entity-mappings>
    <schema name="ACME"/>
    ...
</entity-mappings>