Problem: Load data from multiple tables into a single Java entity.
Approach 1: Use custom SQL loading
@Entity
// Class that will store the query result(s). Class field names are the same than database column names.
@SqlResultSetMapping(name = "customerMapping", entities = @EntityResult(entityClass = CustomerMapping.class))
// Using hibernate annotation to specify out custom SQL queries
@org.hibernate.annotations.NamedNativeQueries(
{
@NamedNativeQuery(name = "loadAllCustomers",
query = "select a.col1,a.col2,b.col1 from table1 a, table2 b where a.col5=b.col5",
resultSetMapping = "customerMapping"),
@NamedNativeQuery(name = "loadByCustNum",
query = "select a.col1,a.col2,b.col1 from table1 a, table2 b where a.col5=b.col5 and a.col1 = ?",
resultSetMapping = "customerMapping")
}
)
// Default SQL query to be used when loading this entity through Hibernate
@Loader(namedQuery = "loadByCustNum")
public class CustomerMapping implements Serializable {
... class body
}
Approach 2: Use one-to-one mapping approach
@Entity
@Table(catalog = "myDB", schema = "dbo", name = "customer_mapping")
public class CustomerMapping implements Serializable {
@OneToOne
// Foreign Key pointing to the corresponding CustomerMaster data.
@JoinColumn(name = "gte_cust_num", unique = true, nullable = true)
// Default hibernate action to be taken when the FK is not null and
// it's not present in the secondary table.
// Hibernate can ignore the error and do nothing or throw an exception.
@NotFound(action=NotFoundAction.IGNORE)
private CustomerMaster masterData;
... class body
}
@Entity
@Table(catalog="myDB", schema="dbo", name="customer_master")
public class CustomerMaster implements Serializable {
... class body
}
NOTE: Be careful when trying to retrieve the objects using HQL:
hql = "from CustomerMapping cmi where cmi.filterField = 'ABCXXX'";
The query above will result in a performance issue if we are retrieving several CustomerMapping objects. This is because hibernate will generate one query to get all the CustomerMapping objects and N queries to get the data to populate each CustomerMaster object (from the one-to-one relation).
SELECT A,B,C FROM CustomerMapping WHERE filterField = 'ABCXXX';
SELECT D,E,F FROM CustomerMaster WHERE id = ?;
SELECT D,E,F FROM CustomerMaster WHERE id = ?;
SELECT D,E,F FROM CustomerMaster WHERE id = ?;
In other words, if we are retrieving N objects, then we will have N+1 database queries.
In order to avoid this, we need to force Hibernate to use JOIN FETCH to retrieve the CustomerMaster data:
hql = "from CustomerMapping cmi left join fetch cmi.masterData where cmi.filterField = 'ABCXXX'";
The query above will produce only one sql query to retrieve CustomerMapping and CustomerMaster data using a join.
SELECT A,B,C FROM CustomerMapping LEFT JOIN CustomerMaster (....) WHERE filterField = 'ABCXXX';
