Structured Query Language (SQL)

We will interact with relational databases using JDBC and SQL. As a domain specific language, SQL is designed for updating and retrieving data in table-based databases. It is beyond the scope of this module to teach you SQL 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.¹

Here is the SQL to create a table:

CREATE TABLE IF NOT EXISTS courses (
  offeringName VARCHAR(15) NOT NULL PRIMARY KEY, 
  title VARCHAR(50) NOT NULL
);

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.

String sql = "CREATE TABLE IF NOT EXISTS courses("
    + "offeringName VARCHAR(15) NOT NULL PRIMARY KEY,"
    + "title VARCHAR(50) NOT NULL"
    + ");";
Statement st = conn.createStatement();
st.execute(sql);

To insert data, we can run the following SQL statement:

INSERT INTO courses (offeringName, title) 
VALUES ('EN.601.226', 'Data Structures');

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

sql = "INSERT INTO courses (offeringName, title)"   
    + "VALUES ('EN.601.226', 'Data Structures');";
st.execute(sql);

Okay! let's add the statements above to the main method of the Demo program:

public static void main(String[] args) throws SQLException {
  try (Connection conn = getConnection()) {
    String sql = "CREATE TABLE IF NOT EXISTS courses("
        + "offeringName VARCHAR(15) NOT NULL PRIMARY KEY,"
        + "title VARCHAR(50) NOT NULL"
        + ");";
    Statement st = conn.createStatement();
    st.execute(sql);

    sql = "INSERT INTO courses (offeringName, title)"
        + "VALUES ('EN.601.226', 'Data Structures');";
    st.execute(sql);

  } catch (URISyntaxException | SQLException e) {
    e.printStackTrace();
  }
}

Run the Demo program. If the program executes without any error, you have created a table with one record on your Heroku PostgreSQL database!

To explored the Postgres database provisioned for your app (and its content) you have a few options.

• You can use the Heroku CLI to run PostgreSQL interactive terminal and execute queries to e.g. view your data in a table. For this, see the guidelines here.
• Most people prefer a graphical user interface to work with their database (much like how you work with data in Google Sheet or Microsoft Excel). Heroku (at the time of writing) does not support this on their platform. However, there are third-party tools like Heroku Explorer that provides a web-based GUI to access your Heroku databases.
• Alternatively (and this is my preferred way), you can use the IntelliJ Database tools that comes with its Ultimate edition. You can connect this tool to your Heroku database. You will then get a graphical user interface to interact with your data (as it can be seen in the image below). For this, see the guidelines here. You may also find it useful to consult Heroku's guidelines on Connecting to Heroku Postgres Databases from Outside of Heroku.