Jump to: navigation, search

Difference between revisions of "EclipseLink/UserGuide/JPA/Basic JPA Development/Entities/SecondaryTable"

(New page: {{EclipseLink_UserGuide |info=y |toc=y |eclipselink=y |eclipselinktype=JPA |api=y |apis= * [http://www.eclipse.org/eclipselink/api/latest/javax/persistence/SecondaryTable.html @SecondaryTa...)
 
 
(22 intermediate revisions by the same user not shown)
Line 7: Line 7:
 
|apis=
 
|apis=
 
* [http://www.eclipse.org/eclipselink/api/latest/javax/persistence/SecondaryTable.html @SecondaryTable]
 
* [http://www.eclipse.org/eclipselink/api/latest/javax/persistence/SecondaryTable.html @SecondaryTable]
 +
* [http://www.eclipse.org/eclipselink/api/latest/javax/persistence/SecondaryTables.html @SecondaryTables]
 +
* [http://www.eclipse.org/eclipselink/api/latest/javax/persistence/PrimaryKeyJoinColumn.html @PrimaryKeyJoinColumn]
 +
* [http://www.eclipse.org/eclipselink/api/latest/javax/persistence/PrimaryKeyJoinColumns.html @PrimaryKeyJoinColumns]
 +
|nativeapi=y
 +
|nativeapis=
 +
* [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/descriptors/ClassDescriptor.html ClassDescriptor]
 +
** [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/descriptors/ClassDescriptor.html#addTableName(java.lang.String) addTable()]
 +
** [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/descriptors/ClassDescriptor.html#addForeignKeyFieldNameForMultipleTable(java.lang.String,%20java.lang.String) addForeignKeyFieldName ForMultipleTable()]
 +
* [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/descriptors/DescriptorQueryManager.html DescriptorQueryManager]
 +
** [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/descriptors/DescriptorQueryManager.html#setMultipleTableJoinExpression(org.eclipse.persistence.expressions.Expression) setMultipleTableJoinExpression()]
 +
* [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/expressions/Expression.html Expression]
 
}}
 
}}
  
 
=@SecondaryTable=
 
=@SecondaryTable=
You can use the <tt>@Table</tt> annotation or <code><nowiki><table></nowiki></code> XML element to configure the table for an entity to do the following:
+
You can use the <tt>@SecondaryTable</tt> annotation or <code><nowiki><secondary-table></nowiki></code> XML element to configure an entity to map to multiple tables.  When an entity instance is persisted, it will insert into each of the tables.  When an entity instance is read, the tables will be joined.  This allows for the entity to define mappings that make use of any of the columns in any of the tables.
* define the name of the entity's table
+
 
* define the schema or catalog if your table is defined in a different schema than your connection and requires to be prefixed
+
Secondary tables should only be used when the entity logically has its data spread across multiple tables.  If the tables represent relationships, then this is better modeled using relationship mappings such as <code>OneToOne</code> and <code>OneToMany</code>.
* define unique constraints for DDL generation
+
 
 +
By default the secondary table is joined to the primary table's id columns.  This assumes that the secondary table has the same id columns defined with the same name.
 +
If the secondary table uses different names for the id columns, then a set or <tt>@PrimaryKeyJoinColumn</tt>s must be provided.  <tt>@PrimaryKeyJoinColumn</tt> can also be used for <tt>JOINED</tt> inheritance to specify how the child table is joined to the parent table.  <tt>@PrimaryKeyJoinColumn</tt> still requires that the secondary table have all of the id columns of the primary table, for more advanced multiple table joins see [[#Advanced Multiple Table Configuration|Advanced Multiple Table Configuration]].
  
{{EclipseLink_Spec|section=Section 11.1.xx "Table Annotation"}}
 
 
{{EclipseLink_AttributeTable
 
{{EclipseLink_AttributeTable
|caption=@Table Attributes
+
|caption=@SecondaryTable Attributes
 
|content=<tr>
 
|content=<tr>
 
  <td>'''<tt>name</tt>'''</td>
 
  <td>'''<tt>name</tt>'''</td>
 
  <td>The name of the table
 
  <td>The name of the table
  <td>entity name (as uppercase)</td>
+
  <td></td>
  <td>No</td>
+
  <td>Yes</td>
 
</tr>
 
</tr>
 
<tr>
 
<tr>
Line 45: Line 57:
 
}}
 
}}
  
 +
{{EclipseLink_Spec|section=Section 11.1.42 "SecondaryTable Annotation"}}
 +
 +
{{EclipseLink_AttributeTable
 +
|caption=@PrimaryKeyJoinColumn Attributes
 +
|content=<tr>
 +
<td>'''<tt>name</tt>'''</td>
 +
<td>The name foreign key column in the secondary table that references the id column in the primary table
 +
<td>id column</td>
 +
<td>No</td>
 +
</tr>
 +
<tr>
 +
<td>'''<tt>referencedColumnName</tt>'''</td>
 +
<td>A name of the id column in the primary table that is being referenced.  This is only required for composite ids.
 +
<td>id column</td>
 +
<td>No</td>
 +
</tr>
 +
<tr>
 +
<td>'''<tt>columnDefinition</tt>'''</td>
 +
<td>Optional column description for use with DDL generation.</td>
 +
<td></td>
 +
<td>No</td>
 +
</tr>
 +
}}
 +
 +
{{EclipseLink_Spec|section=Section 11.1.40 "PrimaryKeyJoinColumn Annotation"}}
  
 
The following example shows how to use this annotation to specify the table for <code>Employee</code>.
 
The following example shows how to use this annotation to specify the table for <code>Employee</code>.
Line 52: Line 89:
 
<source lang="java">
 
<source lang="java">
 
@Entity
 
@Entity
@Table(name="EMP", uniqueConstraints = {@UniqueConstraint(columnNames={"SSN"})})
+
@Table(name="EMP")
 +
@SecondaryTable(name="SALARY")
 
public class Employee implements Serializable {
 
public class Employee implements Serializable {
 
     ...
 
     ...
Line 67: Line 105:
 
<source lang="xml">
 
<source lang="xml">
 
<entity class="Employee">
 
<entity class="Employee">
     <table name="EMP">
+
     <table name="EMP"/>
        <unique-constraint>
+
    <secondary-table name="SALARY"/>
            <column-name>SSN</column-name>
+
        </unique-constraint>
+
    </table>
+
 
     <attributes>
 
     <attributes>
 
         <id name="id"/>
 
         <id name="id"/>
Line 80: Line 115:
 
<br>
 
<br>
  
==Views==
+
==Advanced Multiple Table Configuration==
It is possible to map an entity to a database viewTo do this simply give the view name for the name in the <code>@Table</code> annotation.
+
JPA requires that secondary tables contain the id of the entity.  EclipseLink allows for the multiple table join to be based on foreign keys, or on specific criteria.
  
==Case Sensitively, Reserved Words and Special Characters==
+
To define a secondary table that is joined through a foreign key, the <code>ClassDescriptor.addForeignKeyFieldNameForMultipleTable()</code> API is used.  This column name given to this API should be prefixed by its table name, this allows for foreign keys from any of the secondary tables to be used in the multiple table joinNote that all of the column values still need to be mapped at least once.
Some databases are case sensitive, but most are not.  In general it is best to uppercase all table/column names to be portable.
+
Most databases allow mixed cased names if you put the name in quotes.  To do this use the <code>"</code> character when defining the table's name.  Quotes also allow the usage of spaces, reserved words, and some special characters on most databases.
+
  
======''Example: Using quoted @Table''======
+
If the multiple table join is more complex, an EclipseLink <code>Expression</code> can be used to define the join.  The <code>Expression</code> is set on the <code>ClassDescriptor</code>'s query manager using the <code>DescriptorQueryManager.setMultipleTableJoinExpression()</code> API.  It is even possible to define a join <code>Expression</code> that make use of an outer-join on certain databases.
 +
 
 +
In certain advanced cases multiple tables can be encapsulated using a database view.  This allows for any table join to be define in SQL and for the entity to be simply mapped to the view.  To map an entity to a view, just use the view name instead of the table name.  If the view is not updateable, your database may support instead of triggers to allow updates.
 +
 
 +
======''Example: Using a DescriptorCustomizer to configure a secondary table joined through a foreign key''======
 
<source lang="java">
 
<source lang="java">
 
@Entity
 
@Entity
@Table(name="\"Employee Data\"")
+
@Table(name="EMP")
 +
@SecondaryTable(name="EMP_INFO")
 +
@Customizer(EmployeeCustomizer.class)
 
public class Employee implements Serializable {
 
public class Employee implements Serializable {
    ...
 
 
     @Id
 
     @Id
     public Long getId() {
+
     private Long id;
         return id;
+
    @Basic
 +
    @Column(name="ID", table="EMP_INFO")
 +
    private String infoId;
 +
    ...
 +
}
 +
</source>
 +
 
 +
<source lang="java">
 +
public class EmployeeCustomizer implements DescriptorCustomizer {
 +
    public void customize(ClassDescriptor descriptor) {
 +
         descriptor.getAdditionalTablePrimaryKeyFields().clear();
 +
        descriptor.getMultipleTableForeignKeys()().clear();
 +
        descriptor.addForeignKeyFieldNameForMultipleTable("EMP.INFO_ID", "EMP_INFO.ID");
 
     }
 
     }
 +
}
 +
</source>
 +
 +
======''Example: Using a DescriptorCustomizer to configure a secondary table joined through an Expression''======
 +
<source lang="java">
 +
@Entity
 +
@Table(name="EMP")
 +
@SecondaryTable(name="EMP_INFO")
 +
@Customizer(EmployeeCustomizer.class)
 +
public class Employee implements Serializable {
 +
    @Id
 +
    private Long id;
 +
    @Basic
 +
    @Column(name="ID", table="EMP_INFO")
 +
    private String infoId;
 
     ...
 
     ...
 +
}
 +
</source>
 +
 +
<source lang="java">
 +
public class EmployeeCustomizer implements DescriptorCustomizer {
 +
    public void customize(ClassDescriptor descriptor) {
 +
        ExpressionBuilder emp = new ExpressionBuilder();
 +
        Expression join = emp.getField("EMP.INFO_ID").equal(emp.getField("EMP_INFO.ID").and(emp.getField("EMP_INFO.ARCHIVE").equal(false));
 +
        descriptor.getQueryManager().setMultipleTableJoinExpression(join);
 +
    }
 
}
 
}
 
</source>
 
</source>
Line 103: Line 178:
 
{{EclipseLink_JPA
 
{{EclipseLink_JPA
 
|previous=[[EclipseLink/UserGuide/JPA/Basic JPA Development/Entities/Table|@Table]]
 
|previous=[[EclipseLink/UserGuide/JPA/Basic JPA Development/Entities/Table|@Table]]
|next=    [[EclipseLink/UserGuide/JPA/Basic JPA Development/Entities/Mapping/Basic Mapping|Basic Mapping]]
+
|next=    [[EclipseLink/UserGuide/JPA/Basic JPA Development/Entities/Inheritance|@Inheritance]]
 
|up=      [[EclipseLink/UserGuide/JPA/Basic JPA Development/Entities/Creating_and_Configuring_Entities|Configuring Entities]]
 
|up=      [[EclipseLink/UserGuide/JPA/Basic JPA Development/Entities/Creating_and_Configuring_Entities|Configuring Entities]]
 
|version=2.2.0 DRAFT}}
 
|version=2.2.0 DRAFT}}

Latest revision as of 14:27, 4 August 2011

EclipseLink JPA

@SecondaryTable

You can use the @SecondaryTable annotation or <secondary-table> XML element to configure an entity to map to multiple tables. When an entity instance is persisted, it will insert into each of the tables. When an entity instance is read, the tables will be joined. This allows for the entity to define mappings that make use of any of the columns in any of the tables.

Secondary tables should only be used when the entity logically has its data spread across multiple tables. If the tables represent relationships, then this is better modeled using relationship mappings such as OneToOne and OneToMany.

By default the secondary table is joined to the primary table's id columns. This assumes that the secondary table has the same id columns defined with the same name. If the secondary table uses different names for the id columns, then a set or @PrimaryKeyJoinColumns must be provided. @PrimaryKeyJoinColumn can also be used for JOINED inheritance to specify how the child table is joined to the parent table. @PrimaryKeyJoinColumn still requires that the secondary table have all of the id columns of the primary table, for more advanced multiple table joins see Advanced Multiple Table Configuration.

@SecondaryTable Attributes
Attribute Description Default Required?
name The name of the table Yes
catalog A String catalog name. Default catalog for database No
schema The String name of the schema. Default schema of the database No
uniqueConstraints This is used only by DDL generation. By default only a primary key and foreign key constraints are defined, if desired set the value of this attribute to an array of one or more UniqueConstraint instances.
Elug javaspec icon.gif

For more information, see Section 11.1.49 "UniqueConstraint Annotation" in the JPA Specification.

No additional constraints No
Elug javaspec icon.gif

For more information, see Section 11.1.42 "SecondaryTable Annotation" in the JPA Specification.

@PrimaryKeyJoinColumn Attributes
Attribute Description Default Required?
name The name foreign key column in the secondary table that references the id column in the primary table id column No
referencedColumnName A name of the id column in the primary table that is being referenced. This is only required for composite ids. id column No
columnDefinition Optional column description for use with DDL generation. No
Elug javaspec icon.gif

For more information, see Section 11.1.40 "PrimaryKeyJoinColumn Annotation" in the JPA Specification.

The following example shows how to use this annotation to specify the table for Employee.

Example: Using @Table
@Entity
@Table(name="EMP")
@SecondaryTable(name="SALARY")
public class Employee implements Serializable {
    ...
    @Id
    public Long getId() {
        return id;
    }
    ...
}
Example: Using <table-generator>
<entity class="Employee">
    <table name="EMP"/>
    <secondary-table name="SALARY"/>
    <attributes>
        <id name="id"/>
        ...
    </attributes>
</entity>


Advanced Multiple Table Configuration

JPA requires that secondary tables contain the id of the entity. EclipseLink allows for the multiple table join to be based on foreign keys, or on specific criteria.

To define a secondary table that is joined through a foreign key, the ClassDescriptor.addForeignKeyFieldNameForMultipleTable() API is used. This column name given to this API should be prefixed by its table name, this allows for foreign keys from any of the secondary tables to be used in the multiple table join. Note that all of the column values still need to be mapped at least once.

If the multiple table join is more complex, an EclipseLink Expression can be used to define the join. The Expression is set on the ClassDescriptor's query manager using the DescriptorQueryManager.setMultipleTableJoinExpression() API. It is even possible to define a join Expression that make use of an outer-join on certain databases.

In certain advanced cases multiple tables can be encapsulated using a database view. This allows for any table join to be define in SQL and for the entity to be simply mapped to the view. To map an entity to a view, just use the view name instead of the table name. If the view is not updateable, your database may support instead of triggers to allow updates.

Example: Using a DescriptorCustomizer to configure a secondary table joined through a foreign key
@Entity
@Table(name="EMP")
@SecondaryTable(name="EMP_INFO")
@Customizer(EmployeeCustomizer.class)
public class Employee implements Serializable {
    @Id
    private Long id;
    @Basic
    @Column(name="ID", table="EMP_INFO")
    private String infoId;
    ...
}
public class EmployeeCustomizer implements DescriptorCustomizer {
    public void customize(ClassDescriptor descriptor) {
        descriptor.getAdditionalTablePrimaryKeyFields().clear();
        descriptor.getMultipleTableForeignKeys()().clear();
        descriptor.addForeignKeyFieldNameForMultipleTable("EMP.INFO_ID", "EMP_INFO.ID");
    }
}
Example: Using a DescriptorCustomizer to configure a secondary table joined through an Expression
@Entity
@Table(name="EMP")
@SecondaryTable(name="EMP_INFO")
@Customizer(EmployeeCustomizer.class)
public class Employee implements Serializable {
    @Id
    private Long id;
    @Basic
    @Column(name="ID", table="EMP_INFO")
    private String infoId;
    ...
}
public class EmployeeCustomizer implements DescriptorCustomizer {
    public void customize(ClassDescriptor descriptor) {
        ExpressionBuilder emp = new ExpressionBuilder();
        Expression join = emp.getField("EMP.INFO_ID").equal(emp.getField("EMP_INFO.ID").and(emp.getField("EMP_INFO.ARCHIVE").equal(false));
        descriptor.getQueryManager().setMultipleTableJoinExpression(join);
    }
}

Eclipselink-logo.gif
Version: 2.2.0 DRAFT
Other versions...