Java HashMap as an element in MySQL via JPA

Question

I already asked this question but I didn't receive a straight answer: https://stackoverflow.com/questions/31489973/java-hashmap-in-many-to-many-relationship-mysql?noredirect=1#comment50944140_31489973.

This MySQL database has three tables: Meal, Ingredient, and IngredientListAndAmount. Each row in Meal corresponds to a row in IngredientListAndAmount. IngredientListAndAmount can be stored as a Java HashMap, where an Ingredient ID is the key and an integer amount is the value.

How I would represent this dependency in a MySQL database?

en.wikibooks.org/wiki/Java_Persistence/Relationships#Maps shows how the classes will be defined in a database. — user3584190
– user3584190, Commented Jul 21, 2015 at 11:02

O. Jones · Accepted Answer · 2015-07-21 10:58:07Z

1

What you seek is often called a join table. This is how SQL represents many-to-many relationships between two entities. The purest of pure join tables joining tables A and B consists of two columns, one containing A_id, and the other B_id. The two columns together constitute the primary key of the join table.

In your case you'll have three columns in the join table:

 Meal_id        pk, fk to Meal table
 Ingredient_id  pk, fk to Ingredient table
 Amount         something giving the amount

If you want the ingredients for a particular meal, you use a query like this:

SELECT m.Mealname, i.IngredientName, mi.Amount
  FROM Meal AS m
  JOIN MealIngredient AS mi ON m.Meal_id = mi.Meal_id
  JOIN Ingredient AS i ON mi.Ingredient_id = i.Ingredient_id

answered Jul 21, 2015 at 10:58

O. Jones

109k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user4823369 Over a year ago

Thank you. I actually need to get a meal if I know the ingredients

Collectives™ on Stack Overflow

Java HashMap as an element in MySQL via JPA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related