Relational Databases

Chapter Goals

• 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

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
• Some SQL commands
  • QUERY
  • CREATE TABLE
  • INSERT
  • DROP TABLE

Some SQL Types and Their Corresponding Java Types

SQL to Create a Table

CREATE TABLE Products
(
    Product_Code CHAR(11)
    Description CHAR(40)
    Unit_Price DECIMAL(10, 2)
)

SQL to Add Data to a Database

• Use the INSERT command to insert rows into the table
• Issue one command for each row of the table

INSERT INTO Products
    VALUES ('3554-0632-1', 'Hair dryer', 29.95)

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

Poor Design for an Invoice Table with Replicated Customer Data

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

Linking Tables

• Primary key is a column (or combination of columns) whose value uniquely specifies a table record
• Foreign key is a reference to a primary key in a linked table
• Foreign keys need not be unique

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

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 who live 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?

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

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'

Result of 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. Put the column name in parentheses

  SELECT AVG(Price) FROM Product

Joins

• The information you want may be spread over multiple tables
• 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)

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'

• Both UPDATE and DELETE return the number of rows updated or deleted

File TestDB.java

File SimpleDataSource.java

File database.properties

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);
  
  
  //database commands
  
  
  conn.close();
  

Connecting to the Database

• Decouple connection management from the other database code
• You can use the SimpleDataSource class to do this
• Call its static init method with the name of the database configuration file
• 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();

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 queryStatement = "SELECT * FROM Test";
  ResultSet result = stat.executeQuery(queryStatement);

• 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 a UPDATE statement
• The method returns the number of rows effected

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

Executing SQL Statements

• You can also use a generic execute statement to execute queries
• It returns a boolean value to indicate whether the SQL command yields a result set
• If there is a result set, you can get it with the getResultSet method
• If there is no result set, you can 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 object to execute new SQL commands
• Each Statement object 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
• That will also close the ResultSet

  stat.close();
  

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 cityzone.sql

File ExecSQL.java

Case Study: A Bank Database

• This is a reimplementation of the ATMSimulation
• In the simulation each customer has a customer number, a PIN, a checking account and 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 find method
  • Connects to the database
  • Selects the customer with the given account number
  • Verifies the PIN
  • Creates an customer object from the database information

Case Study: A Bank Database

public Customer find(int customerNumber, int pin)
    throws SQLException
{
   Customer c = null;
   Connection conn = SimpleDataSource.getConnection();
   Statement stat = conn.createStatement();
   ResultSet result = stat.executeQuery("SELECT *"
      + " FROM Customer WHERE Customer_Number = "
      + customerNumber);

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

Case Study: A Bank Database

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

File ATMSimulation.java

File ATM.java

File Bank.java

File BankAccount.java

File Customer.java

Transactions

• A transaction is a set of database updates that should either succeed in their entirety or not happen at all
• If you transfer money from one account to another, you want both the withdraw and deposit to happen or neither
• Use COMMIT and ROLLBACK commands to manage transactions
• To transfer money from one account to another

  UPDATE Account SET Balance = Balance - 1000
     WHERE Account_Number = '95667-2574'
  UPDATE Account SET Balance = Balance + 1000
     WHERE Account_Number = '82041-1196'
  COMMIT
  

Transactions

• COMMIT makes the updates permanent
• ROLLBACK command undoes all changes up to the last COMMIT
• By default, the JDBC library commits all database updates
• For transaction processing, turn off autocommit mode

  Connection conn . . . ;
  conn.setAutoCommit(false);
  Statement stat = conn.getStatement();

Transactions

• Issue the updates that form the transaction then call the commit method of the Statement class

  stat.executeUpdate(
     "UPDATE Account SET Balance = Balance -"
     + amount + " WHERE Account_Number = " +fromAccount);
  stat.executeUpdate(
     "UPDATE Account SET Balance = Balance +"
     + amount + " WHERE Account_Number = " +toAccount);
  COMMIT
  

• If you encounter an error, call the rollback method
• This is usually done in an exception handler

  try
  {
     . . .
  }
  catch(SQLException exception)
  {
     stat.rollback();
  }
  

Scrollable and Updatable Result Sets

• Because the result set of a query may be very large,
  the ResultSet object does not contain the entire result set
• It is a gateway to a result set stored in the database
• Each time you call next a single row is transferred into the ResultSet object
• The user may want to look back at previous row.
• Use a scrollable result set to do this

Scrollable and Updatable Result Sets

• To get a scrollable result set, you first need to create a Statement object
  with its type and concurrency parameters set with constants from the ResultSet class

  Statement stat =
     conn.createStatement(ResultSet.TYPE_SROLL_SENSITIVE,
     ResultSet.CONCUR_READ_ONLY);
  ResultSet result = stat.executeQuery(. . . );
  

Scrollable and Updatable Result Sets

• Possible type constants
  • TYPE_SCROLL_SENSITIVE
    • Result set scrollable
    • Result set is updated when other processes change the information in the database
• TYPE_SCROLL_INSENSITIVE
  • Result set scrollable
  • Result set is NOT updated when other processes change the information in the database
• TYPE_FORWARD_ONLY
  • Result set not scrollable
  • This is the default
• Possible concurrency constants
  • CONCUR_UPDATABLE
    • You can update the database by editing the result set
  • CONCUR_READ_ONLY
    • You can NOT update the database by editing the result set

Scrollable and Updatable Result Sets

• Methods for navigating a scrollable result set
  • previous() - moves to the previous row
  • first() and last() - moves to first or last row
  • relative(n) - moves by n rows
  • absolute(n) - moves to row number n
• A scrollable result set effectively gives you random access to the result set

Updatable Result Sets

• An updatable result set makes it easy to modify the contents of a database without using SQL commands
• Create a statement

  Statement stat = conn.createStatement(
     ResultSet.TYPE_FORWARD_ONLY,
     ResultSet.CONCUR_UPDATABLE);
  

• Get a result set

String accountQuery =
   "SELECT * WHERE Account_Number = " + account;
ResultSet result = stat.executeQuery(accountQuery);

• Get the value from the Balance column

  double balance = result.getDouble("Balance");

• Update the balance in the current row, but not in the database

  result.updateDouble("Balance", balance + deposit);

• Change the data in the database

  result.updateRow()

Updatable Result Sets

• Use an updatable result set to insert new rows
• Move the cursor to a special position called the insert row
• Update all column values
• Call updateRow method

  result.moveToInsertRow();
  result.updateString("Account_Number","54382-5526");
  result.updateString("Customer_Number", "992345");
  result.updateDouble("Balance",1000);
  result.updateRow();
  

Speeding Queries Up

• Indexing
• Prepared statements
• Stored procedures

Indexing

• If a table has a primary key , the database can build an index file
• Index file stores information on how to access a row quickly when its primary key is given
• If the primary key is a single column, you can tag the column with the keyword PRIMARY KEY

  CREATE TABLE Products
  (
     Product_Code CHAR(10) PRIMARY KEY,
     Description CHAR(40),
     Price DECIMAL(10,2)
  )
  

• A table index can speed up database queries

Prepared Statements

• Use a prepared statement for frequently repeated database queries and updates
• When you send a prepared statement to a database
  • The database formulates a query strategy
  • Saves it with the prepared statement

Prepared Statements

• To prepare a query to get the bank account information for a given account

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

• The ? denotes variables that you fill in when you make an actual query
• Use the set method to set the variables

  prepStat.setString(1,accountNumber);

• When you have all variables set, call the executeQuery method

  ResultSet result = prepStat.executeQuery();

Stored Procedures

• Are executed in the database kernel
• This is much faster than issuing sequences of SQL commands
• Databases don't all use the same syntax for stored procedures
• You must first download the stored procedure into the database
• You can use the CREATE PROCEDURE statement to do this

Stored Procedures

• A procedure for transferring money from one account to another

  CREATE PROCEDURE transfer(
     :from INTEGER, :to INTEGER, :amount DECIMAL(10,2)
  BEGIN
     UPDATE Accounts SET Balance = Balance - :amount
        WHERE Account_Number = :from;
     UPDATE Accounts SET Balance = Balance + :amount
        WHERE Account_Number = :to;
  END

• In Java, download a stored procedure by executing SQL statement that defines the procedure

  String definition =
     "CREATE PROCEDURE transfer("
     + . . .
     + ") BEGIN"
     + . . .
     +"END";
  stat.execute(definition);

Stored Procedures

• To call the stored procedure, use the CallableStatement class
• You get a CallableStatement from the prepareCall method of the Connection class
• The { } tell the JDBC driver that the call is not standard SQL and needs to be translated into the syntax of this database

  CallableStatement callStat = conn.prepareCall(
     "{call transfer(?,?,?)});
  

• You need to set values in the ? placeholders before calling the procedure

  callStat.setInt(1, fromAccount);
  callStat.setInt(2, toAccount);
  callStat.setDouble(3, amount);

• Call the procedure

  callStat.execute();