Mapping multiple HashMaps with same Entity keys in JPA

Question

I have the following business model:

Meal - contains name and price

Menu - contains name and a set of Meals with their amounts and alternate prices (usually lower than default prices).

In the past I have solved similar problems using HashMap<Entity, Value>. In this case, I have two values: price and amount. Since Java and JPA don't support multimaps, I decided to go with two HashMaps containing same keys.

@Entity
public class Menu {
    @Column
    private String name;

    @ElementCollection
    @CollectionTable(name = "MENU_MEALS", joinColumns = @JoinColumn(name = "MENU_ID"))
    @MapKeyJoinColumn(name = "MEAL_ID", referencedColumnName = "ID")
    @Column(name="AMOUNT")
    private Map<Meal, BigDecimal> amounts = new LinkedHashMap<Meal, BigDecimal>();

    @ElementCollection
    @CollectionTable(name = "MENU_MEALS", joinColumns = @JoinColumn(name = "MENU_ID"))
    @MapKeyJoinColumn(name = "MEAL_ID", referencedColumnName = "ID")
    @Column(name="PRICE")
    private Map<Meal, BigDecimal> prices = new LinkedHashMap<Meal, BigDecimal>();
}

The generated tables look fine: MENU_ID, MEAL_ID, AMOUNT, PRICE.

I add new Meals to Menus like this (in a method inside my entity), and I do it while it's detached (don't want to store Meals to DB right away - GUI preview comes first):

menu.prices.add(meal, price);
menu.amounts.add(meal, amount);

After adding enough Meals to the detached Menu and clicking an 'OK' button, my Menu is merged.

The em.merge(menu) operation maps into following SQL:

insert into MENU_MEAL (MENU_ID, MEAL_ID, PRICE) values (?, ?, ?)
insert into MENU_MEAL (MENU_ID, MEAL_ID, AMOUNT) values (?, ?, ?)

And here lies the problem. I get a primary key constraint violation exception. The second call should update (MENU_ID, MEAL_ID) entry, instead trying to insert a new one with same foreign keys.

Any suggestions how to force it to do "update-if-exists-insert-otherwise"?

I have temporarily fixed the problem by mapping each HashMap to a different table. But I don't like that solution.

EDIT: I need my merge to behave like this:

insert into MENU_MEAL (MENU_ID, MEAL_ID, PRICE) values(?, ?, ?) on duplicate key update PRICE=values(price)

Any ideas how to achieve that?

score 3 · Answer 1 · answered Oct 13 '14 at 07:49

I know I'm late, but these are my solutions:

1) use a single Map with @Embeddable value:

@Entity
public class Menu
{
    @Column
    private String name;

    @ElementCollection
    @CollectionTable(name = "MENU_MEALS", joinColumns = @JoinColumn(name = "MENU_ID"))
    @MapKeyJoinColumn(name = "MEAL_ID", referencedColumnName = "ID")
    private Map<Meal, Detail> details = new LinkedHashMap<Meal, Detail>();
}

@Embeddable
public class Detail
{
    @Column(name = "AMOUNT")
    private BigDecimal amount;

    @Column(name = "PRICE")
    private BigDecimal price;
}

this mapping has the same table structure you already have.

2) use a single Map with @Entity relation:

@Entity
public class Menu
{
    @Column
    private String name;

    @OneToMany(mappedBy = "menu", cascade = CascadeType.ALL, orphanRemoval = true)
    @MapKey(name = "meal")
    private Map<Meal, MealDetail> details = new LinkedHashMap<Meal, MealDetail>();
}

@Entity
public class MealDetail
{
    @ManyToOne
    @JoinColumn(name = "MENU_ID")
    private Menu menu;

    @ManyToOne
    @JoinColumn(name = "MEAL_ID")
    private Meal meal;

    @Column(name = "AMOUNT")
    private BigDecimal amount;

    @Column(name = "PRICE")
    private BigDecimal price;
}

this mapping adds a ID column, but I think playing with @PrimaryKeyJoinColumns you can get the same mapping as previous.

Joetjah · Answer 2 · 2013-02-14T09:16:15.590

1

If I recall correctly, when you use variable = em.find and update that variable, you can update the values in the entitymanager without persisting.

You can either surround your code where you change or add the values by:

em.getTransaction().begin();
//change
menu.prices.add(meal, price);
menu.amounts.add(meal, amount);
em.getTransaction().commit();

You could also try em.flush().

A more certain answer can be give if you show the code how you created menu. Did you use em.find()?

edited Feb 14 '13 at 09:16

answered Feb 14 '13 at 09:11

Joetjah

6,292
8
55
90

I first create a Menu without any meals and store it. After that, I detach it, so I can add Meals without storing it in database (only for previewing in GUI). After I click OK, em.merge(menu) is called and here I expect all the added Meals to be persisted as well. It worked well in the past for only one HashMap. – alan.sambol Feb 14 '13 at 09:26
Can you try your code but then `em.merge` instead of `em.persist`? – Joetjah Feb 14 '13 at 09:28
My mistake, I actually do `em.merge()`, not `em.persist()`. – alan.sambol Feb 14 '13 at 09:30
I'm afraid I can't test any further here since I'm not at home, but I get the idea you are trying to join the tables two times. I think you try to join the Menu with Meals and Prices thus creating a double key for Meal? Just a guess though. – Joetjah Feb 14 '13 at 09:36
I managed to fix it by overriding SQL INSERT command using @SQLInsert annotation. Take a look at my answer below. – alan.sambol Feb 14 '13 at 10:52

score 0 · Accepted Answer · answered Feb 14 '13 at 10:39

I have found a solution to my problem, when using Hibernate as the JPA ORM provider. Simply override the default SQL INSERT command adding an @SQLInsert Hibernate annotation to the HashMaps.

Solution for H2 database:

@SQLInsert(sql="MERGE INTO MENU_MEAL(MENU_ID, MEAL_ID, PRICE) VALUES (?, ?, ?)")
private Map<Meal, BigDecimal> prices = new LinkedHashMap<Meal, BigDecimal>();

@SQLInsert(sql="MERGE INTO MENU_MEAL(MENU_ID, MEAL_ID, AMOUNT) VALUES (?, ?, ?)")
private Map<Meal, BigDecimal> amounts = new LinkedHashMap<Meal, BigDecimal>();

Solution for other databases (which support ON DUPLICATE KEY):

@SQLInsert(sql="INSERT INTO MENU_MEAL(MENU_ID, MEAL_ID, PRICE) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE set PRICE = VALUES(PRICE)")
private Map<Meal, BigDecimal> prices = new LinkedHashMap<Meal, BigDecimal>();

@SQLInsert(sql="INSERT INTO MENU_MEAL(MENU_ID, MEAL_ID, AMOUNT) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE set AMOUNT = VALUES(AMOUNT)")
private Map<Meal, BigDecimal> amounts = new LinkedHashMap<Meal, BigDecimal>();

Still not completely satisfied with the solution (both database and ORM provider dependant), but better than having one table for each HashMap.

I'm glad it works for now but I agree it's not the best solution. In 7 hours (yeah, long day), I'll be able to look into this for you. Perhaps I can find something more clean for you. — Joetjah, Feb 14 '13 at 10:57

Mapping multiple HashMaps with same Entity keys in JPA

3 Answers3