JDBC FAQ's

How does the Java Database Connectivity (JDBC) work?
The JDBC is used whenever a Java application should communicate with a relational database for which a JDBC driver exists. JDBC is part of the Java platform standard; all visible classes and interfaces used in the JDBC are placed in package Java

Main JDBC classes:

What is a database URL
A database URL (or JDBC URL) is a platform independent way of addressing a database. Exact formation changes from database to database and from one type of driver to another.A database/JDBC URL is of the form

jdbc:[subprotocol]:[node]/[databaseName] Example :

     jdbc:odbc:oracle  --> to access ODBC data source
     jdbc:oracle:thin:@oracleserver:1521:oracle9i   --> to access Oracle using thin driver

What types of JDBC drivers exist?
There are four types of JDBC database driver:

Driver type Explanation Comment
1 The JDBC/ODBC bridge driver A piece of native C-code that translates a JDBC call to an ODBC call. Use this driver for development, not for industrial-strength application environments. Note that you have to have an ODBC database driver manager + an ODBC database driver installed on the server in addition to the JDBC/ODBC bridge.
2 Native API partly java driver A piece of native C-code that translates a java JDBC call to a native database call level API. Use this driver for development and deployment. Due to its native code, this driver can only be used by Java Applications with full computer access (i.e. not Applets).
3 JDBC-Net pure Java driver A piece of pure java code that translates an incoming JDBC call to an outgoing database Net protocol call (such as SQL*Net). Use this driver for development and deployment. Flexible and powerful, This driver can be used by any Java component and requires only connect access to work.
4 Native protocol pure Java driver A piece of pure java code that translates an incoming JDBC call to an outgoing database native protocol call (such as Oracle CLI). Use this driver for development and deployment. This driver type is the recommended one for server-side java development.

How do I create a database connection?
The database connection is created in 3 steps:
  1. Load the database driver
  2. Find a proper database URL
  3. Ask the Java DriverManager class to open a connection to your database

What is the difference between a Statement and a PreparedStatement?
  1. The PreparedStatement is a slightly more powerful version of a Statement,and should always be at least as quick and easy to handle as a Statement.
  2. Another advantage of the PreparedStatement class is the ability to create an incomplete query and supply parameter values at execution time.

What is Metadata and why should I use it?
Metadata (data about data) is information about one of two things
  1. Database information (java.sql.DatabaseMetaData), or
  2. Information about a specific ResultSet (java.sql.ResultSetMetaData).
Use DatabaseMetaData to find information about your database, such as its capabilities and structure. Use ResultSetMetaData to find information about the results of an SQL query, such as name, size and type of each column.

What is a "dirty read"?

Quite often in database processing, we come across the situation wherein one transaction can change a value, and a second transaction can read this value before the original change has been committed or rolled back. This is known as a dirty read scenario because there is always the possibility that the first transaction may rollback the change, resulting in the second transaction having read an invalid value.

While you can easily command a database to disallow dirty reads, this usually degrades the performance of your application due to the increased locking overhead. Disallowing dirty reads also leads to decreased system concurrency.

Q:How do I extract a BLOB from a database?
A BLOB (Binary Large OBject) is essentially an array of bytes (byte[]), stored in the database. You extract the data in two steps:
  1. Call the getBlob() method of the Statement class to retrieve a java.sql.Blob object
  2. Call either getBinaryStream() or getBytes() in the extracted Blob object to retrieve the java byte[] which is the Blob object.
Note A Blob is essentially a pointer to a byte array (called LOCATOR in database-talk), so the java.sql.Blob object essentially wraps a byte pointer. Thus, you must extract all data from the database blob.

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

}

Q:Which is the preferred collection class to use for storing database result sets?
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.

How can I check whether the value I read from a column is null or not null?
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

Do I need to commit after an INSERT call in JDBC or does JDBC do it automatically in the DB?
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.

How can I retrieve only the first N rows, second N rows of a database using a particular WHERE clause ? For example, if a SELECT typically returns a 1000 rows, how do first retrieve the 100 rows, then go back and retrieve the next 100 rows and so on ?
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.

How can I make batch updates using JDBC?
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();
 }

What are SQL3 data types?
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

Can I use the JDBC-ODBC bridge driver in an applet?
No. JDBC-ODBC driver using 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.

Q:What is SQLJ and why would I want to use it instead of JDBC?
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.

How do I insert an image file (or other raw data) into a database?
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.

I am using a type 4 (pure Java) JDBC driver in an applet. It works fine in Netscape, but doesn't work properly in Internet Explorer. Why not?
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())).

How can I get data from multiple ResultSets?
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,Use Statement.getResultSet() to get the ResultSet false indicates next result is an update count Use Statement.getUpdateCount() to get the update count false also indicates no more results Update count is -1 when no more results (usually 0 or positive).

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

How do I execute stored procedures?
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 ) );

What are database cursors?
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.

How can I connect to an Excel spreadsheet file using jdbc?
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.

Can ResultSets and Connections be passed around like other objects?
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.

How can I pass data retrieved from a database by a servlet to a JSP page?
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.

How can I read and write serialized objects to and from a database?
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.

Why do I get an UnsupportedOperationException?
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

Could we get sample code for retrieving more than one parameter from a stored procedure?
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 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.

Q:Can a stored procedure return an updatable ResultSet?
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.

What areas should I focus on for the best performance in a JDBC application?
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.

Why do I get "Driver Not Capable" errors and what does it mean?
This error indicates that: the operation is valid but not supported by either the driver or the data source.

How do I get a scrollable ResultSet?
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");

What's new in JDBC 3.0?
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

How do I create an updatable ResultSet?
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().

How do I get runtime information about the JDBC Driver?
Use the following DatabaseMetaData methods:
  • getDriverMajorVersion()
  • getDriverMinorVersion()
  • getDriverName()
  • getDriverVersion()
Why do I get the message "No Suitable Driver"?
Often the answer is given 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

How can I ensure that my app has the latest data?
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.

How can I connect to mysql database using jdbc?
 // 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");

Where is console output sent (System.out/System.err) in stored procedures written in Java?
With Oracle, console output from System.out.println() statements will be written to trace files in the Oracle UDUMP destination directory.

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

What is the difference between setMaxRows(int) and SetFetchSize(int)? Can either reduce processing time?
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 nuber 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.

Can I get information about a ResultSet's associated Statement and Connection in a method without having or adding specific arguments for the Statement and Connection?
Yes. Use ResultSet.getStatement(). From the resulting Statement you can use Statement.getConnection().

What is pessimistic concurrency?
With a pessimistic approach, locks are used to ensure that no users, other than the one who holds the lock, can update data. It's generally explained that the term pessimistic is used because the expectation is that many users will try to update the same data, so one is pessimistic that an update will be able to complete properly. Locks may be acquired, depending on the DBMS vendor, automatically via the selected Isolation Level. Some vendors also implement 'Select... for Update', which explicitly acquires a lock.

What is optimistic concurrency?
An optimistic approach dispenses with locks (except during the actual update) and usually involves comparison of timestamps, or generations of data to ensure that data hasn't changed between access and update times. It's generally explained that the term optimistic is used because the expectation is that a clash between multiple updates to the same data will seldom occur

What is a JDBC 2.0 DataSource?
The DataSource class was introduced in the JDBC 2.0 Optional Package as an easier, more generic means of obtaining a Connection. The actual driver providing services is defined to the DataSource outside the application (Of course, a production quality app can and should provide this information outside the app anyway, usually with properties files or ResourceBundles ). The documentation expresses the view that DataSource will replace the common DriverManager method.

Some downsides are:

  • Vendor specific set up tools must be used, with no particular standards specified. To quote from the source: "Now he ( the developer - JS ) needs to have his system administrator, SoLan, deploy the DataSource objects so that he... can start using them".
  • JNDI must be used to implement a lookup for the DataSource.

Why can't Tomcat find my Oracle JDBC drivers in classes111.zip?
TOMCAT throws the following exception when i try to connect to Oracle DB from JSP.
javax.servlet.ServletException : oracle.jdbc.driver.OracleDriver

Root Cause : java.lang.ClassNotFoundException: oracle:jdbc:driver:OracleDriver

But, the Oracle JDBC driver ZIP file (classes111.zip)is available in the system classpath.

The problem can be solved by using the following procedure:

  1. Copy the Oracle Driver class file (classes111.zip) into /WEB-INF/lib directory
  2. Rename it to classess111.jar. Because only .jar files are recognized and not .zip files.
How do I insert/update records with some of the columns having NULL value?
Use the following method of PreparedStatement:
public void setNull(int parameterIndex, int sqlType) throws SQLException

What does it mean to "materialize" data?
This term generally refers to Array, Blob and Clob data which is referred to in the database via SQL locators. "Materializing" the data means to return the actual data pointed to by the Locator.

For Arrays, use the various forms of getArray() and getResultSet().

For Blobs, use getBinaryStream() or getBytes(long pos, int length).

For Clobs, use getAsciiStream() or getCharacterStream().

How can I write to the log used by DriverManager and JDBC drivers?
The simplest method is to use DriverManager.println(String message), which will write to the current log.

How do I install the Thin driver?
Put the jar files in a convenient location and include the appropriate jar files in your classpath.

How do I install the OCI driver?
The JDBC OCI driver generally requires an Oracle client-installation of the same version the driver