So far, we set up and connected to our SQLite database, and know we need to communicate with it via SQL statements 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.
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.1 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
When creating a table in SQLite, we can create a primary key
id field for it as follows:
Accordingly, we must update the
Author class to include an
Note that we do not provide the
Author (when we construct it). Instead, we let the RDBS generate it for us; we then use setter and getter methods to update/access the
Insert data into table
To insert data, we can execute a SQL statement such as:
We can reuse the
Statement object (
st) to execute the aforementioned
Let's insert two more!
Search for records in a table
SQL is particularly powerful when it comes to querying data. Here is a simple example to search for all authors where the author name contains
Let's run this query using JDBC:
Notice I've used a different method,
executeQuery to run the above SQL statement. The
executeQuery method returns a
ResultSet object which you can iterate over to extract the retrieved data.
Parameterized SQL with JDBC PreparedStatement
PreparedStatement is a special kind of JDBC
Statement object with some useful additional features. Namely, it makes it easy to insert parameters into the SQL statement.
- A primary key for a table could be a combination of several columns. This is called composite primary key.↩