Creating Content Using JDBC

We will interact with relational databases using Java and the Structured Query Language (SQL).

Note

SQL (Structured Query Language) is a domain specific language designed for updating and retrieving data in table-based databases. We will not cover it in class but you will likely need to use it. SQL is very easy and descriptive. In most cases, a simple Google search will be sufficient to find "how to" do something with SQL. My go-to place to lookup SQL syntax is https://www.w3schools.com/sql/.

Tip

If you are interested in learning more, SQLZoo is one of the best and popular website for learning SQL online. For general knowledge, checkout SQL on Wikipedia.

Recall, from previous section, that we have created a connection to our SQLite database through JDBC:

final String URI = "jdbc:sqlite:./Store.db"; 
Connection conn = DriverManager.getConnection(URI);

Create a table

To create a table, we can execute the following SQL statement:

CREATE TABLE IF NOT EXISTS Course (
    name VARCHAR(30), 
    url VARCHAR(100)
);

Notice SQL reads like English! There is really no need to explain it; is it?!

We can execute a SQL statement by (1) creating a Statement object and (2) invoking its execute method, passing the SQL statement as an argument to it.

Statement st = conn.createStatement();
String sql = "CREATE TABLE IF NOT EXISTS Courses (name VARCHAR(30), url VARCHAR(100));";
st.execute(sql);

Insert data into table

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

INSERT INTO Course (name, url) 
VALUES ('OOSE', 'jhu-oose.com');

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

sql = "INSERT INTO Courses (name, url) VALUES ('OOSE', 'jhu-oose.com');";
st.execute(sql);

Let's insert two more courses!

sql = "INSERT INTO Courses (name, url) VALUES ('Intro OS', 'jhu-os.com');";
st.execute(sql);

sql = "INSERT INTO Courses (name, url) VALUES ('Data Structures', 'jhu-ds.com');";
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 courses where the course name contains OS characters:

SELECT * FROM Course 
WHERE name LIKE '%OS%';

Let's run this query using JDBC:

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

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.

Course gateway = new Course("Gateway", "gateway-computing.org");
PreparedStatement sql = "INSERT INTO Courses (name, url) VALUES (?, ?);";
pst = conn.prepareStatement(sql);
pst.setString(1, gateway.getName());
pst.setString(2, gateway.getUrl());
pst.execute();