Create Entities using @Entity for the additional tables.
Create the appropriate Dao's (either all in one or one per Entity your choice)
Amend the @Database (RoomDB class) entities = to include the other Entity classes in the array of Entities. If using multiple Dao's then add the abstract methods for retrieving the Dao (i.e. similar to public abstract TemplateDao templateDao();).
Saying that at least for the Template tables it would appear that a single table would suffice for all templates if you added a column that denotes the type (people, animal etc)
Example 1 - additional tables
1. Create New Entity
@Entity(tableName = "people_templates")
public class PeopleTemplate implements Serializable {
@PrimaryKey(autoGenerate = true)
public long id; //<<<<<<<<< Should really be long not int
@ColumnInfo(name = "description")
public String titleTemplate;
@ColumnInfo(name = "image_link")
public String imageLink;
public String getTitleTemplate() {
return titleTemplate;
}
public void setTitleTemplate(String titleTemplate) {
this.titleTemplate = titleTemplate;
}
public String getImageLink() {
return imageLink;
}
public void setDrawableID(String imageLink) {
this.imageLink = imageLink;
}
public long getId() { // long
return id;
}
public void setId(long id) { // long
this.id = id;
}
public PeopleTemplate(String titleTemplate, String imageLink){
this.titleTemplate = titleTemplate;
this.imageLink = imageLink;
}
public PeopleTemplate(){}
}
- note that int has been changed to long for the id column. In theory id can be long, Room expects to return long when using @Insert.
2. Amend Dao
@Dao
public interface TemplateDao {
@Insert(onConflict = REPLACE)
void insert(MyTemplate myTemplate);
@Insert(onConflict = REPLACE)
long insert(PeopleTemplate peopleTemplate); // long will hold the id of the inserted row
@Insert(onConflict = REPLACE)
long[] insert(PeopleTemplate...peopleTemplates); // allows many (e.g. ArrayList to be added), returns long[] of id's inserted
@Delete
void delete(MyTemplate myTemplate);
@Delete
int delete(PeopleTemplate...peopleTemplate); // delete 1 or many, returns number deleted
@Delete
void reset(ArrayList<MyTemplate> myTemplates);
@Query("UPDATE my_templates SET description = :sDescription WHERE id = :sID")
void update(int sID, String sDescription);
@Query("UPDATE people_templates SET description = :sDescription WHERE id = :sID")
int update(long sID, String sDescription); // returns number of updates
@Query("SELECT * FROM my_templates")
List<MyTemplate> getAll();
@Query("SELECT * FROM people_templates")
List<PeopleTemplate> getAllPeopleTemplates();
}
3. Incorporate into @Database class
//@Database(entities = {MyTemplate.class}, version = 1, exportSchema = false) <<<<<<<<<< WAS THIS
@Database(entities = {MyTemplate.class,PeopleTemplate.class}, version = 1, exportSchema = false) //<<<<<<<<<< CHANGED
public abstract class RoomDB extends RoomDatabase {
private static RoomDB database;
private final static String DATABASE_NAME = "Templates";
public synchronized static RoomDB getInstance(Context context){
if(database == null){
database = Room.databaseBuilder(context.getApplicationContext(),
RoomDB.class, DATABASE_NAME)
.allowMainThreadQueries()
.fallbackToDestructiveMigration()
.build();
}
return database;
}
public abstract TemplateDao templateDao();
}
Example 2 - Single Table
Here's a working example of a Single Template table catering for multiple Template types
The SingleTemplate Entity
@Entity(tableName = "templates")
public class SingleTemplate implements Serializable {
public static final int MY_TEMPLATE = 0;
public static final int PEOPLE_TEMPLATE = 1;
public static final int ANIMAL_TEMPLATE = 2;
/* suggest not using AUTOGENERATE as it has overheads
using Long and passing null has the same result without overheads
*/
@PrimaryKey
Long id;
@ColumnInfo(name = "description")
String title;
@ColumnInfo(name = "image_link")
String imageLink;
@ColumnInfo(name = "template_type")
int templateType;
public SingleTemplate(){}
// Use @Ignore to suppress warning "There are multiple good constructors and Room will pick the no-arg constructor...."
@Ignore
public SingleTemplate(String title, String imageLink, int templateType) {
this.title = title;
this.imageLink = imageLink;
this.templateType = templateType;
}
/* Method to allow conversion of type to a user readable String */
public static String getType(int templateType) {
switch (templateType) {
case MY_TEMPLATE:
return "MY";
case PEOPLE_TEMPLATE:
return "PEOPLE";
case ANIMAL_TEMPLATE:
return "ANIMAL";
default:
return "UNKNOWN";
}
}
}
The Dao SingleTemplateDao
@Dao
interface SingleTemplateDao {
@Insert(onConflict = REPLACE)
long insert(SingleTemplate singleTemplate);
@Insert(onConflict = REPLACE)
long[] insert(SingleTemplate...singleTemplates);
@Update
int update(SingleTemplate...singleTemplate);
@Query("SELECT * FROM templates WHERE template_type IN (:arrayOfTypes)")
List<SingleTemplate> getTemplatesByTypes(int[] arrayOfTypes);
}
The @Database class RoomDBSingleTemplate
@Database(entities = {SingleTemplate.class}, version = 1, exportSchema = false) //<<<<<<<<<< CHANGED
public abstract class RoomDBSingleTemplate extends RoomDatabase {
private static RoomDBSingleTemplate database;
private final static String DATABASE_NAME = "Templates";
public synchronized static RoomDBSingleTemplate getInstance(Context context){
if(database == null){
database = Room.databaseBuilder(context.getApplicationContext(),
RoomDBSingleTemplate.class, DATABASE_NAME)
.allowMainThreadQueries()
.fallbackToDestructiveMigration()
.build();
}
return database;
}
public abstract SingleTemplateDao getSingleTemplateDao();
}
Invoking/Demo code in MainActivity
public class MainActivity extends AppCompatActivity {
RoomDBSingleTemplate db;
SingleTemplateDao dao;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
db = RoomDBSingleTemplate.getInstance(this);
dao = db.getSingleTemplateDao();
// Add one of each type individually
long id = dao.insert(new SingleTemplate("Template1","Link1",SingleTemplate.MY_TEMPLATE));
dao.insert(new SingleTemplate("Template2","Link2", ANIMAL_TEMPLATE));
dao.insert(new SingleTemplate("Template3","Link3",SingleTemplate.PEOPLE_TEMPLATE));
// Add an unknown type
dao.insert(new SingleTemplate("Template4","LINK4",99999));
// Add many templates type determined according to iteration
int limit = 10;
SingleTemplate[] stList = new SingleTemplate[limit];
for(int i=0; i < limit; i++) {
stList[i] = new SingleTemplate(
"Template" + (i+100),
"Link" + (i+100),
i % 3
);
}
dao.insert(stList);
// Get just Animal templates and update the Link by prefixing with ANIMAL
List<SingleTemplate> animalTemplates = dao.getTemplatesByTypes(new int[]{ANIMAL_TEMPLATE});
stList = new SingleTemplate[animalTemplates.size()];
int i=0;
for (SingleTemplate s: animalTemplates) {
s.imageLink = "ANIMAL" + s.imageLink;
stList[i++] = s;
}
dao.update(stList);
// Get all known types and output to the log
List<SingleTemplate> allTemplates = dao.getTemplatesByTypes(new int[]{MY_TEMPLATE,ANIMAL_TEMPLATE,PEOPLE_TEMPLATE});
for(SingleTemplate s: allTemplates) {
Log.d("TEMPLATEINFO",
"Title is " + s.title +
" Link is " + s.imageLink +
" Type is " + SingleTemplate.getType(s.templateType) +
" ID is " + s.id
);
}
}
}
The Result - Output in the Log.
2021-06-09 07:44:18.163 D/TEMPLATEINFO: Title is Template1 Link is Link1 Type is MY ID is 1
2021-06-09 07:44:18.163 D/TEMPLATEINFO: Title is Template2 Link is ANIMALLink2 Type is ANIMAL ID is 2
2021-06-09 07:44:18.163 D/TEMPLATEINFO: Title is Template3 Link is Link3 Type is PEOPLE ID is 3
2021-06-09 07:44:18.163 D/TEMPLATEINFO: Title is Template100 Link is Link100 Type is MY ID is 5
2021-06-09 07:44:18.163 D/TEMPLATEINFO: Title is Template101 Link is Link101 Type is PEOPLE ID is 6
2021-06-09 07:44:18.164 D/TEMPLATEINFO: Title is Template102 Link is ANIMALLink102 Type is ANIMAL ID is 7
2021-06-09 07:44:18.164 D/TEMPLATEINFO: Title is Template103 Link is Link103 Type is MY ID is 8
2021-06-09 07:44:18.164 D/TEMPLATEINFO: Title is Template104 Link is Link104 Type is PEOPLE ID is 9
2021-06-09 07:44:18.164 D/TEMPLATEINFO: Title is Template105 Link is ANIMALLink105 Type is ANIMAL ID is 10
2021-06-09 07:44:18.164 D/TEMPLATEINFO: Title is Template106 Link is Link106 Type is MY ID is 11
2021-06-09 07:44:18.164 D/TEMPLATEINFO: Title is Template107 Link is Link107 Type is PEOPLE ID is 12
2021-06-09 07:44:18.164 D/TEMPLATEINFO: Title is Template108 Link is ANIMALLink108 Type is ANIMAL ID is 13
2021-06-09 07:44:18.164 D/TEMPLATEINFO: Title is Template109 Link is Link109 Type is MY ID is 14
As can be seen
- 14 rows have been added (last ID is 14)
- All rows that have a type of ANIMAL have had the link updated
- The UNKNOWN row (Template4 with an ID of 4 ) has been omitted from the output
Taking it further
You say :-
Also there will be special table favourites, I will add there all templates user likes. I am going to use RoomDatabase.
Assuming by user, that there are multiple users (doesn't hurt if only 1 User although it doesn't need to be as complex if only 1 User).
The you could have a User table perhaps, for example :-
@Entity(tableName = "user")
public class User {
@PrimaryKey
Long id;
String userName;
String userEmail;
String userPassword;
public User(){};
@Ignore
public User(String userName, String userEmail, String userPassword) {
this.userName = userName;
this.userEmail = userEmail;
this.userPassword = userPassword;
}
}
To link a user with their favourite templates and allowing users to use templates that other users use then a many-many relationship would be required. As such the Favourites table could be a map between the Users and the Templates they like. To map all you need is a unique identifier of the User along with the unique identifier of the Template (e.g. the id columns from each). As such the Favourites table could be :-
/*
Mapping Table for many-many relationship between users and templates
*/
@Entity(
tableName = "favourite",
foreignKeys = {
@ForeignKey(
entity = User.class,
parentColumns = {"id"},
childColumns = {"userId"},
onDelete = CASCADE,
onUpdate = CASCADE
),
@ForeignKey(
entity = SingleTemplate.class,
parentColumns = {"id"},
childColumns = {"templateId"},
onDelete = CASCADE,
onUpdate = CASCADE
)
},
/* composite Primary Key */
primaryKeys = {"userId","templateId"},
indices = {@Index("templateId")}
)
public class Favourites {
long userId;
long templateId;
public Favourites(){}
@Ignore
public Favourites(long userId,long templateId) {
this.userId = userId;
this.templateId = templateId;
}
}
- There are many terms for such a table e.g. associative table, reference table, mapping table ....
- The ForeignKey defnitions defines rules for managing/asserting referential integrity, they are not needed but they can be helpful
- they can protect against orphaned children being added. That is if an attempt is made to add a row where a column value does not exist in the respective parent then a Foreign Key conflict will occur (see the Dao).
- The onDelete = CASCADE will cascade the deletion of a parent so that all rows in the child that reference the parent are deleted.
- The onUpdate is similar BUT it will only result in the referenced column being changed in the children and only if the referenced column is changed (this would be a rare occurrence).
- There are two ForeignKey definitions one for the reference/relationship to the User table and the other for the refrence/relationship to the SingleTemplates table.
- The index on the templateId column isn't required but if omitted Room issue a warning.
At this stage Dao's to insert and retrieve Users and Favourites could be added. However, retrieving the Favourites is not that user friendly they are just numbers with little meaning. e.g.
- 1,13 would would really mean that the user who'ds id is 1 has a link to the template that has an id of 13 ....
As such you would probably want to get the user details along with the template details. Room makes this quite easy to do you create a POJO class (not an Entity) where you Embed one Entity and then define a Relation to the other class (Embed the User WITH a Relation to the Templates) . However, as a mapping table is between the two you (just the Embed and Relation caters for a one-many relationship) then you need an Association which defines the Junction between the three tables.
As such a suitable POJO could be UserWithFavourites:-
public class UserWithFavourites {
@Embedded
User user; /* One User */
@Relation(entity = SingleTemplate.class, parentColumn = "id",entityColumn = "id",
associateBy = @Junction(value = Favourites.class,parentColumn = "userId",entityColumn = "templateId"))
List<SingleTemplate> singleTemplateList; /* A list of Templates for the User */
}
So a Dao would be required to retrieve (Query) the database. So SingleTemplateDao could now be :-
@Dao
interface SingleTemplateDao {
@Insert(onConflict = REPLACE)
long insert(SingleTemplate singleTemplate);
@Insert(onConflict = REPLACE)
long[] insert(SingleTemplate...singleTemplates);
@Insert(onConflict = REPLACE)
long[] insert(User...users);
@Insert(onConflict = IGNORE)
long[] insert(Favourites...favourites);
@Update
int update(SingleTemplate...singleTemplate);
@Query("SELECT * FROM templates WHERE template_type IN (:arrayOfTypes)")
List<SingleTemplate> getTemplatesByTypes(int[] arrayOfTypes);
@Query("SELECT * FROM user")
List<User> getAllUsers();
@Query("SELECT * FROM user WHERE id=:userId")
User getUserById(long userId);
@Query("SELECT * FROM User")
List<UserWithFavourites> getAllUsersWithFavourites();
}
Example3
Note that the code above for insert/updating and extracting has been moved to a method named example2. A new method example3 has been created to demonstrate the addition of the User and Favourites table, so the activity code is now :-
public class MainActivity extends AppCompatActivity {
RoomDBSingleTemplate db;
SingleTemplateDao dao;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
db = RoomDBSingleTemplate.getInstance(this);
dao = db.getSingleTemplateDao();
example2();
example3();
}
private void example3() {
/* Add some Users */
dao.insert(new User("Fred","Fred@email.com","password"), /*ID will be 1 */
new User("Mary","Mary@email.com","password"), /* ID will be 2 */
new User("Sarah","Sarah@email.com","password") /* ID will be 3 */);
/* Using the existing templates map/associate/relate users to templates */
dao.insert(new Favourites(1,1),
new Favourites(1,3),
new Favourites(1,4) /* UNKNOWN TYPE */,
new Favourites(1,10),
new Favourites(2,5),
new Favourites(2,6),
new Favourites(2,7),
new Favourites(2,8),
new Favourites(3,9),
new Favourites(3,11),
new Favourites(3,12),
new Favourites(3,13),
new Favourites(3,14));
/* extract all users with thier templates and then output the results to the log */
List<UserWithFavourites> userWithFavourites = dao.getAllUsersWithFavourites();
for (UserWithFavourites uwf: userWithFavourites) {
Log.d("FAVOURITESINFO",
"User is " + uwf.user.userName +
" email is " + uwf.user.userEmail +
" password is " + uwf.user.userPassword +
" USERID = " + uwf.user.id +
" # of Templates = " + uwf.singleTemplateList.size()
);
for (SingleTemplate st: uwf.singleTemplateList) {
Log.d("FAVOURITESINFO","Template is " + st.title + " Link is " + st.imageLink + " Type is " + getType(st.templateType) + " ID is " + st.id);
}
}
}
private void example2() {
// Add one of each type individually
long id = dao.insert(new SingleTemplate("Template1","Link1",SingleTemplate.MY_TEMPLATE));
dao.insert(new SingleTemplate("Template2","Link2", ANIMAL_TEMPLATE));
dao.insert(new SingleTemplate("Template3","Link3",SingleTemplate.PEOPLE_TEMPLATE));
// Add an unknown type
dao.insert(new SingleTemplate("Template4","LINK4",99999));
// Add many templates type determined according to iteration
int limit = 10;
SingleTemplate[] stList = new SingleTemplate[limit];
for(int i=0; i < limit; i++) {
stList[i] = new SingleTemplate(
"Template" + (i+100),
"Link" + (i+100),
i % 3
);
}
dao.insert(stList);
// Get just Animal templates and update the Link by prefixing with ANIMAL
List<SingleTemplate> animalTemplates = dao.getTemplatesByTypes(new int[]{ANIMAL_TEMPLATE});
stList = new SingleTemplate[animalTemplates.size()];
int i=0;
for (SingleTemplate s: animalTemplates) {
s.imageLink = "ANIMAL" + s.imageLink;
stList[i++] = s;
}
dao.update(stList);
// Get all known types and output to the log
List<SingleTemplate> allTemplates = dao.getTemplatesByTypes(new int[]{MY_TEMPLATE,ANIMAL_TEMPLATE,PEOPLE_TEMPLATE});
for(SingleTemplate s: allTemplates) {
Log.d("TEMPLATEINFO",
"Title is " + s.title +
" Link is " + s.imageLink +
" Type is " + SingleTemplate.getType(s.templateType) +
" ID is " + s.id
);
}
}
}
Example 3 Results When run (for the first time) the log includes:-
2021-06-09 11:50:11.477 D/FAVOURITESINFO: Template is Template1 Link is Link1 Type is MY ID is 1
2021-06-09 11:50:11.477 D/FAVOURITESINFO: Template is Template3 Link is Link3 Type is PEOPLE ID is 3
2021-06-09 11:50:11.477 D/FAVOURITESINFO: Template is Template4 Link is LINK4 Type is UNKNOWN ID is 4
2021-06-09 11:50:11.477 D/FAVOURITESINFO: Template is Template105 Link is ANIMALANIMALLink105 Type is ANIMAL ID is 10
2021-06-09 11:50:11.477 D/FAVOURITESINFO: User is Mary email is Mary@email.com password is password USERID = 2 # of Templates = 4
2021-06-09 11:50:11.477 D/FAVOURITESINFO: Template is Template100 Link is Link100 Type is MY ID is 5
2021-06-09 11:50:11.477 D/FAVOURITESINFO: Template is Template101 Link is Link101 Type is PEOPLE ID is 6
2021-06-09 11:50:11.477 D/FAVOURITESINFO: Template is Template102 Link is ANIMALANIMALLink102 Type is ANIMAL ID is 7
2021-06-09 11:50:11.477 D/FAVOURITESINFO: Template is Template103 Link is Link103 Type is MY ID is 8
2021-06-09 11:50:11.477 D/FAVOURITESINFO: User is Sarah email is Sarah@email.com password is password USERID = 3 # of Templates = 5
2021-06-09 11:50:11.477 D/FAVOURITESINFO: Template is Template104 Link is Link104 Type is PEOPLE ID is 9
2021-06-09 11:50:11.478 D/FAVOURITESINFO: Template is Template106 Link is Link106 Type is MY ID is 11
2021-06-09 11:50:11.478 D/FAVOURITESINFO: Template is Template107 Link is Link107 Type is PEOPLE ID is 12
2021-06-09 11:50:11.478 D/FAVOURITESINFO: Template is Template108 Link is ANIMALANIMALLink108 Type is ANIMAL ID is 13
2021-06-09 11:50:11.478 D/FAVOURITESINFO: Template is Template109 Link is Link109 Type is MY ID is 14
