More Advanced Features - Beginning Hibernate: From Novice to Professional - page 247

There are several dissatisfying aspects to the mapping in Listing A-13. First, rather than
mapping our product table, we have mapped the link table. This makes sense when you con-
sider that the primary key formed from the two columns of this table uniquely identifies a
“colored product,” which the product table alone cannot do.
Second, we are obliged to create a number of distinct objects to represent the class: the
Product class itself, a class to represent the primary key (inevitable where a composite id
occurs), a class to represent the other attributes of the product, and the Color class.
Last, the use of the columns more than once within the mapping requires us to flag
them so that they cannot be written—this is a read-only mapping .
Using a View
Fortunately, most databases provide a simple mechanism for manipulating a schema so
that it better matches the business requirements. A database view will allow you to put
together a join that appears to be a table. By a suitable choice of columns from the existing
tables, you can construct a view that is much easier to map (see Listing A-14).
Listing A-14. A View on the Product Tables
create view vwProduct (ProductKey,ColorKey,Id,SKU,ColorId)
AS
select
p.id as ProductKey,
c.id as ColorKey,
p.id as Id,
p.sku as SKU,
c.id as ColorId
from
product p,
product_color pc,
color c
where
p.id = pc.product_id
and
pc.color_id = c.id;
This view effectively reformats our table so that it has a correct (composite) primary key
formed from the link table's two columns. It makes the SKU data available directly, and it
retains the foreign key into the color table.
Listing A-15 is a much more natural mapping.