Accessing Databases Using JDBCTM

1
Chapter 11

• Accessing Databases Using JDBCTM

2
Introduction

• Databases provide permanent storage of data
• Relational databases maintain data in tables and
  allow relationships between the tables to be
  specified
• A database table provides a grouping of related
  data in terms of rows and columns
• Java simplifies database use through JDBC
• JDBC provides an API for database-independent
  connectivity between the Java program and the
  database
• Through JDBC, Java programs can issue SQL
  statements to manipulate the database

3
The Stocktracker Data Access Class

• Three tasks are necessary to support the GUI
  application
• Create an environment for the database
• Control Panel in Windows
• Create an internal structure for the database and
  insert an initial user record
• Utility program
• Create a data access class to access the database
  and present the data to the user in a GUI format
• StockTrackerDB class

4
(No Transcript)
5
(No Transcript)
6
Problem Analysis

• The GUI provides access only to authorized users
  through a user ID and password
• StockTrackerDB provides an interface to the
  database
• Only administrators can perform user maintenance
• The database maintain a relationship between the
  users and stocks
• MakeDB creates and initializes the database
  structure
• Windows must be configured to make the data
  accessible to the program
• Password and User classes are modified
• Additional GUI and application classes are
  provided

7
Designing a Relational Database

• A column represents a field, or individual data
  element
• A row contains the actual data values for a given
  record
• Fields have a data type and size
• The size of a text field must be specified
• If there are no repeating groups, the data is
  said to be in first normal form
• Tables may have relationships to make
  associations between their fields

8
Designing a Relational Database

• The cardinality of a relationship depends on the
  number of records in a table that may relate to a
  given record in another table
• One-to-one
• One-to-many
• Many-to-many
• An index of a table is a field or group of fields
  used to identify and sort records in a table
• A primary key is an index with a unique, non-null
  value for each record in a table
• A foreign key is one or more fields in a table
  that reference the primary key field(s) of
  another table

9
StockTracker Database
10
(No Transcript)
11
Understanding Persistent Objects

• Saving an object to nonvolatile storage implies
  saving the current state of the object
• The state of an object is the value of its
  attributes at a given point in time
• Persistence is the ability of an object,
  including its state, to be saved over time and
  restored

12
Implementing the Serializable Interface

• The Input/Output streams support the encoding of
  an object into a byte stream that can be stored
  on disk
• Serialization is the conversion of an object into
  a stream
• Deserialization is the process of restoring a
  serialized object for use in a program
• For an object to be serialized, it must implement
  the Serializable interface
• Called a marker interface because it has no
  fields or methods

13
Files needed from Chapter 10(Copy these files to
your Chapter 11 folder)
14
Files needed from Chapter 11 StudentFiles on Data
Drive(Copy these files to your Chapter 11 folder)

• STAction.java
• STLogon.java
• StockTracker.java
• User.java
• UserMaintFrame.java
• MakeDB.java

15
Modify Password.java

• Open your Password.java file inTextPad
• Change the current date in the comments
• Add an import statement (line 10)import
  java.io.
• Modify the class header (line 12)public class
  Password implements Serializable

16

• Save and compile the program
• Close the program

17
Registering an ODBC Data Source Name

• The Microsoft Access database requires an ODBC
  driver to interact with the DBMS
• ODBC (Open Database Connectivity) allows programs
  to make method calls to a database
• The database must be registered as an ODBC data
  source name
• A data source name is the name used instead of
  the actual database name when connecting to the
  database
• Independent of Java and JDBCTM and done through
  the Control Panel

18
Registering an ODBC Data Source Name

• Open book to page 703 and follow steps 1 7
• For step 8, specify the folder on your flash
  drive where the rest of the files for this
  project are being stored.
• Complete steps 8, 9 and 10.

19
Connecting to a Database Using JDBCTM

• JDBC provides interoperability
• The underlying database can change without any
  changes to the application
• A JDBC driver translates between the JDBC API
  and the corresponding commands of the DBMS
• A JDBC-ODBC Bridge driver is used for access to
  the database
• The MakeDB class loads the driver and initializes
  the database

20
JDBCTM Driver Types
21
Establishing a JDBCTM Connection

• An appropriate driver must be loaded
• More than one driver can be loaded in a program
• The forName() method is used to load a JDBC
  driver. It returns a Class object.
• A call to Class.forName(X) would cause the
  class X to be loaded and initialized.
• We need to load the JDBC-ODBC Bridge driver
• See line 16 in MakeDB.java

22
Establishing a JDBCTM Connection

• A connection locates a specific database and
  establishes a database session
• Is the communication pathway between the user
  program and the database
• Connection lasts until the program terminates
• Establishing a connection to a database is like
  opening a file

23
Establishing a JDBCTM Connection

• The getConnection() of the DriverManager class is
  used to obtain a connection to the database ( see
  line 20)
• The Connection object creates a static SQL
  statement with the createStatement() (line 21)
• A Statement object can execute a number of
  different SQL statements until closed
• The parameters of a static SQL statement do not
  change as the program executes

24
SQL Statements
25
Dropping Tables and Indexes in a Database

• A query returns a set of data results based on
  its parameters
• An update is used to add, modify, or delete data
  from the database
• executeUpdate() executes SQL statements, such as
  INSERT, UPDATE, or DELETE
• A drop is the deletion of an index of a table
• Indexes can be dropped explicitly or implicitly
• A table cannot be dropped if it contains fields
  referenced as foreign keys in other tables
• Table containing foreign key must be dropped
  first
• See lines 23 - 66

26
(No Transcript)
27
Creating Table, Indexes, and Keys

• Since UserStocks has fields that are foreign keys
  to other tables, these other tables must be
  created first
• CREATE TABLE is the SQL statement to create a new
  table
• Includes fields, data types, lengths (if
  applicable), and constraints
• Constraint Types
• An integrity constraint identifies a primary or
  foreign key
• A value constraint specifies the allowable value
  of data in a column
• Constraint Levels
• A table constraint restricts the field value with
  respect to all other values in the table
• A column constraint limits the value placed in a
  specific column

28
Creating Table, Indexes, and Keys
See lines 71 85
29
Closer look at SQL lines 75 - 79
stmt.executeUpdate ( "CREATE TABLE Stocks
("symbol TEXT(8) NOT NULL CONSTRAINT
PK_Stocks PRIMARY KEY, name TEXT(50) " )
")
30
Creating Table, Indexes, and Keys
See lines 87 - 104
31
Closer look at SQL lines 91 - 98
stmt.executeUpdate ( "CREATE TABLE Users
("userID TEXT(20) NOT NULL
CONSTRAINT PK_Users PRIMARY KEY, lastName
TEXT(30) NOT NULL, firstName TEXT(30) NOT
NULL, pswd LONGBINARY, admin BIT") " )
32
Creating Table, Indexes, and Keys
See lines 106 - 121
33
Closer look at SQL lines 110 - 115
stmt.executeUpdate("CREATE TABLE UserStocks
(userID TEXT(20) CONSTRAINT FK1_UserStocks
REFERENCES Users (userID), symbol TEXT(8)
, CONSTRAINT FK2_UserStocks FOREIGN KEY
(symbol) REFERENCES Stocks (symbol) ) ")
foreign key to Users table
foreign key to Stocks table
34
Creating Table, Indexes, and Keys
See lines 123 - 135
35
Closer look at SQL lines 127 - 129
stmt.executeUpdate("CREATE UNIQUE INDEX
PK_UserStocks ON UserStocks (userID, symbol)
WITH PRIMARY DISALLOW NULL")
36
SQL Constraints
37
Creating and Executing a Prepared Statement

• Objects that do not need to be manipulated in a
  database can be stored serially with a
  PreparedStatement object
• A PreparedStatement is a Statement object used to
  execute a precompiled or dynamic SQL statement
• Placeholders allow the same prepared statement to
  be used with different parameters each time it is
  executed
• Set methods have designated parameters with a
  given value for a particular data type

38
PreparedStatement
Set up the data for initial user. We must have
an administrative user in the table, since only
an administrative user can add a new user to the
table.
placeholders for parameters
39
PreparedStatement
fill in the parameters
user-defined method
40
Creating and Executing SQL Database Queries

• The executeQuery() method of a Statement or
  PreparedStatement object performs queries
• SELECT the columns to be returned for records
  matching the search condition in WHERE
• The ResultSet object is returned with the results
  of the query
• The result is processed in rows with next()
• Get methods for various data types retrieve the
  data
• getBytes() is used to return the byte array for a
  serialized object

41
is wildcard to get all records
object returned from executeQuery
use next to process records, moves cursor to
next row
get methods access data
42
. . .
user-defined method
43
Verifying that Stocks and UserStocks tables are
empty
44
Serializing an Object

• Create a ByteArrayOutputStream object
• Create an ObjectOutputStream object that wraps
  the ByteArrayOutputStream
• Use writeObject() to write the object to a stream
• Ensure that all bytes are written to the
  ByteArrayOutputStream with flush()
• Close the stream with close()
• Return a byte array with toByteArray()

45
Serializing an Object
46
Deserializing an Object

• Create a ByteArrayOutputStream object with the
  byte array as a parameter
• Create an ObjectOutputStream object to wrap the
  ByteArrayOutputStream
• Use the readObject() method to read the stream
  and return an Object
• Downcast the class to the expected type

47
Deserializing an Object
48
MakeDB.java

• Now, all tables have been created and one record
  is in the Users table.
• Compile the MakeDB.java program
• Run the application

49

• Dropping indexes tables ...
• Could not drop primary key on UserStocks table
  MicrosoftODBC Microsoft Acces
• s Driver Cannot find table or constraint.
• Could not drop UserStocks table MicrosoftODBC
  Microsoft Access Driver Table
• 'UserStocks' does not exist.
• Could not drop Users table MicrosoftODBC
  Microsoft Access Driver Table 'Use
• rs' does not exist.
• Could not drop Stocks table MicrosoftODBC
  Microsoft Access Driver Table 'St
• ocks' does not exist.
• Creating tables ............
• Creating Stocks table with primary key index...
• Creating Users table with primary key index...
• Creating UserStocks table ...
• Creating UserStocks table primary key index...
• Database created.
• Displaying data from database...

Could not drop tables that did not yet exist
New tables created
Data for first record
Other tables are empty
50
Creating a Data Access Class

• A data access (DA) class is a model of the
  database to the GUI program
• A control class is responsible for user access
• A model-view-controller (MVC) design separates
  model, view, and controller functions
• A model-delegate design combines the control and
  view
• StockTrackerDB is a model class
• Connects to the database and loads the driver
• Serializes and deserializes objects

51
Creating a Data Access Class

• Copy StockTrackerDB.java from the Student Files
• Change your name and todays date in the
  comments.

52
StockTrackerDB class
53
(No Transcript)
54
Adding Records with an SQL Insert

• Use the INSERT statement
• Add to the Stocks table

55

• Add to the Stocks table

Note password is serialized
56

• Add to the UserStocks table

57
Modifying Records with an SQL Update

• Only the Users table will be updated in the
  StockTracker application
• The SQL UPDATE statement is used to update field
  values in a record
• The SET clause indicates a comma-delimited list
  of fields and their new values
• The update applies to all records matching the
  search condition following the WHERE keyword

58
(No Transcript)
59
(No Transcript)
60
Performing an SQL Delete

• Delete a Stocks record with DELETE

61
Committing a Transaction to Delete Related Records

• Deleting a record from the Users table is more
  involved because of the relationship to the
  UserStocks table
• Related data must also be deleted
• Groups of related statements can be viewed as one
  transaction
• If all statements are completed successfully, the
  transaction can be committed
• If all changed are not made successfully, the
  program can rollback the changes
• Set a connections auto-commit mode to false to
  allow multiple SQL statements to be grouped as
  one transaction
• Use commit() or rollback() to end a transaction

62

• Delete a Users record with DELETE

63
(No Transcript)
64
(No Transcript)
65

• Access records by stock symbol, list by name

66

• Access all users

Note password is deserialized
67
Obtaining Database Field Values

• Use an ArrayList to store multiple records

68
(No Transcript)
69
Testing

• Run the STLogon class
• Use the StockTrackerDB data access class to
  manipulate the database
• Test all buttons with test data

70
Chapter 11 Java Homework

• Learn It Online1. Chapter Reinforcement
  True/False Multiple Choice, page 772
• All answers must be typed and printed out
• Debugging Assignment
• Page 773
• No Programming Assignments
• Due 12/17/07
• Do not pass in your flash drive
• No folder needed, just staple papers together