Using Java JDBC in Problem Set 2

Getting started

Java includes an API called JDBC for connecting to database from within a Java program.

We have provided the JDBC driver for SQLite in the lib subfolder of the starter code for Problem 3, and we’ve also included the necessary import statements so that your code can use the JDBC framework.

Connecting to a database

To connect to a database using JDBC, you need to use the DriverManager.getConnection() method. It returns a Connection object that can be used to access the database. The constructor that we’ve given you in the starter code already takes care of calling this method, and it assigns the resulting Connection object to the field called db:

this.db = DriverManager.getConnection(...);

Executing a query

Note: We’ve provided you with a method called resultsFor that takes a string representing a SQL query and performs the following two steps for you. We encourage you to use that method, although you would also be welcome to perform the query yourself.

Executing a query involves two steps:

1. Use the Connection object (see above) to create a Statement object, and assign that object to a variable:

   Statement stmt = this.db.createStatement();
   

2. Use the Statement‘s executeQuery() method to perform the SQL query, passing in the query as a string, and assigning the return value to a variable of type `ResultSet. For example:

   ResultSet results = stmt.executeQuery("SELECT foo, bar FROM Baz;");
   

Iterating over the results of a query

As described above, executing a query in JDBC produces an object of type ResultSet. This is also the type of object that is returned by the resultsFor method that we’ve given you.

A ResultSet object is similar to an iterator for a collection of data. However, it starts out before the first tuple (if any) in the results.

If you anticipate that there may be multiple rows of results, you should use a while loop to obtain the results of the query from the ResultSet, one row at a time:

while (results.next()) {
    // code to process each row goes here
}

The next() method attempts to advance to the next row in the results, if there is one. It returns true if there is another row to process, and false if there are no remaining rows. It does not actually return the row. Rather, you will need to make additional method calls to access the attributes of the current row (see below).

If you know that there will be at most one row of results, you don’t need to use a while loop. Instead, you can just perform a single call to results.next():

if (results.next()) {
    // handle the single row of results
} else {
    // handle cases with no results
}

Accessing the attributes in the current row

The individual attributes of the current tuple can be accessed by using one of the ResultSet accessor methods. There are different versions of these methods: ones that take the index of the requested attribute in the tuple, and ones that take the name of the requested attribute.

For example, let’s say that we’ve performed the following query:

ResultSet results = stmt.executeQuery("SELECT name, runtime, earnings_rank FROM Movie;");

One way to get the column values from each row of results is to use the ResultSet accessor methods that take the indices of the columns:

while (results.next()) {
    String name = results.getString(1);   // get column 1
    String rtime = results.getString(2);  // get column 2 as a String
    int rank = results.getInt(3);         // get column 3 as an int
    ...
}

Another way to do this would be to use the ResultSet accessor methods that take the names of the columns:

while (results.next()) {
    String name = results.getString("name");
    String rtime = results.getString("runtime");
    int rank = results.getInt("earnings_rank");
    ...
}

Notes:

• The index of the leftmost attribute in the tuple is 1, not 0.

• ResultSet.getString() returns null if the value of the specified attribute is NULL. ResultSet.getInt() returns 0 in such cases.

• For the purposes of this assignment, you should be able to limit yourself to the ResultSet.getString() methods, regardless of the actual type of the attribute. For example, in the above examples we get the runtime as a String even though it is stored as an integer, and we could also get the earnings_rank as a String as well.

• Because null is a literal, you should check for it as follows, without surrounding it with quotes:

  if (attribute == null) {
      ...
  

Other resources (optional)

• the full Java API
• the JDBC API
• the JDBC tutorial

Last updated on October 1, 2024.