Database Access with JDBC

Accessing a Database

The process of retrieving information from a database via JDBC involves these five basic steps:

1. Register the JDBC driver with the driver manager.

2. Establish a database connection.

3. Execute an SQL statement.

4. Process the results.

5. Close the database connection.

Let's examine how each of these steps is accomplished.

Register the JDBC Driver

Before a JDBC driver can be used to establish a database connection, it must first be registered with the driver manager. The driver manager's job is to maintain a reference to all driver objects that are available to JDBC clients. A JDBC driver automatically registers itself with the driver manager when it is loaded. To load a JDBC driver, use the Class.forName().newInstance() method call as demonstrated here:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();

Class.forName() is a static method that instructs the Java virtual machine to dynamically locate, load, and link the specified class (if not already loaded). If the class cannot be located, a ClassNotFoundException is thrown. The newInstance() method indicates that a new instance of this class should be created. (Alternatively, drivers can also be registered using the DriverManager.registerDriver() method.)

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

As you can see from this example, a JDBC driver is simply a Java class that resides in a valid CLASSPATH. In this case, we are loading the JDBC-ODBC bridge driver located in the sun.jdbc.odbc package.

Establish a Database Connection

Once the driver is loaded, we can use it to establish a connection to the database. A JDBC connection is identified by a database URL that tells the driver manager which driver and data source to use. The standard syntax for a database URL is shown here:

jdbc:SUBPROTOCOL:SUBNAME

The first part of the URL indicates that JDBC is being used to establish the connection. The SUBPROTOCOL is the name of a valid JDBC driver or other database connectivity solution. The SUBNAME is typically a logical name, or alias, that maps to a physical database. Though most database URLs closely follow the standard syntax, JDBC database URL conventions are flexible and allow each driver to define the information that should be included in its URL. To illustrate, Table 16.1 presents the syntax for three common JDBC database URLs. Notice that each type of database driver requires different information within its URL.

Table 16.1 JDBC URLs for Common Data Sources

To establish a database connection, use the DriverManager object's getConnection() method. getConnection() is a static method that can be used like this:

Connection dbConn = DriverManager.getConnection( 
      "jdbc:mysql://localhost/phonebook");

Or, for databases that require authentication, the connection can be established like this:

String username = "Erin", password = "secret"; 
Connection dbConn = DriverManager.getConnection(  
       "jdbc:mysql://localhost/phonebook", username, password);

Within its getConnection() method, the DriverManager queries each registered driver until it finds one that recognizes the specified database URL. Once the correct driver is located, the DriverManager uses it to create the Connection object. The DriverManager and Connection objects (as well as all other JDBC objects) are contained in the java.sql package. Be sure to import this package when using JDBC.

public class JdbcServlet extends HttpServlet

 { 
       //database connection is shared by all requests 
       java.sql.Connection dbConn;  
       public void init() throws ServletException
      { 
             //...open database connection (dbConn) here... 
      }  
}

Execute an SQL Statement

Once established, the database connection can be used to submit SQL statements to the database. An SQL statement performs some operation on the database such as retrieving, inserting, updating, or deleting rows. To execute an SQL command, a Statement object must be created using the Connection object's createStatement() method. The Statement object provides methods to perform various operations against the database. A Statement object can be created as shown here:

Statement stmt = dbConn.createStatement();

Using the Statement object's executeQuery() method, information can be retrieved from the database. The executeQuery() method accepts an SQL SELECT statement and returns a ResultSet object containing the database rows extracted by the query. For inserts, updates, or deletes, use the executeUpdate() method. The ResultSet object can be created like this:

ResultSet rs = stmt.executeQuery("select * from employee");

Process the Results

To process the results, you can traverse the rows of the result set using the ResultSet object's next() and previous() methods (the previous() method is only available in JDBC 2.0 and later using certain types of result sets). The following sample code creates a Statement object, executes a query, and iterates through the result set. The ResultSet object's getString() method is used to extract the value of specific fields.

Statement stmt = dbConn.createStatement();

ResultSet rs = stmt.executeQuery("select * from employee"); 

while (rs.next()) 
{ 
       System.out.println(rs.getString("FIRST_NAME") +" " +
            rs.getString("LAST_NAME"));  
}

It is important to keep in mind that the ResultSet object is tied to the Statement object that created it. If the ResultSet object's Statement is closed or used to execute another query, the ResultSet is closed automatically.

Close the Database Connection

Because database connections are a valuable and limited resource, you should close the connection when processing is complete. The Connection object provides a simple close() method for this purpose.

In addition to closing the database connection, you should also explicitly close all Statement and ResultSet objects using their close() methods. Although the Java virtual machine's garbage collector will eventually release resources that are no longer in scope, it is a good practice to manually release these objects as soon as they are no longer useful. To guarantee that database resources are always released (even in the case of an exception), database connections should be closed within a finally block as shown here:

try 
{ 
//...create statements and result sets here... 

       	rs.close(); //close ResultSet after use 
stmt.close(); //close Statement after use 
} 
catch (SQLException e) 
{ 
 	//...handle exception here... 
} 
finally 
{ 
try 
{ 
if (dbConn "` null) 
{ 
dbConn.close(); //close Connection in finally block 
} 
 	}
catch (SQLException e) 
{ 
 //...handle exception here... 
}  
}

Of course, if you are sharing a database connection across all requests or using a connection-pooling strategy, you may not wish to close the database connection each time it is used. Holding a database connection open eliminates the overhead associated with creating a new connection for each request. For more information on database connection optimization, see the "Database Connection Pooling" section later in this chapter.

JDBC Basics

Creating Database Tables

The Structured Query Language (SQL) is a language used to create, manage, and query relational databases. SQL is largely comprised of two language subsets--the Data Definition Language (DDL) and the Data Manipulation Language (DML). The DDL defines the syntax for creating tables and indexes on an existing database. In contrast, the DML is used to retrieve, insert, delete, and modify data in database tables. This section demonstrates how to create database tables using the DDL. The rest of this chapter focuses on the DML.

The most common DDL statement is the CREATE TABLE command. As you may have guessed, this command is used to create new database tables. The basic syntax to create a database table looks like this:

CREATE TABLE table_name ( 
column1_name data_type    [NULL   |   NOT NULL], 
column2_name data_type    [NULL   |   NOT NULL], 
column3_name data_type    [NULL   |   NOT NULL]  
)

The NULL and NOT NULL optional fields designate whether a column can be left blank when a row is inserted into the table. In addition to the basic syntax presented here, different databases support a wide variety of features. For instance, it is very common for databases to support syntax that describes additional features such as designating indexed fields, default values for columns, auto-incrementing fields, referential integrity constraints (primary and foriegn key definitions), and much more. Other database objects, such as triggers, sequences, and stored procedures, are also commonly supported in high-end relational database management systems (RDBMS).

Listing 16.1 presents the CREATE TABLE commands used to build the database utilized by the sample JDBC servlet presented at the end of this chapter (for improved readability, each column's data type is shown in lowercase). Notice that the ID fields for both tables are declared as auto-incrementing primary key fields. The PRIMARY KEY statement indicates that this column must always contain a unique value. The AUTO_INCREMENT statement indicates that this field will be automatically populated with an increasing sequential number whenever a new row is inserted. Since the database automatically populates AUTO_INCREMENT fields, INSERT statements should either pass these fields a value of NULL or not include them at all. For example, either of the following INSERT statements will allow MySQL to automatically populate the DEPARTMENT_ID field.

INSERT INTO DEPARTMENT VALUES (NULL, 'Human Resources');
INSERT INTO DEPARTMENT (NAME) VALUES ('Sales and Marketing');

For simplicity, no referential integrity constraints are defined between the EMPLOYEE and DEPARTMENT tables (e.g., a foreign key constraint enforcing the rule that any DEPARTMENT_ID in EMPLOYEE must exist in DEPARTMENT ). Keep in mind that portions of Listing 16.1 are specific to the MySQL database (such as the AUTO_INCREMENT statement) and will not work on all databases. The logical database model is shown in Figure 16.6.

Figure 16.6 Logical Database Model

Listing 16.1 MySQL DDL script to create the sample servlet database.

CREATE TABLE DEPARTMENT ( 
DEPARTMENT_ID 	int NOT NULL AUTO_INCREMENT PRIMARY KEY, 
NAME 		varchar(20) NULL 
); 

CREATE TABLE EMPLOYEE ( 
EMPLOYEE_ID 	int NOT NULL AUTO_INCREMENT PRIMARY KEY, 
DEPARTMENT_ID 	int NULL, 
FIRST_NAME 	varchar(20) NULL, 
LAST_NAME 	varchar(20) NULL,
PHONE 		varchar(20) NULL, 
EMAIL 		varchar(50) NULL,
HIRE_DATE 		date NULL 
);

Each database defines its own data types and rules for column names. For instance, most databases allow, at a minimum, column names to consist of any sequence of letters, numbers, and underscores (no spaces or punctuation and often must begin with a letter). However, many databases provide more liberal naming rules (perhaps allowing spaces or some punctuation within column names). Likewise, each database defines its own set of data types. Though the names of data types can vary widely among databases, all databases provide some level of support for basic text and numeric data. High-end databases may support advanced data types like serialized objects or image data. Table 16.2 documents some of the most common data types supported by the MySQL database (see the MySQL documentation for a complete list). MySQL is presented later in this chapter.

In addition to creating tables, it is also possible to alter and drop (i.e., delete) tables using the ALTER TABLE and DROP TABLE commands. Check your database's documentation for a complete list of supported DDL commands.

Retrieving Data

Retrieving rows from the database using an SQL SELECT statement is the most common JDBC operation. As previously demonstrated, a ResultSet can be retrieved by passing an SQL SELECT statement to the Statement object's executeQuery() method. The definition for this method looks like this:

public java.sql.ResultSet executeQuery(String sql) throws
java.sql.SQLException

Rows contained in a ResultSet object can be traversed using the next() and previous() methods. These methods return a boolean value indicating whether the current row is valid. For instance, next() returns false when it moves past the final row in the result set. Also keep in mind that ResultSet objects are always initially positioned before the first row. Therefore, the ResultSet object's next() method must be called before any column values can be retrieved.

Table 16.2 Common Data Types Supported by MySQL

The previous() method allows you to move backward through a result set, but this method often does not work. For instance, since previous() was introduced in JDBC 2.0, older JDBC drivers do not support this functionality. Additionally, the previous() method works only with scrollable result sets (many result sets are defined as forward only). Therefore, when using previous() , you should always verify that your JDBC driver supports the JDBC 2.0 or later specification. And if your JDBC driver does support JDBC 2.0 functionality, you must also verify that the resultSet object is scrollable by calling its getType() method like this:

if (set.getType() "` ResultSet.TYPE_FORWARD_ONLY)
{ 
 	//...set.previous() may be safely called here...  
}

After retrieving a database row using the next() or previous() method, the values of each column in the row can be extracted using the ResultSet object's getObject() method or any of the data type-specific method calls. For example, the getString() method returns a String object and can be used with most data types, including dates and numeric values (of course, the date or numeric value will be returned as a String ). Similarly, methods such as getInt(), getLong(), getFloat(), and getDouble() return the Java primitives int, long, float, and double, respectively. Lastly, special methods like getDate() , getTime() , and getTimestamp() return Java objects of type java.sql.Date , java.sql.Time, and java.sql.Timestamp. Table 16.3 documents the appropriate ResultSet method call for each SQL data type as well as the type of object returned when a column's value is retrieved using the getObject() method.

Table 16.3 Methods Defined by the ResultSet Object for Retrieving Data

The getObject() method and all if the data type-specific "get" methods accept a single parameter--either a string containing the column name or an integer representing the index number corresponding to the column whose value should be returned. Since specifying the column name makes for more readable code, that method is used most often throughout this book. However, there are times that you may need to iterate through all of the columns of a result set. In this case, being able to specify column by index number can be very useful.

The following sample code demonstrates how rows can be retrieved from the EMPLOYEE and DEPARTMENT tables defined in Listing 16.2. Notice how the HIRE_DATE field is retrieved as a java.sql.Date object. Figure 16.7 shows the output produced by the showEmployees servlet.

Figure 16.7 Output from the ShowEmployees Servlet

Listing 16.2 ShowEmployees servlet retrieves rows from a database.

Import   javax.servlet.*; 
import   javax.servlet.http.*; 
import   java.io.*;
import   java.sql.*; 
import   java.text.DateFormat; 

/** 
 * ShowEmployees creates an HTML table containing a list of all 
 * employees (sorted by last name) and the departments to which 
 * they belong.
 */ 
public class ShowEmployees extends HttpServlet 
{ 
Connection dbConn = null; 
/** 
* Establishes a connection to the database.
*/ 
public void init() throws ServletException 
{ 
String jdbcDriver = "org.gjt.mm.mysql.Driver"; 
String dbURL ="jdbc:mysql://localhost/phonebook";

try 
{ 
Class.forName(jdbcDriver) .newInstance() ; //load driver 
dbConn = DriverManager.getConnection(dbURL); //connect
} 
catch(ClassNotFoundException e)
{ 
throw new UnavailableException("JDBC driver not found:" +
jdbcDriver); 
} 
catch (SQLException e) 
{ 
throw new UnavailableException("Unable to connect to: " + 
dbURL); 
} 
catch (Exception e) 
{ 
throw new UnavailableException("Error: " + e);
} 
} 
  
/** 
 * Displays the employees table. 
 */ 

public void service(HttpServletRequest request,
HttpServletResponse response) throws ServletException,
IOException 
{ 
response.setContentType("text/html"); 

PrintWriter out = response.getWriter();

 	try 
{ 
//join EMPLOYEE and DEPARTMENT tables to get all data
String sql = "select * from EMPLOYEE,DEPARTMENT where " +
"EMPLOYEE.DEPARTMENT_ID = DEPARTMENT.DEPARTMENT_ID " + 
"order by LAST_NAME,FIRST_NAME,DEPARTMENT.NAME";

Statement stmt = dbConn.createStatement(); 
ResultSet rs = stmt.executeQuery(sql);

out.println("<HTML>"); 
out.println("<HEAD><TITLE>Show Employees</TITLE></HEAD>");
out.println("<BODY>"); 
out.println("<TABLE BORDER=\"1\" CELLPADDING=\"3\">"); 
out.println("<TR>"); 
out.println("<TH>Name</TH>"); 

out.println("<TH>Department</TH>"); 
out.println("<TH>Phone</TH>"); 
out.println("<TH>Email</TH>"); 

out.println("<TH>Hire Date</TH>"); 
out.println("</TR>"); 

while (rs.next()) 
{ 
out.println("<TR>"); 
out.println("<TD>" + rs.getString("LAST_NAME") + ", " +   
     rs.getString("FIRST_NAME") + "</TD>");
out.println("<TD>" + rs.getString("NAME") + "</TD>"); 
out.println("<TD>" + rs.getString("PHONE") + "</TD>"); 
out.println("<TD>"+ rs.getString("EMAIL") + "</TD>"); 

java.sql.Date hireDate = rs.getDate("HIRE_DATE"); 
DateFormat longDate = DateFormat.getDateInstance( 
                              	     DateFormat.LONG); 
out.println("<TD>" + longDate.format(hireDate) +
  			      "</TD>"); 
out.println("</TR>");
}
 
out.println("</TABLE>"); 
out.println("</BODY></HTML>"); 

rs.close(); 
stmt.close(); 
} 
catch (SQLException e) 
{ 
out.println("<H2>Database currently unavailable.</H2>"); 
} 
out.close(); 

     } 

}

If you do not know beforehand if your SQL statement will retrieve rows or perform an insert/update/delete operation, you can use the Statement object's execute() method. The definition for this method looks like this:

public boolean execute(String sql) throws SQLException

The execute() method returns true if the SQL statement generated a result set. If true, use the Statement object's getResultSet() method to retrieve the ResultSet object; otherwise, use getUpdateCount() to determine the number of rows affected by the SQL operation.

In addition to supporting a wide array of SQL commands, the execute() method can be useful when receiving multiple result sets. If an SQL statement generates multiple result sets, the first set can be retrieved using the getResultSet() method. All subsequent result sets can be acquired by first calling getMoreResults() followed by the getResultSet() method. Each time getMoreResults() is called, it makes the next result set available via the getResultSet() method. When no more result sets are available, getMoreResults() returns false.

Inserting Rows

Rows can be inserted into a database table using the ResultSet object's executeUpdate() method. This method is also used to update and delete rows. The definition for executeUpdate() looks like this:

public int executeUpdate(String sql) throws SQLException

The executeUpdate() method returns the number of rows that were affected by the insert, update, or delete operation. Listing 16.3 presents a Java application that demonstrates how a row can be inserted into a database table using executeUpdate().

Listing 16.3 InsertExample inserts a row into the database.

import java.sql.*; 
import java.text.DateFormat; 

public class InsertExample
{ 
public static void main(String[] args) 
{
String jdbcDriver = "org.gjt.mm.mysql.Driver"; 
String dbURL ="jdbc:mysql://localhost/phonebook"; 
Connection dbConn = null; 

try 
{ 
Class.forName(jdbcDriver) .newInstance() ; 
dbConn = DriverManager.getConnection(dbURL); 
Statement stmt = dbConn.createStatement();

String sql = "insert into EMPLOYEE values (NULL, 1, " + 
"'John', 'Public', '555-2222', 'johnp@email.com', "+
"'2000-12-28')"; 

stmt.executeUpdate(sql); 

stmt.close(); 
} 
catch (ClassNotFoundException e) 
{ 
System.out.println("JDBC driver not found: " + jdbcDriver); 
} 
catch	(SQLException e) 
{ 
System.out.println("Error retrieving data from: " + dbURL); 
} 
catch (Exception e) 
{ 
System.out.println("Error: " + e); 
} 
finally 
{ 
try 
{ 
if (dbConn "` null) 
{ 
dbConn.close(); 
} 
} 
catch (SQLException ignored) {} 
} 
} 
}

In addition to manually executing an SQL INSERT statement, JDBC 2.0 defines a new, more object-oriented way to insert rows into a database. This new method involves retrieving a ResultSet object, moving to the insert row of the result set, updating the insert row's fields, and inserting the row into the result set and the database. The insert row is a special row that serves as a buffer for new information until the row can be committed to the database. The following sample code demonstrates how to insert rows into a database table using the special insert row.

String sql = "select * from EMPLOYEE";

Statement stmt = dbConn.createStatement(); 
ResultSet rs = stmt.executeQuery(sql);

rs.moveToInsertRow(); //move to insert row

//update all columns in insert row 
rs.updateString("EMPLOYEE_ID", null); 
rs.updateInt("DEPARTMENT_ID", 1);
rs.updateString("FIRST_NAME", "John"); 
rs.updateString("LAST_NAME","Public"); 
rs.updateString("PHONE", "555-2222"); 
rs.updateString("EMAIL", "johnp@email.com"); 
rs.updateString("HIRE_DATE", "2000-12-28"); 
   
rs.insertRow(); //insert row into ResultSet and database

That's all there is to it. The insert row makes it extremely easy to add rows to an existing result set. However, remember that this functionality is available only when using a JDBC 2.0-compliant driver and an updatable result set.

Updating Rows

Database rows can be updated using the Statement object's executeUpdate() method. This method returns the number of rows affected by the update operation. Listing 16.4 presents a Java application that demonstrates how database rows can be updated using executeUpdate() .

Listing 16.4 UpdateExample updates rows in the database.

import java.sql.*; 
import java.text.DateFormat; 

public class UpdateExample
{ 
public static void main(String[] args) 
{
String jdbcDriver = "org.gjt.mm.mysql.Driver"; 
String dbURL ="jdbc:mysql://localhost/phonebook";
Connection dbConn = null; 

try 
{ 
Class.forName(jdbcDriver) .newInstance() ; 
dbConn = DriverManager.getConnection(dbURL);
			Statement stmt = dbConn.createStatement();

//move all employees in department 3 to department 4 
String sql = "update EMPLOYEE set DEPARTMENT_ID = 4 " + 
 			   "where DEPARTMENT_ID = 3"; 

int numRows = stmt.executeUpdate(sql);

System.out.println(numRows + " rows updated."); 

stmt.close(); 
} 
catch	(ClassNotFoundException e) 
{ 
System.out.println("JDBC driver not found: " + jdbcDriver);
} 
catch (SQLException e) 
{ 
System.out.println("Error retrieving data from: " + dbURL); 
} 
catch (Exception e) 
{ 
System.out.println("Error: " + e); 
} 
finally 
{ 
try 
{ 
if (dbConn "` null) 
{ 
dbConn.close(); 
} 

} 
catch (SQLException ignored) {} 
}
} 

}

In addition to manually executing an SQL UPDATE statement, JDBC 2.0 defines a new, more object-oriented way to update rows in a database. This new method involves retrieving a ResultSet object, moving to the row to be updated, updating the row's fields, and committing the update to the result set and the database. The following sample code demonstrates this process.

String sql = "select * from EMPLOYEE";

Statement stmt = dbConn.createStatement(); 
ResultSet rs = stmt.executeQuery(sql); 

//move all employees in department 3 to department 4 
while (rs.next()) 
{ 
if (rs.getInt("DEPARTMENT_ID") == 3) 
{ 
rs.updateInt("DEPARTMENT_ID", 4); 
rs.updateRow(); 
}  
}

The previous example illustrates how the updateRow() method can be used to update rows in a database. However, in this case, this method is extremely slow. When performing a bulk update (i.e., you are making the same change to multiple records), it is much more efficient to send an SQL UPDATE command to the database and allow it to update multiple rows rather than to iterate through each row on the client. On the other hand, when the number of rows needing to be updated is small or the update criteria change with each row, this object-oriented method of updating database rows can be very convenient. Again, remember that this functionality is available only when using a JDBC2.0-compliant driver and an updatable result set.

Deleting Rows

Database rows can be deleted using the Statement object's executeUpdate()method. This method returns the number of rows affected by the delete operation. Listing 16.5 presents a Java application that demonstrates how database rows can be deleted using executeUpdate().

Listing 16.5 DeleteExample deletes rows in the database.

import java.sql.*; 
import java.text.DateFormat; 

 public class DeleteExample
{ 
public static void main(String[] args) 
{
String jdbcDriver = "org.gjt.mm.mysql.Driver"; 
String dbURL ="jdbc:mysql://localhost/phonebook";
Connection dbConn = null; 

try 
{ 
Class.forName(jdbcDriver) .newInstance() ; 
dbConn = DriverManager.getConnection(dbURL); 
Statement stmt = dbConn.createStatement();

//delete all employees in department 1
String sql = "delete from EMPLOYEE where DEPARTMENT_ID=1"; 

int numRows = stmt.executeUpdate(sql); 

System.out.println(numRows + " rows deleted.");

stmt.close(); 
} 
catch (ClassNotFoundException e)
{ 
System.out.println("JDBC driver not found: " + jdbcDriver);
} 
catch (SQLException e) 
{ 
System.out.println("Error retrieving data from: " + dbURL); 
} 
finally 
{ 
try 
{ 
if (dbConn "` null) 
{ 
dbConn.close(); 
} 
} 
catch (SQLException ignored) {} 
}
 } 
}

In addition to manually executing an SQL DELETE statement, JDBC 2.0 defines a new, more object-oriented way to delete rows from a database. This new method involves retrieving a ResultSet object, moving to the row to be deleted, and executing the deleteRow() method. The following sample code demonstrates this process.

String sql = "select * from EMPLOYEE";

Statement stmt = dbConn.createStatement(); 
ResultSet rs = stmt.executeQuery(sql);
//delete all employees in department 1 
while (rs.next()) 
{ 
if (rs.getInt("DEPARTMENT_ID") == 1) 
{
rs.deleteRow(); 
}  
}

The previous example illustrates how the deleteRow() method can be used to delete rows from a database. However, in this case, this method is extremely slow. When performing a bulk delete (i.e., you are deleting multiple records based on the same criteria), it is much more efficient to send an SQL DELETE command to the database and allow it to delete multiple rows rather than iterate through each row on the client. In addition, the sample code shown here may not always work as expected. Deleting rows from an existing result set can disrupt the iteration process and result in some rows being skipped. These are important concerns to keep in mind when evaluating the deleteRow() method. Regardless, when the number of rows needing to be deleted is small or the delete criteria change with each row, this object-oriented method of deleting database rows may come in handy. Again, remember that this functionality is available only when using a JDBC 2.0-compliant driver and an updatable result set.

SQL Exceptions

Every method defined by the Connection , Statement , and ResultSet objects throws java.sql.SQLException . Therefore, whenever these objects are used, you must either catch SQLException or throw it. In most cases, as demonstrated throughout this chapter, you should simply catch this exception and handle it in some manner. This may involve returning an error message to the client, logging the error, or just throwing a javax.servlet.UnavailableException indicating that your servlet is not available.

SQLException is similar to other exceptions, except for the fact that it can store a vendor-specific error code, an "SQLState" string (following the XOPEN SQLstate conventions), and nested exceptions. This additional information can be very useful when attempting to troubleshoot database connectivity issues. When a database error occurs, the SQLException object is populated with a vendor-specific error code indicating the error returned by the underlying database. This error code can be retrieved using the getErrorCode() method. The SQLState string can be obtained with the getSQLState() method. See the XOPEN SQL specification for more information regarding the format of the SQLState string. Lastly, a chain of nested exceptions can be encapsulated in an SQLException object. The getNextException() method returns the next SQLException object or null if the end of the chain has been reached. The getNextException() method can be used like this:

catch (SQLException e)
{ 
int count = 1; //exception counter 
 
//first exception
out.println("Exception " + count + ": " + e.getMessage()); 

//chained exceptions 
while ((e = e.getNextException()) "` null) 
{ 
count++; 
out.println("Exception " + count + ": " + e.getMessage()); 
}  
}

The previous code returns the first SQLException as well as all chained exceptions to the client. Though each exception in the chain can offer clues as to the root cause of the database error, the first exception usually contains the most specific information.

When extracting data from an SQLException object, keep in mind that it is up to the JDBC driver and the database to return relevant error information (including an error code, SQLState information, and chained exceptions). Different drivers may provide various amounts of error information.