Introduction
To Java - MFC 158
Week
11 Lecture notes - Fall 2000
Chapter 18 - Java Database connectivity (JDBC)
-
Sequential file processing - useful for
processing nearly all records in a file
-
Random access file processing - useful for
processing a single record and when only a small percentage of records need to
be processed
-
Both
methods are not convenient for querying data
-
Databases allow both
access for single record access, multiple record access and creating
sophisticated queries of data
-
A
language called Structured Query
Language (SQL) is the main language for accessing relational database
systems, using queries.
-
Java
enables programmers to write code that uses SQL queries to access data
Database
Systems
-
an
integrated collection of data
-
the
hardware which the data resides
-
the
software (or DBMS) that controls the storage and retrieval of data
Relational
Database Model
-
a
relational database is composed of tables (analogous to a file of related
information - task file)
-
a
row in the table called a record (a single task within the file)
-
a
row / record consists of columns (or fields - the task description from our
file)
Primary keys
-
tables
normally have primary keys (although not mandatory). These keys can be made of
multiple fields. Also, they can't
contain duplicates.
Refer to page
891 for examples
Rule of Entity
Integrity
-
every
record must have a value in the primary key field
-
the
value in the primary field must be unique
Refer to page
895 - Fig 18.11
Relationships
and Foreign keys
-
the
Titles table has a foreign key called PublisherID
-
this
foreign key in the Titles table is a primary key in another table - the
Publishers table
-
foreign
keys allow data from multiple tables to be joined together for several puposes
Rule of
Referential Integrity
-
foreign
keys help to maintain 'referential integrity'
-
every
foreign key value MUST exist as another table's primary key field (you can't
have a PublisherID value in the Titles table if a Publisher doesn't exist in
the Publishers table!!)
-
this
has interesting implications of a Publisher gets deleted when a Title
references that value
Structured
Query Language - samples
SELECT
* FROM TableNAME
SELECT
* FROM Authors
SELECT
AuthorID, LastName FROM Authors
SELECT
* FROM TableName WHERE criteria
SELECT
* FROM Authors WHERE YearBorn > 1960
SELECT
* FROM Authors WHERE LastName LIKE 'd*'
SELECT
* FROM TableName ORDER BY field ASC
(ascending)
SELECT
* FROM TableName ORDER BY field DESC (descending)
SELECT
* FROM Authors ORDER BY LastName ASC (the ASC is the default, not needed)
SELECT
* FROM Titles WHERE Title Like ' *How ' ORDER BY Title ASC
-------------------------------------------------------------------------------------------------------------------------------
Source: www.oracle.com
Excerpt from: Oracle's
JDBC Drivers
Accessing the Oracle
RDBMS from Java
An
Oracle Technical White Paper
October
20, 1997
JDBC/OCI
Driver 2-Tier Client Server Configuration
The
following diagram shows a traditional two-tier client-server using JDBC/OCI
Driver. The Java application makes calls to the JDBC/OCI driver which in turn
translates the JDBC calls directly into SQL*Net to communicate with Oracle
Database Server.
JDBC/OCI
Driver 3-Tier Configuration A
The
following diagram indicates how the JDBC/OCI Driver can be used either within a
three-tier configuration either in an Intranet setting or an Extranet setting
[where the web server is located behind the firewall]. The client is a web
browser that communicates with the web server using the HTTP protocol. The Java
application [the executable] and the JDBC/OCI driver are installed on the web
server. The user can start/invoke the Java application in a number of different
ways: The user can also use a CGI script, Oracle's Web Request Broker API [if
the application is deployed on Oracle's Web Application Server], or even an
IIOP style invocation. Once the Java application has been invoked, it
communicates with the backend database server using SQL*Net.
JDBC/OCI
Driver 3-Tier Configuration B
An
alternative 3 tier architecture with the JDBC/OCI driver is illustrated below.
In this application, the application logic or business logic is divided into
between Java applets and servlets. These applets and servlets typically
communicate using either RMI or more likely the CORBA (IIOP) protocol. In this
type of configuration, the user downloads an HTML page with a Java applet tag
in it from a web server. By selecting the applet tag, the user then invokes the
Java applet which then communicates with the Java servlet that is deployed on
the middle-tier web server in a stateful manner using the CORBA IIOP protocol
for instance. The servlet in turn communicates with the Oracle database server
using the JDBC/OCI driver.
Thin JDBC
3-Tier/2-Tier Configuration
The
following diagram shows the typical configuration in which the Thin JDBC driver
is used. The Java applet and Thin JDBC are downloaded into the browser after
the user opens a URL. The Thin JDBC communicates directly with the database
server. The web server and the database server can be on physically separate
machines or on the same machine. In an Intranet deployment the entire
configuration is behind a firewall; in an Extranet deployment, the web server
and the database are both behind the firewall.
Access to
Heterogeneous Data Sources
Both
the Oracle JDBC drivers can also be used to access a wide variety of
heterogeneous data sources. By using Oracle's Transparent Gateway products, both
JDBC drivers can provide high performance transparent connectivity to over 25
different enterprise and legacy databases from Java applications or applets as
shown in the figure below.
-------------------------------------------------------------------------------------------------------------------------------
JDBCTM API Documentation
The JDBCTM API provides universal data access from the JavaTM programming language. Using the JDBC 2.0 API, you can access virtually any data source, from relational databases to spreadsheets and flat files. JDBC technology also provides a common base on which tools and alternate interfaces can be built.
Link to
documentation at http://java.sun.com/products/jdk/1.3/docs/guide/jdbc/index.html
Group
Analysis on Page 905