How to include additional data in one entity from other tables (via joins or subqueries) and still have it be insertable/updatable?

Question

So I have the following code working correctly on my ecommerce site.

@Entity
@Table(name = "v_customer_wishlist")
@NamedQuery(name = "VCustomerWishlist.findAll", query = "SELECT w FROM VCustomerWishlist w")
public class VCustomerWishlist implements Serializable {
    @Id
    @Column(name = "cart_id")
    private int _cartId;
    //get/set methods
    ...
    @OneToMany(mappedBy = "_wishlist", cascade = CascadeType.ALL)
    private List<VCustomerWishlistItem> _items;
    //get/set methods
}

@Entity
@Table(name = "v_customer_wishlist_items")
@NamedQuery(name = "VCustomerWishlistItem.findAll", query = "SELECT i FROM VCustomerWishlistItem i")
public class VCustomerWishlistItem implements Serializable {
    ...
    public VCustomerWishlistItem(int cartId, int productId) {
        VCustomerWishlistItemPK id = new VCustomerWishlistItemPK (cartId, productId);
        setId(id);
    }

    @EmbeddedId
    private VCustomerWishlistItemPK id; //is PK for cartId and productId 
    //get/set methods
    ...
    @ManyToOne
    @JoinColumn(name = "cart_id")
    private VCustomerWishlist _wishlist;
    //get/set methods
    ...
    @Column(name = "product_name")
    private String _productName;
    //get/set methods
    ...
}

Then somewhere in my backing bean, I could do somethin like this (simplified version):

...
VCustomerWishlist wishlist = getCustomer().getWishlistById(cartId);
...
VCustomerWishlistItem item = new VCustomerWishlistItem(wishlist.getId(), product.getId());
...
item.setSequenceNum(wishlist.getItems().size()+1);
item.setProductName(product.getName());
item.setQuantity(1);
wishlist.addItem(item);
wishlistItemService.save(item);
...

So I can add items (product references) to wishlist and JPA will correctly generate the INSERT INTO queries and so forth.

However, upon thinking about it, I thought it would be better to retrieve this data directly from my 'master_products' table instead of what was stored in the VCustomerWishlistItem.

This way I would always have the most up-to-date productName, unitPrice and so forth for wishlist items saved weeks or months before.

The thing is, if I modify the view in my database to include this additional info by adding joins or subqueries; as soon as add joins or subqueries to my view, it becomes non-inserable/updatable.

I thought that it could be done via JPLQ in one @NamedQuery definition, but I understand these are designed to be used manually when retrieving desired sets. As opposed to the nice built-in way that JPA automatically retrieves the wishlist.items resolving it with the indicating annotation properties.

The fantasy property would be one where I can specify a direct table source for the entity, ignoring the join and subquery tables.

So for example, if the source for 'v_customer_wishlist_items_readonly' was:

SELECT 
    `cwi`.`cart_id` AS `cart_id`,
    `cwi`.`product_id` AS `product_id`,
    `cwi`.`sequence_num` AS `sequence_num`,
    `mp_readonly`.`product_name` AS `product_name`,
    `mp_readonly`.`product_web_id` AS `product_web_id`,
    `mp_readonly`.`unit_price` AS `unit_price`,
    `cwi`.`quantity` AS `quantity`,
    `mp_readonly`.`unit_price`*`csci`.`quantity` AS `item_subtotal`,
    `cwi`.`create_datetime` AS `create_datetime`,
    `cwi`.`update_datetime` AS `update_datetime`
FROM
    `customer_wishlist_items` `cwi` JOIN `master_products` `mp_readonly` ON `cwi`.`product_id` = `mp_readonly`.`product_id`
ORDER BY `cwi`.`sequence_num`;

It would be ideal to have a an annotation where I could indicate that primary table name is 'customer_wishlist_items', so all updates/inserts would only apply to this table and changes to the rest of the read-only fields would be ignored.

So somethint like this:

@Entity
@Table(name = "v_customer_wishlist_items_readonly")
@PrimaryTable(name = "customer_wishlist_items") //fantasy annotation
@NamedQuery(name = "VCustomerWishlistItem.findAll", query = "SELECT s FROM VCustomerWishlistItem s")
public class VCustomerWishlistItem implements Serializable {
...

Does anyone know what would be the correct way of implementing this?

Thanks a lot in advance!

Answer 1

Why not use derived ids or the MapsId to let JPA set your foreign key/id columns for you?

@Entity
@Table(name = "v_customer_wishlist_items")
@NamedQuery(name = "VCustomerWishlistItem.findAll", query = "SELECT i FROM VCustomerWishlistItem i")
public class VCustomerWishlistItem implements Serializable {
    ...
    public VCustomerWishlistItem(VCustomerWishlist cart, Product product) {
    this._wishList = cart;
    this._product = product;
    setId(new VCustomerWishlistItemPK());//JPA will populate this for you
}

    @EmbeddedId
    private VCustomerWishlistItemPK id; //is PK for cartId and productId 
    //get/set methods
    ...
    @ManyToOne
    @MapsId("cartId")
    @JoinColumn(name = "cart_id")
    private VCustomerWishlist _wishlist;
    //get/set methods
    ...
    @MapsId("productId")
    @JoinColumn(name = "product_id")
    private Product _product;
    //get/set methods
    ...
}

With this, you don't need to have or lookup the cartId/productId values at all as JPA will figure them out and set them for you, allowing you do just use code like:

VCustomerWishlistItem item = new VCustomerWishlistItem(wishlist, product);
...
item.setSequenceNum(wishlist.getItems().size()+1);
item.setQuantity(1);
wishlist.addItem(item);
wishlistItemService.save(item);

You should probably just set the sequenceNum and add the item to the wishlist in the item constructor, though I'm not a fan this approach to sequencing as it can lead to concurrency issues and problems maintaining it.

You can also do away with the EmbeddedId if you don't need it within your entity and use it as a primary key class; you'd just have to change the property names within it to match the relationships names from the entity:

@Entity
@IdClass(VCustomerWishlistItemPK.class)
@Table(name = "v_customer_wishlist_items")
@NamedQuery(name = "VCustomerWishlistItem.findAll", query = "SELECT i FROM VCustomerWishlistItem i")
public class VCustomerWishlistItem implements Serializable {
    ...
    public VCustomerWishlistItem(VCustomerWishlist cart, Product product) {
        this._wishList = cart;
        this._product = product;
    }

    ...
    @ManyToOne
    @Id
    @JoinColumn(name = "cart_id")
    private VCustomerWishlist _wishlist;
    //get/set methods
    ...
    @Id
    @JoinColumn(name = "product_id")
    private Product _product;
    //get/set methods
    ...
}

The primary key class might then look like:

public class VCustomerWishlistItemPK {
  public Integer _product;
  public Integer _wishlist;
  //optional getter/setter methods..
}

The properties within the ID class must match the names of the properties in your entities, but use the type of the primary key from the referenced class.