// Prepare a Statement:
   PreparedStatement stmnt = conn.prepareStatement("select aBlob from BlobTable");
   // Execute
   ResultSet rs = stmnt.executeQuery();

   while(rs.next())
   {
      try
      {
      
        // Get as a BLOB
        Blob aBlob = rs.getBlob(1);
        byte[] allBytesInBlob = aBlob.getBytes(1, (int) aBlob.length());
      
      }
      catch(Exception ex)
      {
      }
   }

}

When retrieving database results, the best collection implementation to use is the LinkedList. The benefits include:

• Retains the original retrieval order
• Has quick insertion at the head/tail
• Doesn't have an internal size limitation like a Vector where when the size is exceeded a new internal structure is created (or you have to find out size beforehand to size properly)
• Permits user-controlled synchronization unlike the pre-Collections Vector which is always synchronized

ResultSet result = stmt.executeQuery("...");
List list = new LinkedList();
while(result.next()) {
  list.add(result.getString("col"));
}

If there are multiple columns in the result set, you'll have to combine them into their own data structure for each row. Arrays work well for that as you know the size, though a custom class might be best so you can convert the contents to the proper type when extracting from database, instead of later.

After using one of the getXXX() methods of ResultSet, you can use wasNull() method to test whether you got null value or not null.

      rs.getString("email");

      if ( rs.wasNull() )
           // no email
      else
           // use email

If your autoCommit flag (managed by the Connection.setAutoCommit() method) is false, you are required to call the commit() method - and vice versa. The default is setting to AutoCommit flag is True.

Use the setFetchSize() method of Statement to indicate the size of each database fetch.

Statement stmt = con.createStatement();
stmt.setFetchSize(100);
ResultSet rs = stmt.executeQuery("select * from customers");

You can also control the direction in which the rows are processed. For instance:

stmt.setFetchDirection(ResultSet.FETCH_REVERSE)

will process the rows from bottom up.The driver manager usually defaults to the most efficient fetch size so you may try experimenting with different value for optimal performance.

One of the more advanced features of JDBC 2.0 is the ability to submit multiple update statements to the database for processing as a single unit. This batch updating can be significantly more efficient compared to JDBC 1.0, where each update statement has to be executed separately.

 try
 {
 dbCon.setAutoCommit(false);

 Statement stmt= dbCon.createStatement();
 stmt.addBatch("INSERT INTO bugs VALUES (...)");
 stmt.addBatch("INSERT INTO bugs VALUES (...)");

 int[] updCnt = stmt.executeBatch();

 dbCon.commit();

 }
 catch (BatchUpdateException be)
 {
 	//handle batch update exception
 	int[] counts = be.getUpdateCounts();
 	for (int i=0; i < counts.length; i++) {
 		System.out.println("Statement["+ i + "] :" + counts[i]);
 	}
 	dbCon.rollback();
 }
 catch (SQLException e)
 {
 	//handle SQL exception
 	dbCon.rollback();
 }

The next version of the ANSI/ISO SQL standard defines some new datatypes, commonly referred to as the SQL3 types. The primary SQL3 types are:

• STRUCT: This is the default mapping for any SQL structured type, and is manifest by the java.sql.Struct type.
• REF: Serves as a reference to SQL data within the database. Can be passed as a parameter to a SQL statement. Mapped to the java.sql.Ref type.
• BLOB: Holds binary large objects. Mapped to the java.sql.Blob type.
• CLOB: Contains character large objects. Mapped to the java.sql.Clob type.
• ARRAY: Can store values of a specified type. Mapped to the java.sql.Array type.

You can retrieve, store and update SQL3 types using the corresponding getXXX(), setXXX(), and updateXXX() methods defined in ResultSet interface.

No. JDBC-ODBC driver uses ODBC driver, which is Native Code. Applets cannot use native code.

You may create a digitally signed applet using a Certificate to circumvent the security sandbox of the browser.

SQL/J is a technology, originally developed by Oracle Corporation, that enables you to embed SQL statements in Java. The purpose of the SQLJ API is to simplify the development requirements of the JDBC API while doing the same thing. Some major databases (Oracle, Sybase) support SQLJ, but others do not.

All raw data types (including binary documents or images) should be read and uploaded to the database as an array of bytes, byte[].

• Read all data from the file using a FileInputStream.
• Create a byte array from the read data.
• Use method setBytes(int index, byte[] data); of java.sql.PreparedStatement to upload the data.

Microsoft's VM loads classes/drivers differently than the Java VM in Netscape browsers (and Sun's reference implementation). Just having a Class.forName(driverClassName) line is insufficient, as it doesn't consider it an active use. You'll also need to create a new instance of the driver Class.forName(driverClassName).newInstance() which registers the driver with the driver manager (java.sql.DriverManager.registerDriver(new DriverClass())).

With certain database systems, a stored procedure can return multiple result sets, multiple update counts, or some combination of both. Also, if you are providing a user with the ability to enter any SQL statement, you don't know if you are going to get a ResultSet or an update count back from each statement, without analyzing the contents.

The Statement.execute() method helps in these cases. Method Statement.execute() returns a boolean to tell you the type of response : true indicates next result is a ResultSet then use Statement.getResultSet() to get the ResultSet, false indicates result is an update count then use Statement.getUpdateCount() to get the update count.

After processing each response, you use Statement.getMoreResults() to check for more results, again returning a boolean.

The following demonstrates the processing of multiple result sets:

boolean result = stmt.execute(" ... ");
int updateCount = stmt.getUpdateCount();

while (result || (updateCount != -1))
{
  if(result)
  {
    ResultSet r = stmt.getResultSet();
    // process result set
  }
  else
    if(updateCount != -1)
    {
    // process update count
    }
  result = stmt.getMoreResults();
  updateCount = stmt.getUpdateCount();
}

Here is an example on how to execute a stored procedure with JDBC.

    CallableStatement cs  = con.prepareCall("{? = call procedure(?,?) }");
    cs.registerOutParameter(1, java.sql.Types.VARCHAR );
    cs.setInt(2, value1 );
    cs.setInt(3, value2 );
    ResultSet lrsReturn = null;
    lrsReturn = msProcedure.executeQuery();

    while( lrsReturn.next() )
    {
       System.out.println("Got from result set: " + lrsReturn.getInt(1));
    }

    System.out.println( "Got from stored procedure: " + cs.getString( 1 ) );

A cursor is actually always on the database server side. When you execute an SQL SELECT and create a ResultSet in JDBC, the RDBMS creates a cursor in response. When created, the cursor usually takes up temporary memory space of some sort inside the database.

Let's say you have created the following Excel spreadsheet in a worksheet called Sheet1 (the default sheet name). And you've saved the file in c:\users.xls.

USERID FIRST_NAME LAST_NAME abc a b xyz x y

Since Excel comes with an ODBC driver, we'll use the JDBC-ODBC bridge driver that comes packaged with Sun's JDK to connect to our spreadsheet.

In Excel, the name of the worksheet is the equivalent of the database table name, while the header names found on the first row of the worksheet is the equivalent of the table field names. Therefore, when accessing Excel via jdbc, it is very important to place your data with the headers starting at row 1.

1. Create a new ODBC Data Source using the Microsoft Excel Driver. Name the DSN "excel", and have it point to c:\users.xls.
2. Type in the following code:

   Connection conn=null;
   Statement stmt=null;
   String sql="";
   ResultSet rs=null;
   
   try
   {
       Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
       conn=DriverManager.getConnection("jdbc:odbc:excel","","");
       stmt=conn.createStatement();
       sql="select * from [Sheet1$]";
       rs=stmt.executeQuery(sql);
       while(rs.next())
       {
          System.out.println(rs.getString("USERID")+ " "+ rs.getString("FIRST_NAME")+" "+ rs.getString("LAST_NAME"));
       }
   }
   catch (Exception e)
   {      System.err.println(e);   }
   finally
   {
       try
       {
            rs.close();
            stmt.close();
            conn.close();
       }
       catch(Exception e){}
   }
   

Notice that we have connected to the Excel ODBC Data Source the same way we would connect to any normal database server.

The only significant difference is in the SELECT statement. Although your data is residing in the worksheet called "Sheet1", you'll have to refer to the sheet as Sheet1$ in your SQL statements. And because the dollar sign symbol is a reserved character in SQL, you'll have to encapsulate the word Sheet1$ in brackets, as shown in the code.

Yes, although, as usual, technically we are passing object references. However, there is a chain of dependency that must be kept in mind and should be tracked for Connection, Statement and ResultSet. For example, if a ResultSet is not scrollable, rows already read are not available, so passing the same ResultSet to different methods may not work as expected. If the originating Statement is closed, the ResultSet is generally no longer available.

One of the better approaches for passing data retrieved from a servlet to a JSP is to use the Model 2 architecture. Basically, you need to first design a bean which can act as a wrapper for storing the resultset returned by the database query within the servlet. Once the bean has been instantiated and initialized by invoking its setter methods by the servlet, it can be placed within the request object and forwarded to a display JSP page as follows:

   com.foo.dbBean bean = new com.foo.dbBean();
   //call setters to initialize bean
   req.setAttribute("dbBean", bean);
   url="..."; //relative url for display jsp page
   ServletContext sc = getServletContext();
   RequestDispatcher rd = sc.getRequestDispatcher(url);
   rd.forward(req, res);

The bean can then be accessed within the JSP page via the useBean tag as:

   <jsp:useBean id="dbBean" class="com.foo.dbBean"  scope="request"/>
   ...
   <%
     //iterate through the rows within dbBean and
     //access the values using a scriptlet
    %>

Also, it is best to design your application such that you avoid placing beans into the session unless absolutely necessary. Placing large objects within the session imposes a heavy burden on the performance of the servlet engine.

If your RDBMS supports them, you can store serialized objects as BLOBs. These are JDBC 2.0 features, but take a look at java.sql.Blob, ResultSet and PreparedStatement.

JDBC 2.0, introduced with the 1.2 version of Java, added several capabilities to JDBC. Instead of completely invalidating all the older JDBC 1.x drivers, when you try to perform a 2.0 task with a 1.x driver, an UnsupportedOperationException will be thrown. You need to update your driver if you wish to use the new capabilities

Assume we have a stored procedure with this signature:
MultiSP (IN I1 INTEGER, OUT O1 INTEGER, INOUT IO1 INTEGER)

The code snippet to retrieve the OUT and INOUT parameters is as follows:

 CallableStatement cs = connection.prepareCall( "(CALL MultiSP(?, ?, ?))" );
 cs.setInt(1, 1); // set the IN parm I1 to 1
 cs.setInt(3, 3); // set the INOUT parm IO1 to 3

 cs.registerOutParameter(2, Types.INTEGER); // register the OUT parm O1
 cs.registerOutParameter(3, Types.INTEGER); // register the INOUT parm IO1

 cs.execute();
 int iParm2 = cs.getInt(2);
 int iParm3 = cs.getInt(3);
 cs.close();

The code really is just additive; be sure that for each IN parameter that setXXX() is called and that for each INOUT and OUT parameter that registerOutParameter() is called.

A:This depends on your driver. If it supports JDBC 2.0, then the answer is yes, although the functionality is in the driver. As you can see from Creating a CallableStatement Object, there is no difference in the stored procedure itself.

These are few points to consider:

• Use a connection pool mechanism whenever possible.
• Use prepared statements. These can be beneficial, for example with DB specific escaping, even when used only once.
• Use stored procedures when they can be created in a standard manner. Do watch out for DB specific SP definitions that can cause migration headaches.
• Even though the jdbc promotes portability, true portability comes from NOT depending on any database specific data types, functions and so on.
• Select only required columns rather than using select * from Table.
• Always close Statement and ResultSet objects as soon as possible.
• Write modular classes to handle database interaction specifics.
• Work with DatabaseMetaData to get information about database functionality.
• Softcode database specific parameters with, for example, properties files.
• Always catch AND handle database warnings and exceptions. Be sure to check for additional pending exceptions.
• Test your code with debug statements to determine the time it takes to execute your query and so on to help in tuning your code. Also use query plan functionality if available.
• Use proper ( and a single standard if possible ) formats, especially for dates.
• Use proper data types for specific kind of data. For example, store birthdate as a date type rather than, say, varchar.

This error indicates that: the operation is valid but not supported by either the driver or the data source.

You can get scrollable ResultSets by using the JDBC 2.0 API. You must have a driver that supports JDBC 2.0. The following code will give a Statement the capability to create scrollable ResultSets:

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

Now use the Statement object to execute the query: For example:

 ResultSet srs = stmt.executeQuery("SELECT FIRST_NAME, AGE FROM STUDENTS_TABLE");

Probably the new features of most interest are:

• Savepoint support
• Reuse of prepared statements by connection pools
• Retrieval of auto-generated keys
• Ability to have multiple open ResultSet objects
• Ability to make internal updates to the data in Blob and Clob objects
• Ability to Update columns containing BLOB, CLOB, ARRAY and REF types

Just as is required with a scrollable ResultSet, the Statement must be capable of returning an updatable ResultSet. This is accomplished by asking the Connection to return the appropriate type of Statement using Connection.createStatement(int resultSetType, int resultSetConcurrency). The resultSetConcurrency parameter must be ResultSet.CONCUR_UPDATABLE. The actual code would look like this:

Statement stmt = con.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE );

Note that the spec allows a driver to return a different type of Statement/ResultSet than that requested, depending on capabilities and circumstances, so the actual type returned should be checked with ResultSet.getConcurrency().

Use the following DatabaseMetaData methods:

• getDriverMajorVersion()
• getDriverMinorVersion()
• getDriverName()
• getDriverVersion()

Often the answer given is that the correct driver is not loaded. This may be the case, but more typically, the JDBC database URL passed is not properly constructed. When a Connection request is issued, the DriverManager asks each loaded driver if it understands the URL sent. If no driver responds that it understands the URL, then the "No Suitable Driver" message is returned.

Typically an application retrieves multiple rows of data, providing a snapshot at an instant of time. Before a particular row is operated upon, the actual data may have been modified by another program. When it is essential that the most recent data is provided, a JDBC 2.0 driver provides the ResultSet.refreshRow() method.

 // loads the JDBC driver
 Class.forName("org.gjt.mm.mysql.Driver").newInstance();
 // get a database connection
 Connection conn = DriverManager.getConnection("jdbc:mysql://hostname/databaseName", "user","password");

With Oracle, console output from System.out.println() statements will be written to trace files in the Oracle UDUMP destination directory.

JDO provides for the transparent persistence of data in a data store agnostic manner, supporting object, hierarchical, as well as relational stores.

setFetchSize(int) defines the number of rows that will be read from the database when the ResultSet needs more rows. The method in the java.sql.Statement interface will set the 'default' value for all the ResultSet derived from that Statement; the method in the java.sql.ResultSet interface will override that value for a specific ResultSet. Since database fetches can be expensive in a networked environment, fetch size has an impact on performance.

setMaxRows(int) sets the limit of the maximum number of rows in a ResultSet object. If this limit is exceeded, the excess rows are "silently dropped". That's all the API says, so the setMaxRows() method may not help performance at all other than to decrease memory usage. A value of 0 (default) means no limit.

Yes. Use ResultSet.getStatement(). From the resulting Statement you can use Statement.getConnection().