Transcript Welcome! [www.web

Intro to JDBC
To effectively use Java Data Base Connectivity we must understand:
1. Relational Database Management Systems (RDBMS)
2. JDBC Drivers
3. SQL (Structured Query Language)
4. Methods in packages java.sql & javax.sql
Intro to JDBC
RDBMS (p.1)
Popular Relational Databases:
•
Oracle
•
Microsoft SQL Server
•
Sybase
•
Informix
•
Microsoft Access
•
mySQL
•
CloudScape
… and many, many more!
Intro to JDBC
RDBMS (p.2)
A Database consists of:
Tables, which store…
records, which contain fields of information.
Relational Databases:
store data in multiple tables that can be related (or linked) to one
another via key fields.
Intro to JDBC
RDBMS (p.3)
Row
Number
Name
Department
Salary
Location
23603
Jones
413
1100
New Jersey
24568
Kerwin
413
2000
New Jersey
34589
Larson
642
1800
Los Angeles
35761
Myers
611
1400
Orlando
47132
Neumann
413
9000
New Jersey
78321
Stephens
611
8500
Orlando
Primary key
Column
An example:
let’s look at the Microsoft Access database we just used.
Intro to JDBC
RDBMS (p.4)
A Sample Relational Database:
Intro to JDBC
RDBMS (p.5)
In this example, a one-to-many relationship exists between the
Contacts table and the Calls table. Contacts.ContactID is the
primary key; Calls.ContactID is the foreign key. Primary and
foreign keys need not have the same name, but must be of the
same data type.
Intro to JDBC
JDBC Drivers (p.1)
Type
1
Description
The JDBC-to-ODBC bridge driver connects Java programs to Microsoft ODBC
(Open Database Connectivity) data sources. The Java 2 Software Development
Kit from Sun Microsystems, Inc. includes the JDBC-to-ODBC bridge driver
(sun.jdbc.odbc.JdbcOdbcDriver). This driver typically requires the
ODBC driver to be installed on the client computer and normally requires
configuration of the ODBC data source. The bridge driver was introduced
primarily for development purposes and should not be used for production
applications.
2
Native-API, partly Java drivers enable JDBC programs to use database-specific
APIs (normally written in C or C++) that allow client programs to access
databases via the Java Native Interface. This driver type translates JDBC into
database-specific code. Type 2 drivers were introduced for reasons similar to the
Type 1 ODBC bridge driver.
3
JDBC-Net pure Java drivers take JDBC requests and translate them into a
network protocol that is not database specific. These requests are sent to a server,
which translates the database requests into a database-specific protocol.
4
Native-protocol pure Java drivers convert JDBC requests to database-specific
network protocols, so that Java programs can connect directly to a database.
Fig. 23.26
JDBC driver types.
Intro to JDBC
JDBC Drivers (p.2)
Sun’s JDBC-ODBC Bridge – a type 1 driver
Tips:
- Always use System DSN, so DSN is available to all
- ODBC Data Sources are only available on LAN/WAN
(Not Internet accessible)
- Use a Java/NativeAPI driver to access databases via internet
- More info: JDBC-ODBC Bridge Driver
Intro to JDBC
JDBC Drivers (p.3)
More drivers available at:
http://industry.java.sun.com/products/jdbc/drivers
Lab: Find a type 2, 3 or 4 driver to use with Microsoft Access
Your driver documentation will give you the class name to use.
For instance, if the class name is jdbc.DriverXYZ , you would
load the driver with the following line of code:
Class.forName("jdbc.DriverXYZ");
Another driver lab: Set up distributed DB at \\B122-1
Intro to JDBC
SQL (Structured Query Language) (p.1)
SQL is an industry standard for querying relational databases.
Although SQL, like everything else in the software industry, exists
in several versions and flavors, basic syntax is consistent and
reliable across all systems and software.
Database languages, such as SQL, ordinarily include two
sublanguages:
•
Data Definition Language (DDL)
•
Data Manipulation Language (DML)
Intro to JDBC
SQL (p.2)
•
Data Definition Language (DDL): Enables developer to create
and modify database architecture (tables, records, fields).
Query keywords:
CREATE TABLE
ALTER TABLE
DROP TABLE
Intro to JDBC
SQL (p.3)
•
Data Manipulation Language (DML): Enables developer to add,
edit and delete records and retrieve data.
Query keywords:
SELECT – retrieves data from table(s)
INSERT – populates table with data
UPDATE – changes existing data in table
DELETE – removes data from table
Intro to JDBC
SQL (p.4)
SELECT statement clauses:
•
SELECT – Select and retrieve records
•
FROM – Tables from which to get fields
•
WHERE – Criteria for filtering selections
•
GROUP BY – How to group found records
•
HAVING – Used with GROUP BY to specify criteria
•
ORDER BY – Criteria for ordering found records
Intro to JDBC
SQL (p.5)
SELECT statement syntax:
•
Basic SELECT Query :
SELECT FieldName, … FROM TableName, …
•
Examples:
SELECT * FROM Authors
SELECT AuthorID, LastName FROM Authors
Intro to JDBC
SQL (p.6)
SELECT statement syntax:
•
SELECT Query with WHERE clause:
SELECT FieldName, … FROM TableName, …
WHERE criteria
•
Examples:
SELECT * FROM Authors
WHERE YearBorn > 1960
SELECT * FROM Authors
WHERE LastName = ‘Dietel’
(WHERE operators: < , > , <= , >= , = , < > , LIKE ( [?, *] )
Intro to JDBC
SQL (p.7)
SELECT statement syntax:
•
ORDER BY Clause:
SELECT FieldName, … FROM TableName, …
WHERE criteria
ORDER BY FieldName, … ASC | DESC
•
Example:
SELECT * FROM Authors
ORDER BY LastName, FirstName DESC
Intro to JDBC
SQL (p.8)
SELECT statement syntax:
•
WHERE with LIKE clause:
SELECT * FROM Authors
WHERE lastName LIKE ‘D*’ [Or, use ‘D%’ other DBs]
authorID
firstName lastName
1
Harvey
Deitel
2
Paul
Deitel
Fig. 23.15 Authors whose last name starts with D from
the authors table.
•
LIKE clause wildcard operators:
? * [Or, _ % other DBs]
Intro to JDBC
SQL (p.9)
SELECT statement syntax:
•
WHERE with LIKE clause:
SELECT authorID, firstName, lastName FROM Authors
WHERE lastName LIKE ‘?i*’ [Or, use ‘_i%’ other DBs]
authorID
firstName lastName
3
Tem
Nieto
Fig. 23.16 The only author from the authors table
whose last name contains i as the second letter.
•
LIKE clause wildcard operators:
? * [Or, _ % other DBs]
Intro to JDBC
SQL (p.10)
SELECT statement syntax:
•
A More Complex Example Using an Implied Join:
SELECT PublisherName, Title
FROM Publishers, Titles
WHERE (Titles.PublisherID = Publishers.PublisherID)
ORDER BY PublisherName, Title ASC
OR
SELECT Publishers.PublisherName, Titles.Title
FROM Publishers, Titles
WHERE (Titles.PublisherID = Publishers.PublisherID)
ORDER BY Publishers.PublisherName, Titles.Title ASC
Intro to JDBC
SQL (p.11)
INSERT statement syntax:
•
Basic INSERT Query :
INSERT INTO tableName ( columnName1, .., columnNameN )
VALUES ( value1, … , valueN )
•
Example:
INSERT INTO authors ( firstName, lastName )
VALUES ( ‘Sue’, ‘Smith’ )
Intro to JDBC
SQL (p.12)
UPDATE statement syntax:
•
Basic UPDATE Query :
UPDATE tableName
SET columnName1 = value1, … , columnNameN = valueN
WHERE criteria
•
Example:
UPDATE authors
SET lastName = ‘Jones’
WHERE lastName = ‘Smith’ AND firstName = ‘Sue’
Intro to JDBC
SQL (p.13)
DELETE statement syntax:
•
Basic DELETE Query :
DELETE FROM tableName
WHERE criteria
•
Example:
DELETE FROM authors
WHERE lastName = ‘Jones’ AND firstName = ‘Sue’
Intro to JDBC
Package java.sql (p.1)
Result Sets
•
ResultSet (Recordset in ASP):
JDBC returns query results in a ResultSet Object
•
ResultSets are retrieved by executing queries:
resultSet = statement.executeQuery( query );
Intro to JDBC
Package java.sql (p.2)
Traversing Result Sets
•
A ResultSet can be traversed forward and backward (depending
on cursor type) using:
resultSet.first();
resultSet.next();
resultSet.previous();
resultSet.last();
… and more!
Intro to JDBC
Package java.sql (p.3)
Data types for Result Sets
•
Data can be retrieved from a ResultSet by using various getXXX
methods:
resultSet.getString(FieldName);
// returns data as type
resultSet.getFloat(FieldName);
resultSet.getDate(FieldName);
resultSet.getInt(FieldName);
resultSet.getMetaData();
// returns ResultSetMetaData obj.
… and more!
Intro to JDBC
Package java.sql (p.4)
Result Set Cursors
ResultSet static
type constant
TYPE_FORWARD_ONLY
Description
Specifies that a ResultSet’s cursor can move only
in the forward direction (i.e., from the first row to the
last row in the ResultSet).
TYPE_SCROLL_INSENSITIVE
Specifies that a ResultSet’s cursor can scroll in
either direction and that the changes made to the
ResultSet during ResultSet processing are not
reflected in the ResultSet unless the program
queries the database again.
TYPE_SCROLL_SENSITIVE
Fig. 23.28
Specifies that a ResultSet’s cursor can scroll in
either direction and that the changes made to the
ResultSet during ResultSet processing are
reflected immediately in the ResultSet.
ResultSet constants for specifying ResultSet type.