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