Home > other >  Create a relationship between two tables in RoomLibrary
Create a relationship between two tables in RoomLibrary

Time:11-15

I have two classes called Transaction and Person.

Transaction Class:

@Entity(tableName = "Transaction")
public class Transaction {
    @PrimaryKey(autoGenerate = true)
    private long id;
    private int traderID;
    private int amount;
    private long transactionDate;
    private long dateOfRegistration;
    private String about;
    private long time;
    private String from;
    private String transactionType;
    private long transactionNum;
    private String description;

//Getter and Setter

Person Class:

@Entity(tableName = "Person")
public class Person {
    @PrimaryKey(autoGenerate = true)
    private long id;
    private String name;
    private long TransactionId;

//Getter and Setter
  • I want these two classes to be connected via TraderId in the Transaction class and TransactionId in the Person class.
  • Is this a one to one relationship?
  • Do I have to build a Dao for each class?

I read the Google Docs; But they seem confusing.

CodePudding user response:

I want these two classes to be connected via TraderId in the Transaction class and TransactionId in the Person class.

Is this a one to one relationship?

No it's a 1-many i.e. a Transaction can be referenced by many Persons.

  • For a one-one relationship you might as well have the one table with Transaction and Person.
  • Perhaps having a Transaction referencing a Person is what you want.
  • Perhaps you want a Transaction to be able to have many Persons and that a Person can have many Transactions, in that case you would want a Many-Many relationship, in which case you would have an intermediate table (associative/reference/mapping table (all names for the same thing)). Such a table would have a column for the Transaction and a Column for the Person.

Do I have to build a Dao for each class?

Dao's can be all in one class or distributed over classes.

However, as you are very likely to want to use the relationship if you go the Dao class per entity then you would probably have extra Dao classes for relationship handling.

That is you are very likely going to want to get a Transaction along with the Person. In which case you'd have a POJO with both Person and Transaction so you'd perhaps have yet another class with Daos.


So along with you Transaction and Person classes you could have :-

PersonDao

@Dao
interface PersonDao {
    @Insert
    long insert(Person person);
    @Query("SELECT * FROM Person")
    List<Person> getAllPersons();
    @Query("SELECT * FROM Person WHERE id=:personId")
    Person getPersonById(long personId);
}

and likewise for TransactionDao

and or you could have AllDao

@Dao
abstract class AllDao {

    /* Abstract Class so need to define methods as abstract, but can have non abstract classes */

    @Insert
    abstract long insert(Transaction transaction);
    @Query("SELECT * FROM `Transaction`")
    abstract List<Transaction> getAllTransactions();
    @Query("SELECT * FROM `Transaction` WHERE id=:transactionId")
    abstract Transaction getTransactionById(long transactionId);


    @Insert
    abstract long insert(Person person);
    @Query("SELECT * FROM Person")
    abstract List<Person> getAllPersons();
    @Query("SELECT * FROM Person WHERE id=:personId")
    abstract Person getPersonById(long personId);

} 
  • Note that the above is an abstract class rather than an interface. abstract classes are a little more flexible.

Of course as mentioned previously you would very likely want to get a Person and their Transaction so you could have a POJO PersonWithTransaction e.g. :-

class PersonWithTransaction {

    @Embedded
    Person person;
    @Relation(
            entity = Transaction.class,
            parentColumn = "id",
            entityColumn = "id"
    )
    Transaction transaction;
}

Perhaps with a PersonWithTransactionDao class e.g. :-

@Dao
interface PersonWithTransactionDao {
    @Query("SELECT * FROM Person")
    List<PersonWithTransaction> getAllPersonsWithTransaction();
    @Query("SELECT * FROM Person WHERE id=:personId")
    PersonWithTransaction getPersonWithTransactionByPersonId(long personId);
}
  • you would likely NOT have inserts. updates or deletes, just queries.
  • of course the above could be in other Dao's

You may also find it useful to be able to get Transactions with the related Person or Persons e.g. TransactionWithPersons POJO

class TransactionWithPersons {
    @Embedded
    Transaction transaction;
    @Relation(
            entity = Person.class,
            parentColumn = "id",
            entityColumn = "id"
    )
    List<Person> persons;
}
  • Note that as in theory a Transaction can have many Persons then you have a Person List. Due to a Person having only 1 Transaction then List is not required in PersonWithTransaction. However, typically @Relation is used with a List.

You may also wish to consider enforcing referential integrity. That is that children (Person is a child to Transaction) or never orphaned.

As it stands you could add a child with a Transaction id that doesn't exist and SQLite would not complain. However, if you tried to get the non-existant Transaction (e.g. using getPersonWithTransactionById query) then you would have issues as the Transaction could not be retrieved.

You can define rules to ensure that the Transaction exists when adding a Person. You use ForeignKeys to define such rules (constraints). So Person could be :-

@Entity(tableName = "Person",
        /* Defining Foreign Key constraints (rules) enforces referential integrity - Optional */
        foreignKeys = {
                @ForeignKey(
                        entity = Transaction.class,
                        parentColumns = "id",
                        childColumns = "id",
                        /* Optional Define onDelete and onUpdate can be useful
                            e.g. if a Transaction is deleted then all the Persons
                            are deleted that reference/relate to the Transaction are deleted

                            onUpdate is less useful but if the Transaction id is changed then
                            all the Persons that referenced the Transaction will be changed
                            to reflect the update id
                         */
                        onDelete = ForeignKey.CASCADE,
                        onUpdate = ForeignKey.CASCADE
                )
        }
)
public class Person {
    @PrimaryKey(autoGenerate = true)
    private long id;
    private String name;
    private long TransactionId;

....
  • Related