Multitable Mappings with @SecondaryTable

Multitable Mappings with @SecondaryTable

Sometimes you have to deal with one logical entity that is stored in two different tables. You want one entity bean class to represent your object, but it is mapped into two different tables because you're working with a legacy database model. Java Persistence allows you to map an entity bean class to one or more tables using the @javax.persistence.SecondaryTable annotation. For example, let's say our Customer bean has properties that define the address of the Customer, but the address data is stored in a separate table. Here's what the tables would look like:

create table CUSTOMER_TABLE 

(
    CUST_ID integer Primary Key Not Null,
    FIRST_NAME varchar(20) not null,
    LAST_NAME varchar(50) not null
);

create table ADDRESS_TABLE 

(
   ADDRESS_ID integer primary key not null,
   STREET varchar(255) not null,
   CITY varchar(255) not null,
   STATE varchar(255) not null
);

To use the @SecondaryTable annotation, the primary key columns of the ADDRESS_TABLE must be joinable with one or more columns in the CUSTOMER_TABLE:

public @interface SecondaryTable
{
   String name( );
   String catalog( ) default "";
   String schema( ) default "";
   PrimaryKeyJoinColumn[] pkJoinColumns( ) default {};
   UniqueConstraint[] uniqueConstraints( ) default {};
}

public @interface PrimaryKeyJoinColumn
{
   String name( ) default "";
   String referencedColumnName( ) default "";
   String columnDefinition( ) default "";
}

The @SecondaryTable annotation looks a lot like the @Table annotation, except it additionally has a pkJoinColumns( ) attribute defined. In the Customer bean class, you would define this annotation and specify that the primary key of the ADDRESS_TABLE was using the embedded @PrimaryKeyJoinColumn annotation. The name( ) attribute of the @PrimaryKeyJoinColumn annotation represents the column in the ADDRESS_TABLE that you will use in the join. The referencedColumnName( ) attribute represents the column name in the CUSTOMER_TABLE that is used to join with the ADDRESS_TABLE.

package com.titan.domain;

import javax.persistence.*;
import com.acme.imaging.JPEG;

@Entity
@Table(name="CUSTOMER_TABLE")
@SecondaryTable(name="ADDRESS_TABLE",
                pkJoinColumns={
                   @PrimaryKeyJoinColumn(name="ADDRESS_ID")}) 

public class Customer implements java.io.Serializable {
...

The @PrimaryKeyJoinColumn specifies the column in the ADDRESS_TABLE that you will join with the primary key of the CUSTOMER _TABLE . In this case, it is ADDRESS_ID. We do not need to specify the referencedColumnName( ) attribute of this annotation because it can default to the Customer entity's primary-key column.

The next step is to map the street, city, and state properties to columns in the ADDRESS_TABLE. If you remember the full @Column annotation, one of the attributes we did not go over fully is the table( ) attribute. You use this to map the address properties to your secondary table:

package com.titan.domain;

import javax.persistence.*;
import com.acme.imaging.JPEG;

@Entity
@Table(name="CUSTOMER_TABLE")
@SecondaryTable(name="ADDRESS_TABLE",
                pkJoinColumns={
                   @PrimaryKeyJoinColumn(name="ADDRESS_ID")})
public class Customer implements java.io.Serializable {
   private long id;
   private String firstName;
   private String lastName;
   private String street;
   private String city;
   private String state;
...
@Column(name="STREET", table="ADDRESS_TABLE")
   public String getStreet( ) { return street; }
   public void setStreet(String street) { this.street = street; }

@Column(name="CITY", table="ADDRESS_TABLE")
   public String getCity( ) { return city; }
   public void setCity(String city) { this.city = city; }

@Column(name="STATE", table="ADDRESS_TABLE")
   public String getState( ) { return state; }
   public void setState(String state) { this.state = state; }
...

What do you do if you have more than one secondary table? For example, let's say that you want to embed credit card properties, but this information was also stored in another table. In that instance, you would use the @SecondaryTables annotation:

package com.titan.domain;

import javax.persistence.*;
import com.acme.imaging.JPEG;

@Entity
@Table(name="CUSTOMER_TABLE")
@SecondaryTables({
    @SecondaryTable(name="ADDRESS_TABLE",
            pkJoinColumns={@PrimaryKeyJoinColumn (name="ADDRESS_ID")}),
    @SecondaryTable(name="CREDIT_CARD_TABLE",
            pkJoinColumns={@PrimaryKeyJoinColumn (name="CC_ID")})
})
public class Customer

You would then match the properties with their appropriate @Column.table( ) attributes set.

Let's look at the XML mapping for this multitable mapping:

<entity-mappings>
   <entity class="com.titan.domain.Customer" access="PROPERTY">
      <table name="CUSTOMER_TABLE"/>
   <secondary-table name="ADDRESS_TABLE">
         <primary-key-join-column name="ADDRESS_ID"/>
      </secondary-table>
      <secondary-table name="CREDIT_CARD_TABLE">
         <primary-key-join-column name="CC_ID"/>
      </secondary-table>
      <attributes>
         <id name="id">
            <generated-value/>
         </id>
         <basic name="street">
            <column name="STREET"
                    table 
="ADDRESS_TABLE"/>
         </basic>
...
      </attributes>
   </entity>
</entity-mappings>

The <secondary-table> element can be declared multiple times within an <entity> element. The <primary-key-join-column> is a subelement of <secondary-table> and has the name, referenced-column-name, and column-definition attributes. You use the <column> table attribute to map the attribute to the secondary table of your choice.