Wednesday, December 8, 2010

Hibernate-Secondary Table Annotation..

When using Hibernate with annotation, SecondaryTable needs to be used carefully while using the Discriminator. We assume that there is a table called Inventory which is the parent table that has common elements including Id, createDate, modifiedDate, SKU#, and productType.
The other tables, which are children of the Inventory table, are TV_Inventory, DVD_inventory etc.. In this example, we consider TV_Inventory which inherits from Inventory (in terms of objects).

@Entity
@Table(name = "INVENTORY")
@Inheritance(strategy = InheritanceType.JOINED)
@DiscriminatorColumn(name = "PRODUCT_TYPE", discriminatorType = DiscriminatorType.STRING, length = 20)
@SequenceGenerator(name = "InventorySequence", sequenceName = "INV_SEQ", allocationSize = 1)



public abstract class Inventory {
 // getters and setters with other annotations for the columns and relevant methods.
}
 
Consider the child object associated with the table TV_INVENTORY. The outline is shown below:
 
@Entity
@DiscriminatorValue("TV")

@SecondaryTable(name = "TV_INVENTORY")

public class TVInventory extends Inventory {
              private Location loc;      //LOCATION Table entity
           // Other relevant columns, getters setters and methods.
}


In using the above definition and annotations, we will get the following error (assuming we are using Oracle DB):

SELECT
<<<< columns from Entities >>>
from
TV_Inventory invento0_,
INVENTORY ainvento0_1_,
LOCATION inventoryl2_
where
invento0_.LOCATION_SEQ=inventoryl2_.LOCATION_SEQ(+)
and ainvento0_.INVENTORY_ID=ainvento0_1_.INVENTORY_ID
and ainvento0_.INVENTORY_ID=?

[10:55:46:218] org.hibernate.jdbc.AbstractBatcher DEBUG about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
[10:55:46:218] org.hibernate.util.JDBCExceptionReporter DEBUG could not load an entity: [com.hibernate.inv.model.aInventory#365746]
[The generated hibernate QUERY......]
java.sql.SQLException: ORA-00942: table or view does not exist
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)


This can be fixed by NOT using SecondaryTable. So now the TVInventory class is re-defined as:

@Entity
@DiscriminatorValue("TV")
@Table(name="TV_INVENTORY")
public class TVInventory extends Inventory {

            private Location loc; //LOCATION Table entity

}

...and it works fine without errors. Now assume that for each TV in our inventory we have features of TV stored in another table, say TV_FEATURES. Then we can use the SecondaryTable annotation as follows:

@Entity

@DiscriminatorValue("TV")
@Table(name="TV_INVENTORY")
@SecondaryTable(name="TV_FEATURES")
public class TVInventory extends Inventory {
                   @OneToMany(fetch = FetchType.EAGER)
                  @JoinColumn(name="INVENTORY_ID")
                   private List tvFeatures;  
}
 
The TVFeature class is defined below:
 
@Entity
@Table(name="TV_FEATURES")
public class TVFeature {
       ///getters and setters for columns
}
 
In the above scenarios, we are not using any inheritance. Instead we are joining the tables together on the inventory_id and defining the relationship between the TVInventory and TVFeature objects. Therefore when using inheritance, SecondaryTable codes can get a little complex in Hibernate.