Associating Tables Using Room Database In Android Studio

March 31, 2024 Post a Comment

How can I associate two tables so that I can create a database that sort of matches the image attached. I understand that the experiment ID, Date, Name has to be part of the paren

Solution 1:

I believe the following Working Example is one of the ways that you can achieve what you want.

The Code

The Experiment Entity (table) Experiment.java

@EntitypublicclassExperiment {

    @PrimaryKey(autoGenerate = true)
    private long experimentId;
    privateString experimentName;
    privateString experimentDate;

    publicExperiment() {
    }

    @IgnorepublicExperiment(String name, String date) {
        this.experimentName = name;
        this.experimentDate = date;
    }

    public long getExperimentId() {
        return experimentId;
    }

    publicvoidsetExperimentId(long experimentId) {
        this.experimentId = experimentId;
    }

    publicStringgetExperimentName() {
        return experimentName;
    }

    publicvoidsetExperimentName(String experimentName) {
        this.experimentName = experimentName;
    }

    publicStringgetExperimentDate() {
        return experimentDate;
    }

    publicvoidsetExperimentDate(String experimentDate) {
        this.experimentDate = experimentDate;
    }
}

Nothing special except perhaps the @Ignore'd constructor (for convenience)

The Trial Entity Trial.java

@EntitypublicclassTrial {

    @PrimaryKey(autoGenerate = true)
    private long trialId;
    @ForeignKey(entity = Experiment.class, parentColumns = {BaseColumns._ID},childColumns = "parentExperiment", onDelete = ForeignKey.CASCADE, onUpdate = ForeignKey.CASCADE)
    private long parentExperiment;
    privateString trialVariable;
    privateString trialResult;

    publicTrial() {
    }

    @IgnorepublicTrial(long parentExperimentId, String variable, String result) {
        this.parentExperiment = parentExperimentId;
        this.trialVariable = variable;
        this.trialResult = result;
    }

    public long getTrialId() {
        return trialId;
    }

    publicvoidsetTrialId(long trialId) {
        this.trialId = trialId;
    }

    public long getParentExperiment() {
        return parentExperiment;
    }

    publicvoidsetParentExperiment(long parentExperiment) {
        this.parentExperiment = parentExperiment;
    }

    publicStringgetTrialVariable() {
        return trialVariable;
    }

    publicvoidsetTrialVariable(String trialVariable) {
        this.trialVariable = trialVariable;
    }

    publicStringgetTrialResult() {
        return trialResult;
    }

    publicvoidsetTrialResult(String trialResult) {
        this.trialResult = trialResult;
    }
}

Nothing special except perhaps the @Ignore'd constructor (for convenience)

Dao.java (combined for convenience)

publicinterfaceDao {

    @Insert(onConflict = OnConflictStrategy.IGNORE)long[] insertExperiments(Experiment... experiments);

    @Insert(onConflict = OnConflictStrategy.IGNORE)longinsertExperiment(Experiment experiment);

    @Insert(onConflict = OnConflictStrategy.IGNORE)long[] insertTrials(Trial... trials);

    @Insert(onConflict = OnConflictStrategy.IGNORE)longinsertTrial(Trial trial);

    @Update(onConflict = OnConflictStrategy.IGNORE)intupdateExperiments(Experiment... experiments);

    @Update(onConflict = OnConflictStrategy.IGNORE)intupdateExperiment(Experiment experiment);

    @Update(onConflict = OnConflictStrategy.IGNORE)intupdateTrials(Trial... trials);

    @Update(onConflict = OnConflictStrategy.IGNORE)intupdateTrial(Trial trial);

    @DeleteintdeleteExperiments(Experiment... experiments);

    @DeleteintdeleteExperiment(Experiment experiment);

    @DeleteintdeleteTrials(Trial... trials);

    @DeleteintdeleteTrial(Trial trial);

    @Query("SELECT * FROM Experiment")
    List<Experiment> getAllexperiments();

    @Query("SELECT * FROM Experiment WHERE experimentDate BETWEEN :startdate AND :enddate")
    List<Experiment> getExperimentsInDateRange(String startdate, String enddate);

    @Query("SELECT * FROM Trial")
    List<Trial> getAllTrials();

    @Query("SELECT * FROM Experiment JOIN Trial ON parentExperiment = experimentId")
    List<TrialWithExperiment> getExperimentsWithTrials();


    publicclassTrialWithExperiment {
        privatelong experimentId;
        private String experimentName;
        private String experimentDate;
        privatelong trialId;
        private String trialVariable;
        private String trialResult;

        publiclonggetExperimentId() {
            return experimentId;
        }

        publicvoidsetExperimentId(long experimentId) {
            this.experimentId = experimentId;
        }

        public String getExperimentName() {
            return experimentName;
        }

        publicvoidsetExperimentName(String experimentName) {
            this.experimentName = experimentName;
        }

        public String getExperimentDate() {
            return experimentDate;
        }

        publicvoidsetExperimentDate(String experimentDate) {
            this.experimentDate = experimentDate;
        }

        publicvoidsetTrialId(long trialId) {
            this.trialId = trialId;
        }

        publiclonggetTrialId() {
            return trialId;
        }

        public String getTrialVariable() {
            return trialVariable;
        }

        publicvoidsetTrialVariable(String trialVariable) {
            this.trialVariable = trialVariable;
        }

        public String getTrialResult() {
            return trialResult;
        }

        publicvoidsetTrialResult(String trialResult) {
            this.trialResult = trialResult;
        }
    }
}

Note the TrialWithExperiment class, this defines a combination of a Trial and it's owning Experiment.
Note how column names are distinct e.g. no just id but experimentId and trialId differentiates them.
Note the last @Query getExperimentsWithTrials() this will return a list of Trials with their experiments.

The @Database ExperimentDatabase.java

@Database(entities = {Experiment.class, Trial.class}, version = 1,exportSchema = false)
publicabstractclassExperimentDatabaseextendsRoomDatabase {
    publicabstract Dao getDao();
}

Finally an activity that utilises the above :-

Baca Juga

publicclassMainActivityextendsAppCompatActivity {

    ExperimentDatabase mDB;

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

        mDB =Room.databaseBuilder(this,ExperimentDatabase.class,"experimentdb").allowMainThreadQueries().build();
        mDB.getDao().insertExperiments(new Experiment[]{
                new Experiment("Experiment 1","2019-01-01"),
                new Experiment("Experiment 2", "2019-02-01")
        });
        List<Experiment> experiments = mDB.getDao().getExperimentsInDateRange("2019-01-01","2019-12-31");
        for (Experiment e: experiments) {
            Log.d("EXPERIMENTS", "Experiment is "+ e.getExperimentName() +" on "+ e.getExperimentDate());
        }

        experiments = mDB.getDao().getAllexperiments();
        for (Experiment e: experiments) {
            Log.d("EXPERIMENTS", "Experiment is "+ e.getExperimentName() +" on "+ e.getExperimentDate());
        }

        for (Experiment e: experiments) {
            mDB.getDao().insertTrial(
                    new Trial(
                            e.getExperimentId(),
                            "Variable for "+ e.getExperimentName(),
                            "Result for Experiment on "+ e.getExperimentDate()
                    )
            );
        }

        List<Trial> trials = mDB.getDao().getAllTrials();
        for (Trial t: trials ) {
            Log.d("TRIAL ",
                    "Trial is for ExperimentID "+String.valueOf(t.getParentExperiment()) +"\n\tVariable = "+ t.getTrialVariable() +"Result = "+ t.getTrialResult()
            );
        }


        List<Dao.TrialWithExperiment> trialsWithExperiments = mDB.getDao().getExperimentsWithTrials();

        for (Dao.TrialWithExperiment te:trialsWithExperiments) {
            Log.d(
                    "TRIALWITHEXPERIMENT",
                    "Experiment Name = "+ te.getExperimentName() +"\n\tExperiment Date = "+ te.getExperimentDate() +"\n\t\tTrial Variable = "+ te.getTrialVariable() +"\n\t\tTrial Result = "+ te.getTrialResult()
            );

        }
    }
}

This :-

Adds 2 experiments (every run)
Retrieves Experiments in a date range (this assumes an SQLite recognised date format) and outputs them to the log.
Retrieves all Experiments and outputs them to the log.
Uses the List of experiments to add a Trial to each experiment.
Retrieves all Trials and outputs them to the log.
Retrieves all Trials along with the parent Experiment and outputs them to the log.

The Result

05-2810:19:42.7705750-5750/?D/EXPERIMENTS:ExperimentisExperiment1on2019-01-0105-2810:19:42.7705750-5750/?D/EXPERIMENTS:ExperimentisExperiment2on2019-02-0105-2810:19:42.7765750-5750/?D/EXPERIMENTS:ExperimentisExperiment1on2019-01-0105-2810:19:42.7765750-5750/?D/EXPERIMENTS:ExperimentisExperiment2on2019-02-0105-2810:19:42.7865750-5750/?D/TRIAL:TrialisforExperimentID1Variable=VariableforExperiment1Result=ResultforExperimenton2019-01-0105-2810:19:42.7865750-5750/?D/TRIAL:TrialisforExperimentID2Variable=VariableforExperiment2Result=ResultforExperimenton2019-02-0105-2810:19:42.7875750-5750/?D/TRIALWITHEXPERIMENT:ExperimentName=Experiment1ExperimentDate=2019-01-01TrialVariable=VariableforExperiment1TrialResult=ResultforExperimenton2019-01-0105-2810:19:42.7875750-5750/?D/TRIALWITHEXPERIMENT:ExperimentName=Experiment2ExperimentDate=2019-02-01TrialVariable=VariableforExperiment2TrialResult=ResultforExperimenton2019-02-01

Alternative/Additional

Another approach could be to utilise @Relation to create an object per Experiment that includes a list of the related/associated Trial(s) in that object.

Expanding upon the above then the following could be added to Dao.java

@Query("SELECT * FROM  Experiment")
List<ExperimentWithTrials> getExperimentsAndTrials();

classExperimentWithTrials {
    private long experimentId;
    privateString experimentName;
    privateString experimentDate;
    @Relation(parentColumn = "experimentId", entityColumn = "parentExperiment")
    List<Trial> trials;

    public long getExperimentId() {
        return experimentId;
    }

    publicvoidsetExperimentId(long experimentId) {
        this.experimentId = experimentId;
    }

    publicStringgetExperimentName() {
        return experimentName;
    }

    publicvoidsetExperimentName(String experimentName) {
        this.experimentName = experimentName;
    }

    publicStringgetExperimentDate() {
        return experimentDate;
    }

    publicvoidsetExperimentDate(String experimentDate) {
        this.experimentDate = experimentDate;
    }

    publicList<Trial> getTrials() {
        return trials;
    }

    publicvoidsetTrials(List<Trial> trials) {
        this.trials = trials;
    }
}

and then the following could be added to MainActivity.java

List<Dao.ExperimentWithTrials> experimentsWithTrials = mDB.getDao().getExperimentsAndTrials();
    for (Dao.ExperimentWithTrials et: experimentsWithTrials ) {
        Log.d(
                "EXPERIMENTANDTRIALS",
                "Experiment Name = "+ et.getExperimentName() +"\n\tExperiment Date = "+ et.getExperimentDate()
        );
        for (Trial t: et.getTrials()) {
            Log.d(
                    "TRIALFOREXPERIMENT",
                    "\t\tVariable = "+ t.getTrialVariable() +"\n\t\tResult = "+ t.getTrialResult()
            );
        }
    }
}

Note how the Trials are obtained via the looping through the list of Trials embedded with the ExperimentWithTrials object, as opposed to the list of combined Experiment and Trial data from the previous.
- This probably to purer OO way.
- However, SQLite wise this appears to be cumbersome/inefficient as it appears to run multiple queries. One to get the Experiments and then another to get the underlying Trials for each experiment.

Resultant additional output

05-2813:05:35.0526524-6524/?D/EXPERIMENTANDTRIALS:ExperimentName=Experiment1ExperimentDate=2019-01-0105-2813:05:35.0526524-6524/?D/TRIALFOREXPERIMENT:Variable=VariableforExperiment1Result=ResultforExperimenton2019-01-0105-2813:05:35.0526524-6524/?D/EXPERIMENTANDTRIALS:ExperimentName=Experiment2ExperimentDate=2019-02-0105-2813:05:35.0526524-6524/?D/TRIALFOREXPERIMENT:Variable=VariableforExperiment2Result=ResultforExperimenton2019-02-01

Note that for convenience all of the above has been run on the main thread (i.e .allowMainThreadQueries() has been used). If you follow recommendations the all database access would be via another thread, in which case the @Transaction annotation is advised for queries.

Index Android