Persist Objects in Databases

So far, we set up and connected to our SQLite database, and ran SQL statements on it from our Java application. How can we use this arrangement to persist our application's data?

As noted, relational databases, like SQLite, are structured around tables (similar to those in a document or spreadsheet). Each class in your Java application maps to a different table. The class name maps to the table name. Each property of the class (fields) map to a column in that table. Each object instance corresponds to a row in that table; it holds the values that describe a particular entry.

Note

In relational databases, we expect each record (row in a table) to be unique. If there is an attribute (column) that can uniquely identify each record, then we are good! That attribute can be identified as the primary key for that table.¹ If a table does not have a primary key, you must make one for it by assigning a unique identifier to each row. Usually, this is as simple as having a column that contains a number that increments every time you create a new record.

In SQLite, you get a primary key for free, called ROWID. This is in every SQLite table whether you ask for it or not. ROWID is assigned a value whenever you INSERT a row. If you include a column of type INTEGER PRIMARY KEY, that column points at (is an alias for) the automatic ROWID column.

Courses Table

When creating a table in SQLite, we can create a primary key id field for it as follows:

CREATE TABLE Courses (
    id INTEGER PRIMARY KEY, 
    name VARCHAR(30) NOT NULL, 
    url VARCHAR(100)
);

Accordingly, we must update the Course class to include an id field.

Note that we do not provide the id for Course (when we construct it).Instead, we let the RDBS generate it for us; we then use setter and getter methods to update/access the id field.

---

1. A primary key for a table could be a combination of several columns. ↩