INSERT and SELECT statements with one-to-many and many-to-many relationships in Android

Question

I am using a SQLite database to store data that can be used to reconstruct some objects that I am using in the application I am developing. I am storing CheckIns, Recipients, and ContactMethods.

These objects are related as follows:

CheckIn <--many -- to -- many--> Recipient
Recipient <--one -- to -- many--> ContactMethod

In Java, these objects' fields are defined as follows:

public class CheckIn {
    private int id;
    private boolean isEnabled;
    private Date startTime;
    private Repetition repetition; 
    private Set<Recipient> recipients;
}

public class Recipient {    
    private String name;
    private Set<ContactMethod> contactMethods;    
}

public class ContactMethod {    
    private String type;
    private String address;    
}

The database schema I have come up with for these objects is defined as follows:

CREATE TABLE checkIn(
  _id               INTEGER PRIMARY KEY AUTOINCREMENT,
  isEnabled         INTEGER,
  startTime         INTEGER,
  repetitionNum     INTEGER,
  repetitionUnits   TEXT
);

CREATE TABLE recipient(
  _id               INTEGER PRIMARY KEY AUTOINCREMENT,
  name              TEXT
);

CREATE TABLE contactMethod(
  _id                       INTEGER PRIMARY KEY AUTOINCREMENT,
  type                      TEXT,
  address                   TEXT,
  recipientID               INTEGER,
  FOREIGN KEY(recipientID) REFERENCES recipient(_id) ON DELETE CASCADE
);

CREATE TABLE checkIn_recipient(
  checkInID         INTEGER,
  recipientID       INTEGER,
  FOREIGN KEY(checkInID) REFERENCES checkIn(_id),
  FOREIGN KEY(recipientID) REFERENCES recipient(_id)
);

I have two questions.

1. How do I efficiently INSERT a CheckIn to the database, along with its related objects?

To be more specific, if I have a CheckIn object in Java, not yet committed to the database, how can I structure an INSERT statement that will insert the CheckIn to the CheckIn table, but also store the new CheckIn's relation to one or more Recipients? It seems like to store the relation to the Recipients, I would need to already know the checkIn._id, which hasn't been set yet, since the CheckIn hasn't been entered into the database yet.

2. In Android, what is the best way to rebuild a CheckIn object, for example, from a database query?

I think I know the SQL query that I will need to get the right data:

SELECT checkIn.*, recipient.name, contactMethod.type, contactMethod.address
FROM checkIn
JOIN checkIn_recipient
    ON (checkIn._id = checkIn_recipient.checkInID)
JOIN recipient
    ON (checkIn_recipient.recipientID = recipient._id)
JOIN contactMethod
    ON (recipient._id = contactMethod.recipientID)

This query will get me rows of data containing all of the information I need to build an object for every CheckIn and Recipient in the database, and I know how to get a Cursor object that will iterate through these rows. However, since the data required for a single CheckIn appears on multiple rows, I am confused about the best way to construct individual CheckIn objects. If I am trying to write a method like public Set<CheckIn> getAllCheckIns() that will return a set of all CheckIns that are stored in the database, do I need to run the query above, then loop through each row with the same checkIn._id, and within that loop, every row with the same recipient._id, and so forth? Is there any better way to do this?

I am sorry for the long question. I have only been working with SQL beyond single table databases since this morning, and I didn't want to leave out any important information. Let me know if I missed something.

Answer 1

Answer to Question 1: There are 2 possible ways.

a. You find the ID of the inserted row and use that to insert into the 2nd table. You can find the ID of inserted row if you are using the Android Insert method as documented here: http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#insert%28java.lang.String,%20java.lang.String,%20android.content.ContentValues%29

Here you must ensure that all DB tables are committed or none.

b. You can create triggers. Triggers are database operations that are automatically performed when a specified database event occurs. Learn how to create triggers: http://www.sqlite.org/lang_createtrigger.html

So for e.g. you can create a AFTER INSERT trigger that will be fired when a row in inserted in check in table. Then in that trigger you can insert a row into another table. When the trigger is fired, you have access to the newly inserted row.

Answer to question 2: I usually do the following :

• Select from table 1 - Check In table
• Iterate over the cursor and prepare the Check In object.
• Within the loop, select from table 2 - Recipient table
• Iterate over the recipient table and prepare the Check in object.

This would involve too many DB selects.

Alternatively, you could select all data once and then iterate to prepare the objects.

The point I am trying to make is that you have to iterate :D