Persist Objects in Databases

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.

info

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 ROWID column.

Authors Table

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

CREATE TABLE IF NOT EXISTS Authors (id INTEGER PRIMARY KEY, name VARCHAR(100) NOT NULL UNIQUE,
int numOfBooks, nationality VARCHAR(100));

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

Note that we do not provide the id for 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 id field.

Insert data into table

To insert data, we can execute a SQL statement such as:

INSERT INTO Authors(id, name, numOfBooks, nationality)
VALUES (NULL, 'George Orwell', 15, 'British');

We can reuse the Statement object (st) to execute the aforementioned INSERT statement.

sql = "INSERT INTO Authors(id, name, numOfBooks, nationality)"+
"VALUES (NULL, 'George Orwell', 15, 'British');";
st.execute(sql);

Let's insert two more!

sql = "INSERT INTO Authors(id, name, numOfBooks, nationality)"+
"VALUES (NULL, 'Emily St. John Mandel', 5, 'Canadian');";
st.execute(sql);
sql = "INSERT INTO Authors(id, name, numOfBooks, nationality)"+
"VALUES (NULL, 'J.D Salinger', 8, 'American');";
st.execute(sql);

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 el characters:

SELECT * FROM Authors
WHERE name LIKE '%el%';

Let's run this query using JDBC:

sql = "SELECT * FROM Authors WHERE name LIKE '%el%';";
ResultSet rs = st.executeQuery(sql);
while (rs.next()) {
System.out.println("Author name: " + rs.getString("name"));
}

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

A JDBC 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.

Author a = new Author ("Franz Kafka", 16, "Czechoslovakian");
String sql = "INSERT INTO Authors (id, name, numOfBooks, nationality)" +
"VALUES (NULL, ?, ?, ?);";
PreparedStatement pst = conn.prepareStatement(sql);
pst.setString(1, a.getName());
pst.setString(2, String.valueOf(a.getNumOfBooks()));
pst.setString(3, a.getNationality());
pst.execute();
  1. A primary key for a table could be a combination of several columns. This is called composite primary key.↩
Edit this page