java – Android Room – save dependencies

Question:

I'm trying to adapt Room to work with one-to-many dependencies. How to use @Relation to read records is described in [1,2,3,4]. But there is very little information about saving dependencies through @Relation – in [3] foreign keys are explicitly set when creating objects, in [4] it is only mentioned that saving (inserting) should go in one transaction. By analogy with [2], I tried this:

CompanyEntity.java

@Entity(tableName = "companies", indices = @Index(value = "name"))
public class CompanyEntity {
    @PrimaryKey (autoGenerate = true)
    public int id;
    @NonNull
    public final String name;

    public CompanyEntity(@NonNull String name) {
        this.name = name;
    }
}

EmployeeEntity.java

@Entity(tableName = "employee_list",
        foreignKeys = @ForeignKey(
                entity = CompanyEntity.class,
                parentColumns = "id",
                childColumns = "company_id",
                onDelete = CASCADE),
        indices = @Index("company_id"))
public class EmployeeEntity {
    @PrimaryKey(autoGenerate = true)
    public int id;
    @ColumnInfo(name = "company_id")
    // If int is used instead Integer 
    // android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787)
    // will be thrown
    public Integer companyId;
    @NonNull
    public final String name;

    public EmployeeEntity(@NonNull String name) {
        this.name = name;
    }
}

The next "dependency class"

public class CompanyEmployees {
    @Embedded
    public CompanyEntity company;

    @Relation (parentColumn = "id", entityColumn = "company_id", entity = EmployeeEntity.class)
    public List<EmployeeEntity> employees;

}

EmployeeDao.java to save

@Dao
public abstract class EmployeeDao {

    @Query("SELECT * FROM companies")
    public abstract List<CompanyEntity> selectAllCompanies();

    @Query("SELECT * FROM companies WHERE name LIKE :companyName")
    public abstract List<CompanyEmployees> getEmployeesByCompanyName(String companyName);

    @Transaction
    public void insert(String companyName, List<String> employeeNames) {
        // Prepare employee entities
        List<EmployeeEntity> employeeEntities = new ArrayList<>(employeeNames.size());
        for (String employeeName : employeeNames) {
            employeeEntities.add(new EmployeeEntity(employeeName));
        }

        // Create "relation" object and set-up fields
        CompanyEmployees companyEmployees = new CompanyEmployees();
        companyEmployees.company = new CompanyEntity(companyName);
        companyEmployees.employees = employeeEntities;

        // Insert "relation" object
        insert(companyEmployees.company);
        for (EmployeeEntity employeeEntity : companyEmployees.employees) {
            insert(employeeEntity);
        }

    }

    @Insert
    public abstract void insert(CompanyEntity company);

    @Insert
    public abstract void insert(EmployeeEntity employee);

}

But unsuccessfully:

If you use the primitive type int for company_id in EmployeeEntity, when you try to insert data, "android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787)" is thrown
When replaced with Integer, the data is successfully inserted into the table, but for all records in the employee_list table, the company_id column is NULL (it is obvious that @Query query will be useless in this case)

Upon further search, I found a similar question in English so[5] – as far as I understood from the discussion, it cannot save @Relation Room[6].

So far I see the following options:

Generate PK yourself (there is a problem with generating a unique key and its size) for CompanyEntity and manually save it to EmployeeEntity
In Dao, first save the CompanyEntity, read it and use the PK that SQLite generated for us when adding a record to the table, when adding the related EmployeeEntity (dislike – clumsy)

Actually the main question:

How is it "normal" to keep the @Relation dependency in Room?
Why is @Relation needed (if indeed persistence is not supported), if SQL JOIN is possible? For convenience (not fiddling with JOINs)?

List of sources:

Link to test project http://rgho.st/6ryvqvZ5f

Answer:

English SO is silent, so I propose the following solution.

The documentation says that the @Insert method can return a long , which is the rowId of the inserted element[1].

If the @Insert method receives only 1 parameter, it can return a long, which is the new rowId for the inserted item. If the parameter is an array or a collection, it should return long[] or List instead.

The SQLite[2] documentation says that if a table contains a column of type INTEGER PRIMARY KEY, then that column becomes an alias for ROWID :

If a table contains a column of type INTEGER PRIMARY KEY, then that column becomes an alias for the ROWID. You can then access the ROWID using any of four different names, the original three names described above or the name given to the INTEGER PRIMARY KEY column. All these names are aliases for one another and work equally well in any context.

From the above, I conclude that the INTEGER PRIMARY KEY (I also have it AUTOINCREMENT) of the record inserted into the table, and the rowId returned, when this record is inserted, must match. And you can do the following:

1) CompanyEntity.java

@Entity(tableName = "companies", indices = @Index(value = "name", unique = true)) public class CompanyEntity {
    @PrimaryKey (autoGenerate = true)
    public int id;
    @NonNull
    @ColumnInfo(name = "name")
    private final String mCompanyName;

    public CompanyEntity(@NonNull String companyName) {
        mCompanyName = companyName;
    }

    @NonNull
    public String getCompanyName() {
        return mCompanyName;
    }
}

2) EmployeeEntity.java

@Entity(tableName = "employee_list",
        foreignKeys = @ForeignKey(
                entity = CompanyEntity.class,
                parentColumns = "id",
                childColumns = "company_id",
                onDelete = CASCADE),
        indices = @Index("company_id"))
public class EmployeeEntity {
    @PrimaryKey(autoGenerate = true)
    public int id;
    @ColumnInfo(name = "company_id")
    private long mCompanyId;
    @NonNull
    @ColumnInfo(name = "name")
    private final String mName;

    public EmployeeEntity(@NonNull String name) {
        mName = name;
    }

    @NonNull
    public String getName() {
        return mName;
    }

    public long getCompanyId() {
        return mCompanyId;
    }

    public void setCompanyId(long companyId) {
        mCompanyId = companyId;
    }
}

3) EmployeeDao.java

@Dao
public abstract class EmployeeDao {

    @Query("SELECT * FROM companies")
    public abstract List<CompanyEntity> selectAllCompanies();

    @Transaction
    @Query("SELECT * FROM companies WHERE name LIKE :companyName")
    public abstract List<CompanyEmployees> getEmployeesByCompanyName(String companyName);

    @Transaction
    public void insert(CompanyEntity companyEntity, List<EmployeeEntity> employeeEntities) {

        // Save rowId of inserted CompanyEntity as companyId
        final long companyId = insert(companyEntity);

        // Set companyId for all related employeeEntities
        for (EmployeeEntity employeeEntity : employeeEntities) {
            employeeEntity.setCompanyId(companyId);
            insert(employeeEntity);
        }

    }

    // If the @Insert method receives only 1 parameter, it can return a long,
    // which is the new rowId for the inserted item.
    // https://developer.android.com/training/data-storage/room/accessing-data
    @Insert(onConflict = REPLACE)
    public abstract long insert(CompanyEntity company);

    @Insert
    public abstract void insert(EmployeeEntity employee);

}

This option works for me.

Full test project source code https://github.com/relativizt/android-room-one-to-many-auto-pk

Links

Post Views: 18