Baixe Capítulo 34 - Programación de bases de datos con Java - Liang-Java-Comp11e e outras Resumos em PDF para Informática, somente na Docsity!
Objectives
■ ■ To understand the concepts of databases and database management
systems (§34.2).
■ ■ To understand the relational data model: relational data structures, con-
straints, and languages (§34.2).
■ ■ To use SQL to create and drop tables and to retrieve and modify data
■ ■ To learn how to load a driver, connect to a database, execute state-
ments, and process result sets using JDBC (§34.4).
■ ■ To use prepared statements to execute precompiled SQL statements
■ ■ To use callable statements to execute stored SQL procedures and func-
tions (§34.6).
■ ■ To explore database metadata using the DatabaseMetaData and
ResultSetMetaData interfaces (§34.7).
Java Database
Programming
CHAPTER
34-2 Chapter 34 Java Database Programming
34.1 Introduction
Java provides the API for developing database applications that works with any
relational database systems.
You may have heard a lot about database systems. Database systems are everywhere.
Your social security information is stored in a database by the government. If you shop
online, your purchase information is stored in a database by the company. If you attend a
university, your academic information is stored in a database by the university. Database systems
not only store data, they also provide means of accessing, updating, manipulating, and ana-
lyzing data. Your social security information is updated periodically, and you can register for
courses online. Database systems play an important role in society and in commerce.
This chapter introduces database systems, the SQL language, and how database applica-
tions can be developed using Java. If you already know SQL, you can skip Sections 34.
and 34.3.
34.2 Relational Database Systems
SQL is the standard database language for defining and accessing databases.
A database system consists of a database, the software that stores and manages data in the
database, and the application programs that present data and enable the user to interact with
the database system, as shown in Figure 34.1.
Point
Key
Point database system Key
Figure 34.1 A database system consists of data, database management software, and appli-
cation programs.
database
Application Users
Application Programs
Database Management System (DBMS)
System Users
A database is a repository of data that form information. When you purchase a database
system—such as MySQL, Oracle, IBM’s DB2 and Informix, Microsoft SQL Server, or
Sybase—from a software vendor, you actually purchase the software comprising a database
management system (DBMS). Database management systems are designed for use by profes-
sional programmers and are not suitable for ordinary customers. Application programs are built
on top of the DBMS for customers to access and update the database. Thus, application pro-
grams can be viewed as the interfaces between the database system and its users. Application
programs may be stand-alone GUI applications or Web applications and may access several
different database systems in the network, as shown in Figure 34.2.
Most of today’s database systems are relational database systems. They are based on the
relational data model, which has three key components: structure, integrity, and language.
DBMS
34-4 Chapter 34 Java Database Programming
34.2.2 Integrity Constraints
An integrity constraint imposes a condition that all the legal values in a table must satisfy.
Figure 34.6 shows an example of some integrity constraints in the Subject and Course
tables.
In general, there are three types of constraints: domain constraints, primary key con-
straints, and foreign key constraints. Domain constraints and primary key constraints are
known as intrarelational constraints , meaning that a constraint involves only one relation.
The foreign key constraint is interrelational , meaning that a constraint involves more than
one relation.
integrity constraint
Figure 34.4 A Student table stores student information.
deptID 444111110 Jacob R Smith 9129219434 1985-04-09 99 Kingston Street 31435 BIOL 444111111 John K Stevenson 9129219434 null 100 Main Street 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. 31411 BIOL
Student Table ssn firstName mi lastName phone birthDate street zipCode
Figure 34.5 An Enrollment table stores student enrollment information.
Enrollment Table
444111110 11111 2004-03-19 A 444111110 11112 2004-03-19 B 444111110 11113 2004-03-19 C 444111111 11111 2004-03-19 D 444111111 11112 2004-03-19 F 444111111 11113 2004-03-19 A 444111112 11114 2004-03-19 B 444111112 11115 2004-03-19 C 444111112 11116 2004-03-19 D 444111113 11111 2004-03-19 A 444111113 11113 2004-03-19 A 444111114 11115 2004-03-19 B 444111115 11115 2004-03-19 F 444111115 11116 2004-03-19 F 444111116 11111 2004-03-19 D 444111117 11111 2004-03-19 D 444111118 11111 2004-03-19 A 444111118 11112 2004-03-19 D 444111118 11113 2004-03-19 B
ssn courseId dateRegistered grade
Figures 34.4 and 34.5. The Course table and the Enrollment table are related through their
common attribute courseId , and the Enrollment table and the Student table are related
through ssn.
34.2 Relational Database Systems 34-
Domain Constraints
Domain constraints specify the permissible values for an attribute. Domains can be specified
using standard data types, such as integers, floating-point numbers, fixed-length strings, and
variant-length strings. The standard data type specifies a broad range of values. Additional con-
straints can be specified to narrow the ranges. For example, you can specify that the
numOfCredits attribute (in the Course table) must be greater than 0 and less than 5. If an
attribute has different values for each tuple in a relation, you can specify the attribute to be unique.
You can also specify whether an attribute can be null , which is a special value in a database
meaning unknown or not applicable. As shown in the Student table, birthDate may be null.
Primary Key Constraints
A primary key is a set of attributes that uniquely identifyies the tuples in a relations. Why is it
called a primary key, rather than simply key? To understand this, it is helpful to know superkeys,
keys, and candidate keys. A superkey is an attribute or a set of attributes that uniquely identifies
the relation. That is, no two tuples have the same values on a superkey. By definition, a relation
consists of a set of distinct tuples. The set of all attributes in the relation forms a superkey.
A key K is a minimal superkey, meaning that any proper subset of K is not a superkey. A
relation can have several keys. In this case, each of the keys is called a candidate key. The
primary key is one of the candidate keys designated by the database designer. The primary key
is often used to identify tuples in a relation. As shown in Figure 34.6, courseId is the primary
key in the Course table, and ssn and courseId form a primary key in the Enrollment table.
Foreign Key Constraints
In a relational database , data are related. Tuples in a relation are related, and tuples in different
relations are related through their common attributes. Informally speaking, the common attrib-
utes are foreign keys. The foreign key constraints define the relationships among relations.
Formally, a set of attributes FK is a foreign key in a relation R that references relation T if
it satisfies the following two rules:
■ ■ The attributes in FK have the same domain as the primary key in T.
■ ■ A nonnull value on FK in R must match a primary key value in T.
domain constraint
superkey
primary key
candidate key
relational database
foreign key constraint foreign key
Figure 34.6 The Enrollment table and the Course table have integrity constraints.
11111 CSCI 1301 Introduction to Java I 4 11112 CSCI 1302 Introduction to Java II 3 11113 CSCI 3720 Database Systems 3 ...
444111110 11111 2004-03-19 A 444111110 11112 2004-03-19 B 444111110 11113 2004-03-19 C ...
Course Table
Each value in the numOfCredits column must be greater than 0 and less than 5
Each value in courseId in the Enrollment table must match a value in courseId in the Course table
Each row must have a value for courseId, and the value must be unique
Enrollment Table ssn courseId dateRegistered grade
courseId subjectId courseNumber title numOfCredits
34.3 SQL 34-
34.3.1 Creating a User Account on MySQL
Assume you have installed MySQL 5 with the default configuration. To match all the examples
in this book, you should create a user named scott with the password tiger. You can perform
the administrative tasks using the MySQL Workbench or using the command line. MySQL
Workbench is a GUI tool for managing MySQL databases. Here are the steps to create a user
from the command line:
1. From the DOS command prompt, type
mysql –uroot -p
You will be prompted to enter the root password, as shown in Figure 34.7.
2. At the mysql prompt, enter
use mysql;
3. To create user scott with password tiger , enter
create user 'scott'@'localhost' identified by 'tiger';
4. To grant privileges to scott , enter
grant select, insert, update, delete, create, create view, drop, execute, references on . to 'scott'@'localhost';
■ ■ If you want to enable remote access of the account from any IP address, enter
grant all privileges on . to 'scott'@'%' identified by 'tiger';
■ ■ If you want to restrict the account’s remote access to just one particular IP address,
enter
grant all privileges on . to 'scott'@'ipAddress' identified by 'tiger';
5. Enter
exit;
to exit the MySQL console.
Figure 34.7 You can access a MySQL database server from the command window.
34-8 Chapter 34 Java Database Programming
Note On Windows, your MySQL database server starts every time your computer starts. You can stop it by typing the command net stop mysql and restart it by typing the command net start mysql.
By default, the server contains two databases named mysql and test. The mysql database
contains the tables that store information about the server and its users. This database is
intended for the server administrator to use. For example, the administrator can use it to cre-
ate users and grant or revoke user privileges. Since you are the owner of the server installed
on your system, you have full access to the mysql database. However, you should not cre-
ate user tables in the mysql database. You can use the test database to store data or create
new databases. You can also create a new database using the command create data-
base databasename or delete an existing database using the command drop database
databasename.
34.3.2 Creating a Database
To match the examples in this book, you should create a database named javabook. Here are
the steps to create it:
1. From the DOS command prompt, type
mysql –uscott -ptiger
to login to mysql, as shown in Figure 34.8.
2. At the mysql prompt, enter
create database javabook;
stop mysql start mysql
Figure 34.8 You can create databases in MySQL.
For your convenience, the SQL statements for creating and initializing tables used in
this book are provided in Supplement IV.A. You can download the script for MySQL and
save it to script.sql. To execute the script, first switch to the javabook database using the
following command:
use javabook;
then type
source script.sql;
as shown in Figure 34.9.
run script file
34-10 Chapter 34 Java Database Programming
If a table is no longer needed, it can be dropped permanently using the drop table
command. For example, the following statement drops the Course table:
drop table Course;
If a table to be dropped is referenced by other tables, you have to drop the other tables
first. For example, if you have created the tables Course , Student , and Enrollment and
want to drop Course , you have to first drop Enrollment , because Course is referenced by
Enrollment.
Figure 34.10 shows how to enter the create table statement from the MySQL console.
drop table
Figure 34.10 A table is created using the create table statement.
Figure 34.11 (a) You can use Notepad to create a text file for SQL commands. (b) You
can run the SQL commands in a script file from MySQL.
(a) (b)
If you make typing errors, you have to retype the whole command. To avoid retyping, you
can save the command in a file, then run the command from the file. To do so, create a text file
to contain commands, named, for example, test.sql. You can create the text file using any text
editor, such as Notepad, as shown in Figure 34.11a. To comment a line, precede it with two
dashes. You can now run the script file by typing source test.sql from the SQL command
prompt, as shown in Figure 34.11b.
34.3.4 Simple Insert, Update, and Delete
Once a table is created, you can insert data into it. You can also update and delete records. This
section introduces simple insert, update, and delete statements.
The syntax to insert a record into a table is:
insert into tableName [(column1, column2, ..., column)] values (value1, value2, ..., valuen);
34.3 SQL 34-
For example, the following statement inserts a record into the Course table. The new record
has the courseId ‘11113’, subjectId ‘CSCI’, courseNumber ‘3720’, title ‘Database
Systems’, and creditHours 3.
insert into Course (courseId, subjectId, courseNumber, title, numOfCredits) values ( '11113' , 'CSCI' , '3720' , 'Database Systems' , 3 );
The column names are optional. If they are omitted, all the column values for the record must
be entered, even though the columns have default values. String values are case sensitive and
enclosed inside single quotation marks in SQL.
The syntax to update a table is:
update tableName set column1 = newValue1 [, column2 = newValue2, ... ] [ where condition];
For example, the following statement changes the numOfCredits for the course whose title
is Database Systems to 4.
update Course set numOfCredits = 4 where title = 'Database Systems' ;
The syntax to delete records from a table is:
delete from tableName [ where condition];
For example, the following statement deletes the Database Systems course from the Course
table:
delete from Course where title = 'Database Systems' ;
The following statement deletes all the records from the Course table:
delete from Course;
34.3.5 Simple Queries
To retrieve information from tables, use a select statement with the following syntax:
select column-list from table-list [ where condition];
The select clause lists the columns to be selected. The from clause refers to the tables
involved in the query. The optional where clause specifies the conditions for the selected
rows.
Query 1: Select all the students in the CS department, as shown in Figure 34.12.
select firstName, mi, lastName from Student where deptId = 'CS' ;
34.3 SQL 34-
34.3.7 The like , between-and , and is null Operators
SQL has a like operator that can be used for pattern matching. The syntax to check whether
a string s has a pattern p is
s like p or s not like p
You can use the wildcard characters % (percent symbol) and _ (underline symbol) in the
pattern p. % matches zero or more characters, and _ matches any single character in s. For
example, lastName like '_mi%' matches any string whose second and third letters
are m and i. lastName not like '_mi%' excludes any string whose second and third
letters are m and i.
Note In earlier versions of MS Access, the wildcard character is ***** , and the character? matches any single character.
The between-and operator checks whether a value v is between two other values, v1 and
v2 , using the following syntax:
v between v1 and v2 or v not between v1 and v
v between v1 and v2 is equivalent to v >= v1 and v <= v2 , and v not between
v1 and v2 is equivalent to v < v1 or v > v.
The is null operator checks whether a value v is null using the following syntax:
v is null or v is not null
Query 3: Get the Social Security numbers of the students whose grades are between ‘C’
and ‘A’.
select ssn from Enrollment where grade between 'C' and 'A' ;
34.3.8 Column Alias
When a query result is displayed, SQL uses the column names as column headings. Usually the
user gives abbreviated names for the columns, and the columns cannot have spaces when the
table is created. Sometimes it is desirable to give more descriptive names in the result heading.
You can use the column aliases with the following syntax:
select columnName [ as ] alias
Query 4: Get the last name and ZIP code of the students in the CS department. Display the
column headings as “Last Name” for lastName and “Zip Code” for zipCode. The query result
is shown in Figure 34.13.
Figure 34.13 You can use a column alias in the display.
34-14 Chapter 34 Java Database Programming
select lastName as "Last Name" , zipCode as "Zip Code" from Student where deptId = 'CS' ;
Note The as keyword is optional in MySQL and Oracle, but it is required in MS Access.
34.3.9 The Arithmetic Operators
You can use the arithmetic operators * (multiplication), / (division), + (addition), and − (sub-
traction) in SQL.
Query 5: Assume a credit hour is 50 minutes of lectures and get the total minutes for each
course with the subject CSCI. The query result is shown in Figure 34.14.
select title, 50 * numOfCredits as "Lecture Minutes Per Week" from Course where subjectId = 'CSCI' ;
Figure 34.15 (a) The duplicate tuples are displayed. (b) The distinct tuples are displayed.
(a) (b)
Figure 34.14 You can use arithmetic operators in SQL.
34.3.10 Displaying Distinct Tuples
SQL provides the distinct keyword, which can be used to eliminate duplicate tuples in the
result. Figure 34.15a displays all the subject IDs used by the courses, and Figure 34.15b dis-
plays all the distinct subject IDs used by the courses using the following statement:
select distinct subjectId as "Subject ID" from Course;
34-16 Chapter 34 Java Database Programming
select lastName, firstName, deptId from Student where deptId = 'CS' order by lastName desc , firstName asc ;
34.3.12 Joining Tables
Often you need to get information from multiple tables, as demonstrated in the next query.
Query 7: List the courses taken by the student Jacob Smith. To solve this query, you need
to join tables Student and Enrollment , as shown in Figure 34.18.
Figure 34.18 Student and Enrollment are joined on ssn.
A tuple
Student Table
ssn lastName mi firstName …
Enrollment Table
ssn courseId …
Equal
Figure 34.19 Query 7 demonstrates queries involving multiple tables.
You can write the query in SQL as follows:
select distinct lastName, firstName, courseId from Student, Enrollment where Student.ssn = Enrollment.ssn and lastName = 'Smith' and firstName = 'Jacob' ;
The tables Student and Enrollment are listed in the from clause. The query examines
every pair of rows, each made of one item from Student and another from Enrollment and
selects the pairs that satisfy the condition in the where clause. The rows in Student have the
last name, Smith, and the first name, Jacob, and both rows from Student and Enrollment
have the same ssn values. For each pair selected, lastName and firstName from Student
and courseId from Enrollment are used to produce the result, as shown in Figure 34.19.
Student and Enrollment have the same attribute ssn. To distinguish them in a query, use
Student.ssn and Enrollment.ssn.
34.4 JDBC 34-
For more features of SQL, see Supplements IV.H and IV.I.
34.3.1 Create the tables Course , Student , and Enrollment using the create table
statements in Section 34.3.3, Creating and Dropping Tables. Insert rows into the
Course , Student , and Enrollment tables using the data in Figures 34.3–34.5.
34.3.2 List all CSCI courses with at least four credit hours.
34.3.3 List all students whose last names contain the letter e two times.
34.3.4 List all students whose birthdays are null.
34.3.5 List all students who take Math courses.
34.3.6 List the number of courses in each subject.
34.3.7 Assume each credit hour is 50 minutes of lectures. Get the total minutes for the
courses that each student takes.
34.4 JDBC
JDBC is the Java API for accessing relational database.
The Java API for developing Java database applications is called JDBC. JDBC is the trade-
marked name of a Java API that supports Java programs that access relational databases. JDBC
is not an acronym, but it is often thought to stand for Java Database Connectivity.
JDBC provides Java programmers with a uniform interface for accessing and manipulat-
ing relational databases. Using the JDBC API, applications written in the Java programming
language can execute SQL statements, retrieve results, present data in a user-friendly interface,
and propagate changes back to the database. The JDBC API can also be used to interact with
multiple data sources in a distributed, heterogeneous environment.
The relationships among Java programs, JDBC API, JDBC drivers, and relational databases
are shown in Figure 34.20. The JDBC API is a set of Java interfaces and classes used to write
Java programs for accessing and manipulating relational databases. Since a JDBC driver serves
as the interface to facilitate communications between JDBC and a proprietary database, JDBC
drivers are database specific and are normally provided by the database vendors. You need
Point
Check
Point
Key
Figure 34.20 Java programs access and manipulate databases through JDBC drivers.
Java Programs
JDBC API
DB2 JDBC Driver
Local or remote ORACLE DB
Local or remote DB2 DB
MySQL JDBC Driver
Local or remote MySQL DB
Oracle JDBC Driver
34.4 JDBC 34-
Java DB has two versions: embedded and networked. Embedded version is used when you
access Java DB locally, while the network version enables you to access Java DB on the net-
work. To use these drivers, you have to add their jar files in the classpath using the following
DOS command on Windows:
*set classpath=%classpath%;c:\book\lib* mysql-connector-java-5.1.26. jar;c:\book\lib\ojdbc6.jar;c:\program files\jdk1.8.0\db\lib\derby. jar
If you use an IDE such as Eclipse or NetBeans, you need to add these jar files into the library
in the IDE.
Note com.mysql.jdbc.Driver is a class in mysql-connector-java-5.1.26.jar , and oracle.jdbc.driver.OracleDriver is a class in ojdbc6.jar. mysql- connector-java-5.1.26.jar , ojdbc6.jar , and derby.jar contains many classes to support the driver. These classes are used by JDBC but not directly by JDBC programmers. When you use a class explicitly in the program, it is automatically loaded by the JVM. The driver classes, however, are not used explicitly in the program, so you have to write the code to tell the JVM to load them.
Note Java supports automatic driver discovery, so you don’t have to load the driver explicitly. At the time of this writing, however, this feature is not supported for all database drivers. To be safe, load the driver explicitly.
2. Establishing connections.
To connect to a database, use the static method getConnection(databaseURL) in the
DriverManager class, as follows:
Connection connection = DriverManager.getConnection(databaseURL);
where databaseURL is the unique identifier of the database on the Internet. Table 34.4 lists
the URL patterns for the MySQL, Oracle, and Java DB.
why load a driver?
automatic driver discovery
Database Driver Class Source
MySQL com.mysql.jdbc.Driver mysql-connector-java-5.1.26.jar
Oracle oracle.jdbc.driver.OracleDriver ojdbc6.jar
Java DB (embedded) org.apache.derby.jdbc.EmbeddedDriver derby.jar
Java DB (network) org.apache.derby.jdbc.ClientDriver derbynet.jar
Table 34.3 JDBC Drivers
Database URL Pattern MySQL jdbc:mysql://hostname/dbname
Oracle jdbc:oracle:thin:@hostname:port#:oracleDBSID
Java DB (embedded) jdbc:derby:dbname
Java DB (network) jdbc:derby://hostname/dbname
Table 34.4 JDBC URLs
34-20 Chapter 34 Java Database Programming
The databaseURL for a MySQL database specifies the host name and database name to locate
a database. For example, the following statement creates a Connection object for the local
MySQL database javabook with username scott and password tiger :
Connection connection = DriverManager.getConnection ( "jdbc:mysql://localhost/javabook" , "scott" , "tiger" );
Recall that by default, MySQL contains two databases named mysql and test. Section 34.3.2,
Creating a Database, created a custom database named javabook. We will use javabook in
the examples.
The databaseURL for an Oracle database specifies the hostname , the port# where the
database listens for incoming connection requests, and the oracleDBSID database name to
locate a database. For example, the following statement creates a Connection object for the
Oracle database on liang.armstrong.edu with the username scott and password tiger :
Connection connection = DriverManager.getConnection ( "jdbc:oracle:thin:@liang.armstrong.edu:1521:orcl" , "scott" , "tiger" );
3. Creating statements.
If a Connection object can be envisioned as a cable linking your program to a database, an
object of Statement can be viewed as a cart that delivers SQL statements for execution by
the database and brings the result back to the program. Once a Connection object is created,
you can create statements for executing SQL statements as follows:
Statement statement = connection.createStatement();
4. Executing statements.
SQL data definition language (DDL) and update statements can be executed using
executeUpdate(String sql) , and an SQL query statement can be executed using
executeQuery(String sql). The result of the query is returned in ResultSet. For
example, the following code executes the SQL statement create table Temp (col
char(5), col2 char(5)) :
statement.executeUpdate ( "create table Temp (col1 char(5), col2 char(5))" );
This next code executes the SQL query select firstName, mi, lastName from Student
where lastName = 'Smith' :
// Select the columns from the Student table ResultSet resultSet = statement.executeQuery ( "select firstName, mi, lastName from Student where lastName "
5. Processing ResultSet.
The ResultSet maintains a table whose current row can be retrieved. The initial row position
is null. You can use the next method to move to the next row and the various getter methods
to retrieve values from a current row. For example, the following code displays all the results
from the preceding SQL query:
// Iterate through the result and print the student names while (resultSet.next()) System.out.println(resultSet.getString( 1 ) + " " + resultSet.getString( 2 ) + " " + resultSet.getString( 3 ));
connect MySQL DB
connect Oracle DB
