The Bean-managed Enterprise JavaBeans^TM auction application with its Remote Method Invocation (RMI) and Common Object Request Broker (CORBA) variants have used simple JDBC^TM calls to retrieve and update information from a database using a JDBC connection pool. By default, JDBC database access involves opening a database connection, running SQL commands in a statement, processing the returned results, and closing the database connection.

Overall, the default approach works well for low volume database access, but how do you manage a large number of requests that update many related tables at once and still ensure data integrity? This section explains how with the following topics.

• JDBC Drivers
• Database Connections
• Statements
  • Callable Statements
  • Statements
  • Prepared Statements
• Caching Database Results
• Result Sets
• Scrolling Result Sets
• Controlling Transactions
• Escaping Characters
• Mapping Database Types
• Mapping Date types

JDBC Drivers

To get a general idea of what the JDBC driver does, you can examine the JDCConnectionDriver.java file. The JDCConnectionDriver class implements the java.sql.Driver class and acts as a pass-through driver by forwarding JDBC requests to the real database JDBC Driver. The JDBC driver class is loaded with a call to Class.forName(drivername).

These next lines of code show how to load three different JDBC driver classes:

  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
  Class.forName("postgresql.Driver"); 
  Class.forName("oracle.jdbc.driver.OracleDriver");

The jdbc-odbc bridge accepts Uniform Resource Locators (URLs) starting with jdbc:odbc: and uses the next field in that URL to specify the data source name. The data source name identifies the particular database scheme you wish to access. The URL can also include more details on how to contact the database and enter the account.

//access the ejbdemo tables
  String url = "jdbc:odbc:ejbdemo";

  String url = "jdbc:oracle:thin:user/password@(
	description=(address_list=(
	               address=(protocol=tcp)
	(host=dbmachine)(port=1521)))(source_route=yes)
	(connect_data=(sid=ejbdemo)))";

  String url = 
   "jdbc:mysql://localhost/ejbdemo?user=user;
      password=pass";

Type 1 Drivers

Type 2 Drivers

A Type 2 driver might need client-side database code to connect over the network.

Type 3 Drivers

Type 4 Drivers

Database Connections

  Connection con = DriverManager.getConnection(url);
  Connection con = DriverManager.getConnection(url, 
			"user", "password");

One useful feature of the DriverManager class is the setLogStream 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:

  DriverManager.setLogStream(System.out);

The Connection Pooling 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.

Statements

Callable Statements

This next example creates a CallableStatement object with three parameters for storing account login information.

  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);

Statements

Query Statement: This code segment creates a Statement object and calls the Statement.executeQuery method to select text from the dba database. The results of the query are returned in a ResultSet object. How to retrieve results from a ResultSet object is explained in Result Sets below.

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

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

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

Prepared Statements

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

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

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

  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();

Once the PreparedStatement template is initialized, only the changed values are inserted for each call.

  pstmt.setString(1, anotherEmailAddress);

---

Note: Not all database drivers compile prepared statements.

---

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

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();

Caching Database results

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 item_id.

Result Sets

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

  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();

Scrolling Result Sets

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.

One advantage to the new result set is you can update a set of matching rows without having to issue an additional executeUpdate 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.

Both Statements and PreparedStatements 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:

• ResultSet.TYPE_FORWARD_ONLY
  Default behavior in JDBC 1.0, application can only call next() on the result set.

• ResultSet.SCROLL_SENSITIVE
  ResultSet is fully navigable and updates are reflected in the result set as they occur.

• ResultSet.SCROLL_INSENSITIVE
  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.

• ResultSet.CONCUR_READ_ONLY
  The result set is read only.

• ResultSet.CONCUR_UPDATABLE
  The result set can be updated.

  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); 
 } 

Navigating the ResultSet

• beforeFirst(): Default position. Puts cursor before the first row of the result set.

• first(): Puts cursor on the first row of the result set.

• last(): Puts cursor before the last row of the result set.

• afterLast() Puts cursor beyond last row of the result set. Calls to previous moves backwards through the ResultSet.

• absolute(pos): Puts cursor at the row number position where absolute(1) is the first row and absolute(-1) is the last row.

• relative(pos): Puts cursor at a row relative to its current position where relative(1) moves row cursor one row forward.

Updating the Result Set

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 rs.updateRow(), which updates the underlying database. Closing the result set before calling updateRow will lose any edits applied to the result set.

  rs.first();
  updateDouble("balance", 
	rs.getDouble("balance") - 5.00);

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

Batch Jobs

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

   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();

Storing Classes, Images and Other Large Objects

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

Storing and retrieving an image: It is very easy to store an object that can be serialized or converted to a byte array. Unfortunately, java.awt.Image is not Serializable. 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.

  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();

  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);

Storing and retrieving an object: 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 RegistrationImpl class is changed to support default serialization by adding implements Serializable to the Class declaration.

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

  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) {
       }
     }
  }

This next example shows the changes needed to the RegistrationServer.refresh method to retrieve the registration instance from the database.

  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) {}
     }
  }

BLOBs and CLOBs: 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.

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 BLOB blob=getBlob(1) or CLOB clob=getClob(1), and then retrieve the data with a call to blob.getBinaryStream() or clob.getBinaryStream().

Controlling Transactions

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

  Connection con= getConnection();
  con.setAutoCommit(false);

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.

  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(
		"update registration 
		set balance=balance -0.50 
		where theuser= ?");
       ps.setString(1, seller);
       ps.close();
       stmt= con.createStatement();
       stmt.executeQuery(
		"select balance from registration 
		where theuser='"+seller+"'");
       rs = stmt.getResultSet();
       if(rs.next()) {
         balance=rs.getDouble(1);
       }
       stmt.close();
       if(balance <0) {
         con.rollback();
         con.close();
         return (-1);
       }

       stmt= con.createStatement();
       stmt.executeUpdate(
		"update auctionitems set 
		counter=counter+1");
       stmt.close();
       con.commit();
       con.close();
       return(0);
     } catch(SQLException e) {
       try {
         con.rollback();
         con.close();
         stmt.close();
         ps.close();
       }catch (Exception ignore){}
     }
     return (0);
  }

Escaping Characters

This next statements shows how you would use the escape keyword to look for the value 10%.

  stmt.executeQuery(
   "select tax from sales where tax like 
       '10\%' {escape '\'}");

To solve this problem, the following method escapes any ' symbol found in the input line. This method can be extended to escape any other characters such as commas , that the database or database driver might interpret another way.

static public String escapeLine(String s) {
  String retvalue = s;
  if (s.indexOf ("'") != -1 ) {
    StringBuffer hold = new StringBuffer();
    char c;
    for(int i=0; i < s.length(); i++ ) {
      if ((c=s.charAt(i)) == '\'' ) {
      hold.append ("''");
    }else {
      hold.append(c);
    }
  }
  retvalue = hold.toString();
  }
  return retvalue;
}

stmt.executeQuery(
"select tax from sales where tax like 
     '10\%' {escape '\'}");

preparedstmt = C.prepareStatement(
                  "update tax set tax = ?");

Mapping Database Types

Your program can determine the database column type from the database meta data and use that information to check the value before retrieving it. This next code checks that the value is in fact type INTEGER before retrieving its value.

  int count=0;
  Connection con=getConnection();
  Statement stmt= con.createStatement();
  stmt.executeQuery(
         "select counter from auctionitems");
  ResultSet rs = stmt.getResultSet();
  if(rs.next()) {
    if(rs.getMetaData().getColumnType(1) == 
                             Types.INTEGER) {
    Integer i=(Integer)rs.getObject(1);
    count=i.intValue();
    }
  }
  rs.close();

Mapping Date types

• A DATE type that represents the date only (03/23/99).
• A TIME type that specifies the time only (12:03:59)
• A TIMESTAMP that represents time value in nanoseconds.

---

Note: The Timestamp class loses precision when it is converted to a java.util.Date because java.util.Date does not contain a nanosecond field, it is better to not convert a Timestampinstance if the value will be written back to the database.

---

This example uses the java.sql.Date class to convert the java.util.Date value returned by the call to Calendar.getTime to a java.sql.Date.

  Calendar currenttime=Calendar.getInstance();
  java.sql.Date startdate=
     new java.sql.Date((
           currenttime.getTime()).getTime());

  SimpleDateFormat template = 
	new SimpleDateFormat("yyyy-MM-dd"); 
  java.util.Date enddate = 
	new java.util.Date("10/31/99"); 
  java.sql.Date sqlDate = 
	java.sql.Date.valueOf(
	                template.format(enddate)); 

_______
¹ As used on this web site, the terms "Java virtual machine" or "JVM" mean a virtual machine for the Java platform.