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;
    });

}

score 4 · Accepted Answer · answered Feb 04 '22 at 02:36

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 :-

THANK YOU SO MUCH! I've never had a so kind and complete answer. Yes getting id of the inserted item was what I needed. Thank you for all suggestions in particular for ForeignKey and I didn't know at all that I could do a Query method without SQL statement. — Daniele Scalco, Feb 04 '22 at 10:39
@DanieleScalco the `@Query("")` just fools room into allowing the @Transaction otherwise the insert method is little different from yours the main difference being that it doesn't require the Dao instance within the body. P.S If you believe that the above helped then please tick it as the answer, this helps others to understand that the answer was useful. — MikeT, Feb 04 '22 at 18:42
Everything worked, thank you again, but I have a question: why in CardTagCrossRef you put index only on idTag and not on idCard? — Daniele Scalco, Feb 05 '22 at 09:00
@DanieleScalco as Primary Key (an Index) is idCard,idTag you have an index according to idCard already. — MikeT, Feb 05 '22 at 09:04
For CardTagCrossRef.java, change "CASCADE" to "ForeignKey.CASCADE" (4 times) to fix unresolved-symbol error with newer versions of Room (no longer a suggestion as indicated in the comment for that code). — UpLate, Apr 23 '23 at 23:55

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

1 Answers1