SQLite One-to-Many Relationship Set/Get Foreign Key

Question

I am building an android app project with SQLite DB.

I got stuck on One-To-Many RelationShip.

This is One

private static final String createTableOrders =
        "CREATE TABLE " + TABLE_ORDER + "("
                + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                ...
                + KEY_COLUMN_FORMATS + " INTEGER REFERENCES " + TABLE_FORMATS + "(" + KEY_ID + ")"
                + ");";

This is Many

private static final String createTableFormats =
        "CREATE TABLE " + TABLE_FORMATS + "("
                + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                  ...
                + ");";

My problems is with set/get methods. Lets say that I would need to get list of ids of all Formats that are in one Order. I guess same thing goes for set method.

I tried to find this kind of question on SO but most of the question were just for SQL part.

P.S. Spent 3 hours trying to make it by myself but the only thing I got to was using GSON to code list of ids into String but it was just a dead-end.

EDIT: I need to get that information inside the code.

P.S.S I have been doing this for 18 hours so sorry if this is uber-stupid question.

Answer 1

A one-to-many relationship requires the foreign key column in the "many" table:

In SQL:

CREATE TABLE Orders (
    ID PRIMARY KEY
);
CREATE TABLE Formats (
    ID PRIMARY KEY,
    OrderID REFERENCES Orders(ID)
);

To get all formats belonging to an order, you just look up rows with the ID of that order:

SELECT * FROM Formats WHERE OrderID = ?

In Java:

Cursor cursor = db.query(TABLE_FORMATS,
                         new String[] { whatever columns you need },
                         "OrderID = " + orderID,
                         null, null, null, null, null);
while (cursor.moveToNext()) {
    // read one order from the cursor
}
cursor.close();