JPA @SecondaryTables

Up to now, I have assumed that an entity gets mapped to a single table, also known as a  primary table. But sometimes when you have an existing data model, you need to spread the data across multiple tables, or secondary tables. To do this, you need to use the annotation @SecondaryTable to associate a secondary table to an entity or @SecondaryTables (with an “s”) for several secondary tables. You can distribute the data of an entity across columns in both the primary table and the secondary tables simply by defining the secondary tables with anno-tations and then specifying for each attribute which table it is in (with the @Column annotation, which I’ll describe in the “Attributes” section in more detail).

Example below shows an Address entity mapping its attributes in one primary table and two secondary tables.

TABLE CREATION:

T_ADDRESS Table

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

T_CITY Table

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

T_COUNTRY Table

CREATE TABLE T_COUNTRY 
(
  ID NUMBER NOT NULL 
, COUNTRY VARCHAR2(50) 
, CONSTRAINT T_COUNTRY_PK PRIMARY KEY 
  (
    ID 
  )
  ENABLE 
);

SEQUENCES AND TRIGGERS CREATION:

CREATE SEQUENCE T_ADDRESS_SEQ;

CREATE TRIGGER T_ADDRESS_TRG 
BEFORE INSERT ON T_ADDRESS 
FOR EACH ROW 
BEGIN
    IF :NEW.ID IS NULL THEN
      SELECT T_ADDRESS_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
    END IF;
END;
/

CONSTRAINT:

T_CITY Table

ALTER TABLE T_CITY
ADD CONSTRAINT T_CITY_FK FOREIGN KEY
(
  ID 
)
REFERENCES T_ADDRESS
(
  ID 
)
ENABLE;

T_COUNTRY Table

ALTER TABLE T_COUNTRY
ADD CONSTRAINT T_COUNTRY_FK FOREIGN KEY
(
  ID 
)
REFERENCES T_ADDRESS
(
  ID 
)
ENABLE;

INSERT TEST DATA:

T_ADDRESS Table

REM INSERTING into T_ADDRESS
Insert into T_ADDRESS (ID,STREET1,STREET2) values (1,'STREET_1 1','STREET_2 1');
Insert into T_ADDRESS (ID,STREET1,STREET2) values (2,'STREET_1 2','STREET_2 2');
Insert into T_ADDRESS (ID,STREET1,STREET2) values (3,'STREET_1 3','STREET_2 3');

T_CITY Table

REM INSERTING into T_CITY
Insert into T_CITY (ID,CITY,STATE,ZIPCODE) values (1,'CITY 1','STATE 1','111');
Insert into T_CITY (ID,CITY,STATE,ZIPCODE) values (2,'CITY 2','STATE 2','222');
Insert into T_CITY (ID,CITY,STATE,ZIPCODE) values (3,'CITY 3','STATE 3','333');

T_COUNTRY Table

REM INSERTING into T_COUNTRY
Insert into T_COUNTRY (ID,COUNTRY) values (1,'COUNTRY 1');
Insert into T_COUNTRY (ID,COUNTRY) values (2,'COUNTRY 2');
Insert into T_COUNTRY (ID,COUNTRY) values (3,'COUNTRY 3');

CLASS CREATION:

TAddress Class

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

    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @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;
    
    //generate getters, setters, toString(), hashCode(),equals()
}

JUNIT TEST CASE:

public class InheritanceJUnit {
    
    static EntityManagerFactory emf;
    static EntityManager em;
    static EntityTransaction trx;
    
    @BeforeClass
    public static void initEntityManager() throws Exception {
        emf = Persistence.createEntityManagerFactory("JavaApplicationJPAPU");
        em = emf.createEntityManager();
        trx = em.getTransaction();
    }
    
    @AfterClass
    public static void closeEntityManager() throws Exception {
        if (em != null) {
            em.close();
        }
        if (emf != null) {
            emf.close();
        }
    }
    
    @Before
    public void initTransaction() throws Exception {
        trx.begin();
    }
    
    @After
    public void endTransaction() throws Exception {
        if (!trx.getRollbackOnly()) {
            trx.commit();
        }
    }

    @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 1 10");
        
        tAddress.setCity("City 10");
        tAddress.setState("State 10");
        tAddress.setZipcode("1010");
        assertNotNull(tAddress);
        em.persist(tAddress);
        System.out.println("TAddress : " + tAddress);
        
        TAddress tAddress2 = new TAddress();
        tAddress2.setId(11);
        tAddress2.setStreet1("Street 1 11");
        tAddress2.setStreet2("Street 2 11");
        
        tAddress2.setCountry("Country 1 11");
        
        
        assertNotNull(tAddress2);
        em.persist(tAddress2);
        System.out.println("TAddress2 : " + tAddress2);
        
    }

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

        tAddress.setStreet1("Street 1 10 edited");
        tAddress.setStreet2("Street 2 10 edited");
        
        tAddress.setCountry("Country 1 10 edited");
        
        tAddress.setCity("City 10 edited");
        tAddress.setState(null);
        tAddress.setZipcode("1010");
        em.merge(tAddress);
        System.out.println("TAddress : " + tAddress);
        
        TAddress tAddress2 = em.find(TAddress.class, 11);
        assertNotNull(tAddress2);
        
        tAddress2.setStreet1("Street 1 11 edited");
        tAddress2.setStreet2("");
        
        tAddress2.setCountry("Country 1 11 edited");
        
        tAddress2.setCity("City 10 edited");
        tAddress2.setState("State 10 edited");
        tAddress2.setZipcode(null);
        
        em.merge(tAddress2);
        System.out.println("TAddress2 : " + tAddress2);
    }
    
    @Test
    @Ignore
    public void testSecondaryTableDelete() {
        
        TAddress tAddress = em.find(TAddress.class, 10);
        assertNotNull(tAddress);
        
        em.detach(tAddress);
        System.out.println("TAddress : " + tAddress);
        
        TAddress tAddress2 = em.find(TAddress.class, 11);
        assertNotNull(tAddress2);
        
        em.detach(tAddress2);
        System.out.println("TAddress2 : " + tAddress2);
        
    }
}