jdbc.html

jdbc书

<H4>Updating the Result Set</H4>

You can update a value in a result set by calling the 
<CODE>ResultSet.update&lt;type&gt;</CODE> method on the row where the 
cursor is positioned. The type value here is the same used when 
retrieving a value from the result set, for example, 
<CODE>updateString</CODE> updates a String value in the result set.

<P>
This next code updates the balance for a user from the result
set created earlier. The update applies only to the result set until 
the call to <CODE>rs.updateRow()</CODE>, which updates the underlying
database. Closing the result set before calling <CODE>updateRow</CODE> 
will lose any edits applied to the result set.

<PRE>
  rs.first();
  updateDouble("balance", 
	rs.getDouble("balance") - 5.00);
</PRE>

<P>
Inserting a new row uses the same <CODE>update&lt;type&gt;</CODE> methods.
The only difference being that the method <CODE>rs.moveToInsertRow</CODE>
is called before and <CODE>rs.insertRow()</CODE> is called 
after the fields have been initialized. You can delete the current
row with a call to <CODE>rs.deleteRow()</CODE>.

<H4>Batch Jobs</H4>

By default, every JDBC statement is sent to the database individually.
Apart from the additional network requests, this process incurs
additional delays if a transaction spans several of the statements.
JDBC 2.0 lets you submit multiple statements at one time with
the <CODE>addBatch</CODE> method.

<P>
This next code shows how to use the <CODE>addBatch</CODE> statement.
The calls to <CODE>stmt.addBatch</CODE> append statements to the 
original <CODE>Statement</CODE>, and the call to <CODE>executeBatch</CODE>
submits the entire statement with all the appends to the database.

<PRE>
   Statement stmt = con.createStatement();
   stmt.addBatch(
	"update registration set balance=balance-5.00
        where theuser="+theuser);
   stmt.addBatch(
	"insert into auctionitems(
	               description, startprice) 
        values("+description+","+startprice+")");

   int[] results = stmt.executeBatch();
</PRE>

The return result of the <CODE>addBatch</CODE> method is an array of 
row counts affected for each statement executed in the batch job. If a 
problem occurred, a <CODE>java.sql.BatchUpdateException</CODE> is thrown. 
An incomplete array of row counts can be obtained from 
<CODE>BatchUpdateException</CODE> by calling its 
<CODE>getUpdateCounts</CODE> method.

<H4>Storing Classes, Images and Other Large Objects</H4>

Many databases can store binary data as part of a row if the database
field is assigned a <CODE>long raw</CODE>, <CODE>longvarbinary</CODE>,
or other similar type. These fields can accommodate up to two Gigabytes 
of data. This means if you can convert the data into a binary stream or 
array of bytes, it can be stored and retrieved from the database in the same 
way you would store a string or double. 

<P>
This technique can be used to store and retrieve images and Java
objects. 

<P>
<STRONG>Storing and retrieving an image</STRONG>:
It is very easy to store an object that can be serialized or converted 
to a byte array. Unfortunately, <CODE>java.awt.Image</CODE> is not 
<CODE>Serializable</CODE>. However, as shown in this next code example,
you can store the image data to a file and store 
the the information in the file as bytes in a database binary field.

<PRE>
  int itemnumber=400456;

  File file = new File(itemnumber+".jpg");
  FileInputStream fis = new FileInputStream(file);
  PreparedStatement pstmt = con.prepareStatement(
	"update auctionitems 
        set theimage=? where id= ?");
  pstmt.setBinaryStream(1, fis, (int)file.length()):
  pstmt.setInt(2, itemnumber);
  pstmt.executeUpdate();
  pstmt.close();
  fis.close();
</PRE>

To retrieve this image and create a byte array that can be passed
to <CODE>createImage</CODE>, do the following:
<PRE>
  int itemnumber=400456;
  byte[] imageBytes;
 
  PreparedStatement pstmt = con.prepareStatement(
    "select theimage from auctionitems where id= ?");
  pstmt.setInt(1, itemnumber);
  ResultSet rs=pstmt.executeQuery();
  if(rs.next()) {
    imageBytes = rs.getBytes(1);
  }
  pstmt.close();
  rs.close();

  Image auctionimage = 
	Toolkit.getDefaultToolkit().createImage(
	                              imageBytes);
</PRE>

<P>
<STRONG>Storing and retrieving an object</STRONG>:
A class can be serialized to a binary database field in much the same 
way as the image was in the previous example. In this example, the 
<CODE>RegistrationImpl</CODE> class is changed to support default 
serialization by adding <CODE>implements Serializable</CODE> to
the Class declaration. 

<P>
Next, a  <CODE>ByteArrayInputStream</CODE> is created 
to be passed as the JDBC Binary Stream. To create the 
<CODE>ByteArrayInputStream</CODE>, <CODE>RegistrationImpl</CODE> 
is first piped through an <CODE>ObjectOutputStream</CODE> to an underlying 
<CODE>ByteArrayInputStream</CODE> with a call to 
<CODE>RegistrationImpl.writeObject</CODE> The <CODE>ByteArrayInputStream</CODE>
is then converted to a byte array, which can then be used to create the
<CODE>ByteArrayInputStream</CODE>. The <CODE>create</CODE> method in
<CODE>RegistrationServer.java</CODE> is changed as follows:

<PRE>
  public registration.RegistrationPK create(
	String theuser, 
	String password, 
	String emailaddress, 
	String creditcard) 
	throws registration.CreateException{

     double balance=0;
     Connection con = null;
     PreparedStatement ps = null;;

     try {
        con=getConnection();
        RegistrationImpl reg= new RegistrationImpl();
        reg.theuser = theuser;
        reg.password = password;
        reg.emailaddress = emailaddress;
        reg.creditcard = creditcard;
        reg.balance = balance;

        ByteArrayOutputStream regStore = 
		new ByteArrayOutputStream();
        ObjectOutputStream regObjectStream = 
		new ObjectOutputStream(regStore);
           regObjectStream.writeObject(reg);

        byte[] regBytes=regStore.toByteArray();
        regObjectStream.close();
        regStore.close();
        ByteArrayInputStream regArrayStream = 
		new ByteArrayInputStream(regBytes);
        ps=con.prepareStatement(
		"insert into registration (
		theuser, theclass) values (?, ?)");
        ps.setString(1, theuser);
        ps.setBinaryStream(2, regArrayStream, 
             regBytes.length);

        if (ps.executeUpdate() != 1) {
            throw new CreateException ();
        }
        RegistrationPK primaryKey = 
                         new RegistrationPKImpl();
        primaryKey.theuser(theuser);
        return primaryKey;
     } catch (IOException ioe) {
       throw new CreateException ();
     } catch (CreateException ce) {
       throw ce;
     } catch (SQLException sqe) {
       System.out.println("sqe="+sqe);
       throw new CreateException ();
     } finally {
       try {
          ps.close();
         con.close();
       } catch (Exception ignore) {
       }
     }
  }
</PRE>

The object is retrieved and reconstructed by extracting the bytes
from the database, creating a <CODE>ByteArrayInputStream</CODE> from 
those bytes to be read from an <CODE>ObjectInputStream</CODE>, and calling 
<CODE>readObject</CODE> to create the instance again. 

<P>
This next example shows the changes needed to the <CODE>RegistrationServer.refresh</CODE> 
method to retrieve the registration instance from the database.

<PRE>
  private Registration refresh(RegistrationPK pk)
		throws FinderException {

      if (pk == null) {
          throw new FinderException ();
      }
      Connection con = null;
      PreparedStatement ps = null;
      try {
         con=getConnection();
         ps=con.prepareStatement("
		select theclass from 
                registration where theuser = ?");
         ps.setString(1, pk.theuser());
         ps.executeQuery();
         ResultSet rs = ps.getResultSet();
         if(rs.next()){
            byte[] regBytes = rs.getBytes(1);
            ByteArrayInputStream regArrayStream = 
                new ByteArrayInputStream(regBytes);
            ObjectInputStream regObjectStream = 
                new ObjectInputStream(
                      regArrayStream);
            RegistrationImpl reg= 
                (RegistrationImpl)
                       regObjectStream.readObject();
            return reg;
         }
         else {
             throw new FinderException ();
         }
     } catch (Exception sqe) {
         System.out.println("exception "+sqe);
         throw new FinderException ();
     }
     finally {
        try {
           rs.close();
           ps.close();
           con.close();
        }
        catch (Exception ignore) {}
     }
  }
</PRE>

<P>
<STRONG>BLOBs and CLOBs</STRONG>:
Storing large fields in a table with the other data
is not necessarily the optimum place especially if the data has
a variable size.  One way to handle large, variable sized objects is with
the Large Objects (LOBs) type. LOBs use a locator, essentially a pointer, in the 
database record that points to the real database field. 

<P>
There are two types of LOBs: Binary Large Objects (BLOBs) and Character 
Large Objects (CLOBs). When you access a BLOB or CLOB, the data is not 
copied to the client. To retrieve the actual data from a result set, you have 
to retrieve the pointer with a call to <CODE>BLOB blob=getBlob(1)</CODE>
or <CODE>CLOB clob=getClob(1)</CODE>, and then retrieve the data with a call to
<CODE>blob.getBinaryStream()</CODE> or <CODE>clob.getBinaryStream()</CODE>. 

<A NAME="trans"></A>
<H3>Controlling Transactions</H3>

By default, JDBC statements are processed in full auto-commit mode.
This mode works well for a single database query, but if
an operation depends on several database statements that all have
to complete successfully or the entire operation is cancelled, a finer
transaction is needed.

<P>
A description of transaction isolation levels is covered in more detail in 
<A HREF="bmp.html">Chapter 3: Data and Transaction Management</A>. To 
use transaction management in the JDBC platform, you first need to disable 
the full auto-commit mode by calling:

<PRE>
  Connection con= getConnection();
  con.setAutoCommit(false);
</PRE>

At this point, you can either <CODE>commit</CODE> any following JDBC 
statements or undo any updates by calling the <CODE>Connection.rollback</CODE> 
method. The <CODE>rollback</CODE> call is commonly placed in the Exception 
handler, although it can be placed anywhere in the transaction flow. 

<P>
This next example inserts an auction item and decrements the user's balance. 
If the balance is less than zero, the entire transaction is rolled back and 
the auction item is removed.

<PRE>
  public int insertItem(String seller, 
	String password, 
	String description, 
	int auctiondays, 
	double startprice, 
	String summary) {
     Connection con = null;
     int count=0;
     double balance=0;
     java.sql.Date enddate, startdate;
     Statement stmt=null;

     PreparedStatement ps = null;
     try {
       con=getConnection();
       con.setAutoCommit(false);
       stmt= con.createStatement();
       stmt.executeQuery(
              "select counter from auctionitems");
       ResultSet rs = stmt.getResultSet();
       if(rs.next()) {
         count=rs.getInt(1);
       }
       Calendar currenttime=Calendar.getInstance();
       java.util.Date currentdate=currenttime.getTime();
       startdate=new java.sql.Date(
                       currentdate.getTime());
       currenttime.add(Calendar.DATE, auctiondays);
       enddate=new java.sql.Date((
		currenttime.getTime()).getTime());

       ps=con.prepareStatement(
                "insert into auctionitems(
		id, description, startdate, enddate, 
		startprice, summary) 
		values (?,?,?,?,?,?)");
       ps.setInt(1, count);
       ps.setString(2, description);
       ps.setDate(3, startdate);
       ps.setDate(4, enddate);
       ps.setDouble(5, startprice);
       ps.setString(6, summary);
       ps.executeUpdate();
       ps.close();

       ps=con.prepareStatement(