Android: how to insert data in manyToMany relationship in Room (Java)

Question

Guys I'm trying to create a database with a manyToMany relationship, I'm able to create the 2 tables of elements but I'm not able to populate the joining table. I don't know how should I insert datas.

This is Card.class:

@Entity
public class Card {

@PrimaryKey(autoGenerate = true)
private Long idCard;

@ColumnInfo(name = "title")
private String title;

@ColumnInfo(name = "taboo_word_1")
private String tabooWord1;

@ColumnInfo(name = "taboo_word_2")
private String tabooWord2;

@ColumnInfo(name = "taboo_word_3")
private String tabooWord3;

@ColumnInfo(name = "taboo_word_4")
private String tabooWord4;

@ColumnInfo(name = "taboo_word_5")
private String tabooWord5;

public Long getIdCard() {
    return idCard;
}

public void setIdCard(Long idCard) {
    this.idCard = idCard;
}

public String getTitle() {
    return title;
}

public void setTitle(String title) {
    this.title = title;
}

public String getTabooWord1() {
    return tabooWord1;
}

public void setTabooWord1(String tabooWord1) {
    this.tabooWord1 = tabooWord1;
}

public String getTabooWord2() {
    return tabooWord2;
}

public void setTabooWord2(String tabooWord2) {
    this.tabooWord2 = tabooWord2;
}

public String getTabooWord3() {
    return tabooWord3;
}

public void setTabooWord3(String tabooWord3) {
    this.tabooWord3 = tabooWord3;
}

public String getTabooWord4() {
    return tabooWord4;
}

public void setTabooWord4(String tabooWord4) {
    this.tabooWord4 = tabooWord4;
}

public String getTabooWord5() {
    return tabooWord5;
}

public void setTabooWord5(String tabooWord5) {
    this.tabooWord5 = tabooWord5;
}


}

Tag:

@Entity
public class Tag {

@PrimaryKey(autoGenerate = true)
private long idTag;

@ColumnInfo(name = "tag")
private String tag;

public Tag(String tag) {
    this.tag = tag;
}

public long getIdTag() {
    return idTag;
}

public void setIdTag(long idTag) {
    this.idTag = idTag;
}

public String getTag() {
    return tag;
}

public void setTag(String tag) {
    this.tag = tag;
}


@Override
public String toString() {
    return getTag();
}
}

This is DatabaseTaboom.class:

@Database(entities = {Card.class, Tag.class, CardTagCrossRef.class},
      version = 1)
public abstract class DatabaseTaboom extends RoomDatabase {

public static final String DATABASE_NAME = "db_taboom-1";

public abstract CardDAO cardDao();

public static DatabaseTaboom db;

// Singleton pattern
public static DatabaseTaboom getDatabase(Context applicationContext) {
    if (db == null) {
        db = Room.databaseBuilder(applicationContext, DatabaseTaboom.class, DATABASE_NAME)
                //.allowMainThreadQueries()
                .build();
    }
    return db;
}
}

This is CardDAO.class:

@Dao
public interface CardDAO {

@Insert(onConflict = OnConflictStrategy.REPLACE)
public void insertCard(Card card);

@Insert(onConflict = OnConflictStrategy.REPLACE)
public void insertTag(Tag tag);

@Insert(onConflict = OnConflictStrategy.REPLACE)
public void insertCardWithTags(CardTagCrossRef cardTagCrossRef);

// If called on an item not present in the DB it won't do anything
@Update
public void updateCard(Card card);

@Delete
public void deleteCard(Card card);

// With a query method you can also perform complex inserts/updates/deletes
// Transaction needed for relational classes
@Transaction
@Query("SELECT * FROM Card")
LiveData<List<CardWithTags>> getAllCards();
}

This is CardTagCrossRef.class:

@Entity(primaryKeys = {"idCard", "idTag"})
public class CardTagCrossRef {

public long idCard;
public long idTag;
}

CardWithTags:

public class CardWithTags {

@Embedded private Card card;
@Relation(
        parentColumn = "idCard",
        entityColumn = "idTag",
        associateBy = @Junction(CardTagCrossRef.class)
)

private List<Tag> tagList;

public CardWithTags() {

}

public CardWithTags(Card card, List<Tag> tagList) {
    this.card = card;
    this.tagList = tagList;
}

public Card getCard() {
    return card;
}

public void setCard(Card card) {
    this.card = card;
}

public List<Tag> getTagList() {
    return tagList;
}

public void setTagList(List<Tag> tagList) {
    this.tagList = tagList;
}

@Override
public String toString() {

    String s = getCard().toString();
    s += ", TAG[";
    for (Tag t: getTagList()) {
        s += t + "";
    }
    s+="]";

    return s;
}
}

And this is the method that I wrote to insert a card:

public void insertCard(CardWithTags card) {

    Log.d(TAG, ">>insertCard(): " + card);
    executor.execute(() -> {

        cardDAO.insertCard(card.getCard());
        for (Tag t: card.getTagList()) {
            cardDAO.insertTag(t);
            CardTagCrossRef cardTagCrossRef = new CardTagCrossRef();
            cardTagCrossRef.idCard = card.getCard().getIdCard();
            cardTagCrossRef.idTag = t.getIdTag();
            Log.d(TAG, "CardCrossRef:" + cardTagCrossRef.idCard + cardTagCrossRef.idTag);
            cardDAO.insertCardWithTags(cardTagCrossRef);
        }

        // Check if tags already exists
        cardListIsUpdatedWithDb = false;
    });

}

Answer 1

First you should amend the Dao's so that they return the id of the inserted row enabling you to ascertain the actual id of the inserted rows. So :-

@Insert(onConflict = OnConflictStrategy.REPLACE)
public long insertCard(Card card);

@Insert(onConflict = OnConflictStrategy.REPLACE)
public long insertTag(Tag tag);

@Insert(onConflict = OnConflictStrategy.REPLACE)
public long insertCardWithTags(CardTagCrossRef cardTagCrossRef);

This allows you to retrieve the respective id when you insert either a Card or a Tag (note that for a CardTagCrossRef insertion this will be the rowid, a normally hidden row).

So you could then have use long cardId = cardDAO.insertCard(card.getCard()); and not then need to attempt to use cardTagCrossRef.idCard = card.getCard().getIdCard(); where the card DOES NOT have the id of the inserted card (part of the issue you are facing).

And likewise for the Tag.

So you could use :-

    long cardId = cardDAO.insertCard(card.getCard());
    for (Tag t: card.getTagList()) {
        long tagId = cardDAO.insertTag(t);
        CardTagCrossRef cardTagCrossRef = new CardTagCrossRef();
        cardTagCrossRef.idCard = cardId;
        cardTagCrossRef.idTag = tagId;
        Log.d(TAG, "CardCrossRef:" + cardTagCrossRef.idCard + cardTagCrossRef.idTag);
        cardDAO.insertCardWithTags(cardTagCrossRef);
    }

However, with a few changes I believe that that can make things far more flexible and have an insert that effectively does what you want within the Dao's.

SO perhaps consider the following that culminates in a working DEMO

Card

@Entity
public class Card {

    @PrimaryKey/*(autoGenerate = true) SUGGESTED suppression of autogenerate as will still autogenerate but more efficiently */
    private Long idCard;

    @ColumnInfo(name = "title")
    private String title;

    @ColumnInfo(name = "taboo_word_1")
    private String tabooWord1;

    @ColumnInfo(name = "taboo_word_2")
    private String tabooWord2;

    @ColumnInfo(name = "taboo_word_3")
    private String tabooWord3;

    @ColumnInfo(name = "taboo_word_4")
    private String tabooWord4;

    @ColumnInfo(name = "taboo_word_5")
    private String tabooWord5;

    /* Constructors added */
    public Card(){}

    @Ignore
    public Card(Long idCard,String title, String tabooWord1, String tabooWord2, String tabooWord3, String tabooWord4, String tabooWord5) {
        this.idCard = idCard;
        this.title = title;
        this.tabooWord1 = tabooWord1;
        this.tabooWord2 = tabooWord2;
        this.tabooWord3 = tabooWord3;
        this.tabooWord4 = tabooWord4;
        this.tabooWord5 = tabooWord5;
    }

    public Long getIdCard() {
        return idCard;
    }

    public void setIdCard(Long idCard) {
        this.idCard = idCard;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getTabooWord1() {
        return tabooWord1;
    }

    public void setTabooWord1(String tabooWord1) {
        this.tabooWord1 = tabooWord1;
    }

    public String getTabooWord2() {
        return tabooWord2;
    }

    public void setTabooWord2(String tabooWord2) {
        this.tabooWord2 = tabooWord2;
    }

    public String getTabooWord3() {
        return tabooWord3;
    }

    public void setTabooWord3(String tabooWord3) {
        this.tabooWord3 = tabooWord3;
    }

    public String getTabooWord4() {
        return tabooWord4;
    }

    public void setTabooWord4(String tabooWord4) {
        this.tabooWord4 = tabooWord4;
    }

    public String getTabooWord5() {
        return tabooWord5;
    }

    public void setTabooWord5(String tabooWord5) {
        this.tabooWord5 = tabooWord5;
    }
}

• 2 changes an more flexible constructor and not using autogenerate = true (but that does automatically generate id's BUT without the overheads of the SQLite AUTOINCREMENT which is what autogenerate = true adds).

Tag (similar changes)

@Entity
public class Tag {

    @PrimaryKey/*(autoGenerate = true) SUGGESTED suppression of autogenerate*/
    private Long idTag;

    @ColumnInfo(name = "tag")
    private String tag;

    public Tag(){}

    @Ignore
    public Tag(Long idTag, String tag) {
        this.idTag = idTag;
        this.tag = tag;
    }

    @Ignore
    public Tag(String tag) {
        this.tag = tag;
    }

    public Long getIdTag() {
        return idTag;
    }

    public void setIdTag(Long idTag) {
        this.idTag = idTag;
    }

    public String getTag() {
        return tag;
    }

    public void setTag(String tag) {
        this.tag = tag;
    }

    @Override
    public String toString() {
        return getTag();
    }
}

CardTagCrossRef (added ForeignKey constraints to enforce/manage referential integrity)

@Entity(
        primaryKeys = {"idCard", "idTag"}
        /* SUGGESTED */
        , foreignKeys = {
                @ForeignKey(
                        entity = Card.class,
                        parentColumns = "idCard",
                        childColumns = "idCard",
                        /* SUGGESTED with ForeignKey */
                        onDelete = CASCADE,
                        onUpdate = CASCADE
                ),
                @ForeignKey(
                        entity = Tag.class,
                        parentColumns = "idTag",
                        childColumns = "idTag",
                        /* SUGGESTED with ForeignKey */
                        onDelete = CASCADE,
                        onUpdate = CASCADE
                )
        }
        )
public class CardTagCrossRef {

    public long idCard;
    @ColumnInfo(index = true) /* SUGGESTED */
    public long idTag;

    public CardTagCrossRef(){}
    @Ignore
    public CardTagCrossRef(long idCard, long idTag) {
        this.idCard = idCard;
        this.idTag = idTag;
    }
}

CardWithTags

identical other than @Ignore annotation on the CardWithTags(Card card, List<Tag> tagList) constructor to supress warnings about multiple good consctructors.

i.e.

....
@Ignore /*<<<<< SUGGESTED */
public CardWithTags(Card card, List<Tag> tagList) {
    this.card = card;
    this.tagList = tagList;
}
....

CardDAO (new INSERT + return values)

@Dao
abstract class CardDAO {
/* public interface CardDAO {  CHANGED TO abstract class to allow functions with bodies */

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    abstract long insertCard(Card card); /* Returns long (inserted row id) */

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    abstract long insertTag(Tag tag); /* Returns long (inserted row id) */

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    abstract long insertCardWithTags(CardTagCrossRef cardTagCrossRef); /* Returns long (inserted row id) */

    /* NEW INSERT */
    @Query("")
    @Transaction
    long[] insert(Card card, List<Tag> tags) {
        long[] rv = new long[tags.size() + 1];
        int ix = 0;
        rv[ix++] = insertCard(card);
        if (rv[ix-1] > -1) {
            for (Tag t : tags) {
                rv[ix++] = insertTag(t);
                if (rv[ix-1] > -1) {
                    insertCardWithTags(new CardTagCrossRef(rv[0],rv[ix-1]));
                }
            }

        }
        return rv;
    }

    // If called on an item not present in the DB it won't do anything
    @Update
    abstract int updateCard(Card card); /* returns number of updated rows */

    @Delete
    abstract int deleteCard(Card card); /* returns number of deleted rows */



    // With a query method you can also perform complex inserts/updates/deletes
// Transaction needed for relational classes
    @Transaction
    @Query("SELECT * FROM Card")
    /* abstract LiveData<List<CardWithTags>> getAllCards(); COMMENTED OUT to allow demo to run on main thread */
    abstract List<CardWithTags> getAllCards(); /* same but not with LiveData */
}

DatabaseTaboom (allow main thread + exportSchema = false to suppress warning)

@Database(entities = {Card.class, Tag.class, CardTagCrossRef.class},
        version = 1/* SUGGESTED */ , exportSchema = false)
public abstract class DatabaseTaboom extends RoomDatabase {


    public static final String DATABASE_NAME = "db_taboom-1";
    abstract CardDAO cardDao();
    public static DatabaseTaboom db;

    // Singleton pattern
    public static DatabaseTaboom getDatabase(Context applicationContext) {
        if (db == null) {
            db = Room.databaseBuilder(applicationContext, DatabaseTaboom.class, DATABASE_NAME)
                    .allowMainThreadQueries() /* uncommented for testing */
                    .build();
        }
        return db;
    }
}

Finally the DEMO MainActivity with some examples of inserting Cards, Tags and CardTagCrossRef's followed by extracting them all outputting the results to the log.

public class MainActivity extends AppCompatActivity {

    DatabaseTaboom db;
    CardDAO dao;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        db = DatabaseTaboom.getDatabase(this);
        dao = db.cardDao();

        /* Simple but long winded */
        long c1id = dao.insertCard(new Card(null,"Card1","tw1","tw2","tw3","tw4","tw5"));
        long t1id = dao.insertTag(new Tag(null,"TAG1"));
        CardTagCrossRef ctcr1 = new CardTagCrossRef();
        ctcr1.idCard = c1id;
        ctcr1.idTag = t1id ;
        dao.insertCardWithTags(ctcr1);
        /* Using additional constructor for CardTagCrossRef */
        long t2id = dao.insertTag(new Tag("TAG2"));
        dao.insertCardWithTags(new CardTagCrossRef(c1id,t2id));
        /* More dynamic  BUT don't know the actual inserted id's of the Card and Tag */
        dao.insertCardWithTags(
                new CardTagCrossRef(dao.insertCard(new Card(100l,"Card2","c2tw1","c2tw2","c2tw3","c2tw4","c2tw5")),dao.insertTag(new Tag(null,"TAG3"))));

        CardWithTags cwt = new CardWithTags(
                new Card(null,"CARD3","c3tw1","c3tw2","c3tw3","c3tw4","c3tw5"),
                Arrays.asList(
                        new Tag(null,"TAG4"), new Tag("TAG5"), new Tag("TAG6")
                )
        );

        /* Amended insert function */
        insertCard(cwt,dao);

        /* Using new insert funciotn */
        dao.insert(
                new Card(1000l,"CARD4","c4tw1","c4tw2","c4tw3","c4tw4","c4tw5"),
                Arrays.asList(
                        new Tag(null,"TAG7"), new Tag(500l,"TAG8"),new Tag(null,"TAG9")
                )
        );
         /* Extract the results and output to the log */
        for(CardWithTags cwtlist: dao.getAllCards()) {
            Log.d("CWTINFO","Card is " + cwtlist.getCard().getTitle() + " TabooWord1 is " + cwtlist.getCard().getTabooWord1() + " it has " + cwtlist.getTagList().size() + " tags. They are:-");
            for(Tag t: cwtlist.getTagList()) {
                Log.d("CWTINFO_TAG","\tTAG is " + t.getTag());
            }
        }
    }
    public void insertCard(CardWithTags card, CardDAO cardDAO) {
        final String TAG = "INSERTCARDINFO";

        Log.d(TAG, ">>insertCard(): " + card);
        /*
        executor.execute(() -> {
         */

            long currentCardId = cardDAO.insertCard(card.getCard());
            for (Tag t: card.getTagList()) {
                long currentTagId = cardDAO.insertTag(t);
                CardTagCrossRef cardTagCrossRef = new CardTagCrossRef();
                cardDAO.insertCardWithTags(new CardTagCrossRef(currentCardId,currentTagId));

                /*
                cardTagCrossRef.idCard = card.getCard().getIdCard();
                cardTagCrossRef.idTag = t.getIdTag();
                 */
                /*
                 OR with new Contsructor

                CardTagCrossRef ctcr = new CardTagCrossRef(currentCardId,currentTagId);
                */
                /* AND THEN cardDAO.insertCardWithTags(cardTagCrossRef); */
                Log.d(TAG, "CardCrossRef:" + cardTagCrossRef.idCard + cardTagCrossRef.idTag);
            }

            /*
            // Check if tags already exists
            cardListIsUpdatedWithDb = false;
             */
        /*})*/;
    }
}

When run (after new install as only designed to run the once) the Log includes:-

2022-02-04 13:29:10.569D/INSERTCARDINFO: >>insertCard(): a.a.so70979022javaroom.Card@d751e5e, TAG[TAG4TAG5TAG6]
2022-02-04 13:29:10.573D/INSERTCARDINFO: CardCrossRef:00
2022-02-04 13:29:10.578I/chatty: uid=10194(a.a.so70979022javaroom) identical 1 line
2022-02-04 13:29:10.581D/INSERTCARDINFO: CardCrossRef:00
2022-02-04 13:29:10.600D/CWTINFO: Card is Card1 TabooWord1 is tw1 it has 2 tags. They are:-
2022-02-04 13:29:10.600D/CWTINFO_TAG:   TAG is TAG1
2022-02-04 13:29:10.601D/CWTINFO_TAG:   TAG is TAG2
2022-02-04 13:29:10.601D/CWTINFO: Card is Card2 TabooWord1 is c2tw1 it has 1 tags. They are:-
2022-02-04 13:29:10.601D/CWTINFO_TAG:   TAG is TAG3
2022-02-04 13:29:10.601D/CWTINFO: Card is CARD3 TabooWord1 is c3tw1 it has 3 tags. They are:-
2022-02-04 13:29:10.601D/CWTINFO_TAG:   TAG is TAG4
2022-02-04 13:29:10.601D/CWTINFO_TAG:   TAG is TAG5
2022-02-04 13:29:10.601D/CWTINFO_TAG:   TAG is TAG6
2022-02-04 13:29:10.601D/CWTINFO: Card is CARD4 TabooWord1 is c4tw1 it has 3 tags. They are:-
2022-02-04 13:29:10.601D/CWTINFO_TAG:   TAG is TAG7
2022-02-04 13:29:10.601D/CWTINFO_TAG:   TAG is TAG8
2022-02-04 13:29:10.601D/CWTINFO_TAG:   TAG is TAG9

Via App Inspection then :-

and :-

and :-