Transcript Chapter 9 - kuroski.net

Using Relational Databases
Chapter 9
Java Programming: Advanced Topics
1
Objectives
• Discover what Java DataBase Connectivity
means and what composes the JDBC API
• Learn some best practices for programming
relational databases
• See what Java types map to SQL types or
encapsulate database concepts
• Learn how to connect to relational databases
through JDBC drivers
Java Programming: Advanced Topics
2
Objectives (Cont.)
• Create and execute SQL statements using the
java.sql package
• Perform advanced operations such as batch
updates and updatable result sets
• Program for transactional integrity
• Consider the advantages of data sources,
connection pooling, and distributed transactions
as supported by the javax.sql package
Java Programming: Advanced Topics
3
Best Practices for Programming
for Databases
• Extend the design pattern of model-view
separation
• Design for model-persistence separation
• Define a mapping between Java objects and
data elements
• Use the power of the Database Manager
• Design your application in terms of transactions
• Use Enterprise JavaBeans
Java Programming: Advanced Topics
4
View-Model-Persistence
Separation
Java Programming: Advanced Topics
5
Mapping between Java Objects
and Data Elements
• The schema is the design of the database—its
tables, columns, and relationships between
them
• Entity relationships are foreign-key
relationships between tables
• A table can map directly onto a class or
JavaBean
Java Programming: Advanced Topics
6
Database Schema and ObjectOriented Concepts
Java Programming: Advanced Topics
7
Database Schema and ObjectOriented Concepts (Cont.)
Java Programming: Advanced Topics
8
Sample Database Schema
Java Programming: Advanced Topics
9
Leverage the Power of the
Database Manager
• The primary key or identifier for a row must
be unique
• Use foreign key constraints to signal an error
if a program tries to insert a row in a
dependent table for which there is no row in
the parent table
• Set rules of referential integrity
Java Programming: Advanced Topics
10
Design Your Application in Terms
of Transactions
• Transaction: a logical unit of work containing all
changes to persistent data during the process
of completing a task (and all that must be
undone if the task fails at any point during
processing)
• To commit is to send an instruction to the
database to confirm updates and make them
permanent
• To roll back is to send an instruction to the
database to cancel updates and revert to the
previous data
Java Programming: Advanced Topics
11
Design Your Application in Terms
of Transactions (Cont.)
• The goal is to create components that
implement transactions with the following
ACID properties:
– Atomic
– Consistent
– Isolated
– Durable
Java Programming: Advanced Topics
12
JDBC Drivers for RDBM Systems
• Java Database Connectivity (JDBC) driver: a
set of classes that provide the bridge between a
Java program running on the Java platform and
an RDBM system that is usually running on a
native operating system
• JDBC is composed of two packages:
– java.sql
– javax.sql
Java Programming: Advanced Topics
13
SQL to Java Type Mapping
Java Programming: Advanced Topics
14
SQL to Java Type Mapping (Cont.)
Java Programming: Advanced Topics
15
Understanding the Database Used
in This Chapter
• The SKICLUB database contains three
tables:
Java Programming: Advanced Topics
16
Using the java.sql API
•
•
The package java.sql contains JDBC
classes
The SQL package also defines exception
types:
–
–
–
–
BatchUpdateException
DataTruncation
SQLException
SQLWarning
Java Programming: Advanced Topics
17
java.sql Package
Java Programming: Advanced Topics
18
java.sql Package (Cont.)
Java Programming: Advanced Topics
19
Creating and Executing SQL
Statements
1.
2.
3.
4.
5.
6.
7.
Get a connection to the database by calling the
DriverManager
Get a Statement object from the Connection object
acquired in Step 1
Build the SQL statement in a string
Call a method for the Statement object created in Step
2, passing the SQL command as its argument
Receive results as the return value of the method of
the Statement interface
Process the results
Implicitly or explicitly close the Statement object
Java Programming: Advanced Topics
20
Establishing a Database
Connection
• The Connection interface defines the behavior of
the context within which you issue SQL
statements and receive results
• The DriverManager class is a service class that
manages JDBC 1 drivers
• The getConnection method establishes a
connection to the database at the given URL
Java Programming: Advanced Topics
21
Issuing Dynamic SQL Statements
• The Statement interface encapsulates the
behavior of dynamic SQL statements
• Use Statement objects to execute Dynamic
SQL queries and obtain results
• Call the Connection.createStatement method to
create a Statement object
Java Programming: Advanced Topics
22
Processing a ResultSet
• Objects of type ResultSet encapsulate the
data retrieved from the database
• A ResultSet is a collection of rows
• The ResultSet interface defines methods
to get and update column values
• All ResultSet objects have an associated
cursor object that points to one row at a
time, known as the current row
Java Programming: Advanced Topics
23
Code that Uses a Scrollable
ResultSet
Java Programming: Advanced Topics
24
Using Precompiled SQL
• Some or all of the literal values of the
precompiled SQL statement are replaced by
question marks when the statement is compiled
• Use the class PreparedStatement for
precompiled SQL
• Call the Connection.prepareStatement method
to create a PreparedStatement object
Java Programming: Advanced Topics
25
Using Stored Procedures
• Use CallableStatement objects to execute stored
procedures and obtain results
• Call the Connection.prepareCall method to
create a CallableStatement object
• The argument of Connection.prepareCall is a
string holding a parameterized SQL statement in
which question marks represent parameters
Java Programming: Advanced Topics
26
Updating the Database Using a
ResultSet
• To update an existing row, follow these steps:
– Make sure the cursor is positioned at the row to be
changed
– Call one of the updateXxx methods to update each
column that you want to change
– Call updateRow to write the change to the
database
Java Programming: Advanced Topics
27
Coding Transactions
• The Connection class controls the transactional
properties of database operations
• By default, connections are opened in
autoCommit mode
• AutoCommit means that the changes made by
an INSERT, UPDATE, or DELETE statement
immediately become permanent
Java Programming: Advanced Topics
28
Coding Transactions (Cont.)
• To undo a database operation in autoCommit
mode, use the process called one-phase commit
• A two-phase commit is used in distributed
transactions that involve more than one RDBM
system or other enterprise information systems
and is available only when you acquire
connections from DataSource objects
Java Programming: Advanced Topics
29
Connection Methods for
Transactions
Java Programming: Advanced Topics
30
Transaction Isolation Levels
• The isolation level controls the way the database
manager behaves when two or more programs
try to access the same database at the same
time
• The isolation level is typically set when an
application is bound to the database, but can be
changed programmatically by calling
Connection.setTransactionLevel
Java Programming: Advanced Topics
31
Transaction Levels Defined in the
Connection Class
Java Programming: Advanced Topics
32
Using the javax.sql API
• The javax.sql types implement an architecture in
which the application components interact with
application servers
• Quality-of-service features include the following
services:
–
–
–
–
Connection pooling
Transaction management
Security services
Activity tracing and logging error, warning, and
informational messages
Java Programming: Advanced Topics
33
Interfaces Defined in the
javax.sql Package
Java Programming: Advanced Topics
34
Interfaces Defined in the
javax.sql Package (Cont.)
Java Programming: Advanced Topics
35
Classes Defined in the
javax.sql Package
Java Programming: Advanced Topics
36
The DataSource Architecture
• JNDI namespace: a registry in which objects are
stored and retrieved by name
• A program that uses a datasource accesses the
datasource from the JNDI service provided by
an application server
• An object of type DataSource is a factory for
creating connections and contains all the
information that the factory needs to open a
connection (such as the name of the database,
the database user ID, and the password for that
user)
Java Programming: Advanced Topics
37
Using a Datasource with
WebSphere Application Server
• To develop and test code that uses a
datasource with WebSphere Application Server
you can use Application Developer
– Install the datasource on the application server and
bind it to the server’s JNDI service
– Create a Java application that uses the datasource
– Use the datasource to get a java.sql.Connection
object
– Use the connection with the classes and interfaces
defined in the java.sql package
Java Programming: Advanced Topics
38
Connection Pooling
• A connection pool is a set of connections
opened and maintained by an application server
• A JDBC driver that supports connection pooling
must provide a class that implements
javax.sql.ConnectionPoolDataSource
• When a program calls the getConnection
method on a DataSource object, the server
assigns one of the connections in the pool
Java Programming: Advanced Topics
39
Distributed Transactions
• The transactions managed by the RDBM
systems are called local transactions because
they are local to one RDBM system
• A distributed or global transaction is managed
not by one RDBM or resource manager, but by
the application server
• Global transactions are transactions that
conform to JTA or XA architecture and extend
the commit and rollback processes to a
distributed environment
Java Programming: Advanced Topics
40
Summary
• The Java Database Connectivity (JDBC) classes
communicate with the database through the
driver
• JDBC is composed of two packages: java.sql
and javax.sql
• When designing applications that access
databases, use the model-persistence
separation
• Call methods of the java.sql.Statement class to
run dynamic SQL, use the class
PreparedStatement for precompiled SQL, and
the class CallableStatement - for stored
procedures
Java Programming: Advanced Topics
41
Summary (Cont.)
• Update the database by updating the ResultSet
object produced by calling executeQuery
• The java.sql.Connection class supports
transactional processing with methods
setAutoCommit, commit, and rollback
• The javax.sql package adds the ability to work in
an enterprise environment and use the services
of a J2EE-compliant application server
• Local transactions are managed by RDMS, while
distributed or global transactions are managed
by the application server
Java Programming: Advanced Topics
42