JPA @SecondaryTables: Mapping Entities to Multiple Tables

In JPA, the @SecondaryTables annotation allows an entity to be mapped across multiple database tables. This is especially useful for existing data models where related data is stored in separate tables.

Scenario Overview

We demonstrate how to map an Address entity across three tables:

1. T_ADDRESS: Primary table for basic address details.
2. T_CITY: Stores city-related information.
3. T_COUNTRY: Stores country-related information.

Database Table Structure

1. Primary Table: T_ADDRESS

CREATE TABLE T_ADDRESS (
    ID NUMBER NOT NULL,
    STREET1 VARCHAR2(245),
    STREET2 VARCHAR2(245),
    CONSTRAINT T_ADDRESS_PK PRIMARY KEY (ID)
);

2. Secondary Table: T_CITY

CREATE TABLE T_CITY (
    ID NUMBER NOT NULL,
    CITY VARCHAR2(45),
    STATE VARCHAR2(50),
    ZIPCODE VARCHAR2(10),
    CONSTRAINT T_CITY_PK PRIMARY KEY (ID),
    CONSTRAINT T_CITY_FK FOREIGN KEY (ID) REFERENCES T_ADDRESS (ID)
);

3. Secondary Table: T_COUNTRY

CREATE TABLE T_COUNTRY (
    ID NUMBER NOT NULL,
    COUNTRY VARCHAR2(50),
    CONSTRAINT T_COUNTRY_PK PRIMARY KEY (ID),
    CONSTRAINT T_COUNTRY_FK FOREIGN KEY (ID) REFERENCES T_ADDRESS (ID)
);

JPA Entity Mapping

Entity: TAddress

@Entity
@Table(name = "T_ADDRESS")
@SecondaryTables({
    @SecondaryTable(name = "T_COUNTRY"),
    @SecondaryTable(name = "T_CITY")
})
public class TAddress implements Serializable {

    @Id
    @Column(name = "ID")
    private Integer id;

    @Column(name = "STREET1")
    private String street1;

    @Column(name = "STREET2")
    private String street2;

    @Column(table = "T_COUNTRY", name = "COUNTRY")
    private String country;

    @Column(table = "T_CITY", name = "CITY")
    private String city;

    @Column(table = "T_CITY", name = "STATE")
    private String state;

    @Column(table = "T_CITY", name = "ZIPCODE")
    private String zipcode;

    // Getters, Setters, hashCode(), equals(), and toString()
}

JUnit Test Cases

1. Test Insert with @SecondaryTables

@Test
@Ignore
public void testSecondaryTableInsert() {
    TAddress tAddress = new TAddress();
    tAddress.setId(10);
    tAddress.setStreet1("Street 1 10");
    tAddress.setStreet2("Street 2 10");
    tAddress.setCountry("Country 10");
    tAddress.setCity("City 10");
    tAddress.setState("State 10");
    tAddress.setZipcode("1010");
    
    em.persist(tAddress);
    System.out.println("Inserted TAddress: " + tAddress);
}

2. Test Select with @SecondaryTables

@Test
@Ignore
public void testSecondaryTableSelect() {
    TAddress tAddress = em.find(TAddress.class, 10);
    assertNotNull(tAddress);
    System.out.println("Fetched TAddress: " + tAddress);
}

3. Test Update with @SecondaryTables

@Test
@Ignore
public void testSecondaryTableUpdate() {
    TAddress tAddress = em.find(TAddress.class, 10);
    assertNotNull(tAddress);

    tAddress.setStreet1("Updated Street 1");
    tAddress.setCountry("Updated Country");
    tAddress.setCity("Updated City");
    
    em.merge(tAddress);
    System.out.println("Updated TAddress: " + tAddress);
}

4. Test Delete with @SecondaryTables

@Test
@Ignore
public void testSecondaryTableDelete() {
    TAddress tAddress = em.find(TAddress.class, 10);
    assertNotNull(tAddress);
    
    em.remove(tAddress);
    System.out.println("Deleted TAddress: " + tAddress);
}

Key Points

1. Mapping Across Multiple Tables:
   • Use @SecondaryTable or @SecondaryTables to define additional tables for an entity.
2. Column Mapping:
   • Use the @Column annotation with the table parameter to specify the table for each attribute.
3. Primary Key Consistency:
   • All tables (primary and secondary) must share the same primary key for the entity.

Advantages of @SecondaryTables

1. Better Data Normalization:
   • Allows logical grouping of data across tables while maintaining a single entity representation.
2. Simplifies Entity Management:
   • Access data spread across multiple tables using a single entity class.
3. Flexible for Existing Schemas:
   • Easily adapts to legacy database schemas with multiple related tables.

Conclusion

The JPA @SecondaryTables annotation provides a powerful way to map entities to multiple tables, especially for existing databases with normalized data. The provided example demonstrates how to use this feature effectively with CRUD operations.

Would you like additional examples or assistance with advanced JPA mappings? 😊

Ahmad Gohar

With over 18 years of experience in software architecture, Java technologies, and leadership, I specialize in crafting scalable, future-proof solutions for global organizations. Whether it’s transforming legacy systems, building cutting-edge cloud-native applications, or mentoring teams to excel, I’m committed to delivering value-driven results.

See Full Bio