jdbc.html

jdbc书

The highest level of driver reimplements the database network API in
the Java language. Type 4 drivers can also be used on thin clients as 
they also have no native code.

<A NAME="connect"></A>
<H3>Database Connections</H3>

A database connection can be established with a call to the
<CODE>DriverManager.getConnection</CODE> method. The call takes a
URL that identifies the database, and optionally, the database
login user name and password. 

<PRE>
  Connection con = DriverManager.getConnection(url);
  Connection con = DriverManager.getConnection(url, 
			"user", "password");
</PRE>

After a connection is established, a statement can be run
against the database. The results of the statement can be retrieved
and the connection closed.

<P>
One useful feature of the <CODE>DriverManager</CODE> class is the
<CODE>setLogStream</CODE> method. You can use this method
to generate tracing information so help you
diagnose connection problems that would normally not be visible. 
To generate tracing information, just call the method like
this:

<PRE>
  DriverManager.setLogStream(System.out);
</PRE>

<P>
The <A HREF="conpool.html">Connection Pooling</A> section in 
Chapter 8 shows you how to improve the throughput of JDBC 
connections by not closing the connection once the statement
completes. Each JDBC connection to a database incurs overhead in 
opening a new socket and using the username and password
to log into the database. Reusing the connections reduces the
overhead. The Connection Pool keeps a list of open connections and
clears any connections that cannot be reused.

<A NAME="statement"></A>
<H3>Statements</H3>

There are three basic types of SQL statements used in the JDBC API: 
<CODE>CallabelStatement</CODE>, <CODE>Statement</CODE>,
and <CODE>PreparedStatement</CODE>.
When a <CODE>Statement</CODE> or <CODE>PreparedStatement</CODE>
is sent to the database, the database driver translates it into
a format the underlying database can recognize. 

<A NAME="call"></A>
<H4> Callable Statements</H4>

Once you have established a connection to a database, you
can use the <CODE>Connection.prepareCall</CODE> method
to create a callable statement. A callable statement
lets you execute SQL stored procedures.

<P>
This next example creates a
<CODE>CallableStatement</CODE> object with three parameters
for storing account login information.

<PRE>
  CallableStatement cs =
        con.prepareCall("{call accountlogin(?,?,?)}");
  cs.setString(1,theuser);
  cs.setString(2,password);
  cs.registerOutParameter(3,Types.DATE);

  cs.executeQuery();
  Date lastLogin = cs.getDate(3);
</PRE>

<A NAME="ment"></A>
<H4>Statements</H4>

The <CODE>Statement</CODE> interface lets you execute a simple
SQL statement with no parameters.
The SQL instructions are inserted into the <CODE>Statement</CODE>
object when the <CODE>Statement.executeXXX</CODE> method is called.

<P>
<STRONG>Query Statement</STRONG>:
This code segment creates a <CODE>Statement</CODE> object and
calls the <CODE>Statement.executeQuery</CODE> method to
select text from the <CODE>dba</CODE> database. The results
of the query are returned in a <CODE>ResultSet</CODE> object.
How to retrieve results from a <CODE>ResultSet</CODE> object
is explained in <A HREF="#result">Result Sets</A> below.

<PRE>
 Statement stmt = con.createStatement();
 ResultSet results = stmt.executeQuery(
                       "SELECT TEXT FROM dba ");
</PRE>

<P>
<STRONG>Update Statement</STRONG>:
This code segment creates a <CODE>Statement</CODE> object and
calls the <CODE>Statement.executeUpdate</CODE> method to
add an email address to a table in the <CODE>dba</CODE> database.

<PRE>
  String updateString = 
           "INSERT INTO dba VALUES (some text)";
  int count = stmt.executeUpdate(updateString);
</PRE>

<A NAME="prepare"></A>
<H4>Prepared Statements</H4>

<P>
The <CODE>PreparedStatement</CODE> interface descends from the
<CODE>Statement</CODE> interface and uses a template to create a SQL request.
Use a <CODE>PreparedStatement</CODE> to send precompiled SQL statements
with one or more parameters.

<P>
<STRONG>Query PreparedStatement</STRONG>:
You create a <CODE>PreparedStatement</CODE> object by specifying
the template definition and parameter placeholders.
The parameter data is inserted into the <CODE>PreparedStatement</CODE> 
object by calling its <CODE>setXXX</CODE> methods and specifying
the parameter and its data.
The SQL instructions and parameters are sent to the database
when the <CODE>executeXXX</CODE> method is called.

<P>
This code segment creates a <CODE>PreparedStatement</CODE> object
to select user data based on the user's email address. The
question mark ("?") indicates this statement has one parameter.

<PRE>
  PreparedStatement pstmt = con.prepareStatement(
                              select theuser from 
                              registration where 
                              emailaddress like ?");
//Initialize first parameter with email address
  pstmt.setString(1, emailAddress);
  ResultSet results = ps.executeQuery();
</PRE>

<P>
Once the <CODE>PreparedStatement</CODE> template is initialized, only 
the changed values are inserted for each call. 

<PRE>
  pstmt.setString(1, anotherEmailAddress);
</PRE>

<BLOCKQUOTE>
<HR>
<STRONG>Note:</STRONG>
Not all database drivers compile prepared statements.
<HR>
</BLOCKQUOTE>

<P>
<STRONG>Update PreparedStatement</STRONG>:

This code segment creates a <CODE>PreparedStatement</CODE>
object to update a seller's registration record. 
The template has five parameters, which are set
with five calls to the apprpriate 
<CODE>PreparedStatement.setXXX</CODE> methods.

<PRE>
PreparedStatement ps = con.prepareStatement(
	"insert into registration(theuser, password, 
           emailaddress, creditcard, 
           balance) values (
           ?, ?, ?, ?, ?)");
  ps.setString(1, theuser);
  ps.setString(2, password);
  ps.setString(3, emailaddress);
  ps.setString(4, creditcard);
  ps.setDouble(5, balance);
  ps.executeUpdate();
</PRE>

<A NAME="cache"></A>
<H3>Caching Database results</H3>

The <CODE>PreparedStatement</CODE> concept of reusing requests can be
extended to caching the results of a JDBC call. For example,
an auction item description remains the same until the seller changes it.
If the item receives thousands of requests, the results of the
statement: <CODE>query "select description
from auctionitems where item_id='4000343'"</CODE> might be stored
more efficiently in a hash table.

<P>
Storing results in a hash table requires the JDBC call be
intercepted before creating a real statement to return the cached
results, and the cache entry be cleared if there is a corresponding
update to that <CODE>item_id</CODE>.

<A NAME="result"></A>
<H3>Result Sets</H3>

The <CODE>ResultSet</CODE> interface manages access to data returned
from a query. The data returned equals one row in a database table.
Some queries return one row of data while many queries return multiple rows
of data.

<P>
You use <CODE>getType</CODE> methods to retrieve data from specific
columns for each row returned by the query. This example retrieves the 
<CODE>TEXT</CODE> column from all tables with a <CODE>TEXT</CODE> column  
in the <CODE>dba</CODE> database. The <CODE>results.next</CODE> method moves 
to the next retrieved row until all returned rows are processed.

<PRE>
  Statement stmt = con.createStatement();
  ResultSet results = stmt.executeQuery(
                        "SELECT TEXT FROM dba ");
  while(results.next()){
    String s = results.getString("TEXT");
    displayText.append(s + "\n");
  }
  stmt.close();
</PRE>

<A NAME="scroll"></A>
<H3>Scrolling Result Sets</H3>

Before JDBC 2.0, JDBC drivers returned read-only result sets with 
cursors that moved in one direction, forwards. Each element was
retrieved by calling the <CODE>next</CODE> method on the result set.  

<P>
JDBC 2.0 introduces scrollable results sets whose values can be read 
and updated if reading and updating is supported by the underlying
database. With scrollabel result sets, any row can be selected at 
random, and the result set can be traversed forwards and backwards. 

<P>
One advantage to the new result
set is you can update a set of matching rows without having to issue
an additional <CODE>executeUpdate</CODE> call. The updates are made
using JDBC calls and so no custom SQL commands need to be generated.
This improves the portability of the database code you create.

<P>
Both <CODE>Statements</CODE> and <CODE>PreparedStatements</CODE> have 
an additional constructor that accepts a scroll type and an update type 
parameter.  The scroll type value can be one of the following values:

<UL>
<LI><FONT FACE="Verdana, Arial, Helvetica, sans-serif">
<CODE><STRONG>ResultSet.TYPE_FORWARD_ONLY</STRONG></CODE>
<BR>Default behavior in JDBC 1.0, application can only call 
<CODE>next()</CODE> on the result set.</FONT>

<P>
<LI><FONT FACE="Verdana, Arial, Helvetica, sans-serif">
<CODE><STRONG>ResultSet.SCROLL_SENSITIVE</STRONG></CODE>
<BR>ResultSet is fully navigable and updates are reflected in
the result set as they occur.</FONT>

<P>
<LI><FONT FACE="Verdana, Arial, Helvetica, sans-serif">
<CODE><STRONG>ResultSet.SCROLL_INSENSITIVE</STRONG></CODE>
<BR>Result set is fully navigable, but updates are only visible
after the result set is closed. You need to create a new result
set to see the results.</FONT>
</UL>

The update type parameter can be one of the following two values:
<UL>
<LI><FONT FACE="Verdana, Arial, Helvetica, sans-serif">
<STRONG><CODE>ResultSet.CONCUR_READ_ONLY</CODE></STRONG><BR>
The result set is read only.</FONT>

<P>
<LI><FONT FACE="Verdana, Arial, Helvetica, sans-serif">
<STRONG><CODE>ResultSet.CONCUR_UPDATABLE</CODE></STRONG><BR>
The result set can be updated.</FONT>
</UL>

You can verify that your database supports these types by calling
<CODE>con.getMetaData().supportsResultSetConcurrency()</CODE>
method as shown here.

<PRE>
  Connection con = getConnection();
  if(con.getMetaData().supportsResultSetConcurrency(
     ResultSet.SCROLL_INSENSITIVE,
     ResultSet.CONCUR_UPDATABLE)) {

     PreparedStatement pstmt = con.prepareStatement( 
       "select password, emailaddress,
       creditcard, balance from 
       registration where theuser = ?", 
       ResultSet.SCROLL_INSENSITIVE,
       ResultSet.CONCUR_UPDATABLE); 
 } 
</PRE>

<H4>Navigating the ResultSet</H4>

The fully scrollable result set returns a cursor which can be moved
using simple commands. By default the result set cursor points to
the row before the first row of the result set. A call to 
<CODE>next()</CODE> retrieves the first result set row. The 
cursor can also be moved by calling one of the following 
<CODE>ResultSet</CODE> methods:

<UL>
<LI><FONT FACE="Verdana, Arial, Helvetica, sans-serif">
<CODE><STRONG>beforeFirst()</STRONG></CODE>:
Default position. Puts cursor before the first row of the 
result set.</FONT>

<P>
<LI><FONT FACE="Verdana, Arial, Helvetica, sans-serif">
<CODE><STRONG>first()</STRONG></CODE>: Puts cursor
on the first row of the result set.</FONT>


<P>
<LI><FONT FACE="Verdana, Arial, Helvetica, sans-serif">
<CODE><STRONG>last()</STRONG></CODE>: Puts cursor
before the last row of the result set.</FONT>

<P>
<LI><FONT FACE="Verdana, Arial, Helvetica, sans-serif">
<CODE><STRONG>afterLast()</STRONG></CODE>
Puts cursor beyond last row of the result set. 
Calls to <CODE>previous</CODE> moves backwards through the ResultSet.</FONT>

<P>
<LI><FONT FACE="Verdana, Arial, Helvetica, sans-serif">
<CODE><STRONG>absolute(pos)</STRONG></CODE>:
Puts cursor at the row number position where absolute(1) is the first 
row and absolute(-1) is the last row.</FONT>

<P>
<LI><FONT FACE="Verdana, Arial, Helvetica, sans-serif">
<CODE><STRONG>relative(pos)</STRONG></CODE>: Puts cursor
at a row relative to its current position where relative(1) moves
row cursor one row forward.</FONT>
</UL>