jdbc Statements to update and execute query

JDBC Drivers

The connection to the database is handled by the JDBC Driver class. The Java SDK contains only one JDBC driver, a jdbc-odbc bridge that can communicate with an existing Open DataBase Conectivity (ODBC) driver. Other databases need a JDBC driver specific to that database.

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

Each JDBC driver is configured to understand a specific URL so multiple JDBC drivers can be loaded at any one time. When you specify a URL at connect time, the first matching JDBC driver is selected.

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

This next example contains the Oracle SQL*net information on the particular database called ejbdemo on machine dbmachine

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

This next examples uses mysql to connect to the ejbdemo database on the local machine. The login user name and password details are also included.

  String url = 

   "jdbc:mysql://localhost/ejbdemo?user=user;

      password=pass";

JDBC drivers are divided into four types. Drivers may also be categorized as pure Java or thin drivers to indicate if they are used for client applications (pure Java drivers) or applets (thin drivers). Newer drivers are usually Type 3 or 4. The four types are as follows: Type 1 Drivers

Type 1 JDBC drivers are the bridge drivers such as the jdbc-odbc bridge. These drivers rely on an intermediary such as ODBC to transfer the SQL calls to the database. Bridge drivers often rely on native code, although the jdbc-odbc library native code is part of the Java¹ 2 virtual machine. Type 2 Drivers

Type 2 Drivers use the existing database API to communicate with the database on the client. Although Type 2 drivers are faster than Type 1 drivers, Type 2 drivers use native code and require additional permissions to work in an applet.

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

Type 3 Drivers call the database API on the server. JDBC requests from the client are first proxied to the JDBC Driver on the server to run. Type 3 and 4 drivers can be used by thin clients as they need no native code. Type 4 Drivers

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. Database Connections

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

  Connection con = DriverManager.getConnection(url);

  Connection con = DriverManager.getConnection(url, 

                       "user", "password");

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.

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

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

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

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

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

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

The PreparedStatement 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: query "select description from auctionitems where item_id='4000343'" might be stored more efficiently in a hash table.

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

The ResultSet 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.

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

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 next method on the result set.

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.

The update type parameter can be one of the following two values:

• ResultSet.CONCUR_READ_ONLY
  The result set is read only.
• ResultSet.CONCUR_UPDATABLE
  The result set can be updated.

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

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

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 next() retrieves the first result set row. The cursor can also be moved by calling one of the following ResultSet methods:

• 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

You can update a value in a result set by calling the ResultSet.update<type> 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, updateString updates a String value in 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

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 addBatch method.

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

The return result of the addBatch method is an array of row counts affected for each statement executed in the batch job. If a problem occurred, a java.sql.BatchUpdateException is thrown. An incomplete array of row counts can be obtained from BatchUpdateException by calling its getUpdateCounts method. Storing Classes, Images and Other Large Objects

Many databases can store binary data as part of a row if the database field is assigned a long raw, longvarbinary, 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.

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

To retrieve this image and create a byte array that can be passed to createImage, do the following:

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

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

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

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.

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

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

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

The JDBC API provides the escape keyword so you can specify the character you want to use to escape characters. For example, if you want to use the percent sign (%) as the percent sign and not have it interpreted as the SQL wildcard used in SQL LIKE queries, you have to escape it with the escape character you specify with the escape keyword.

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 '\'}");

If your program stores names and addresses to the database entered from the command line or by way of a user interface, the single quotes (') symbol might appear in the data. Passing single quotes directly into a SQL string causes problems when the SQL statement is parsed because SQL gives this symbol another meaning unless it is escaped.

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

However, if you use a PreparedStatement instead of a simple Statement, most of these escape problems go away. For example, instead of this line with the escape sequence:

stmt.executeQuery(

"select tax from sales where tax like 

     '10\%' {escape '\'}");

You could use this line:

preparedstmt = C.prepareStatement(

                  "update tax set tax = ?");

Mapping Database Types

Apart from a few JDBC types such as INTEGER that are represented as an INTEGER in most popular databases, you might find that the JDBC type for a table column does not match the type as it is represented in the database. This means calls to ResultSet.getObject, PreparedStatement.setObject and CallableStatement.getObject() will very likely fail.

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

The DATE type is where most mismatches occur. This is because the java.util.Date class represents both Date and Time, but SQL has the following three types to represent data and time information:

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

These three additional types are provided in the java.sql package as java.sql.Date, java.sql.Time and java.sql.Timestamp and are all subclasses of java.util.Date. This means you can use convert java.util.Date values to the type you need to be compatible with the database type.

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

You can also use the java.text.SimpleDateFormat class to do the conversion. This example uses the java.text.SimpleDateFormat class to convert a java.util.Date object to a java.sql.Date object:

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

If you find a database date representation cannot be mapped to a Java type with a call to getObject or getDate, retrieve the value with a call to getString and format the string as a Date value using the SimpleDateFormat class shown above.