Database Applications

Resources

Overview

Two options to interact with a database:

In this chapter, we will study how to develop a database application that uses a library.

Every database application follows the same routine:

  1. Establish / open the connection with the DBMS,
  2. Interact with the DBMS (Update, Query, Delete, Insert),
  3. Terminate / close the connection with the DBMS.

Which API is used vary with the pair Language / DBMS. Here are some of the most commonly used pairs:

Language API Website
Python Python Database API Python
C, C++ MySQL C API C, C++
C# MySQL Connector/Net C#
Java Java DataBase Connectivity Java

In this chapter, we will more precisely study how to develop a database application coded in Java that uses the Java DataBase Connectivity library.

Java’s Way

Java actually uses

diagram

Note that the A.P.I. is needed when you write and compile your program, and the driver / connector is needed when you execute it. We will come back to this when we explore our first program.

Flash Intro to Java

For a quick introduction to Java, cf. A Very Short Intro to Java.

A First Program

We will write and compile a simple java program that manipulates a simple database. Even if the creation and population of the database could have been done from within the program, we will do it as a preliminary step, using the C.L.I., to make our program simpler.

This program ows a lot to the one presented at JDBC Programing by Examples with MySQL.

The Database (SQL)

For this program, we will use the following database:

/* code/sql/HW_EBookshop.sql */
DROP SCHEMA IF EXISTS HW_EBookshop;

CREATE DATABASE HW_EBookshop;

USE HW_EBookshop;

CREATE TABLE BOOKS (
  ID INT PRIMARY KEY,
  title VARCHAR(50),
  author VARCHAR(50),
  price DECIMAL(10, 2),
  qty INT
);

-- Cf. https://en.wikipedia.org/wiki/List_of_best-selling_books
INSERT INTO BOOKS
VALUES (
  1,
  'The Communist Manifesto',
  'Karl Marx and
    Friedrich Engels',
  11.11,
  11);

INSERT INTO BOOKS
VALUES (
  2,
  'Don Quixote',
  'Miguel de Cervantes',
  22.22,
  22);

INSERT INTO BOOKS
VALUES (
  3,
  'A Tale of Two Cities',
  'Charles Dickens',
  33.33,
  33);

INSERT INTO BOOKS
VALUES (
  4,
  'The Lord of the Rings',
  'J. R. R. Tolkien',
  44.44,
  44);

INSERT INTO BOOKS
VALUES (
  5,
  'Le Petit Prince',
  'Antoine de
    Saint-Exupéry',
  55.55,
  55);

SELECT *
FROM BOOKS;	
MariaDB [HW_EBookshop]> SELECT * FROM BOOKS;
+----+-------------------------+--------------------------------+-------+------+
| ID | title                   | author                         | price | qty  |
+----+-------------------------+--------------------------------+-------+------+
|  1 | The Communist Manifesto | Karl Marx and Friedrich Engels | 11.11 |   11 |
|  2 | Don Quixote             | Miguel de Cervantes            | 22.22 |   22 |
|  3 | A Tale of Two Cities    | Charles Dickens                | 33.33 |   33 |
|  4 | The Lord of the Rings   | J. R. R. Tolkien               | 44.44 |   44 |
|  5 | Le Petit Prince         | Antoine de Saint-Exupéry       | 55.55 |   55 |
+----+-------------------------+--------------------------------+-------+------+
5 rows in set (0.00 sec)

You can copy and paste the code, then execute it, or use MySQL’s batch mode: you can find the code previously given at code/sql/HW_EBookshop.sql, i.e., at RocketGit. Open a terminal (or command-line interpreter), navigate to the folder where you stored that file (using cd), and type

mysql -u testuser -p < HW_EBookshop.sql

for linux, or (something like)

"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe" -u testuser -p < HW_EBookshop.sql

for Windows. Refer to the Logging-In as testuser section if you forgot how to log-in to your database.

You just discovered MySQL’s batch mode, that perform series of instructions from a file. You can easily make sure that the database and the table were indeed created, and the values inserted, by logging the way you used to, and executing the usual commands.

Executing Database Application

As we are about to see, a database application needs to be written following this order:

  1. Load the API,
  2. Try to open the connection (i.e., create Connection and Statement objects), using a try/catch statement,
  3. Perform the required actions on the database (using Statement object),
  4. Close the connection.

and the program needs to load the driver (which is specific to DBMS) at execution time.

Of course, if the second step failed, then the program needs to exit gracefully, or to provide debugging information to the user. The program we will obtain can (normally) be compiled, using something like javac FirstProg.java (or an equivalent command for windows). But another refinment is needed when you want to execute it. We need to set up the driver (or connector) to make the java SQL API and MySQL communicate. To do so,

  1. Go to MySQL
  2. Select “Platform Independent”,
  3. Click on “Download” in front of “Platform Independent (Architecture Independent), ZIP Archive”
  4. Look for the (somewhat hidden) “No thanks, just start my download.”
  5. Download the file named "mysql-connector-java-***.zip", where *** is the version number.
  6. Unzip the file, and locate the "mysql-connector-java-***.jar" file (normally, in the root folder).
  7. Copy that file in the same folder as where you intend to compile your program.

Once this is done and your program was compiled, you can execute it using (where you replace *** with the actual number, of course, e.g. 8.0.19):

java -cp .:mysql-connector-java-***.jar FirstProg

in Linux, or

java -cp .;mysql-connector-java-***.jar FirstProg

in Windows. The -cp option lists the places where java should look for the class used in the program: we are explicitely asking java to use the mysql-connector-java-***.jar executable (the driver) to execute our FirstProg executable.

If we try to execute FirstProg without that flag, we obtain the following error message:

$ java FirstProg
java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/HW_EBOOKSHOP
at java.sql.DriverManager.getConnection(DriverManager.java:689)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at FirstProg.main(FirstProg.java:9)

The Application Program (java)

// code/java/FirstProg.java

import java.sql.*;

public class FirstProg {
  public static void main(String[] args) {
    try (Connection conn =
            DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/HW_EBOOKSHOP", "testuser", "password");
        /*
         * If at execution time you receive an error that starts with
         * "java.sql.SQLException: The server time zone value 'EDT' is unrecognized or
         * represents more than one time zone. You must configure either the server ..."
         * add ?serverTimezone=UTC at the end of the previous string,
         * i.e., replace the previous lines of code with:
         * Connection conn = DriverManager.getConnection(
         * "jdbc:mysql://localhost:3306/HW_EBOOKSHOP?serverTimezone=UTC",
         * "testuser","password");
         * cf. for instance https://stackoverflow.com/q/26515700
         * Or, change your server's configuration, cf.
         * https://stackoverflow.com/a/44720416
         */
        Statement stmt = conn.createStatement(); ) {
      String strSelect = "SELECT title, price, qty FROM BOOKS WHERE qty > 40";
      System.out.print("The SQL query is: " + strSelect + "\n");
      ResultSet rset = stmt.executeQuery(strSelect);

      System.out.println("The records selected are:");
      int rowCount = 0;
      String title;
      double price;
      int qty;

      while (rset.next()) {
        title = rset.getString("title");
        price = rset.getDouble("price");
        qty = rset.getInt("qty");
        System.out.println(title + ", " + price + ", " + qty);
        rowCount++;
      }

      System.out.println("Total number of records = " + rowCount);
      conn.close();

    } catch (SQLException ex) {
      ex.printStackTrace();
    }
  }
}

A couple of comments:

The Result

If you store the program in FirstProg.java, compile it, with

javac FirstProg.java

and then execute it, with

java -cp .:mysql-connector-java-***.jar FirstProg

(refer back to “Executing Database Application” for more details) then you should obtain:

The `SQL` query is: SELECT title, price, qty FROM BOOKS WHERE qty > 40
The records selected are:
The Lord of the Rings, 44.44, 44
Le Petit Prince, 55.55, 55
Total number of records = 2

Take the time to make sure you have the same result on your installation, and that you understand how the code works before moving on.

A Variation

If you were to replace the body of try in the previous program with

String strSelect = "SELECT * FROM BOOKS";
ResultSet rset = stmt.executeQuery(strSelect);

System.out.println("The records selected are:");

ResultSetMetaData rsmd = rset.getMetaData();
int columnsNumber = rsmd.getColumnCount();
String columnValue;
while (rset.next()) {
  for (int i = 1; i <= columnsNumber; i++) {
    if (i > 1) System.out.print(",  ");
    columnValue = rset.getString(i);
    System.out.print(columnValue + " " + rsmd.getColumnName(i));
  }
  System.out.println();

You would obtain:

The records selected are:
1 ID,  The Communist Manifesto title,  Karl Marx and Friedrich Engels author, 
    11.11 price,  11 qty
2 ID,  Don Quixote title,  Miguel de Cervantes author,  22.22 price,  22 qty
3 ID,  A Tale of Two Cities title,  Charles Dickens author,  33.33 price,  33 qty
4 ID,  The Lord of the Rings title,  J. R. R. Tolkien author,  44.44 price,  44 qty
5 ID,  Le Petit Prince title,  Antoine de Saint-Exupéry author,  55.55 price,  55 qty

In that code, please note:

Overall, this code would work equally well if the table had a different number of columns, as opposed to our first program.

Mapping Datatypes

Note that in the previous code, we read everything as a string. But, actually, SQL and JAVA datatypes can be mapped as follows:

SQL JAVA
INTEGER int
CHARACTER(n) String
VARCHAR(n) String
REAL float
DOUBLE double
DECIMAL(t,d) java.math.BigDecimal
DATE java.sql.date
BOOLEAN boolean
BIT(1) byte

(DECIMAL(t,d) was not previously introduced: the t stands for the number of digits, the d for the precision.)

We cannot always have that correspondance: what would correspond to a reference variable? To a private attribute? This series of problems is called “object-relational impedance mismatch”, it can be overcomed, but at a cost.

Differences Between executeQueryexecuteUpdate and execute

Previously, we used executeQuery to send a SQL command to the DBMS. This method is tailored for SELECT statement, and it is not the only method we can use.

Name executeQuery executeUpdate execute
Used for SELECT INSERTUPDATEDELETE Any type
Input Type string string string
Return Type ResultSet int, the number of rows affected by the query booleantrue if the query returned a ResultSetfalse if the query returned an int or nothing

To retrieve the ResultSet obtained by an execute statement, you need to use getResultSet or getUpdateCount. For more details, consult Oracle

A Second Program

The program in Problem 5.1 (Advanced Java Programming) uses the modifications discussed below. Please refer to it once you are done with this section.

Passing Options

We can pass options (values of fields) when connecting to the database:

Connection conn =
      DriverManager.getConnection(
          "jdbc:mysql://localhost:3306/HW_DBPROG"
              + "?user=testuser"
              + "&password=password"
              + "&allowMultiQueries=true"
              + "&createDatabaseIfNotExist=true"
              + "&useSSL=true");

On top of user and password (which are self-explanatory), setting allowMultiQueries to true allows to pass multiple queries with one executeUpdate statement, and createDatabaseIfNotExist creates the schema passed in the url (so, here, HW_DBPROG) if it does not already exist.

The syntax used is the syntax of querying strings, i.e., it follows the pattern

?field1=value1&field2=value2…&fieldN=valueN

That is, it starts with an ? and then “pile up” the field / value pairs with &. In particular, if you needed to add ?serverTimezone=UTC in the first application program we used, you will need here to replace

+ "&useSSL=true");

with

+ "&useSSL=true"
+ "&serverTimezone=UTC");

You can read about other options at MySQL or JDBC.

Creating a Table

We can create a table with the method stmt.execute.

stmt.execute(
    "CREATE TABLE DVD ("
        + "Title CHAR(25) PRIMARY KEY, "
        + "Minutes INTEGER, "
        + "Price DOUBLE)");

If we were to execute SHOW TABLES; after this execute instruction directly in the MySQL interpreter, this would display at the screen:

+---------------------+
| Tables_in_HW_DBPROG |
+---------------------+
| DVD                 |
+---------------------+

But here, to access this information, we will use the connection’s metadata. The DatabaseMetaData is a class used to get information about the database: the driver, the user, the versions, etc. We can use the getMetaData() method of this class to obtain information about the schema we just created:

DatabaseMetaData md = conn.getMetaData();

ResultSet rs = md.getTables("HW_DBPROG", null, "%", null);

The first parameter of getMetaData() is the schema’s name, as you probably guessed, and the the third parameter is String tableNamePattern, i.e., what must match the table name stored in the database to be selected. Here, by using the wildcard %, we select all the table names (which is only “DVD” at this point).

The getMetaData() method returns a ResultSet (here named rs), where 3 is the TABLE_NAME. We can now iterate over this rs object to list all the elements in it, as we would with any ResultSet object:

while (rs.next()) {
  System.out.println(rs.getString(3));
}

You can read at Oracle the full specification of this method.

Inserting Values

To insert values in our table, we can use stmt.executeUpdate:

String sqlStatement = "INSERT INTO DVD VALUES ('Gone With The Wind', 221, 3);";
int rowsAffected = stmt.executeUpdate(sqlStatement);
System.out.print(sqlStatement + " changed " + rowsAffected + " row(s).\n");

Note that the executeUpdate returns an integer, the number of rows changed. We can even use this method to perform multiple insertions at the same time, if allowMultiQueries was set to true, cf. StackOverflow:

String insert1 = "INSERT INTO DVD VALUES ('Aa', 129, 0.2)";
String insert2 = "INSERT INTO DVD VALUES ('Bb', 129, 0.2)";

stmt.executeUpdate(insert1 + ";" + insert2);

Another way of “batch processing” statements (i.e., of executing multiple insertions at the same time) is to use addBatch (that “loads” statements in the statement object) and executeBatch() (that execute all the statements loaded):

String insert3 = "INSERT INTO DVD VALUES ('Cc', 129, 0.2)";
String insert4 = "INSERT INTO DVD VALUES ('DD', 129, 0.2)";
stmt.addBatch(insert3);
stmt.addBatch(insert4);
stmt.executeBatch();

Note that the database is not solicited until the executeBatch method is called: we simply loaded the instruction in the program, and connect to the database only once, with all the instructions, when this executeBatch() instruction is met.

Note also that executeBatch may be used, per Oracle:

for updating, inserting, or deleting a row; and it may also contain DDL statements such as CREATE TABLE and DROP TABLE. It cannot, however, contain a statement that would produce a ResultSet object, such as a SELECT statement.

Also, the name suggests that it should be possible to fetch the SQL instructions from a file and load them in your Java program, but there is actually no easy way to do this, c.f. StackOverflow.

Prepared Statements

A prepared statement is “a query with a slot”: it is a query that takes one or multiple parameters, is parsed and stored on the database, but not executed. It is only after the value of the slot(s) are fixed by the program that this query can be executed. The program can re-use the same prepared statement with multile (different) values multiple times.

Compared to executing SQL statements directly, prepared statements have three main advantages:

Let us look at a first example:

/*
 * We create a string with an empty slot,
 * represented by "?".
 */
sqlStatement = "SELECT title FROM DVD WHERE Price <= ?";
/*
 * We create a PreparedStatement object, using that string with an
 * empty slot.
 */
PreparedStatement ps = conn.prepareStatement(sqlStatement);

/*
 * Then, we "fill" the first slot with the value of a variable.
 */
double maxprice = 0.5;
ps.setDouble(1, maxprice);
/*
 * Finally,  we can execute the query, and display the results.
 */
ResultSet result = ps.executeQuery();

System.out.printf("For %.2f you can get:\n", maxprice);

while (result.next()) {
  System.out.printf("\t %s \n", result.getString(1));
}

Note that once the ps PreparedStatement object is created, we cannot change the content of the query, beside instantiating the slot. cf. e.g. the discussion at StackOverflow.

As we said earlier, a prepared statement can have multiple “slots”, as we can see in that second example:

sqlStatement = "INSERT INTO DVD VALUES (?, ?, ?)";
// Now, our string has 3 empty slots, and it is an INSERT statement.
PreparedStatement preparedStatement = conn.prepareStatement(sqlStatement);

preparedStatement.setString(1, "The Great Dictator");
preparedStatement.setInt(2, 124);
preparedStatement.setDouble(3, 5.4);

rowsAffected = preparedStatement.executeUpdate();
/* You can check "by hand" that this statement was correctly
 * executed. Note that the toString method is quite verbose.
 */
System.out.print(preparedStatement.toString() + " changed " + rowsAffected + " row(s).\n");

Where we stored the integer value returned by executeUpdate and displayed the the prepared statement using the toString method.

If we try to mess things up, i.e., provide wrong datatypes:

preparedStatement.setString(1, "The Great Dictator");
preparedStatement.setString(2, "Not-an-integer");
preparedStatement.setString(3, "Not-a-double");

/* This command will make your program crash:
 * rowsAffected = preparedStatement.executeUpdate();
 */

Java compiler will be ok, but we’ll have an error at execution time when executing the query.

Executing rowsAffected = preparedStatement.executeUpdate(); would return an error containing

com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect integer value: 
'Not-an-integer' for column `HW_DBPROG`.`DVD`.`Minutes` at row 1

since "Not-an-integer" is not … a valid integer!

Of course, prepared statements are particularly convenient when you want to automate some tasks or repeat them multiple times, as you write the query only once, and then re-use it. For instance, inserting the whole “Saw” franchise can be made into a loop:

for (int i = 1; i < 5; i++) {
  preparedStatement.setString(1, "Saw " + i);
  preparedStatement.setInt(2, 100);
  preparedStatement.setDouble(3, .5);
  preparedStatement.executeUpdate();
}

More Complex Statement Objects

When you create the Statement objects, you can give two arguments to the createStatement method:

Statement stmtNew =
    conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

Those options change two things about the ResultSet we obtain using this statement The first argument indicates whenever you can scroll (go forward and backward) in the ResultSet objects that will be created using this Statement object:

Allowing to go in both direction extends the methods one can use in the ResultSet class: now, to scroll through the results, one can use:

The second argument is the concurrency level, it indicates whenever you can update the values into the ResultSet directly.

In other terms, manipulting the ResultSet object will directly impact the data stored in the database if we set the second parameter to CONCUR_UPDATABLE.

This createStatement method is documented at Oracle.

You can find below a simple example of “scrollable” ResultSet:

// code/java/ScrollingProgram.java

import java.sql.*;

public class ScrollingProgram {
  public static void main(String[] args) {
    try (Connection conn =
            DriverManager.getConnection(
                // We connect to the database, not to a particular schema.
                "jdbc:mysql://localhost:3306/"
                    + "?user=testuser"
                    + "&password=password"
                    + "&allowMultiQueries=true"
                /*
                 * We want to allow multiple statements
                 * to be shipped in one execute() call.
                 */
                );
        Statement stmt = conn.createStatement(
		ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
        /*
         * Finally, we want to be able to move back and forth in our
         * ResultSets. This implies that we have to also chose if the
         * ResultSets will be updatable or not: we chose to have them
         * to be "read-only".
         */
        ) {
      /*
       * Before you ask: no, there are no "simple" way of
       * constructing a string over multiple lines,
       * besides concatenating them,
       * cf. e.g. https://stackoverflow.com/q/878573
       */

      stmt.execute(
          "DROP SCHEMA IF EXISTS HW_SCROLLABLE_DEMO;"
              +
              /*
               * We drop the schema we want to use if it already exists.
               * (This allows to execute the same program multiple times.)
               */
              "CREATE SCHEMA HW_SCROLLABLE_DEMO;"
              + "USE HW_SCROLLABLE_DEMO;"
              +
              // We create and use the schema.
              "CREATE TABLE TEST("
              + "    Id INT"
              + ");"
          // The schema contains only one very simple table.
          );
      /*
       * We can execute all those queries at once
       * because we passed the "allowMultiQueries=true"
       * token when we created the Connection object.
       */

      // Let us insert some dummy values in this dummy table:
      for (int i = 0; i < 10; i++) stmt.addBatch("INSERT INTO TEST VALUES (" + i + ")");
      /*
       * no ";" in the statements that we add
       * to the batch!
       */
      stmt.executeBatch();
      // We execute the 10 statements that were loaded at once.

      // Now, let us write a simple query, and navigate in the result:
      ResultSet rs = stmt.executeQuery("SELECT * FROM TEST");
      /*
      * We select all the tuples in the table.
      * If we were to execute this instruction on the
      * command-line interface, we would get:

      * MariaDB [HW_SCROLLABLE_DEMO]> SELECT * FROM TEST;
      * +----+
      * | Id |
      * +----+
      * | 0  |
      * | 1  |
      * | 2  |
      * | 3  |
      * | 4  |
      * | 5  |
      * | 6  |
      * | 7  |
      * | 8  |
      * | 9  |
      * +----+
      * 10 rows in set (0.001 sec)
      */

      // We can "jump" to the 8th result in the set:
      rs.absolute(8);
      System.out.printf("%-22s %s %d.\n", "After absolute(8),", "we are at Id", 
	  rs.getInt(1));
      /* Note that this would display "7" since the
       * 8th result contains the value 7 (sql starts
       * counting at 1.
       */

      // We can move back 1 item:
      rs.relative(-1);
      System.out.printf("%-22s %s %d.\n", "After relative(-1),", "we are at Id", 
	  rs.getInt(1));

      // We can move to the last item:
      rs.last();
      System.out.printf("%-22s %s %d.\n", "After last(),", "we are at Id", rs.getInt(1));

      // We can move to the first item:
      rs.first();
      System.out.printf("%-22s %s %d.\n", "After first(),", "we are at Id", rs.getInt(1));

      conn.close();
    } catch (SQLException ex) {
      ex.printStackTrace();
    }
  }
}

You can also have a look at the end of code/java/AdvancedProg.java, which creates a second Statement object is created and used.

Exercises

Exercise 5.1

What are the technologies that make it possible for a Java application to communicate with a DBMS?

Exercise 5.2

Why is it important to have the statements creating the connection to the database inside a try…catch statement?

Exercise 5.3

Name three classes in the SQL API of java.

Exercise 5.4

What JDBC method do you call to get a connection to a database?

Exercise 5.5

What is the class of the object used to create a ResultSet object?

Exercise 5.6

Briefly explain what the next() method from the ResultSet class does and give its return type.

Exercise 5.7

How do you submit a SELECT statement to the DBMS?

Exercise 5.8

What method should be used to perform an INSERT command from your program?

Exercise 5.9

Where is a ResultSet object’s cursor initially pointing? How do you move the cursor forward in the result set?

Exercise 5.10

Give three navigation methods provided by ResultSet.

Exercise 5.11

Explain this JDBC URL format:

jdbc:mysql://localhost:3306/HW_NewDB?createDatabaseIfNotExist=true&useSSL=true
Exercise 5.12

In what class is the getColumnName() method?

Exercise 5.13

Assuming stmt is a Statement object, in the statement:

modif = stmt.executeUpdate(strC);

What is…

  1. … the datatype of modif?
  2. … the datatype of strC?
  3. … a possible value for strC?
Exercise 5.14

What is a prepared statement?

Exercise 5.15

Assume ps is the prepared statement:

INSERT INTO EXAM VALUES (?, ?);

Write the three statements needed to allocate “Quiz” and “5” to the two slots and to execute the prepared statement in the database.

Exercise 5.16

In the code below, there are five errors between line 13 and line 32. They are not subtle Java errors (like misspelling a key word) and do not come from the DBMS (so you should assume that the password is correct, that the database exists, etc.). Highlight each error and explain why it is an error.

// code/java/ProgWithErrors.java

import java.sql.*;

public class ProgWithErrors {
  public static void main(String[] args) {
    try (Connection conn =
            DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/" 
				+ "HW_TestDB?user=testuser&password=password");
        Statement stmt = conn.createStatement(); ) {

      // Errors after this point.

      String strSelect = "SELECT title FROM DISKS WHERE qty > 40;";
      ResultSet rset = stmt.executeUpdate(strSelect);

      System.out.println("The records selected are: (listed last first):");
      rset.last();

      while (rset.previous()) {
        String title = rset.getDouble("title");
        System.out.println(title + "\n");
      }

      String sss = "SELECT title FROM DISKS WHERE Price <= ?";
      PreparedStatement ps = conn.prepareStatement(sss);
      ResultSet result = ps.executeQuery();

      conn.close();

      // Errors before this point.

    } catch (SQLException ex) {
      ex.printStackTrace();
    }
  }
}
Exercise 5.17

Write a program that determines if the null value from Java code is equal to the NULL value in the DBMS.

Solution to Exercises

Solution 5.1

The technologies theat make it possible for a Java application to communicate with w DBMS are API’s and the drivers to implement them.

Solution 5.2

It is important to put the statements that create the connection to the database inside the try…catch statement because the program will interact with the environment if this interraction fails (typically, if the connection does not succeed), for which we want to be able to catch the exception and recover from that failure.

Solution 5.3

There are many classes in the SQL API if Java. There are ConnectionDatabaseMetaDataResultSetMetaDataPreparedStatement, and Statement to name a few. You can find them listed at Oracle.

Solution 5.4

The JDBC method that must be called to connect to a database is DriverManager.getConnection()

Solution 5.5

The class of the object used to create a ResultSet object is the Statement class. A Statement object is used to create a ResultSet object, e.g. by calling the executeQuery method.

Solution 5.6

The next() method checks if there is data to read and, if there is, it moves the cursor to read it. Its return type is Boolean.

Solution 5.7

You submit a SELECT statement to the DBMS by using .executeQuery(strSelect).

Solution 5.8

The executeUpdate() or execute() methods can be used to perform an INSERT command from our program.

Solution 5.9

The ResultSet object’s cursor is initially pointing at the position before the first line. We move the cursor forward by using the next() method.

Solution 5.10

There are many navigation methods provided by ResultSet. They are the first()last()next()previous()relative(), and absolute() methods.

Solution 5.11

This JDBC URL format connects to localhost:3306, creates a new database if needed, and uses the secure SSL connection.

Solution 5.12

The getColumnName() method is in the ResultSetMetaData class.

Solution 5.13

In the statement modif = stmt.executeUpdate(strC);

  1. modif is an integer (the number of rows modified by the query).
  2. strC is a String (a SQL command).
  3. A possible value for strC is DELETE FROM BOOKS Where Price > 0.5.
Solution 5.14

A prepared statement is a feature used to execute SQL statements repeatedly with high efficiency that protects against SQL injections.

Solution 5.15
ps.setString(1,"Quiz");
ps.setInt(2, 5);
ps.execute();
Solution 5.16

The errors are:

You can find the corrected program in `code/java/ProgWithErrorsPatched.java`, 
			which looks like:

```{.bash}
16c16
<       ResultSet rset = stmt.executeUpdate(strSelect);
---
>       ResultSet rset = stmt.executeQuery(strSelect); // Error 1
21,24c21,24
<       while(rset.previous()) {
<         String title = rset.getDouble("title");
<         System.out.println(title + "\n");
<       }
---
>       do { // Error 2
>         String title = rset.getString("title"); // Error 3
>         System.out.println(title); // Not an error, 
			but we probably do not need two new lines.
>       }while(rset.previous()); // Error 2 bis
27a28
>       ps.setInt(1, 10); // Error 4
```
Solution 5.17

Here is what the program should look like:

// code/java/TestForNull.java

import java.sql.*;

public class TestForNull {
  public static void main(String[] args) {
    try (Connection conn =
            DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/HW_DBPROG?user=testuser&password=
				password&createDatabaseIfNotExist=
				true&serverTimezone=UTC");
        Statement stmt = conn.createStatement(); ) {
      stmt.execute("CREATE TABLE Test (" + "A CHAR(25), " + "B INTEGER, " 
	  + "C DOUBLE)");

      String strAdd = "INSERT INTO Test VALUES (NULL, NULL, NULL);";
      int number_of_row_changed = stmt.executeUpdate(strAdd);
      System.out.print("This last query changed " + number_of_row_changed 
	  + " row(s).\n");

      ResultSet result = stmt.executeQuery("SELECT * FROM Test");

      if (result.next()) {
        System.out.print(result.getString(1) + " " + result.getDouble(2) 
		+ " " + result.getInt(3));
        if (result.getString(1) == null) {
          System.out.print("\nAnd null for CHAR in SQL is null 
		  for String in Java.\n");
        }
      }
      conn.close();
    } catch (SQLException ex) {
      ex.printStackTrace();
    }
  }
}

This program should display:

This last query changed 1 row(s).
null 0.0 0
And null for CHAR in `SQL` is null for String in Java.

Problems

Problem 5.1 (Advanced Java Programming)

Read, execute, break, edit, compile, patch, hack and (most importantly) understand the following program:

// code/java/AdvancedProg.java

/*
 * This is a long program, introducing:
 * I. How to pass options when connecting to the database,
 * II. How to create a table and read its meta-data,
 * III. How to insert values,
 * IV. How to use prepared statements,
 * V. How to read backward and write in ResultSets.
 *
 * To be able to execute this program multiple times, the schema 
 * is dropped and re-created.
 *
 */

import java.sql.*;

public class AdvancedProg {
  public static void main(String[] args) {
    try (
    // I. Passing options to the database

    // start snippet passing-options
    Connection conn =
            DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/HW_DBPROG"
                    + "?user=testuser"
                    + "&password=password"
                    + "&allowMultiQueries=true"
                    + "&createDatabaseIfNotExist=true"
                    + "&useSSL=true");
        // end snippet passing-options

        Statement stmt = conn.createStatement(); ) {
      /*
       * Below, we drop the schema and re-create it to allow multiple 
       * execution of the
       * program. You can ignore this part if you want.
       */

      stmt.execute(
          "DROP SCHEMA IF EXISTS HW_DBPROG;" + "CREATE SCHEMA HW_DBPROG;" 
		  + "USE HW_DBPROG;");

      // II. Creating a table and reading its meta-data

      // start snippet table-creation
      stmt.execute(
          "CREATE TABLE DVD ("
              + "Title CHAR(25) PRIMARY KEY, "
              + "Minutes INTEGER, "
              + "Price DOUBLE)");
      // end snippet table-creation

      // start snippet table-metadata-1
      DatabaseMetaData md = conn.getMetaData();

      ResultSet rs = md.getTables("HW_DBPROG", null, "%", null);
      // end snippet table-metadata-1

      // start snippet table-metadata-2
      while (rs.next()) {
        System.out.println(rs.getString(3));
      }
      // end snippet table-metadata-2

      // III. Inserting values

      // start snippet inserting-1
      String sqlStatement = "INSERT INTO DVD VALUES (
	  'Gone With The Wind', 221, 3);";
      int rowsAffected = stmt.executeUpdate(sqlStatement);
      System.out.print(sqlStatement + " changed " + rowsAffected 
	  + " row(s).\n");
      // end snippet inserting-1

      // start snippet inserting-2
      String insert1 = "INSERT INTO DVD VALUES ('Aa', 129, 0.2)";
      String insert2 = "INSERT INTO DVD VALUES ('Bb', 129, 0.2)";

      stmt.executeUpdate(insert1 + ";" + insert2);
      // end snippet inserting-2

      // start snippet inserting-3
      String insert3 = "INSERT INTO DVD VALUES ('Cc', 129, 0.2)";
      String insert4 = "INSERT INTO DVD VALUES ('DD', 129, 0.2)";
      stmt.addBatch(insert3);
      stmt.addBatch(insert4);
      stmt.executeBatch();
      // end snippet inserting-3

      // IV. Prepared Statements

      // start snippet prepared-queries-1

      /*
       * We create a string with an empty slot,
       * represented by "?".
       */
      sqlStatement = "SELECT title FROM DVD WHERE Price <= ?";
      /*
       * We create a PreparedStatement object, using that string with an
       * empty slot.
       */
      PreparedStatement ps = conn.prepareStatement(sqlStatement);

      /*
       * Then, we "fill" the first slot with the value of a variable.
       */
      double maxprice = 0.5;
      ps.setDouble(1, maxprice);
      /*
       * Finally,  we can execute the query, and display the results.
       */
      ResultSet result = ps.executeQuery();

      System.out.printf("For %.2f you can get:\n", maxprice);

      while (result.next()) {
        System.out.printf("\t %s \n", result.getString(1));
      }
      // end snippet prepared-queries-1

      // start snippet prepared-queries-2
      sqlStatement = "INSERT INTO DVD VALUES (?, ?, ?)";
      // Now, our string has 3 empty slots, and it is an INSERT statement.
      PreparedStatement preparedStatement = conn.prepareStatement(sqlStatement);

      preparedStatement.setString(1, "The Great Dictator");
      preparedStatement.setInt(2, 124);
      preparedStatement.setDouble(3, 5.4);

      rowsAffected = preparedStatement.executeUpdate();
      /* You can check "by hand" that this statement was correctly
       * executed. Note that the toString method is quite verbose.
       */
      System.out.print(preparedStatement.toString() + " changed " + rowsAffected 
	  + " row(s).\n");
      // end snippet prepared-queries-2

      // start snippet prepared-queries-3
      preparedStatement.setString(1, "The Great Dictator");
      preparedStatement.setString(2, "Not-an-integer");
      preparedStatement.setString(3, "Not-a-double");

      /* This command will make your program crash:
       * rowsAffected = preparedStatement.executeUpdate();
       */
      // end snippet prepared-queries-3

      // start snippet prepared-queries-4
      for (int i = 1; i < 5; i++) {
        preparedStatement.setString(1, "Saw " + i);
        preparedStatement.setInt(2, 100);
        preparedStatement.setDouble(3, .5);
        preparedStatement.executeUpdate();
      }
      // end snippet prepared-queries-4

      // V. Reading backward and writing in ResultSets

      // start snippet new-statement-1
      Statement stmtNew =
          conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, 
		  ResultSet.CONCUR_UPDATABLE);
      // end snippet new-statement-1

      // Reading backward
      sqlStatement = "SELECT title FROM DVD WHERE Price < 1;";
      result = stmtNew.executeQuery(sqlStatement);

      System.out.println("For $1, you can get:");

      if (result.last()) {
        // We can jump to the end of the ResultSet
        System.out.print(result.getString("Title") + " ");
      }

      System.out.print("and also, (in reverse order)");

      while (result.previous()) {
        // Now we can scroll back!
        System.out.print(result.getString("Title") + " ");
      }

      // Changing the values
      System.out.print("\n\nLet us apply a 50% discount. 
	  Currently, the prices are:\n");

      sqlStatement = "SELECT title, price FROM DVD;";
      result = stmtNew.executeQuery(sqlStatement);
      while (result.next()) {
        System.out.printf("%20s \t $%3.2f\n", result.getString("title"), 
		result.getDouble("price"));
      }

      // We need to scroll back!
      result.absolute(0);

      while (result.next()) {
        double current = result.getDouble("price");
        result.updateDouble("price", (current * 0.5));
        result.updateRow();
      }
      System.out.print("\n\nAfter update, the prices are:\n");

      // We need to scroll back!
      result.absolute(0);

      while (result.next()) {
        System.out.printf("%20s \t $%3.2f\n", result.getString("title"), 
		result.getDouble("price"));
      }

      conn.close();
    } catch (SQLException ex) {
      ex.printStackTrace();
    }
  }
}
Problem 5.2 (A GUEST Java Program)

Consider the code below:

// code/java/GuestProgram.java

// java.util.Scanner is an API to read from the keyboard.
import java.sql.*;
import java.util.Scanner;

// This first part is "standard". Just note that we allow multiple statements.
public class GuestProgram {
  public static void main(String[] args) {
    try (Connection conn =
            DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/?user=testuser&password=password"
                    + "&allowMultiQueries=true");
        Statement stmt = conn.createStatement(); ) {
      // We create a schema, use it, create two tables, 
	  // and insert a value in the second one.
      stmt.execute(
          "CREATE SCHEMA HW_GUEST_PROGRAM;"
              + "USE HW_GUEST_PROGRAM;"
              + "CREATE TABLE GUEST("
              + "Id INT PRIMARY KEY,"
              + "Name VARCHAR(30),"
              + "Confirmed BOOL"
              + ");"
              + "CREATE TABLE BLACKLIST("
              + "Name VARCHAR(30)"
              + ");"
              + "INSERT INTO BLACKLIST VALUES (\"Marcus Hells\");");

      /*
       * INSERT HERE Solution to exercises 1, 2 and 3.
       * Tip for Exercise 1, this solves the first item.
       */
      System.out.print("How many guests do you have?\n");
      Scanner key = new Scanner(System.in);
      int guest_total = key.nextInt();

    } catch (SQLException ex) {
      ex.printStackTrace();
    }
  }
}

In the following three exercises, you will add some code below the comment // INSERT HERE Solution to exercises 1, 2 and 3. in order to obtain a behavior like the following one (you do not have to reproduce it exactly!). The user input is underlined, and hitting “enter” is represented by :

How many guests do you have?
͟2͟↵
Enter name of guest 1.
M͟a͟r͟c͟u͟s͟ ͟H͟e͟l͟l͟s͟↵
Enter name of guest 2.
C͟y͟n͟t͟h͟i͟a͟ ͟H͟e͟a͟v͟e͟n͟s͟↵
……………⌛……………
Oh no, (at least) one of the guest from the black list confirmed their presence!
The name of the first one is Marcus Hells.

Do you want to remove all the guests that are on the black list and 
who have confirmed their presence? 
Enter "Y" for yes, anything else for no.

You should suppose that BLACKLIST contains more than one name, and that some other operations are performed where ……………⌛…………… is (typically, some guests will confirm their presence). Using batch processing or prepared statements will be a plus, but is not mandatory to solve these exercises.

  1. Write a snippet that
    1. Asks the user how many guests they have,
    2. For each guest, asks their name (using key.nextLine(), that returns the String entered by the user),
    3. For each guest name entered, inserts in the GUEST table an integer that is incremented after each insertion, the name entered by the user, and NULL.
  2. Write a snippet such that if there is at least one guest who confirmed their presence and whose name is on the blacklist, a message will be displayed on the screen containing the name of (at least) one of those guests.
  3. Write a snippet that asks the user whenever they want to remove from the guest list all the persons on the blacklist that confirmed their presence, and do so if they enter “yes” (or some variation).

Solutions to Selected Problems

Solution to Problem 5.2 (A GUEST Java Program)

The file code/java/GuestProgramSolution.java contains the whole code for you to compile and test.

Pb 5.2 – Solution to Q. 1

We explore two solutions, one with batch processing, the second with prepared statement.

They both start with:

int guest_id;
String guest_name;
int counter = 0;

Then the solution using batch processing could be:

while (counter < guest_total) {
  // Ask the name of the guest.
  System.out.print("Enter name of guest " + (counter + 1) + ".\n");
  // Read the name of the guest.
  guest_name = key.nextLine();
  stmt.addBatch("INSERT INTO GUEST VALUES (" + counter + ", \"" + guest_name 
  + "\", NULL)");
  // Add to the batch the statement to insert the required data in the table
  counter++;
}
stmt.executeBatch(); // Execute the batch statement.

while the solution using prepared statements could be:

PreparedStatement ps = conn.prepareStatement("INSERT INTO GUEST VALUES(
		  ?, ?, NULL);");
while (counter < guest_total) {
    System.out.print("Enter name of guest " + (counter + 1) + ".\n");
    guest_name = key.nextLine();
    ps.setInt(1, counter);
    ps.setString(2, guest_name);
    ps.executeUpdate();
    counter++;
}

Pb 5.2 – Solution to Q. 2

We let SQL do all the hard work:

int guest_id;
String guest_name;
int counter = 0;

Then the solution using batch processing could be:

while (counter < guest_total) {
  // Ask the name of the guest.
  System.out.print("Enter name of guest " + (counter + 1) + ".\n");
  // Read the name of the guest.
  guest_name = key.nextLine();
  stmt.addBatch("INSERT INTO GUEST VALUES (" + counter + ", \"" + guest_name 
  + "\", NULL)");
  // Add to the batch the statement to insert the required data in the table
  counter++;
}
stmt.executeBatch(); // Execute the batch statement.

while the solution using prepared statements could be:

PreparedStatement ps = conn.prepareStatement("INSERT INTO GUEST VALUES(
		  ?, ?, NULL);");
while (counter < guest_total) {
    System.out.print("Enter name of guest " + (counter + 1) + ".\n");
    guest_name = key.nextLine();
    ps.setInt(1, counter);
    ps.setString(2, guest_name);
    ps.executeUpdate();
    counter++;
}

Pb 5.2 – Solution to Q. 2

We let SQL do all the hard work:

ResultSet rset =
    stmt.executeQuery(
        "SELECT * FROM GUEST, BLACKLIST WHERE GUEST.Name = BLACKLIST.Name 
		AND GUEST.Confirmed = true");
if (rset.next()) {
  System.out.print(
      "Oh no, (at least) one of the guest from the black list confirmed their presence!
	  \nThe name of the first one is "
          + rset.getString(2)
          + ".\n");
}

Pb 5.2 – Solution to Q. 3

Similarly, we let SQL do all the hard work:

System.out.print(
    "Do you want to remove all the guests that are on the black list 
	and confirmed their presence? 
	Enter \"Y\" for yes, anything else for no.\n");
if (key.nextLine().equals("Y")) {
  stmt.execute(
      "DELETE FROM GUEST WHERE NAME IN (SELECT NAME FROM BLACKLIST) 
	  AND Confirmed = true;");
}