javaandroiddatabaseandroid-room
How can I do Many to Multiple Many relationship in Room Android?
I want to create a database like below. I followed the documentation but couldn't find any similar examples. Can you help me? There will be days and within these days there will be multiple Daily Meals and Daily Water tables
A day database will be created as soon as the app is started and then whatever day it is in (can change the dates manually) daily water and daily meals will be added into that day
"days":
[
{
"id": "Integer",
"date": "String",
"weight": "Float",
"height": "Integer",
"waist_circumference": "Integer",
"neck_circumference": "Integer",
"hip_circumference": "Integer",
"steps": "Long",
"taken_kcal": "Float",
"burned_kcal": "Float",
"carb": "Float",
"protein": "Float",
"fat": "Float",
"water_ml": "Integer",
"water_percent": "Float",
"meals":
[
{
"day_id": "Integer",
"id": "Integer",
"food_meal_time": "String",
"food_name": "String",
"food_image": "String",
"food_kcal": "Float",
"food_portion": "String",
"food_carb": "Float",
"food_protein": "Float",
"food_fat": "Float"
},
{
"day_id": "Integer",
"id": "Integer",
"food_meal_time": "String",
"food_name": "String",
"food_image": "String",
"food_kcal": "Float",
"food_portion": "String",
"food_carb": "Float",
"food_protein": "Float",
"food_fat": "Float"
}
],
"water":
[
{
"day_id": "Integer",
"id": "Integer",
"water_ml": "Float",
"water_percent": "Float",
"water_time": "Long"
},
{
"day_id": "Integer",
"id": "Integer",
"water_ml": "Float",
"water_percent": "Float",
"water_time": "Long"
},
{
"day_id": "Integer",
"id": "Integer",
"water_ml": "Float",
"water_percent": "Float",
"water_time": "Long"
}
]
}
]
Solution
You have 3 core tables (@Entity annotated classes defined in the entities list of the @Database annotation), Days, Meals and Water.
If (see However below) you want a many-many relationship between Days and Meals and also between Days and Water then you would have an associative/mapping/reference (and other terms) table for each many-many relationship. Such a table has two columns one that uniquely associates/references/maps to the Day and the other that uniquely associates/reference/maps to the Meals, for the Days-Meals or in the case of the Days-Water associates/references/maps to the Water. Typically the association/reference/map will be to the id column.
So in your case (if as per the heading) you would have two of these tables.
To extract related data (Days with the related Meals and with the related Waters) then you have a POJO that has the Days @Embedded and an @Relation to the Meals, defining the associative/reference/mapping table using the @Associate and the @Junction annotation, likewise an @Relation to the Waters.
However, your JSON describes two one-many relationships rather then two many-many relationships. That is the Meals have a reference to the parent Days and likewise the Waters have a reference to the parent Days.
In this case you just need the 3 core tables. For extracting the Days with the related Meals and also with the related Waters. You use the @Embedded annotation for the Days and @Relation for both the Meals and the Waters without the @Associate and @Juntion as there is no associative/reference/mapping table.
Working Example This working example uses both a
- 1 (Day) to many (Meals and Waters), and
- Many-Many (via mapping tables) allowing the same Meal to be used by many Days and also likewise the same Water to be used by many Days a Day being capable of having many of either.
So the 3 core tables (entities) Day, Water and Meal.
Day (note a row is a Day so typically it is the Day table rather than Days table) :-
@Entity
public class Day {
@PrimaryKey
Long dayId = null;
String date;
Float weight;
/* etc */
Day(){} /* Room will use this constructor */
@Ignore /* Tell Room to ignore this constructor, but still usable outside of Room, so makes life easier */
Day(String date, Float weight) {
this.dayId = null; /* not needed but effectively what happens */
this.date = date;
this.weight = weight;
}
/* No getters and setters required as members aren't private/protected */
}
Meal
@Entity(
/* Optional but suggested */
foreignKeys = {
@ForeignKey(
entity = Day.class,
parentColumns = {"dayId"},
childColumns = {"dayId_Meal_Map"}
/* Optional but can be useful */
, onDelete = ForeignKey.CASCADE
, onUpdate = ForeignKey.CASCADE
)
}
)
class Meal {
@PrimaryKey
Long mealId=null;
@ColumnInfo(index = true)
long dayId_Meal_Map;
String food_meal_time;
/* etc */
Meal(){}
@Ignore
Meal(long parentDayId, String food_meal_time) {
this.dayId_Meal_Map = parentDayId;
this.food_meal_time = food_meal_time;
}
@Ignore
Meal(String food_meal_time) {
this.food_meal_time = food_meal_time;
}
}
- Note for M2M then you wouldn't have the reference to the parent day, as this is held within the mapping table, as such you wouldn't have the Foreign Key definition either, nor the index on the reference column.
- For a similar reason two @Ignored constructors when one would suffice depending upon wether 1-M or M-M is used.
Water
@Entity(
/* Optional but suggested */
foreignKeys = {
@ForeignKey(
entity = Day.class,
parentColumns = {"dayId"},
childColumns = {"dayId_Water_Map"}
/* Optional but can be useful */
, onDelete = ForeignKey.CASCADE
, onUpdate = ForeignKey.CASCADE
)
}
)
class Water {
@PrimaryKey
Long waterId=null;
@ColumnInfo(index = true)
long dayId_Water_Map;
Float water_ml;
/* etc */
Water(){}
@Ignore
Water(long parentDayId, Float water_ml) {
this.dayId_Water_Map = parentDayId;
this.water_ml = water_ml;
}
/* Constructor for m2m i.e. reference to dayId is not needed as in the mapping table */
@Ignore
Water(Float water_ml) {
this.water_ml = water_ml;
}
}
For the Many-Many relationship then the 2 mapping tables DayMealMap and DayWaterMap
DayMealMap
@Entity(
primaryKeys = {"dayMealMap_dayId_Reference","dayMealMap_mealId_Reference"},
/* Optional but suggested */
foreignKeys = {
@ForeignKey(
entity = Day.class,
parentColumns = {"dayId"},
childColumns = {"dayMealMap_dayId_Reference"}
/* Optional but helpful */
, onDelete = ForeignKey.CASCADE
, onUpdate = ForeignKey.CASCADE
),
@ForeignKey(
entity = Meal.class,
parentColumns = "mealId",
childColumns = "dayMealMap_mealId_Reference"
, onDelete = ForeignKey.CASCADE
, onUpdate = ForeignKey.CASCADE
)
}
)
class DayMealMap {
long dayMealMap_dayId_Reference;
/* Index optional but would likely improve efficiency */
/* Also ROOM will issue warning if not indexed */
@ColumnInfo(index = true)
long dayMealMap_mealId_Reference;
DayMealMap(){}
@Ignore
DayMealMap(long dayIdMap, long mealIdMap) {
this.dayMealMap_dayId_Reference = dayIdMap;
this.dayMealMap_mealId_Reference = mealIdMap;
}
}
DayWaterMap
@Entity(
primaryKeys = {"dayWaterMap_dayId_Reference","dayWaterMap_waterId_Reference"},
foreignKeys = {
@ForeignKey(
entity = Day.class,
parentColumns = "dayId",
childColumns = "dayWaterMap_dayId_Reference"
, onDelete = ForeignKey.CASCADE
, onUpdate = ForeignKey.CASCADE
),
@ForeignKey(
entity = Water.class,
parentColumns = "waterId",
childColumns = "dayWaterMap_waterId_Reference"
,onDelete = ForeignKey.CASCADE
,onUpdate = ForeignKey.CASCADE
)
}
)
class DayWaterMap {
long dayWaterMap_dayId_Reference;
@ColumnInfo(index = true)
long dayWaterMap_waterId_Reference;
DayWaterMap(){}
@Ignore
DayWaterMap(long dayIdMap, long waterIdMap) {
this.dayWaterMap_dayId_Reference = dayIdMap;
this.dayWaterMap_waterId_Reference = waterIdMap;
}
}
For extract of the Days with the Related Meals and Waters via the 1-M relationship then a POJO
class DayWithRelatedMealsAlsoWithRelatedWaters {
@Embedded
Day day;
@Relation(
entity = Meal.class,
parentColumn = "dayId",
entityColumn = "dayId_Meal_Map"
)
List<Meal> mealList;
@Relation(
entity = Water.class,
parentColumn = "dayId",
entityColumn = "dayId_Water_Map"
)
List<Water> waterList;
}
For the Many-Many, to get the same related data it's POJO (via the mapping table (aka the associative tables))
class M2MDayWithRelatedMealsAlsoWithRealtedWaters {
@Embedded
Day day;
@Relation(
entity = Meal.class,
parentColumn = "dayId",
entityColumn = "mealId",
associateBy = @Junction(
value = DayMealMap.class,
parentColumn = "dayMealMap_dayId_Reference",
entityColumn = "dayMealMap_mealId_Reference"
)
)
List<Meal> mealList;
@Relation(
entity = Water.class,
parentColumn = "dayId",
entityColumn = "waterId",
associateBy = @Junction(
value = DayWaterMap.class,
parentColumn = "dayWaterMap_dayId_Reference",
entityColumn = "dayWaterMap_waterId_Reference"
)
)
List<Water> waterList;
}
An @Dao annotated abstract class (could be an interface but I prefer an abstract class as it is more flexible (reasons beyond this question))
AllDao (i.e. All of the DAO methods in one, for convenience):-
@Dao
abstract class AllDao {
@Insert(onConflict = OnConflictStrategy.IGNORE)
abstract long insert(Day day);
@Insert(onConflict = OnConflictStrategy.IGNORE)
abstract long insert(Meal meal);
@Insert(onConflict = OnConflictStrategy.IGNORE)
abstract long insert(Water water);
/* For many-many mapping table inserts */
@Insert(onConflict = OnConflictStrategy.IGNORE)
abstract long insert(DayMealMap dayMealMap);
@Insert(onConflict = OnConflictStrategy.IGNORE)
abstract long insert(DayWaterMap dayWaterMap);
@Transaction
@Query("SELECT * FROM day")
abstract List<DayWithRelatedMealsAlsoWithRelatedWaters> getListOfDaysWithRelatedMealsAndRelatedWaters();
@Transaction
@Query("SELECT * FROM day")
abstract List<M2MDayWithRelatedMealsAlsoWithRealtedWaters> getM2mListOfDaysWithRelatedMealsAlsoWithRelatedWaters();
}
- obviously you'd only use the one get..... depending upon how you proceed with the relationship type
Starting to put it all together from a Room perspective the @Database annotated class.
@Database(
entities = {
/* Either 1-many or many-many use the 3 core tables */
Day.class,
Meal.class,
Water.class
/* for many-many then the 2 mapping tables */
, DayMealMap.class
, DayWaterMap.class
},
version = 1,
exportSchema = false
)
abstract class TheDatabase extends RoomDatabase {
abstract AllDao getAllDao();
private volatile static TheDatabase INSTANCE= null;
public static TheDatabase getINSTANCE(Context context) {
if (INSTANCE==null) {
INSTANCE = Room.databaseBuilder(context,TheDatabase.class,"the_database.db")
.allowMainThreadQueries()
.build();
}
return INSTANCE;
}
}
- note
.allowMainThreadQueriesused for convenience and brevity/clarity.
Actually using all of the above to add some data and to then extract the data for both types of realtionships:-
MainActivity
public class MainActivity extends AppCompatActivity {
TheDatabase mDB;
AllDao mDao;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
mDB = TheDatabase.getINSTANCE(this);
mDao = mDB.getAllDao();
/* 1-many */
long day001Id = mDao.insert(new Day("2022-07-08",10.10F));
long day002Id = mDao.insert(new Day("2022-07-09",11.11F));
long day003Id = mDao.insert(new Day("2022-07-09",12.12F));
/* Add some related Meals (1-M) */
long m011Id = mDao.insert(new Meal(day001Id,"09:00"));
long m012Id = mDao.insert(new Meal(day001Id,"11:00"));
long m013Id = mDao.insert(new Meal(day001Id,"13:00"));
long m014Id = mDao.insert(new Meal(day001Id,"18:00"));
long m015Id = mDao.insert(new Meal(day001Id,"21:00"));
long m021Id = mDao.insert(new Meal(day002Id,"09:30"));
long m022Id = mDao.insert(new Meal(day002Id,"10:15"));
long m023Id = mDao.insert(new Meal(day002Id,"12:45"));
long m024Id = mDao.insert(new Meal(day002Id,"17:30"));
long m025Id = mDao.insert(new Meal(day002Id,"20:30"));
/* Add some Waters (1-M) */
long w011Id = mDao.insert(new Water(day001Id,100.100F));
long w012Id = mDao.insert(new Water(day001Id,200.200F));
long w021Id = mDao.insert(new Water(day002Id, 120.12F));
long w022Id = mDao.insert(new Water(day002Id, 220.22F));
/* Use the above Days, Meals and Waters to add M2M stuff */
/* Day 1 just a subset of the 1-m's */
mDao.insert(new DayMealMap(day001Id,m011Id));
mDao.insert(new DayMealMap(day001Id,m013Id));
mDao.insert(new DayMealMap(day001Id,m015Id));
/* Day 2 just a subset of 1-m's */
mDao.insert(new DayMealMap(day002Id, m022Id));
mDao.insert(new DayMealMap(day002Id,m024Id));
/* Note with 1-M day 3 was empty, this relates existing meals from day 1 and day 2 to day 3 */
mDao.insert(new DayMealMap(day003Id,m011Id));
mDao.insert(new DayMealMap(day003Id,m021Id));
for (DayWithRelatedMealsAlsoWithRelatedWaters d: mDao.getListOfDaysWithRelatedMealsAndRelatedWaters()) {
logADayWithRelatedMealsAndWaters(d,"_1-M");
}
for (M2MDayWithRelatedMealsAlsoWithRealtedWaters m: mDao.getM2mListOfDaysWithRelatedMealsAlsoWithRelatedWaters()) {
logM2MDayWithRelatedMealsAndWaters(m,"_M-M");
}
}
private void logADayWithRelatedMealsAndWaters(DayWithRelatedMealsAlsoWithRelatedWaters dwrmawrw, String suffix) {
Log.d(
"DAYINFO" + suffix,
"Day is " + dwrmawrw.day.date +
" Weight is " + dwrmawrw.day.weight +
" ID is " + dwrmawrw.day.dayId
+ " The Day has " + dwrmawrw.mealList.size() + " meal(s) "
+ " and it has " + dwrmawrw.waterList.size() + " water(s)."
);
for (Meal m: dwrmawrw.mealList) {
Log.d(
"DAYINFO" + suffix,
"\tMeal is " + m.food_meal_time + " it's parent Day has the ID " + m.dayId_Meal_Map
);
}
for (Water w: dwrmawrw.waterList) {
Log.d(
"DAYINFO" + suffix,
"\tWater is " + w.water_ml + " it's parent Day has the ID " + w.dayId_Water_Map
);
}
}
private void logM2MDayWithRelatedMealsAndWaters(M2MDayWithRelatedMealsAlsoWithRealtedWaters mdwrmawrw, String suffix) {
Log.d(
"DAYINFO" + suffix,
"Day is " + mdwrmawrw.day.date +
" Weight is " + mdwrmawrw.day.weight +
" ID is " + mdwrmawrw.day.dayId
+ " The Day has " + mdwrmawrw.mealList.size() + " meal(s) "
+ " and it has " + mdwrmawrw.waterList.size() + " water(s)."
);
for (Meal m: mdwrmawrw.mealList) {
Log.d(
"DAYINFO" + suffix,
"\tMeal is " + m.food_meal_time + " it's parent Day has the ID " + m.dayId_Meal_Map
);
}
for (Water w: mdwrmawrw.waterList) {
Log.d(
"DAYINFO" + suffix,
"\tWater is " + w.water_ml + " it's parent Day has the ID " + w.dayId_Water_Map
);
}
}
}
- note the above is only designed to be run once as a demonstration.
THE RESULT (i.e. the output written to the log, with spaces added to split the two types of relationshsips):-
2022-07-08 12:20:01.522 D/DAYINFO_1-M: Day is 2022-07-08 Weight is 10.1 ID is 1 The Day has 5 meal(s) and it has 2 water(s).
2022-07-08 12:20:01.522 D/DAYINFO_1-M: Meal is 09:00 it's parent Day has the ID 1
2022-07-08 12:20:01.522 D/DAYINFO_1-M: Meal is 11:00 it's parent Day has the ID 1
2022-07-08 12:20:01.522 D/DAYINFO_1-M: Meal is 13:00 it's parent Day has the ID 1
2022-07-08 12:20:01.522 D/DAYINFO_1-M: Meal is 18:00 it's parent Day has the ID 1
2022-07-08 12:20:01.522 D/DAYINFO_1-M: Meal is 21:00 it's parent Day has the ID 1
2022-07-08 12:20:01.522 D/DAYINFO_1-M: Water is 100.1 it's parent Day has the ID 1
2022-07-08 12:20:01.523 D/DAYINFO_1-M: Water is 200.2 it's parent Day has the ID 1
2022-07-08 12:20:01.523 D/DAYINFO_1-M: Day is 2022-07-09 Weight is 11.11 ID is 2 The Day has 5 meal(s) and it has 2 water(s).
2022-07-08 12:20:01.523 D/DAYINFO_1-M: Meal is 09:30 it's parent Day has the ID 2
2022-07-08 12:20:01.523 D/DAYINFO_1-M: Meal is 10:15 it's parent Day has the ID 2
2022-07-08 12:20:01.523 D/DAYINFO_1-M: Meal is 12:45 it's parent Day has the ID 2
2022-07-08 12:20:01.523 D/DAYINFO_1-M: Meal is 17:30 it's parent Day has the ID 2
2022-07-08 12:20:01.523 D/DAYINFO_1-M: Meal is 20:30 it's parent Day has the ID 2
2022-07-08 12:20:01.523 D/DAYINFO_1-M: Water is 120.12 it's parent Day has the ID 2
2022-07-08 12:20:01.523 D/DAYINFO_1-M: Water is 220.22 it's parent Day has the ID 2
2022-07-08 12:20:01.523 D/DAYINFO_1-M: Day is 2022-07-09 Weight is 12.12 ID is 3 The Day has 0 meal(s) and it has 0 water(s).
2022-07-08 12:20:01.527 D/DAYINFO_M-M: Day is 2022-07-08 Weight is 10.1 ID is 1 The Day has 3 meal(s) and it has 0 water(s).
2022-07-08 12:20:01.527 D/DAYINFO_M-M: Meal is 09:00 it's parent Day has the ID 1
2022-07-08 12:20:01.527 D/DAYINFO_M-M: Meal is 13:00 it's parent Day has the ID 1
2022-07-08 12:20:01.527 D/DAYINFO_M-M: Meal is 21:00 it's parent Day has the ID 1
2022-07-08 12:20:01.527 D/DAYINFO_M-M: Day is 2022-07-09 Weight is 11.11 ID is 2 The Day has 2 meal(s) and it has 0 water(s).
2022-07-08 12:20:01.527 D/DAYINFO_M-M: Meal is 10:15 it's parent Day has the ID 2
2022-07-08 12:20:01.527 D/DAYINFO_M-M: Meal is 17:30 it's parent Day has the ID 2
2022-07-08 12:20:01.528 D/DAYINFO_M-M: Day is 2022-07-09 Weight is 12.12 ID is 3 The Day has 2 meal(s) and it has 0 water(s).
2022-07-08 12:20:01.528 D/DAYINFO_M-M: Meal is 09:00 it's parent Day has the ID 1
2022-07-08 12:20:01.528 D/DAYINFO_M-M: Meal is 09:30 it's parent Day has the ID 2
- As can be seen, the related data is dependant upon relationship type and the data was purposefully applied to highlight the difference.
- Restricting access to static methds with ArchUnit
- Java day of the week from string
- Populating Spinner using ArrayList in Android
- Ionic Capacitor - Unknown Kotlin JVM target: 21
- Singleton & Multithreading in Java
- Maven version management w multiple child modules
- get number of working days in specific month
- Issue with Regex Pattern (split string with characters & numbers)
- Why would I ever create a separate mutex/lock object?
- Happens before guarantee in Volatile
- android.security.KeyStoreException: Signature/MAC verification failed
- array of parameterized types
- JavaFX WARNING: Unsupported JavaFX configuration: classes were loaded from 'unnamed module @...'
- unit test @Valid of springboot without having to spin up / mock the server
- Why dec 31 2010 returns 1 as week of year?
- Performance Difference of AtomicInteger vs Integer
- how do i restric a float value to only one placess after decimal in kotlin android
- consecutive pairs divisible by 3
- How to call a service from Main application calls Spring Boot?
- Setting Namespace Attributes on an Element
- How to find jUnit tests in a Java project?
- validate the credit card expiry date using java?
- A failure occurred while executing com.android.build.gradle.internal.tasks.FinalizeBundleTask$BundleToolRunnable
- Is Java completely Platform Independent?
- What does it exactly mean to use @DirtiesContext annotation on a class?
- Spring JBDC bad SQL grammar on PostgreSQL
- Test REST APIs on a real server (without localhost )
- How can I pass an array of objects in query params in postman for the post request?
- Is there a way to do 24-hour format and 60-minute format in Java?
- Removing files installed by previous version in Inno Setup