Transcript Chapter 37

1
Chapter 37 Java Database Programming
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
2
What is a Database System?
A p p lication U sers
A p p lication P rogram s
S ystem U sers
D atab ase M an agem en t S ystem (D B M S )
e.g., Access, MySQL,
Oracle, and MS SQL
Server
d atab ase
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
3
Database Application Systems
A p p lication U sers
A p p lication P rogram s
D atab ase M an agem en t S ystem
…
…
D atab ase M an agem en t S ystem
…
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
d atab ase
4
Relational Databases
Most of today’s database systems are relational database
systems, based on the relational data model.
Components:

Structure – data representation

Integrity – constraints

Language – for accessing and manipulating data.
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
5
Course Table
C olu m n s/A ttrib u tes
R elation /T ab le N am e
C ou rse T ab le
T u p les/
R ow s
courseId
subjectId
courseNumber
title
numOfCredits
11111
11112
11113
11114
11115
11116
11117
11118
CSCI
CSCI
CSCI
CSCI
MATH
MATH
EDUC
ITEC
1301
1302
3720
4750
2750
3750
1111
1344
Introduction to Java I
Introduction to Java II
Database Systems
Rapid Java Application
Calculus I
Calculus II
Reading
Database Administration
4
3
3
3
5
5
3
3
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
6
Student Table
S tud en t T ab le
ssn
firstName
mi lastName
phone
birthDate
street
444111110
Jacob
R
Smith
9129219434
1985 -04-09 99 Kingston Street 31435
BIOL
444111111
John
K
Stevenson
9129219434
null
31411
BIOL
444111112
George
K
Smith
9129213454
1974 -10-10 1200 Abercorn St.
31419
CS
444111113
Frank
E
Jones
9125919434
1970 -09-09 100 Main Street
31411
BIOL
444111114
Jean
K
Smith
9129219434
1970-02-09 100 Main Street
31411
CHEM
444111115
Josh
R
Woo
7075989434
1970 -02-09 555 Franklin St.
31411
CHEM
444111116
Josh
R
Smith
9129219434
1973 -02-09 100 Main Street
31411
BIOL
444111117
Joy
P
Kennedy
9129229434
1974 -03-19 103 Bay Street
31412
CS
444111118
Toni
R
Peterson
9129229434
1964 -04-29 103 Bay Street
31412
MATH
444111119
Patrick
R
Stoneman
9129229434
1969 -04-29 101 Washington St. 31435
MATH
444111120
Rick
R
Carter
9125919434
1986 -04-09 19 West Ford St.
BIOL
100 Main Street
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
zipCode
31411
deptID
7
Enrollment
Table
E n rollm en t T ab le
ssn
courseId
dateRegistered
grade
444111110
11111
2 0 0 4- 0 3 - 1 9
A
444111110
11112
2 0 0 4- 0 3 - 1 9
B
444111110
11113
2 0 0 4- 0 3 - 1 9
C
444111111
11111
2 0 0 4- 0 3 - 1 9
D
444111111
11112
2 0 0 4- 0 3 - 1 9
F
444111111
11113
2 0 0 4- 0 3 - 1 9
A
444111112
11114
2 0 0 4- 0 3 - 1 9
B
444111112
11115
2 0 0 4- 0 3 - 1 9
C
444111112
11116
2 0 0 4- 0 3 - 1 9
D
444111113
11111
2 0 0 4- 0 3 - 1 9
A
444111113
11113
2 0 0 4- 0 3 - 1 9
A
444111114
11115
2004-03-19
B
444111115
11115
2 0 0 4- 0 3 - 1 9
F
444111115
11116
2 0 0 4- 0 3 - 1 9
F
444111116
11111
2 0 0 4- 0 3 - 1 9
D
444111117
11111
2 0 0 4- 0 3 - 1 9
D
444111118
11111
2 0 0 4- 0 3 - 1 9
A
444111118
11112
2004-03-19
D
444111118
11113
2 0 0 4- 0 3 - 1 9
B
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
8
Table vs. File
NOTE:
Table (relation) is not the same as file.
Most relational database systems store
multiple tables in a file.
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
9
Integrity Constraints
Integrity constraints impose conditions that the
relations must satisfy.
intra-relational constraints (involve 1 relation)
domain constraint
primary key constraint
inter-relational (involve more than 1 relation)
foreign key constraint.
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
10
Domain Constraints
E n rollm en t T ab le
specify the permissible
values for an attribute.
ssn
courseId
dateRegistered
grade
444111110
11111
2004-03-19
A
444111110
11112
2004-03-19
B
444111110
11113
2004-03-19
C
...
E ach valu e in cou rseId in th e
E n rollm en t tab le m u st m atch a valu e
in cou rseId in th e C ou rse tab le
C ou rse T ab le
courseId
subjectId
courseNumber
title
numOfCredits
11111
11112
11113
...
CSCI
CSCI
CSCI
1301
1302
3720
Introduction to Java I
Introduction to Java II
Database Systems
4
3
3
E ach row m u st h ave a
valu e for cou serId , an d
th e valu e m u st b e u niqu e
domain
constrai
nt
E ach valu e in th e
n u m O fC red its colu m n m u st b e
greater th an 0 and less than 5
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
11
Primary Key Constraints
E n rollm en t T ab le
Used to identify
records in a relation
ssn
courseId
dateRegistered
grade
444111110
11111
2004-03-19
A
444111110
11112
2004-03-19
B
444111110
11113
2004-03-19
C
...
E ach valu e in cou rseId in th e
E n rollm en t tab le m u st m atch a valu e
in cou rseId in th e C ou rse tab le
C ou rse T ab le
courseId
subjectId
courseNumber
title
numOfCredits
11111
11112
11113
...
CSCI
CSCI
CSCI
1301
1302
3720
Introduction to Java I
Introduction to Java II
Database Systems
4
3
3
E ach row m u st h ave a
valu e for cou serId , an d
th e valu e m u st b e u niqu e
Primary key
constraint
E ach valu e in th e
n u m O fC red its colu m n m u st b e
greater th an 0 and less than 5
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
12
Foreign Key Constraints
E n rollm en t T ab le
ssn
courseId
dateRegistered
grade
444111110
11111
2004-03-19
A
444111110
11112
2004-03-19
B
444111110
11113
2004-03-19
C
Define relationships
between tables
...
E ach valu e in cou rseId in th e
E n rollm en t tab le m u st m atch a valu e
in cou rseId in th e C ou rse tab le
C ou rse T ab le
courseId
subjectId
courseNumber
title
numOfCredits
11111
11112
11113
...
CSCI
CSCI
CSCI
1301
1302
3720
Introduction to Java I
Introduction to Java II
Database Systems
4
3
3
E ach row m u st h ave a
valu e for cou serId , an d
th e valu e m u st b e u niqu e
Foreign key
constraint
E ach valu e in th e
n u m O fC red its colu m n m u st b e
greater th an 0 and less than 5
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
13
Superkey
Superkey
Key
Candidate
key
Primary
key
A superkey is an attribute or a set of attributes
that uniquely identify the relation.
No two tuples have the same values on the
superkey.
A relation consists of a set of distinct tuples.
The set of all attributes in the relation forms a
superkey.
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
14
Key and Candidate Key
Superkey
Key
A key K is a minimal superkey, meaning that any
proper subset of K is not a superkey.
Candidate
key
It is possible that a relation has several keys.
Primary key
In this case, each of the keys is called a
candidate key.
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
15
Primary Key
Superkey
Key
The primary key is a candidate key designated by
the database designer.
Candidate
key
Often used to identify tuples in a relation.
Primary
key
create table Course(
subjectCode char(4),
courseNumber int,
title varchar(50), numOfCredits int
constraint greaterThanOne check (numOfCredits >= 1),
primary key (subjectCode, courseNumber));
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
16
Foreign Key Example
create table Enrollment (
ssn char(9),
courseId char(5),
dateRegistered date,
grade char(1),
primary key (ssn, courseId),
foreign key (ssn) references Student,
foreign key (courseId) references Course
);
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
17
Foreign Key Discussion, cont.
A foreign key is not necessarily the primary key or
part of the primary in the relation.
The referencing relation and the referenced relation
may be the same table. (supervisorId is a foreign
key in Faculty that references facultyId in Faculty)
Foreign key and its referenced primary key can have
different names, as long as they have the same domain.
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
18
SQL
SQL: Structured Query Language
 To access database data
To manipulate data
Universal language for accessing relational database
systems
 Application programs may allow users to access
database without directly using SQL, since the
underlying applications use SQL.
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
Examples of simple SQL statements
Create table
Drop table
Describe
table
Select
Insert
Delete
Update
create table Course (
courseId char(5),
subjectId char(4) not null,
courseNumber integer,
title varchar(50) not null,
numOfCredits integer,
primary key (courseId)
);
create table Student (
ssn char(9),
firstName varchar(25),
mi char(1),
lastName varchar(25),
birthDate date,
street varchar(25),
phone char(11),
zipCode char(5),
deptId char(4),
primary key (ssn)
);
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
19
Examples of simple SQL statements
Create table
Drop table
Describe
table
Select
Insert
Delete
Update
drop table Enrollment;
drop table Course;
drop table Student;
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
20
Examples of simple SQL statements
Create table
Drop table
Describe
table
Select
Insert
Delete
Update
describe Course; -- Oracle
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
21
Examples of simple SQL statements
Create table
Drop table
Describe
table
Select
Insert
Delete
Update
select firstName, mi, lastName
from Student
where deptId = 'CS';
select firstName, mi, lastName
from Student
where deptId = 'CS' and zipCode = '31411';
select *
from Student
where deptId = 'CS' and zipCode = '31411';
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
22
Examples of simple SQL statements
Create table
Drop table
Describe
table
Select
Insert
Delete
Update
insert into Course (courseId, subjectId, courseNumber, title)
values ('11113', 'CSCI', '3720', 'Database Systems', 3);
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
23
Examples of simple SQL statements
Create table
Drop table
Describe
table
Select
Insert
Update
Delete
update Course
set numOfCredits = 4
where title = 'Database Systems';
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
24
Examples of simple SQL statements
Create table
Drop table
Describe
table
Select
Insert
Update
Delete
delete Course
where title = 'Database System';
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
25
26
The Architecture of JDBC
Ja va A p p li catio n s/
A p p lets
JD B C A P I
O ra cl e JD B C
D ri ve r
JD B C -O D B C
B rid ge D ri ve r
O ra cl e O D B C
D ri ve r
Lo cal o r rem o te
O RACLE D B
M icro so ft
O D B C D ri ve r
M icro soft A ccess
D atab ase
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
27
The JDBC Interfaces
Loading
drivers
D river
C o n nectio n
Establishing
connections
C o n nectio n
S tate m e nt
S tate m e nt
S tate m e nt
S tate m e nt
Creating and
executing
statements
R esultS et
R esultS et
R esultS et
R esultS et
Processing
ResultSet
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
Developing JDBC Programs
Loading
drivers
Statement to load a driver:
Class.forName("JDBCDriverClass");
Establishing
connections
A driver is a class. For example:
Creating and
executing
statements
Processing
ResultSet
Database
Access
MySQL
Oracle
Driver Class
sun.jdbc.odbc.JdbcOdbcDriver
com.mysql.jdbc.Driver
oracle.jdbc.driver.OracleDriver
Source
Already in JDK
Website
Website
The JDBC-ODBC driver for Access is bundled in JDK.
MySQL driver class is in mysqljdbc.jar
Oracle driver class is in classes12.jar
To use the MySQL and Oracle drivers, you have to add mysqljdbc.jar and
classes12.jar in the classpath using the following DOS command on
Windows:
classpath=%classpath%;c:\book\mysqljdbc.jar;c:\book\classes12.jar
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
28
29
Developing JDBC Programs
Loading
drivers
Connection connection = DriverManager.getConnection(databaseURL);
Database URL Pattern
jdbc:odbc:dataSource
jdbc:mysql://hostname/dbname
Oracle
jdbc:oracle:thin:@hostname:port#:oracleDBSID
Establishing Access
connections MySQL
Creating and
executing
statements
Processing
ResultSet
Examples:
For Access:
See Supplement IV.D for
creating an ODBC data source
Connection connection = DriverManager.getConnection
("jdbc:odbc:ExampleMDBDataSource");
For MySQL:
Connection connection = DriverManager.getConnection
("jdbc:mysql://localhost/test");
For Oracle:
Connection connection = DriverManager.getConnection
("jdbc:oracle:thin:@liang.armstrong.edu:1521:orcl", "scott", "tiger");
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
Developing JDBC Programs
Loading
drivers
Establishing
connections
Creating
and
executing
statements
Processing
ResultSet
Creating statement:
Statement statement = connection.createStatement();
Executing statement (for update, delete, insert):
statement.executeUpdate
("create table Temp (col1 char(5), col2 char(5))");
Executing statement (for select):
// Select the columns from the Student table
ResultSet resultSet = statement.executeQuery
("select firstName, mi, lastName from Student where lastName "
+ " = 'Smith'");
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
30
Developing JDBC Programs
Loading
drivers
Establishing
connections
Creating and
executing
statements
Processing
ResultSet
31
Executing statement (for select):
// Select the columns from the Student table
ResultSet resultSet = stmt.executeQuery
("select firstName, mi, lastName from Student where lastName "
+ " = 'Smith'");
Processing ResultSet (for select):
// Iterate through the result and print the student names
while (resultSet.next())
System.out.println(resultSet.getString(1) + " " + resultSet.getString(2)
+ ". " + resultSet.getString(3));
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
import java.sql.*;
public class SimpleJdbc {
public static void main(String[] args)
throws SQLException, ClassNotFoundException {
// Load the JDBC driver
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Driver loaded");
Simple
JDBC
Example
// Establish a connection
Connection connection = DriverManager.getConnection
("jdbc:mysql://localhost/test");
System.out.println("Database connected");
// Create a statement
Statement statement = connection.createStatement();
// Execute a statement
ResultSet resultSet = statement.executeQuery
("select firstName, mi, lastName from Student where lastName "
+ " = 'Smith'");
// Iterate through the result and print the student names
while (resultSet.next())
System.out.println(resultSet.getString(1) + "\t" +
resultSet.getString(2) + "\t" + resultSet.getString(3));
// Close the connection
connection.close();
}
}
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
32
Creating ODBC Data Source
Please follow the steps in Supplement on the
Companion Website to create an ODBC data source on
Windows.
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
33
34
Example:
Accessing Database from Java Applets
This example demonstrates connecting to a database
from a Java applet. The applet lets the user enter the
SSN and the course ID to find a student’s grade.
FindGrade
Run
NOTE: To run this program from here, you need:
1.
To have a MySQL database setup just like the one in the text.
2.
Set MySQL JDBC driver in the classpath.
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
35
Processing Statements
Once a connection to a particular database is
established, it can be used to send SQL statements
from your program to the database. JDBC provides
the Statement, PreparedStatement, and
CallableStatement interfaces to facilitate sending
statements to a database for execution and receiving
execution results from the database.
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
Processing Statements Diagram
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
36
37
execute, executeQuery, executeUpdate
Methods for executing SQL statements:
execute
executeQuery (single result set)
executeUpdate (0 or 1 update counts)
Parameters: string containing a SQL statement ,
passed to database for execution
Use execute if multiple result sets, multiple update
counts, or a combination of result sets and update
counts are produced.
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
38
Retrieving Database Metadata
Database metadata: information that describes
the database itself.
JDBC includes
DatabaseMetaData interface for obtaining
database wide information
ResultSetMetaData interface for descriptive
information on a specific ResultSet.
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
39
Database Metadata, cont.
DatabaseMetaData methods can be divided into
three groups:
 retrieve general information:
 find database capabilities
 get object descriptions
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
40
Database Metadata, cont.
DatabaseMetaData methods can be divided into
three groups:
 retrieve general information:
 URL
 username
product version
 driver name / version
available functions / data types
 find database capabilities
 get object descriptions
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
41
Database Metadata, cont.
DatabaseMetaData methods can be divided into
three groups:
 retrieve general information:
 find database capabilities
 does the database supports GROUP BY ?
 is add column option valid in ALTER TABLE?
supported types of SQL grammar
 get object descriptions
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
42
Database Metadata, cont.
DatabaseMetaData methods can be divided into
three groups:
 retrieve general information:
 find database capabilities
get object descriptions
 describe table
 describe view
 describe procedure
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
DatabaseMetaData dbMetaData = connection.getMetaData();
System.out.println("database URL: " + dbMetaData.getURL());
System.out.println("database username: " +
dbMetaData.getUserName());
System.out.println("database product name: " +
dbMetaData.getDatabaseProductName());
System.out.println("database product version: " +
dbMetaData.getDatabaseProductVersion());
System.out.println("JDBC driver name: " +
dbMetaData.getDriverName());
System.out.println("JDBC driver version: " +
dbMetaData.getDriverVersion());
System.out.println("JDBC driver major version: " +
new Integer(dbMetaData.getDriverMajorVersion()));
System.out.println("JDBC driver minor version: " +
new Integer(dbMetaData.getDriverMinorVersion()));
System.out.println("Max number of connections: " +
new Integer(dbMetaData.getMaxConnections()));
System.out.println("MaxTableNameLentgh: " +
new Integer(dbMetaData.getMaxTableNameLength()));
System.out.println("MaxColumnsInTable: " +
new Integer(dbMetaData.getMaxColumnsInTable()));
connection.close();
Examples
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671
43
44
Sample Run
Liang, Introduction to Java Programming, Seventh Edition, (c) 2009 Pearson Education, Inc. All
rights reserved. 0136012671