Tài liệu Java Database Programming Bible- P4 doc

Chapter 4:Introduction to JDBC

-149-

2. Set a new value for each column in the row by using the appropriate update method.

3. Call the method insertRow() to insert the new row into the result set and, simultaneously, into the

database.

Listing 4-9 demonstrates the use of the UpdatableResultSet to insert a new row into a

database.

Listing 4-9: Using UpdatableResultSet to insert a new row

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con = DriverManager.getConnection ("jdbc:odbc:Contacts");

Statement stmt = con.createStatement(

ResultSet.TYPE_SCROLL_INSENSITIVE,

ResultSet.CONCUR_UPDATABLE);

ResultSet rs = stmt.executeQuery(query);

rs.moveToInsertRow();

rs.updateInt("Contact_ID", 150);

rs.updateString("First_Name", "Nigel");

rs.updateString("Last_Name", "Thornebury");

rs.insertRow();

If you insert a row without supplying a value for every column in the row, the default

value for the column will be used if there is one. Otherwise, if the column accepts

SQL NULL values, a NULL will be inserted. Failing either of those, a SQLException

will be thrown.

You will also get a SQLException if a required table column is missing in the

ResultSet you use to insert the row, so the query used to get the ResultSet object

should generally select all columns, though you will probably want to use a WHERE

clause to limit the number of rows returned by your SELECT statement.

Caution If you move the cursor from the insert row before calling the method

insertRow(), you will lose all of the values you have added to the insert

row.

To move the cursor from the insert row back to the result set, you can use any of the

methods that put the cursor on a specific row: first, last, beforeFirst, afterLast, and

absolute. You can also use the methods previous and relative because the result set

maintains a record of the current row while accessing the insert row.

In addition, you can use a special method: moveToCurrentRow(), which can be called

only when the cursor is on the insert row. This method moves the cursor from the

insert row back to the row that was previously the current row.

TEAMFLY

Team-Fly®

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Chapter 4:Introduction to JDBC

-150-

Deleting a Row

Deleting a row in an UpdatableResultSet is very simple. All you have to do is move

the cursor to the row you want to delete and call the method deleteRow().

The example in the following code snippet shows how to delete the third row in a

result set by getting the ResultSet object, moving the cursor to the third row, and

using the deleteRow() method:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con = DriverManager.getConnection ("jdbc:odbc:Contacts");

Statement stmt = con.createStatement(

ResultSet.TYPE_SCROLL_INSENSITIVE,

ResultSet.CONCUR_UPDATABLE);

ResultSet rs = stmt.executeQuery(query);

rs.absolute(3);

rs.deleteRow();

Caution Be aware that different JDBC drivers handle deletions in different ways.

Some remove a deleted row so that it is no longer visible in a result set,

and others insert a blank row where the deleted row used to be.

When you make a change to a ResultSet, the change may not necessarily be visible.

The next section explains the reasons.

Seeing Changes in ResultSets

Changes made to a ResultSet are not necessarily visible, either to the ResultSet itself

or to other open transactions. In this context, the terms visible and not visible have

the following meanings:

ß An update is visible if the updated value can be retrieved by calling the appropriate getter method

after making an update.

ß An update is not visible if the getter method still returns the initial column value.

Similarly, an inserted row is visible if it appears in the ResultSet after calling

insertRow(). Deletions are visible if deleted rows are either removed from the result

set or if deleted rows leave a hole in the result set.

There are a number of factors affecting the visibility of changes, including the

following:

ß JDBC driver implementation

ß Transaction isolation level in effect

ß Result-set type

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Chapter 4:Introduction to JDBC

-151 -

An application can determine if the changes a result set makes are visible to the

result set itself by calling these DatabaseMetaData methods:

ß ownUpdatesAreVisible(int ResultSet.TYPE_XXX)

ß ownDeletesAreVisible(int ResultSet.TYPE_XXX)

ß ownInsertsAreVisible(int ResultSet.TYPE_XXX)

The DatabaseMetaData interface also provides the following methods that allow an

application to determine whether a JDBC driver can detect changes for a particular

result-set type:

ß insertsAreDetected(ResultSet.TYPE_XXX)

ß deletesAreDetected(ResultSet.TYPE_XXX)

ß updatesAreDetected(ResultSet.TYPE_XXX)

If these methods return true, the following methods can be used to detect changes to

a ResultSet:

ß wasInserted()

ß wasDeleted()

ß wasUpdated()

Remember that if you modify data in a ResultSet object, the change will always be

visible if you close the ResultSet and reopen it by executing the same query again

after the changes have been made.

Another way to get the most recent data is to use the method refreshRow(), which

gets the latest values for a row straight from the database. This is done by positioning

the cursor to the desired row and calling refreshRow(), as shown here:

rs.absolute(3);

rs.refreshRow();

Note The result set should be TYPE_SCROLL_SENSITIVE; if you use the

method refreshRow() with a ResultSet object that is

TYPE_SCROLL_INSENSITIVE, refreshRow() does nothing.

Another way to get data from a database is to use a RowSet object. RowSets add

JavaBeans support to the functionality of the ResultSet, as explained in the next

section.

RowSets

A RowSet is an object that contains a set of rows from a result set or some other

source of tabular data, like a fi le or spreadsheet. RowSet is an extension of ResultSet,

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Chapter 4:Introduction to JDBC

-152 -

with the added feature that it adds JavaBeans support to the JDBC API. Similarly, the

RowSetMetaData interface extends the ResultSetMetaData interface.

Being JavaBeans, RowSets follow the JavaBeans model for setting and getting

properties and for event notification, so they are easy to combine with other

components in an application.

RowSets make it easy to send tabular data over a network. They can also be used as

a wrapper, providing scrollable result sets or updatable result sets when the

underlying JDBC driver does not support them.

There are two main types of RowSets: connected and disconnected.

ß A connected RowSet, like a ResultSet, maintains a connection to a data source for as long as the

RowSet is in use.

ß A disconnected RowSet gets a connection to a data source to load data or to propagate changes

back to the data source, but most of the time it does not have a connection open.

While it is disconnected, a RowSet does not need a JDBC driver or the full JDBC API,

so its footprint is very small.

Because it is not continually connected to its data source, a disconnected RowSet

stores its data in memory. It maintains MetaData about the columns it contains and

information about its internal state. It also includes methods for making connections,

executing commands, and reading and writing data to and from the data source.

Implementations of RowSets include the following:

ß JDBCRowSet — A connected RowSet that serves mainly as a thin wrapper around a ResultSet

object to make a JDBC driver look like a JavaBeans component

ß CachedRowSet — A disconnected RowSet that caches its data in memory

ß WebRowSet — A connected RowSet that uses the HTTP protocol internally to talk to a Java

servlet that provides data access

Creating a Rowset and Setting Properties

Since RowSets are JavaBeans, they contain setter and getter methods for retrieving

and setting properties.

These methods include the following:

ß setCommand — The SQL command to be executed

ß setConcurrency — Read only or updatable

ß setType — Scrollable or foward only

ß setDataSourceName — Used with DataSource access

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Chapter 4:Introduction to JDBC

-153 -

ß setUrl — used with DriverManager access

ß setUsername

ß setPassword

ß setTransactionIsolation

You need only set those properties that are needed for your particular use of a

RowSet.

The following lines of code make the CachedRowSet object crset scrollable and

updatable.

CachedRowSet crset = new CachedRowSet();

crset.setType(ResultSet.TYPE_SCROLL_INSENSITIVE);

crset.setConcurrency(ResultSet.CONCUR_UPDATABLE);

crset.setCommand("SELECT * FROM Customers");

crset.setDataSourceName("jdbc/customers");

crset.setUsername("myName");

crset.setPassword("myPwd");

crset.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

crset.addRowSetListener(listener);

If the DriverManager were being used to make a connection, you would set the

properties for a JDBC URL, a user name, and a password. The preferred means of

getting a connection is to use a DataSource object with the owner's user name and

the owner's password.

Now that the CachedRowSet has been created and initialized, all that remains is to

call the execute() method; the RowSet uses the information in its properties to make a

connection and execute the query. The data in the RowSet can then be accessed

and updated.

Rowset Events

A RowSetEvent is generated when something important happens in a RowSet, such

as a change in a column value. Being JavaBeans, RowSets can use the Java event

model to notify listeners when the RowSet is changed.

These are the RowSetListener methods:

ß rowChanged (Called when the RowSet is changed)

ß rowSetChanged(Called when a RowSet is inserted, updated, or deleted)

ß cursorMoved (Called when a RowSet's cursor is moved)))

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.