Transcript Advanced JDBC lecture

Object-Oriented Enterprise
Application Development
Advanced JDBC
Topics
During this class we will examine:
Statement batching
DataSources
Connection pooling
Distributed transactions
RowSets
JDBC Extensions
JDBC 2.0
JDBC 2.0 provides some standard
extensions to the JDBC 1.0 API.
This includes some new interfaces as well
as some changes to existing ones.
JDBC 2.0 Goal
The goal of JDBC 2.0 are to provide
additional capabilities within the JDBC API
to support advanced database functionality.
JDBC 2.0 Features
Some of the new features of JDBC 2.0
include:
Scrollable result sets
Updateable result sets
Batch updates
New SQL3-compatible data types.
JDBC 2.0 & Java
Required Packages
JDBC 2.0 includes a new javax.sql
package as well as some changes to the
original java.sql package.
This new packages supports capabilities
such as database connection pooling and
distributed transactions.
Common Interfaces
We won't use all of the interfaces provided
by JDBC 2.0 in this class. We'll focus on the
most critical.
We’ll also revisit some of the interfaces
from JDBC 1.0.
Result Sets
Revisited
Enhancements
The ResultSet has been enhanced to
make them scrollable and updateable.
This allows them to compete more
effectively with other technologies such as
the Visual Basic and PowerBuilder.
Vendors aren’t required to implement
scrollable and updateable ResultSets to
be JDBC 2.0 compliant.
Scrollability
A ResultSet can now have one (1) of
four (3) possible scrolling attributes:
Forward and backward
Scroll insensitive
Scroll sensitive
Scroll-Sensitive
A ResultSet that’s scroll-sensitive is
“live.”
As other users modify the data reflected in
the ResultSet, the ResultSet can be
changed to reflect the revised view of the
data.
Scroll-Insensitive
A ResultSet that’s scroll-insensitive is a
static view of the data.
If the data in the ResultSet is changed
by other clients, the ResultSet isn’t
updated accordingly.
Creating Scrollable ResultSets
(1 of 3)
To create a scrollable ResultSet we use
the overloaded createStatement()
and prepareStatement() methods:
Statement createStatement(
int resultSetType,
int resultSetConcurrency)
throws SQLException
PreparedStatement prepareStatement(
String SQL,
int resultSetType,
int resultSetConcurrency)
throws SQLException
Creating Scrollable ResultSets
(2 of 3)
Legal ResultSet types include:
TYPE_FORWARD_ONLY
TYPE_SCROLL_INSENSITIVE
TYPE_SCROLL_SENSITIVE
These values are defined in the
ResultSet interface.
Creating Scrollable ResultSets
(3 of 3)
Legal ResultSet concurrency
include:
CONCUR_READ_ONLY
CONCUR_UPDATEABLE
These values are defined in the
ResultSet interface.
Sample Code – Query
(1 of 1)
1. Class.forName(
"sun.jdbc.odbc.JdbcOdbcDriver");
2. conn = DriverManager.getConnection(
"jdbc:odbc:se452", "", "" );
3. Statement stmt =
conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
4. ResultSet rs =
stmt.executeQuery( SQL );
Sample Code – Prepared Query
(1 of 1)
1. Class.forName(
"sun.jdbc.odbc.JdbcOdbcDriver");
2. conn = DriverManager.getConnection(
"jdbc:odbc:se452", "", "" );
3. PreparedStatement stmt =
conn.preparedStatement(
SQL,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
4. ResultSet rs =
stmt.executeQuery( );
Absolute Positioning
A ResultSet supports absolute
positioning using the following methods:
boolean absolute(int row)
boolean first()
boolean last()
void afterLast()
void beforeFirst()
Relative Positioning
A ResultSet supports relative
positioning using the following methods:
boolean relative(int row)
boolean next()
boolean previous()
Testing Position
A ResultSet allows you to test for
various positions:
boolean isAfterLast()
boolean isBeforeFirst()
boolean isFirst()
boolean isLast()
int getRow()
Updateability
(1 of 2)
A ResultSet can be updated.
Changes made to the ResultSet object
will be reflected in the underlying data
store.
To be updateable, the ResultSet must
have a concurrency type of
CONCUR_UPDATEABLE.
Updateability
(2 of 2)
Only queries that meet the following
restrictions can have an updateable
ResultSet:
The query references a single table.
The query returns the primary key.
The query returns all non-null columns that do
not also have a default value.
Updating Rows
(1 of 3)
There are methods provided for updating
each particular type of column.
Examples include:
void updateString(int col,String val)
throws SQLException
void updateString(String col,String val)
throws SQLException
Updating Rows
(2 of 3)
Although the data in the row has been
changed, those changes haven’t yet been
pushed out to the database.
To save the changes to the underlying data
store, use the updateRow() method.
void updateRow()
throws SQLException
Updating Rows
(3 of 3)
If any of the positioning methods are
invoked on the ResultSet prior to the
call to the updateRow() method, then all
changes made to that row will be discarded.
Deleting Rows
To delete the current row from the
underlying data store, use the
deleteRow() method.
void deleteRow()
throws SQLException
Inserting Rows
(1 of 5)
Inserting a new row consists of four (4)
steps:
Call the moveToInsertRow() method
Update the columns within the new row
Call the insertRow() method
Returning to the original row in the
ResultSet
Inserting Rows
(2 of 5)
First call the the moveToInsertRow()
method.
This creates a new “buffer” area within the
ResultSet for the new row.
It isn’t legal to call the updateRow() or
deleteRow() method while the cursor is
positioned on the insert row.
Inserting Rows
(3 of 5)
Next use the same update() methods
used to update the ResultSet.
This sets the various data members within the
new row.
If you navigate off of this new row, your
changes will be discarded.
Inserting Rows
(4 of 5)
Next call the insertRow() method.
This performs the physical insertion of the row
into the underlying data store.
Any values that haven’t been set on
columns in the ResultSet are assumed to
be null and will be interpreted that way by
the data store.
Inserting Rows
(5 of 5)
Finally call the moveToCurrentRow()
method.
This returns you to the row on which you were
positioned prior to beginning the insertion
process.
Batch Updates
Features
(1 of 2)
A batch update allows multiple statements
to be executed against the database at one
time.
These statements are not executed as part of
the same transaction unless
setAutoCommit() has been invoked
with a value of false.
Features
(2 of 2)
Batch updates are not meant for SQL
select operations.
To construct a batch, use the addBatch()
methods:
void addBatch(String SQL)
throws SQLException
void addBatch()
throws SQLException
Sample Code – Batch
(1 of 2)
1.
import java.sql.*;
2.
3.
public class Batch {
public static
void main(String [] args) {
Class.forName("JData2_0.sql.$Driver");
Connection conn =
DriverManager.getConnection(
"jdbc:JDataConnect://localhost/se452");
Statement stmt =
conn.createStatement();
4.
5.
6.
Sample Code – Batch
(2 of 2)
7.
8.
stmt.addBatch(
"insert into STATE values ('SK','Ski')");
stmt.addBatch(
"insert into STATE values ('FD','Fre')");
9.
int [] rows = stmt.executeBatch();
10.
11.
System.out.println("1st: " + rows[0]);
System.out.println("2nd: " + rows[1]);
12.
stmt.close();
13.
conn.close();
14.
}
15. }
Exceptions
Each query within the batch might result in
an exception.
Rather than throw a SQLException, the
executeBatch() method can throw the
BatchUpdateException.
The getUpdateCounts() method on
this exception tells you how many rows
were updated.
Database Connectivity
Revisited
DataSources
Connection allocation in JDBC 2.0 is
usually implemented using the
DataSource interface.
The approach is to place the DataSource
reference into a naming service.
The client application then retrieves this
reference and uses it to create connections.
Sample Code – DataSource
(1 of 1)
1.
2.
3.
4.
5.
import java.sql.*;
import javax.sql.*;
import com.NetDirect.Jdbc.*;
public class StateDataSource {
public static void main(String [] args)
throws Exception {
6.
JDataSource jds = new JDataSource();
7.
jds.setURL(
"jdbc:JDataConnect://localhost/se452");
8.
DataSource ds = jds;
9.
Connection conn = ds.getConnection();
10.
// do database stuff….
11.
}
12. }
Database Connectivity
Opening a database connection is an
expensive process in terms of machine
resources and wall-clock time.
As a general rule we open as few of these
connections as possible.
With JDBC 2.0 we can create a connection
pool that allows us to reuse connections.
Connection Pool API
The javax.sql package defines several
new interfaces to support the use of
connection pools:
ConnectionPoolDataSource
PoolableConnection
ConnectionEventListener
The use of these interfaces is vendor
specific.
Connection Pooling
(1 of 2)
Provides a cache, or pool, of prefabricated
database connections.
When a client needs a database connection,
it goes to the pool.
When the client is done with the
connection, that connection is returned to
the pool.
Connection Pooling
(2 of 2)
Pools increase performance by avoiding the
cost of creating new connections for each
client.
Pools decrease resource usage by using a
small number of connections to service a
large number of clients.
Transactions Revisited
Defined
A distributed transaction is one that spans
two (2) or more DataSource references.
Such transactions make use of middle-tier
software called a transaction manager.
This manager takes over the responsibility for
transaction demarcation. This makes it illegal
for a developer to provide their own transaction
support.
Distributed Transaction API
The javax.sql package defines several
interfaces to support distributed
transactions:
XADataSource
XAConnection
DataSource
The use of these interfaces is vendor
specific.
Participants
(1 of 3)
A resource is some participant that has
transactional state.
Examples include:
Database connections
JMS message queue connections
Our application objects typically don't have
transactional state unless we implement it
programmatically.
Participants
(2 of 3)
A resource manager is some participant
that controls and manages some resource.
Examples include:
DriverManager references
DataSource references
The resource manager will ultimately be
responsible for instructing a given resource
to perform its transactional processing.
Participants
(3 of 3)
A transaction manager is some participant
that controls and manages a transaction
across multiple resource managers.
By interacting with the transaction manager
we can control the scope and duration of a
distributed transaction.
JTA and JTS
When building a transaction service, a
vendor may choose to provide
implementations of:
JTA – Java Transaction API
JTS – Java Transaction Service
RowSets
RowSet API
The javax.sql package defines several
interfaces to support RowSets:
RowSet
RowSetInternal
RowSetListener
RowSetMetaData
RowSetReader
RowSetWriter
Features
(1 of 2)
A RowSet is an extension of a
ResultSet that provides data in
accordance with the JavaBeans API.
This class won't provide an in-depth
examination of JavaBeans.
We'll see them again briefly in our discussions
of JavaServer Pages.
Features
(2 of 2)
A RowSet can be registered with a
RowSetListener object.
Such a listener can report on changes to the
RowSet such as:
Database cursor movement
Single-row changes
RowSet changes
Sample Code – RowSet
(1 of 2)
1.
2.
3.
import java.sql.*;
import javax.sql.*;
import com.NetDirect.Jdbc.*;
4.
5.
public class StateRowSet {
public static void main(String [] args)
throws Exception {
JDBCRowset jrs = new JDBCRowset();
jrs.setCommand("SELECT * FROM STATE");
jrs.setUrl(
"jdbc:JDataConnect://localhost/se452");
6.
7.
8.
9.
jrs.execute();
Sample Code – RowSet
(2 of 2)
10.
11.
12.
13.
14.
15.
}
16. }
while ( jrs.next() ) {
System.out.println("STATE_I: " +
jrs.getString("STATE_I"));
System.out.println("STATE_M: " +
jrs.getString("STATE_M"));
}
jrs.close();
Review
During this class we have discussed:
Statement batching
DataSources
Connection pooling
Distributed transactions
RowSets
Resources
JDBC Database Access with Java
Graham Hamilton, Rick Cattell, Maydene
Fisher, Addison-Wesley, 1997.
ISBN: 0-201-30995-5
Database Programming with JDBC and
Java, 2nd Edition
George Reese, O’Reilly, Inc., 2000.
ISBN: 1-56592-616-1
Coming Attractions
Next week we'll begin looking at Servlets.
Please read Chapters 1-2 in your text.