Chapter 25

Relational Databases

Chapter Goals

To understand how relational databases store information
To learn how to query a database with the Structured Query Language (SQL)
To connect to databases with Java Database Connectivity (JDBC)
To write database programs that insert, update, and query data in a relational database

Organizing Database Information

Relational database
- Stores information in tables
- Each table column has a name and data type

Product Table in a Relational Database

Product Table

Each row corresponds to a product
Column headers correspond to attributes of the product
All items in the same column are the same data type

SQL

SQL stands for Structured Query Language
SQL is a command language for interacting with databases
Most relational databases follow the SQL standard
SQL is not case sensitive "create table" = "CREATE TABLE"
We will use uppercase letters for SQL keywords and mixed case for table and column names

Some SQL Types and Their Corresponding Java Types

SQL Data Type	Java Data Type
`INTEGER` or `INT`	`int`
`REAL`	`float`
`DOUBLE`	`double`
`DECIMAL` (m, n)	Fixed-point decimal numbers with m total digits and n digits after the decimal point; similar to `BigDecimal`.
`BOOLEAN`	`boolean`
`CHARACTER` (n) or `CHAR` (n)	Fixed-length string of length n; similar to `String`.

SQL Command to Create a Table

CREATE TABLE Product
(
   Product_Code CHAR(11),
   Description CHAR(40),
   Price DECIMAL(10, 2)
)

SQL Command to Add Data to a Database

Use the INSERT INTO command to insert rows into the table

Issue one command for each row, such as

INSERT INTO Product
    VALUES ('257-535', 'Hair dryer', 29.95)

SQL

SQL uses single quotes ('), not double quotes, to delimit strings
What if you have a string that contains a single quote?
- Write the single quote twice:
```
'Sam''s Small Appliances'
```
To remove an existing table use the DROP TABLE command
```
DROP TABLE Test
```

A Customer Table

Consider a Java class Customer:

public class Customer
{
   . . .
   private String name;
   private String address;
   private String city;
   private String state;
   private String zip;
}

It is simple to come up with a database table structure that allows you to store customers

A Customer Table

An Invoice Table

For other objects, it is not so easy to come up with an equivalent database table structure

Consider an invoice; each invoice object contains a reference to a customer object:

public class Invoice
{
   . . .
   private int invoiceNumber;
   private Customer theCustomer;
   . . .
}

You might consider simply entering all the customer data into the invoice tables
- It is wasteful
- It is dangerous; on a change, you can forget to update all occurrences
In a Java program, neither of these problems occur
- Multiple Invoice objects can contain references to a single Customer

Poor Design for an Invoice Table with Replicated Customer Data

Linking Tables

Replicating the same data in multiple rows has two problems
It is wasteful to store the same information multiple times
If the data needs to change it has to be changed in multiple places
Instead you should distribute the data over multiple tables

Two Tables for Invoice and Customer Data

Linking Tables

In the table above, customer data are not replicated
Customer table contains a single record for each customer
Invoice table has no customer data
The two tables are linked by the Customer_Number field
The customer number is a unique identifier
- We introduced the customer number because the customer name by itself may not be unique

Primary Keys

Primary key is a column (or combination of columns) whose value uniquely specifies a table record
Not all database tables need a primary key
You need a primary key if you want to establish a link from another table
Foreign key is a reference to a primary key in a linked table
Foreign keys need not be unique

Productivity Hint: Avoid Unnecessary Data Replication

Implementing One-to-Many Relationships

Each invoice may have many items
Do not replicate columns, one for each item
Do distribute the information in two tables, invoice and item
Link each item to its invoice with an Invoice_Number foreign key in the item table

Poor Design for Invoice Table with Replicated Columns

Linked Invoice and Item Tables

Sample Database

Self Check

Would a telephone number be a good primary key for a customer table?
In the database of Section 25.1.3, what are all the products that customer 3176 ordered?

Answers

The telephone number for each customer is unique–a necessary requirement for the primary key. However, if a customer moves and the telephone number changes, both the primary and all foreign keys would need to be updated. Therefore, a customer ID is a better choice.
Customer 3176 ordered ten toasters.

Queries

Once the database is constructed, we can query it for information
- What are the names and addresses of all customers?
- What are the names and addresses of all customers in California?
- What are the names and addresses of all customers who buy toasters?
- What are the names and addresses of all customers with unpaid invoices?

A Sample Database

Simple Query

Use the SQL SELECT statement to query a database
The query to select all data from the Customer table:
```
SELECT * FROM customer
```
The outcome of a query is a view:
A view is a "window" through which you can see some of the database

An Interactive SQL Tool

Selecting Columns

You may want to view only some of the columns in the table
The query to select the city and state of all customers from the Customer table:
```
SELECT City, State FROM Customer
```

Selecting Subsets

You can select rows that fit a particular criteria
When you want to select a subset , use the WHERE clause
The query to find all customers in California:
```
SELECT * FROM Customer WHERE State = 'CA'
```
The query to select all customers NOT in California
```
SELECT * FROM Customer WHERE State <> 'CA'
```

Selecting Subsets

You can match patterns with LIKE
The right-hand side is a string that can contain special characters
- Special symbol _ matches exactly one character
- Special symbol % matches any character sequence
The expression to match all Name strings whose second character is an "o":
```
Name LIKE '_o%'
```

Selecting Subsets

You can combine expressions with logical connectives AND, OR, NOT

SELECT *
   FROM Product
   WHERE Price < 100
   AND Description <> 'Toaster'

You can select both row and column subsets

SELECT Name, City FROM Customer WHERE State = 'CA'

Calculations

Use the COUNT function to find out how many customers there are in California
```
SELECT COUNT(*) FROM Customer WHERE State = 'CA'
```
The * means you want to calculate whole records
Other functions are SUM, AVG, MAX, MIN
These functions must access a specific column:
```
SELECT AVG(Price) FROM Product
```

Joins

The information you want may be spread over multiple tables
TableName.ColumnName denotes the column in a particular table
- Use Product.Product_Code to specify the Product_Code column in the Product table
- Use Item.Product_Code to specify the Product_Code column in the Item table

To select all invoices that include a car vacuum

SELECT Item.Invoice_Number
   FROM Product, Item
   WHERE Product.Description = 'Car vacuum'
      AND Product.Product_Code = Item.Product_Code

Joins

A query that involves multiple tables is a join
When using a join, do these things
- List all tables that are involved in the query in the FROM clause
- Use the TableName.ColumnName syntax to refer to column names
- List all join conditions in the WHERE clause
  (TableName1.ColumnName1 = TableName2.ColumnName2)

Joins

You may want to know in what cities hair dryers are popular
You need to add the Customer table to the query–it contains the customer addresses

Customers are referenced by invoices, add that table as well:

SELECT Customer.City, Customer.State, Customer.Zip
   FROM Product, Item, Invoice, Customer
   WHERE Product.Description = 'Hair dryer'
      AND Product.Product_Code = Item.Product_Code
      AND Item.Invoice_Number = Invoice.Invoice_Number
      AND Invoice.Customer_Number = Customer.Customer_Number

Joining Tables without Specifying a Link Condition

SELECT Invoice.Invoice_Number, Customer.Name
   FROM Invoice, Customer

Joining Tables without Specifying a Link Condition

SELECT Invoice.Invoice_Number, Customer.Name
   FROM Invoice, Customer
   WHERE Invoice.Customer_Number = Customer.Customer_Number

Updating and Deleting Data

The DELETE and UPDATE commands modify the database
The DELETE command deletes rows
To delete all customers in California
```
DELETE FROM Customer WHERE State = 'CA'
```

Updating and Deleting Data

The UPDATE query lets you update columns of all records that fulfill a certain condition

To add one to the quantity of every item in invoice number 11731

UPDATE Item
   SET Quantity = Quantity + 1
   WHERE Invoice_Number = '11731'

Update multiple column values by specifying multiple update expressions
in the SET clause, separated by commas
Both UPDATE and DELETE return the number of rows updated or deleted

Self Check

How do you query the names of all customers that are not from Alaska or Hawaii?
How do you query all invoice numbers of all customers in Hawaii?

Answers

SELECT Name FROM Customer WHERE State <> 'AK' AND State <> 'HI'

SELECT Invoice.Invoice_Number FROM Invoice, Customer
   WHERE Invoice.Invoice_Number = Customer.Customer_Number
   AND Customer.State = 'HI'

Databases and Privacy

Installing a Database

A wide variety of database systems are available. Among them are
- High-performance commercial databases, such as Oracle, IBM DB2, or
- Microsoft SQL Server
- Open-source databases, such as PostgreSQL or MySQL
- Lightweight Java databases, such as McKoi and HSQLDB
- Desktop databases, such as Microsoft Access

Installing a Database

Detailed instructions for installing a database vary widely
General sequence of steps on how to install a database and test your installation:
1. Obtain the database program
2. Read the installation instructions
3. Install the program (may even need to compile from source code)
4. Start the database
5. Set up user accounts
6. Run a test:
```
CREATE TABLE Test (Name CHAR(20))
INSERT INTO Test VALUES ('Romeo')
SELECT * FROM Test
DROP TABLE Test
```
  Note that you may need a special terminator for each SQL statement (e.g. ';')

JDBC

JDBC: Java Database Connectivity
You need a JDBC driver to access a database from a Java program
Different databases require different drivers
Drivers may be supplied by the database manufacturer or a third party
When your Java program issues SQL commands,
the driver forwards them to the database and lets your program analyze the results

JDBC Architecture

Testing the JDBC Driver

Find the class path for the driver, e.g.
```
c:\mckoi\mkjdbc.jar
```
Find the name of the driver class that you need to load, e.g.
```
com.mckoi.JDBCDriver
```

Find the name of the database URL that your driver expects

jdbc:subprotocol:driver-specific data
jdbc:mckoi://localhost/

Testing the JDBC Driver

Edit the file database.properties and supply
- The driver class name
- The database URL
- Your database user name
- Your database password
Compile the program as
```
javac TestDB.java
```

Run the program as

java -classpath driver_class_path;. TestDB database.properties
java -classpath driver_class_path:. TestDB database.properties

Testing the JDBC Driver: Possible Problems

Missing driver
- Check the class path and the driver name
Driver cannot connect to the database
- The database may not be started
- Database may not be reachable
Failed login
- Check the database name, user name, and password
A missing Test table
- Make sure you create and populate the table as described in the database test

File TestDB.java

File SimpleDataSource.java

File database.properties

Self Check

After installing a database system, how can you test that it is properly installed?
You are starting a Java database program to connect to the McKoi database and get the following error message:
```
Exception in thread "main" java.lang.ClassNotFoundException:
com.mckoi.JDBCDriver
```
What is the most likely cause of this error?

Answers

Connect to the database with a program that lets you execute SQL instructions. Try creating a small database table, adding a record, and selecting all records. Then drop the table again.
You didn't set the class path correctly. The JAR file containing the JDBC driver must be on the class path.

Database Programming in Java: Connecting to the Database

Use a Connection object to access a database from a Java program
Load the database driver
Ask the DriverManager for a connection

When you are done, close the database connection

String driver = . . .;
String url = . . .;
String username = . . .;
String password = . . .;


Class.forName(driver); // Load driver
Connection conn = DeviceManager.getConnection(url, username, password);

. . .

conn.close();

Connecting to the Database

Decouple connection management from the other database code
We supply a SimpleDataSource class to do this
Call its static init method with the name of the database configuration file
```
SimpleDataSource.init("database.properties");
```

Configuration file is a text file containing four lines

jdbc.driver= . . .
jdbc.url= . . .
jdbc.username= . . .
jdbc.password= . . .

Connecting to the Database

The init method uses the Properties class to read the file

Properties class has a load method to read a file of key/value pairs from a stream

Properties props = new Properties();
FileInputStream in = new FileInputStream(fileName);
props.load(in);

The getProperty method returns the value of a given key
```
String driver = props.getProperty("jdbc.driver");
```

Now when you need a connection call

Connection conn = SimpleDataSource.getConnection();

You need to close the connection by calling
```
conn.close();
```

Executing SQL Statements

A Connection object can create Statement objects
```
Statement stat = conn.createStatement();
```

The execute method of the Statement class executes a SQL statement

stat.execute("CREATE TABLE Test (Name CHAR(20))");
stat.execute("INSERT INTO Test VALUES ('Romeo')");

Use executeQuery method of the Statement class to issue a query

String query = "SELECT * FROM Test";
ResultSet result = stat.executeQuery(query);

The result of a SQL query is returned in a ResultSet object

Executing SQL Statements

Use the executeUpdate method of the Statement class to execute an UPDATE statement

The method returns the number of rows effected

String command = "UPDATE Item"
      + " SET Quantity = Quantity + 1"
      + " WHERE Invoice_Number = '11731'";
int count = stat.executeUpdate(command);

Executing SQL Statements

If your statement has variable parts, use a PreparedStatement

String query = "SELECT * WHERE Account_Number = ?";
PreparedStatement stat = conn.prepareStatement(query);

The ? symbols denote variables that you fill in when you make an actual query
Call a set method to fill this variables
```
stat.setString(1, accountNumber);
```
The first parameter the set methods denotes the variable position (1 is the first ?)
There are also methods setInt and setDouble
After setting all variables, call executeQuery or executeUpdate

Executing SQL Statements

You can also use a generic execute statement to execute queries
It returns a boolean value to indicate whether SQL command yields a result set
If there is a result set, get it with the getResultSet method

Otherwise, get the update count with the getUpdateCount method

String command = . . . ;
boolean hasResultSet = stat.execute(command);
if (hasResultSet)
{
   ResultSet result = stat.getResultSet();
   . . .
{
else
{
   int count = stat.getUpdateCount();
   . . .
}

Executing SQL Statements

You can reuse a Statement or PreparedStatement object
For each statement, you should only have one active ResultSet
If you need to look at multiple result sets at the same time, create multiple Statement objects
Close the current ResultSet before issuing a new query on the same statement
```
result.close();
```
When you are done with a Statement object, close it
```
stat.close();
```
That will also close the ResultSet

Analyzing Query Results

Use the next method of the ResultSet to iterate through the query results a row at a time
When a result set is first returned from an executeQuery, no row data are available
Use the next method to move to the first row
The next method returns a boolean value indicating whether more rows of data are available
```
while (result.next())
{
   Inspect column data from the current row
}
```

Analyzing Query Results

To get the column values from a row, use one of the various get methods
There are two get methods for each type of data (string, integer, double . . .)
One uses an integer parameter that indicates the column position

Column positions start at 1

String productCode = result.getString(1);

Analyzing Query Results

The other type of get method uses a string parameter for the column name
```
String productCode = result.getString("Product_Code");
```

Use getInt to fetch an integer column value

int quantity = result.getInt("Quantity");

Use getDouble to fetch an double column value

double unitPrice = result.getDouble("Price");

Result Set Meta Data

Result set meta data describes the properties of the result set
Use the ResultSetMetaData class to find out the column names in an unknown table
You can get the meta data object form the result set
```
ResultSetMetaData metaData = result.getMetaData();
```

Result Set Meta Data

Use getColumnCount method to get the number of columns
Use getColumnLabel method to get the name of each column

Use getColumnDisplaySize method to get the column width

for (int i = 1; i <= metaData.getColumnCount(); i++)
{
   String columnName = metaData.getColumnLabel(i);
   int columnSize = metaData.getColumnDisplaySize(i);
   . . .
}

Result Set Meta Data

The following program reads a file containing SQL statements and executes them
If there is a result set, the result set is printed
Meta data from the result set is used to determine the column count and column label

Run the program as

java ExecSQL database.properties product.sql

Or interactively as
```
java ExecSQL database.properties
```

File Product.sql

File ExecSQL.java

Self Check

Suppose you want to test whether there are any customers in Hawaii. Issue the statement
```
ResultSet result = stat.executeQuery(
      "SELECT * FROM Customer WHERE State = 'HI'");
```
Which Boolean expression answers your question?
Suppose you want to know how many customers are in Hawaii. What is an efficient way to get this answer?

Answers

result.hasNext(). If there is at least one result, then hasNext returns true.

ResultSet result = stat.executeQuery(
      "SELECT COUNT(*) FROM Customer WHERE State = 'HI'");
result.next();
int count = result.getInt(1);

Note that the following alternative is significantly slower if there are many such customers.

ResultSet result = stat.executeQuery(
      "SELECT * FROM Customer WHERE State = 'HI'");
while (result.next()) count++; // Inefficient

Case Study: A Bank Database

This is a reimplementation of the ATM simulation
In the simulation each customer has:
- A customer number
- A PIN
- A checking account
- A savings account
The data will be stored in two tables in a database

Tables for ATMSimulation

Case Study: A Bank Database

The Bank class needs to connect to the database whenever it is asked to find a customer
Its findCustomer method
- Connects to the database
- Selects the customer with the given account number
```
SELECT * FROM BankCustomer WHERE Customer_Number = . . .
```
- Verifies the PIN
- Creates an customer object from the database information

Case Study: A Bank Database

public Customer findCustomer(int customerNumber, int pin)
       throws SQLException
{
   Connection conn = SimpleDataSource.getConnection();
   try
   {
      Customer c = null;
      PreparedStatement stat = conn.prepareStatement(
            "SELECT * FROM BankCustomer WHERE Customer_Number = ?");
      stat.setInt(1, customerNumber);

      ResultSet result = stat.executeQuery();
      if (result.next() && pin == result.getInt("PIN"))
         c = new Customer(customerNumber,
               result.getInt("Checking_Account_Number"),
               result.getInt("Savings_Account_Number"));
      return c;
   }
   finally
   {
      conn.close();
   }
}

Case Study: A Bank Database

The BankAccount methods are different now
The getBalance method gets the balance from the database
The withdraw and deposit methods update the database immediately

Case Study: A Bank Database

public double getBalance()
      throws SQLException
{
   Connection conn = SimpleDataSource.getConnection();
   try
   {
      double balance = 0
      PreparedStatement stat = conn.prepareStatement(
            "SELECT Balance FROM Account WHERE Account_Number = ?");
      stat.setInt(1, accountNumber);

      ResultSet result = stat.executeQuery();
      if (result.next())
         balance = result.getDouble(1);
      return balance;
   }
   finally
   {
      conn.close();
   }
}

Case Study: A Bank Database

public void deposit(double amount)
      throws SQLException
{
   Connection conn = SimpleDataSource.getConnection();
   try
   {
      PreparedStatement stat = conn.prepareStatement(
            "UPDATE Account"
            + " SET Balance = Balance + ?"
            + " WHERE Account_Number = ?");
      stat.setDouble(1, amount);
      stat.setInt(2, accountNumber);
      stat.executeUpdate();
   }
   finally
   {
      conn.close();
   }
}

File Bank.java

File BankAccount.java

Self Check

Why doesn't the Bank class store an array of Customer objects?
Why do the BankAccount methods throw an SQLException instead of catching it?

Answers

The customer data are stored in the database. The Bank class is now merely a conduit to the data.
The methods are not equipped to handle the exception. What could they do? Print an error report? To the console or a GUI window? In which language?