Create table with Foreign Collection Field

Question

I have this abstract class:

DomainItem

abstract public class DomainItem {

    @DatabaseField(generatedId = true)
    protected long id;

    @ForeignCollectionField(eager = false)
        protected ForeignCollection<ContentItem> contentItens;

    //getters and setters
}

ContentItem:

abstract public class ContentItem {

    @DatabaseField(generatedId = true)
    protected long id;

    @DatabaseField(foreign = true)
    protected DomainItem domainItem;


    @DatabaseField()
    protected String content;

    //getters and setters
}

And these (no abstract):

@DatabaseTable()
public class PhytoterapicItem extends DomainItem{

    public PhytoterapicItem(){

    }

}

PhytoterapicContent

@DatabaseTable(tableName = "phytoterapiccontent")
public class PhytoterapicContent extends ContentItem {

    @DatabaseField(canBeNull = false)
    private String defaultName;

    @DatabaseField(canBeNull = false)
    private String scientificName;

    //getters and setters
}

In my DatabaseHelper I trying create the tables:

//DatabaseHelper
...
@Override
public void onCreate(SQLiteDatabase db, ConnectionSource connectionSource) {
    try {
        Log.i(TAG, "onCreate");
        TableUtils.createTable(connectionSource, PhytoterapicContent.class);
        Log.i(TAG, "Created table PhytoterapicContent");

        TableUtils.createTable(connectionSource, PhytoterapicItem.class);
        Log.i(TAG, "Created table PhytoterapicItem");
    catch{
       ...
    }

The table PhytoterapicContent is created. But I got the follow error:

java.sql.SQLException: Foreign collection class br.com.project.model.ContentItem for field 'contentItens' column-name does not contain a foreign field of class br.com.project.model.PhytoterapicItem

Solution 1

So the exception message was designed to help here. To quote it with the class names removed:

Foreign collection class ContentItem for field contentItens column-name does not contain a foreign field of class PhytoterapicItem

That looks to be the case. Whenever you have ForeignCollection, the class contained by the collection must have a foreign field back to the parent class.

In your case, PhytoterapicItem extends the DomainItem class which has a ForeignCollection of ContentItem objects. That means that ContentItem must have a foreign field of type PhytoterapicItem. Otherwise, how would ORMLite know which of the ContentItem items in the table are associated with a particular PhytoterapicItem.

The example of Account and Order objects in the foreign collection documentation may help you with your schema. Each Account has a foreign collection of Order objects. Whenever you query for an Account a separate query is performed to find the collection of Order objects that correspond to a particular Account. That means that each Order has to have a foreign Account object.

Solution 2

It took me a while to notice/understand this critical block from the docs (http://ormlite.com/docs/foreign-collection):

Remember that when you have a ForeignCollection field, the class in the collection must (in this example Order) must have a foreign field for the class that has the collection (in this example Account). If Account has a foreign collection of Orders, then Order must have an Account foreign field. It is required so ORMLite can find the orders that match a particular account.

The reason I was confused is because I didn't find any example code (in docs or example projects) where the "contained class" references the class with the collection. It is described verbally, but given the nature of the relationship - for some the description I think can be a bit tricky to follow when seeing it the first few times.

As listed above in the original question (which is how I finally got the idea to try the solution I stumbled upon), the example block below seems to be the correct way to map a one-to-many collection relationship in OrmLite.

In addition, there is a note about how the collection-holder class needs to be set into the collection element class.

---

Here are the main steps to take care of:

A. In the class that has the collection (DomainItem in this case), annotate collection field this way:

@ForeignCollectionField(eager = true)

B. In the class that is contained in the collection (ContentItem in this case), you must have an explicit reference back to the parent class that contains the collection:

@DatabaseField(foreign = true) protected DomainItem domainItem;

C. Before persisting ContentItem, you must save the DomainItem to that ContentItem in order to set the foreign key back to DomainItem:

curContentItem.setDomainItem(curDomainItem);

contentItemDao.create(curContentItem);`

I figured this out when my collection wasn't being retrieved initially. I looked at the table for ContentItem, and the DomainItem_id was never set there.

EXAMPLE:

public class DomainItem {

    @DatabaseField(generatedId = true)
    protected long id;

    @ForeignCollectionField(eager = false)
    protected ForeignCollection<ContentItem> contentItens;

    // ...
}


public class ContentItem {

    @DatabaseField(generatedId = true)
    protected long id;

    @DatabaseField(foreign = true)
    protected DomainItem domainItem;

    public void setDomainItem(DomainItem domainItem) {

       this.domainItem = domainItem;
    }
}