Java Database Connectivity JDBC

1
Java Database Connectivity (JDBC)

• Presented by
• Rola Othman
• Pat Wiseman
• Mary Gleason

8 April 2003
2
Objectives

• Relational database tables
• SQL (Structured Query Language)
• Register a database as an ODBC data source
• Connecting to a database from Java
• Build a database using JDBC and SQL
• Query a database with java
• Metadata and Result set
• Aggregate Functions
• The use of Prepared statements
• Use database GUI
• Process database Transactions with the GUI

3
Database Tables and SQL Queries

• SQL
• Allows for the creation, updating, and querying a
  database
• Standard commands are used
• Relational Database Tables
• Data is stored in tables

Customer Table
4
Relational Databases Cont

• Rows represent info needed for one customer
• Fields are assigned
• The key is a unique identifier
• Keys are used to retrieve information from the
  data
• Relationships
• Association established between common fields
  (columns) in two tables.
• 1-1
• 1-M
• M-M

5
Relational Database Cont

• primary key One or more fields (columns) whose
  value or values uniquely identify each record in
  a table. A primary key cannot allow Null values
  and must always have a unique index. A primary
  key is used to relate a table to foreign keys in
  other tables
• foreign key One or more table fields (columns)
  that refer to the primary key field or fields in
  another table. A foreign key indicates how the
  tables are related.

6
SQL-Structured Query Language

• Standardized language that retrieves and updates
  info to the database
• SQL statements are executed within Java
• Statements that are used
• CREATE
• CREATE TABLE Customer (CustomerID VARCHAR(4),
  CustomerName VARCHAR(25), Address VARCHAR(25),
  BalanceDue CURRENCY)
• INSERT
• INSERT INTO Customer VALUES (5678,'Darnell
  Davis','33 Second St.',130.95)
• DELETE
• DELETE FROM Item WHERE Description 'CD player'
• UPDATE
• UPDATE Item SET Quantity 12
• SELECT (Mostly with Querying), WHERE, AND, OR
• Query- Retrieval of information from a database
• SELECT FROM Customer
• WHERE CUSTID 1
• WHERE-Specific conditions need to be met

7
Connecting to a Database

• Two-tiered
• Database server
• Resides on one machine
• Various clients connect when access to data is
  needed

• Three-tiered
• Application Server
• Acts as a server to various application clients
• Client of database server

8
JDBC

• Java Database Connectivity
• Write Java programs that will work no matter
  which database system
• JDBC Driver
• Provides a uniform interface to Java programs
• Vendors provide JDBC drivers for use with each
  database system
• Translates JDBC statements to the specific
  commands of a particular database system
• ODBC bridge to work with Access
• Open Database Connectivity (ODBC)

9
Lab Time

• Lab 1
• Creating an ODBC Data Source

10
Connecting from Java
11
Building the Database
Once a connection is made to the database SQL
statements are used to create to populate the
database
12
Retrieving Information

• JDBC can be used to extract information from a
  database
• executeQuery method
• Returns
• ResultSet

13
Lab Time

• Lab 2
• Connect, Create, and Extract information from a
  Database

14
Metadata and Aggregate Functions

• Metadata
• Database
• Properties of the database
• Table Names
• Column types and names
• Result sets that are obtained
• Result Set
• Information about each table
• Aggregate
• Functions that compute values
• Uses rows to produce results

15
Lab Time

• Lab 3
• Metadata, Result set metadata, and Aggregate
  functions

16
Prepared Statements and Transactions

• Translates statement to low level database
  commands once
• Execution possible many times
• No more redundancy
• Transactions must be complete
• Commitment must occur after transaction
  completion
• Execution of several transactions
• Bank example

Withdraw
Deposit
Commit
17
Lab Time

• Lab 4
• Statements and Transactions

18
GUI for database queries

• Graphical User Interface
• User Composes queries
• User controls actions

19
Lab Time

• Lab 5
• GUI

20
If you have any questions

• Please call Rola in Florida!

The End