ArrayList of primitive types in Hibernate

Question

I have a question regarding an ArrayList of Integers, or primitive types in general. Suppose I'm designing a POS program and each product may have several prices.

Let's assume I can represent a price value with ints and in the Product class I have the field ArrayList<Integer> prices. What's the best way to map this with Hibernate?

I could map it to a product_prices table with a field containing the price value and a field for the foreign key referencing the product in question, but this seems overkill.

On the other hand, I could concatenate all prices in a String and store it as a field in the products table, with the prices separated by semicolons, for instance. This way I'm saving one table and a future select, but it doesn't seem quite OO.

What is the best to do here?

Answer 1

On the other hand, I could concatenate all prices in a String and store it as a field in the products table, with the prices separated by semicolons, for instance. This way I'm saving one table and a future select, but it doesn't seem quite OO.

No, it's not relational. That breaks the rules for the first normal form.

I don't see why you're worried about saving a table and a SELECT. This is premature optimization at its worst.

A product may have several prices, but there will also be criteria that tell you when one applies (e.g., effective date, discount conditions, etc.) You should add those into your schema as well.

I'd recommend a Product table, without any pricing or discount information in it.

It sounds like there'd be a many-to-many relationship between products and prices if Price has an effective date in it, so you'd have a Product_Price JOIN table as well.

Answer 2

Let's forget the sample here (which may not be the best one). With Hibernate, you can map a collection of basic types or embeddedable objects with the @CollectionOfElements annotation (and optionally an @IndexColumn for ordered collections):

@Entity
public class Product {
    @Id @GeneratedValue
    private Long id;

    @CollectionOfElements @IndexColumn(name="price_index")
    private List<Integer> prices = new ArrayList<Integer>();

    ...
}

Semantically, this is close to a @OneToMany except that the elements of the collection are not Entities, they don't have an id property, and their lifecycle is fully dependent on the owner object.

From a database point of view, this would result in a table for the product and a table for the prices:

create table Product (id bigint not null, primary key (id))
create table Product_prices (Product_id bigint not null, element integer, price_index integer not null, primary key (Product_id, price_index))
alter table Prodcut_prices add constraint FK9D26D06FB343359D foreign key (Product_id) references Product

In JPA 2.0, this annotation has been standardized so prefer the new @ElementCollection annotation if you are using JPA 2.0.

That being said, for the particular case of Product and Price, what @duffymo said is very true and they should probably not be implemented using the mentioned annotations.