Chapter 44
JDBC

by Krishna Sankar

JDBC is a Java database connectivity API that is a part of the Java Enterprise APIs from JavaSoft. From a developer's point of view, JDBC is the first standardized effort to integrate relational databases with Java programs. JDBC has opened all the relational power that can be mustered to Java applets and applications. In this chapter and the next, you take an in-depth look at the JDBC classes and methods.

JDBC Overview

JDBC is Java database connectivity-- a set of relational database objects and methods for interacting with SQL data sources. The JDBC APIs are part of the Enterprise APIs of Java 1.1 and, thus, are a part of all Java Virtual Machine ( JVM) implementations.


TIP: Even though the objects and methods are based on the relational database model, JDBC makes no assumption about the underlying data source or the data storage scheme. You can access and retrieve audio or video data from many sources and load into Java objects using the JDBC APIs. The only requirement is that there should be a JDBC implementation for that source.


JavaSoft introduced the JDBC API specification in March 1996 as a draft Version 0.50 and open for public review. The specification went from Version 0.50 through 1.0 to 1.10 and now to 1.22. The JDK 1.1 includes JDBC. So you need not download JDBC separately. The JDBC Version 1.22 specification available at http://splash.javasoft.com/jdbc/ includes all of the improvements from the review by vendors, developers, and the general public.


NOTE: The JDBC Web site has four important documents related to the JDBC specification. They are JDBC Specification (jdbc.spec-0122.pdf), JDBC API documentation Part I--JDBC interfaces (jdbc.api.1-0122.pdf), and JDBC API documentation Part II--Classes and exceptions (jdbc.api.2-0122.pdf). Also available with the JDK 1.1 documentation (jdbc.pdf) is the JDBC Guide: Getting Started.


Now look at the origin and design philosophies. The JDBC designers based the API on X/Open SQL Call Level Interface (CLI). It is not coincidental that ODBC is also based on the X/Open CLI. The JavaSoft engineers wanted to gain leverage from the existing ODBC implementation and development expertise, thus making it easier for independent software vendors (ISVs) and system developers to adopt JDBC. But ODBC is a C interface to Database Management Systems (DBMS) and thus is not readily convertible to Java. So JDBC design followed ODBC in spirit as well as in its major abstractions and implemented the SQL CLI with "a Java interface that is consistent with the rest of the Java system," as the JDBC specification describes it in Section 2.4. For example, instead of the ODBC SQLBindColumn and SQLFetch to get column values from the result, JDBC used a simpler approach (which you learn about later in this chapter).

How Does JDBC Work?

As previously discussed, JDBC is designed on the CLI model. JDBC defines a set of API objects and methods to interact with the underlying database. A Java program first opens a connection to a database, makes a statement object, passes SQL statements to the underlying DBMS through the statement object, and retrieves the results as well as information about the result sets. Typically, the JDBC class files and the Java applet reside in the client. They can be downloaded from the network also. To minimize the latency during execution, it is better to have the JDBC classes in the client. The DBMS and the data source are typically located in a remote server.

Figure 44.1 shows the JDBC communication layer alternatives. The applet and the JDBC layers communicate in the client system, and the driver takes care of interacting with the database over the network.

FIG. 44.1
JDBC communication layer alternatives: The JDBC driver can be a native library, like the JDBC-ODBC bridge, or a Java class talking across the network to a RPC or HTTP listener process in the database server.

The JDBC classes are in the java.sql package, and all Java programs use the objects and methods in the java.sql package to read from and write to data sources. A program using the JDBC will need a driver for the data source with which it wants to interface. This driver can be a native module (like the JDBCODBC.DLL for the Windows JDBC-ODBC Bridge developed by Sun/Intersolv), or it can be a Java program that talks to a server in the network using some RPC or a HT TP talker-listener protocol. Both schemes are shown in Figure 44.1.

It is conceivable that an application will deal with more than one data source--possibly heterogeneous data sources. (A database gateway program is a good example of an application that accesses multiple heterogeneous data sources.) For this reason, JDBC has a DriverManager whose function is to manage the drivers and provide a list of currently loaded drivers to the application programs.


Data Source, Database, or DBMS?
Even though the word "database" is in the name JDBC, the form, content, and location of the data is immaterial to the Java program using JDBC so long as there is a driver for that data. Hence the notation data source to describe the data is more accurate than "database," "DBMS," "DB," or just "file." In the future, Java devices such as televisions, answering machines, or network computers will access, retrieve, and manipulate different types of data (audio, video, graphics, time series, and so on) from various sources that are not relational databases at all. Much of the data might not even come from mass storage. For example, the data could be video stream from a satellite or audio stream from a telephone.


ODBC also refers to data sources rather than databases when describing in general terms.

Security Model

Security is always an important issue, especially when databases are involved. As of the writing of this book, JDBC follows the standard security model in which applets can connect only to the server from where they are loaded; remote applets cannot connect to local databases. Applications have no connection restrictions. For pure Java drivers, the security check is automatic, but, for drivers developed in native methods, the drivers must have some security checks.


NOTE: With Java 1.1 and the Java Security API, you have the ability to establish "trust relationships," which allows you to verify trusted sites. You can then give applets downloaded from trusted sources more functionality by giving them access to local resources. For more information on Java security, refer to Chapter 36, "Java Security in Depth".


JDBC-ODBC Bridge

As a part of JDBC, JavaSoft also delivers a driver to access ODBC data sources from JDBC. This driver is jointly developed with Intersolv and is called the JDBC-ODBC bridge. The JDBC-ODBC bridge is implemented as the JdbcOdbc.class and a native library to access the ODBC driver. For the Windows platform, the native library is a DLL (JDBCODBC.DLL).

As JDBC is close to ODBC in design, the ODBC bridge is a thin layer over JDBC. Internally, this driver maps JDBC methods to ODBC calls and, thus, interacts with any available ODBC driver. The advantage of this bridge is that now JDBC has the capability to access almost all databases, as ODBC drivers are widely available. You can use this bridge (Version 1.2001) to run the example programs in this and the next chapter.

JDBC Implementation

JDBC is implemented as the java.sql package. This package contains all of the JDBC classes and methods, as shown in Table 44.1.
Table 44.1 JDBC Classes
Type Class
Driver java.sql.Driver java.sql.DriverManager java.sql.DriverPropertyInfo
Connection java.sql.Connection
Statements java.sql.Statement java.sql.PreparedStatement java.sql.CallableStatement
ResultSet java.sql.ResultSet
Errors/Warning java.sql.SQLException java.sql.SQLWarning
Metadata java.sql.DatabaseMetaData java.sql.ResultSetMetaData
Date/Time java.sql.Date java.sql.Time java.sql.Timestamp
Miscellaneous java.sql.Types java.sql.DataTruncation

Now look at these classes and see how you can develop a simple JDBC application.

JDBC Classes--Overview

When you look at the class hierarchy and methods associated with it, the topmost class in the hierarchy is the DriverManager. The DriverManager keeps the driver information, state information, and more. When each driver is loaded, it registers with the DriverManager. The DriverManager, when required to open a connection, selects the driver depending on the JDBC URL.


JDBC URL
True to the nature of the Internet, JDBC identifies a database with an URL. The URL is of the form:

jdbc:<subprotocol>:<subname related to the DBMS/Protocol>




For databases on the Internet or intranet, the subname can contain the Net URL //hostname:port/ The <subprotocol> can be any name that a database understands. The odbc subprotocol name is reserved for ODBC style data sources. A normal ODBC database JDBC URL looks like the following:

jdbc:odbc:<ODBC DSN>;User=<username>;PW=<password>

If you are developing a JDBC driver with a new subprotocol, it is better to reserve the subprotocol name with JavaSoft, which maintains an informal subprotocol registry.


The java.sql.Driver class is usually referred to for information such as PropertyInfo, version number, and so on. This class could be loaded many times during the execution of a Java program using the JDBC API.

Looking at the java.sql.Driver and java.sql.DriverManager classes and methods as listed in Table 44.2, you see that the DriverManager returns a Connection object when you use the getConnection() method.
Table 44.2 Driver, DriverManager and Related Methods
Return Type Method Name Parameter
java.sql.Driver
Connection connect (String url, java.util.Properties info)
boolean acceptsURL (String url)
DriverPropertyInfo[] getPropertyInfo (String url, java.util.Properties info)
int getMajorVersion ()
int getMinorVersion ()
boolean jdbcCompliant ()
java.sql.DriverManager
Connection getConnection (String url, java.util.Properties info)
Connection getConnection (String url, String user, String password)
Connection getConnection (String url)
Driver getDriver (String url)
void registerDriver (java.sql.Driver driver)
void deregisterDriver (Driver driver)
java.util.Enumeration getDrivers ()
void setLoginTimeout (int seconds)
int getLoginTimeout ()
void setLogStream (java.io.PrintStream out)
java.io.PrintStream getLogStream ()
void println (String message)
Class Initialization Routine
void initialize ()
Other useful methods include the registerDriver(), deRegister(), and getDrivers() methods. By using the getDrivers() method, you can get a list of registered drivers. Figure 44.2 shows the JDBC class hierarchy as well as the flow of a typical Java program using the JDBC APIs.

In the next section, you'll follow the steps required to access a simple database access using JDBC and the JDBC-ODBC driver.

Anatomy of a JDBC Application

To handle data from a database, a Java program follows the following general steps. Figure 44.2 shows the general JDBC objects, the methods, and the sequence. First, the program calls the getConnection() method to get the Connection object. Then it creates the Statement object and prepares a SQL statement.

FIG. 44.2
JDBC class hierarchy and a JDBC API flow.

A SQL statement can be executed immediately (Statement object), can be a compiled statement (PreparedStatement object), or can be a call to a stored procedure (CallableStatement object). When the method executeQuery() is executed, a ResultSet object is returned. SQL statements such as update or delete will not return a ResultSet. For such statements, the executeUpdate() method is used. The executeUpdate() method returns an integer that denotes the number of rows affected by the SQL statement.

The ResultSet contains rows of data that is parsed using the next() method. In case of a transaction processing application, methods such as rollback() and commit() can be used either to undo the changes made by the SQL statements or permanently affect the changes made by the SQL statements.

JDBC Examples

These examples access the Student database, the schema of which is shown in Figure 44.3. The tables in the examples that you are interested in are the Students table, Classes table, Instructors table, and Students_Classes table. This database is a Microsoft Access database. The full database and sample data is generated by the Access Database Wizard. You access the database using JDBC and the JDBC-ODBC bridge (Beta Version 1.0005).

FIG. 44.3
JDBC example database schema.

Before you jump into writing a Java JDBC program, you need to configure an ODBC data source. As you saw earlier, the getConnection() method requires a data source name (DSN), user ID, and password for the ODBC data source. The database driver type or subprotocol name is odbc. So the driver manager finds out from the ODBC driver the rest of the details.

But wait, where do you put the rest of the details? This is where the ODBC setup comes into the picture. The ODBC Setup program runs outside the Java application from the Microsoft ODBC program group. The ODBC Setup program allows you to set up the data source so that this information is available to the ODBC Driver Manager, which in turn loads the Microsoft Access ODBC driver. If the database is in another DBMS form--say, Oracle--you configure this source as Oracle ODBC driver. In Windows 3.x, the Setup program puts this information in the ODBC.INI file. With Windows 95 and Windows NT 4.0, this information is in the Registry. Figure 44.4 shows the ODBC setup screen.

FIG. 44.4
ODBC Setup for the Example database. After this setup, the example database URL is jdbc:odbc:Student DB;uid="admin";pw="sa".

JDBC Query Example In this example, you list all of the students in the database with a SQL SELECT statement. The steps required to accomplish this task using the JDBC API are listed here. For each step, the Java program code with the JDBC API calls follows the description of the steps.

//Declare a method and some variables.
public void ListStudents() throws SQLException {
   int i, NoOfColumns;
   String StNo,StFName,StLName;
   //Initialize and load the JDBC-ODBC driver.
   Class.forName ("jdbc.odbc.JdbcOdbcDriver");
   //Make the connection object.
   Connection Ex1Con = DriverManager.getConnection( "jdbc:odbc:StudentDB;uid="admin";pw="sa");
   //Create a simple Statement object.
   Statement Ex1Stmt = Ex1Con.createStatement();
   //Make a SQL string, pass it to the DBMS, and execute the SQL statement.
   ResultSet Ex1rs = Ex1Stmt.executeQuery(
     "SELECT StudentNumber, FirstName, LastName FROM Students");
   //Process each row until there are no more rows.
   // Displays the results on the console.
   System.out.println("Student Number        First Name      Last Name");
   while (Ex1rs.next()) {
      // Get the column values into Java variables
      StNo = Ex1rs.getString(1);
      StFName = Ex1rs.getString(2);
      StLName = Ex1rs.getString(3);
      System.out.println(StNo,StFName,StLName);
      }
   }

As you can see, it is a simple Java program using the JDBC API. The program illustrates the basic steps needed to access a table and lists some of the fields in the records.

JDBC Update Example
In this example, you update the FirstName field in the Students table by knowing the student's StudentNumber. As in the last example, the code follows the description of the step.

//Declare a method and some variables and parameters.
public void UpdateStudentName(String StFName, String StLName,
   String StNo) throws SQLException {
   int RetValue;
   // Initialize and load the JDBC-ODBC driver.
   Class.forName ("jdbc.odbc.JdbcOdbcDriver");
   // Make the connection object.
   Connection Ex1Con = DriverManager.getConnection( "jdbc:odbc:StudentDB;uid="admin";pw="sa");
   // Create a simple Statement object.
   Statement Ex1Stmt = Ex1Con.createStatement();
   //Make a SQL string, pass it to the DBMS, and execute the SQL statement
   String SQLBuffer = "UPDATE Students SET FirstName = "+
   StFName+", LastName = "+StLName+
     " WHERE StudentNumber = "+StNo
   RetValue = Ex1Stmt.executeUpdate( SQLBuffer);
   System.out.println("Updated " + RetValue + " rows in the Database.");
   }

In this example, you execute the SQL statement and get the number of rows affected by the SQL statement back from the DBMS.

The previous two examples show how you can do simple yet powerful SQL manipulation of the underlying data using the JDBC API in a Java program. In the following sections, you examine each JDBC class in detail.

The Connection Class

The Connection class is one of the major classes in JDBC. It packs a lot of functionality, ranging from transaction processing to creating statements, in one class as seen in Table 44.3.
Table 44.3 java.sql.Connection Methods and Constants
Return Type Method Name Parameter
Statement-Related Methods
Statement createStatement ()
PreparedStatement prepareStatement (String sql)
CallableStatement prepareCall (String sql)
String nativeSQL (String sql)
void close ()
boolean isClosed ()
Metadata-Related Methods
DatabaseMetaData getMetaData ()
void setReadOnly (boolean readOnly)
boolean isReadOnly ()
void setCatalog (String catalog)
String getCatalog ()
SQLWarning getWarnings ()
void clearWarnings ()
Transaction-Related Methods
void setAutoCommit (boolean autoCommit)
boolean getAutoCommit ()
void commit ()
void rollback ()
void setTransactionIsolation (int level)
int getTransactionIsolation ()
The TransactionIsolation constants are defined in the java.sql.Connection as integers with the following values:
TransactionIsolation Constant Name Value
TRANSACTION_NONE 0
TRANSACTION_READ_UNCOMMITTED 1
TRANSACTION_READ_COMMITTED 2
TRANSACTION_REPEATABLE_READ 4
TRANSACTION_SERIALIZABLE 8
As you saw earlier, the connection is for a specific database that can be interacted with in a specific subprotocol. The Connection object internally manages all aspects about a connection, and the details are transparent to the program. Actually, the Connection object is a pipeline into the underlying DBMS driver. The information to be managed includes the data source identifier, the subprotocol, the state information, the DBMS SQL execution plan ID or handle, and any other contextual information needed to interact successfully with the underlying DBMS.


NOTE: The data source identifier could be a port in the Internet database server that is identified by the //<server name>:port/... URL or just a data source name used by the ODBC driver or a full path name to a database file in the local computer. For all you know, it could be a pointer to data feed of the stock market prices from Wall Street.


Another important function performed by the Connection object is the transaction management. The handling of the transactions depends on the state of an internal autocommit flag that is set using the setAutoCommit() method, and the state of this flag can be read using the getAutoCommit() method. When the flag is true, the transactions are automatically committed as soon as they are completed. There is no need for any intervention or commands from the Java application program. When the flag is false, the system is in the manual mode. The Java program has the option to commit the set of transactions that happened after the last commit or rollback the transactions using the commit() and rollback() methods.


NOTE: JDBC also provides methods for setting the transaction isolation modularity. When you are developing multi-tiered applications, there will be multiple users performing concurrently interleaved transactions that are on the same database tables. A database driver has to employ sophisticated locking and data buffering algorithms and mechanisms to implement the transaction isolation required for a large-scale JDBC application. This is more complex when there are multiple Java objects working on many databases that could be scattered across the globe. Only time will tell what special needs for transaction isolation there will be in the new Internet/intranet paradigm.


Once you have a successful Connection object to a data source, you can interact with the data source in many ways. The most common approach, from an application developer standpoint, is the objects that handle the SQL statements. In JDBC, there are three main types of statements:

The Connection object has the createStatement(), prepareStatement(), and prepareCall() methods to create these statement objects. Chapter 45, "JDBC Explored," deals with the statement-type objects in detail.

Another notable method in the Connection object is the getMetadata() method that returns an object of the DatabaseMetaData type, which is the topic for the following section.

Metadata Functions

Speaking theoretically, metadata is information about data. The MetaData methods are mainly aimed at the database tools and wizards that need information about the capabilities and structure of the underlying DBMS. Many times these tools need dynamic information about the resultset, which a SQL statement returns. JDBC has two classes of metadata: ResultSetMetaData and DatabaseMetadata. As you can see from the method tables, a huge number of methods are available in this class of objects.

DatabaseMetaData

DatabaseMetaDatas are similar to the catalog functions in ODBC, where an application queries the underlying DBMS's system tables and gets information. ODBC returns the information as a result set. JDBC returns the results as a ResultSet object with well-defined columns.

The DatabaseMetaData object and its methods give a lot of information about the underlying database. This information is more useful for database tools, automatic data conversion, and gateway programs. Table 44.4 gives all of the methods for the DatabaseMetaData object. As you can see, it is a very long table with more than 100 methods. Unless they are very exhaustive GUI tools, most of the programs will not use all of the methods. But, as a developer, there will be times when one needs to know some characteristic about the database or see whether a feature is supported. It is those times when the following table comes in handy.
Table 44.4 DatabaseMetaData Methods
Return Type Method Name Parameter
boolean allProceduresAreCallable ()
boolean allTablesAreSelectable ()
String getURL ()
String getUserName ()
boolean isReadOnly ()
boolean nullsAreSortedHigh ()
boolean nullsAreSortedLow ()
boolean nullsAreSortedAtStart ()
boolean nullsAreSortedAtEnd ()
String getDatabaseProductName ()
String getDatabaseProductVersion ()
String getDriverName ()
String getDriverVersion ()
int getDriverMajorVersion ()
int getDriverMinorVersion ()
boolean usesLocalFiles ()
boolean usesLocalFilePerTable ()
boolean supportsMixedCaseIdentifiers ()
boolean storesUpperCaseIdentifiers ()
boolean storesLowerCaseIdentifiers ()
boolean storesMixedCaseIdentifiers ()
boolean supportsMixedCaseQuotedIdentifiers ()
boolean storesUpperCaseQuotedIdentifiers ()
boolean storesLowerCaseQuotedIdentifiers ()
boolean storesMixedCaseQuotedIdentifiers ()
String getIdentifierQuoteString ()
String getSQLKeywords ()
String getNumericFunctions ()
String getStringFunctions ()
String getSystemFunctions ()
String getTimeDateFunctions ()
String getSearchStringEscape ()
String getExtraNameCharacters ()
boolean supportsAlterTableWithAddColumn ()
boolean supportsAlterTableWithDropColumn ()
boolean supportsColumnAliasing ()
boolean nullPlusNonNullIsNull ()
boolean supportsConvert ()
boolean supportsConvert (int fromType, int toType)
boolean supportsTableCorrelationNames ()
boolean supportsDifferentTableCorrelation ()
Names
boolean supportsExpressionsInOrderBy ()
boolean supportsOrderByUnrelated ()
boolean supportsGroupBy ()
boolean supportsGroupByUnrelated ()
boolean supportsGroupByBeyondSelect ()
boolean supportsLikeEscapeClause ()
boolean supportsMultipleResultSets ()
boolean supportsMultipleTransactions ()
boolean supportsNonNullableColumns ()
boolean supportsMinimumSQLGrammar ()
boolean supportsCoreSQLGrammar ()
boolean supportsExtendedSQLGrammar ()
boolean supportsANSI92EntryLevelSQL ()
boolean supportsANSI92IntermediateSQL ()
boolean upportsANSI92FullSQL ()
boolean supportsIntegrityEnhancement ()
Facility
boolean supportsOuterJoins ()
boolean supportsFullOuterJoins ()
boolean supportsLimitedOuterJoins ()
String getSchemaTerm ()
String getProcedureTerm ()
String getCatalogTerm ()
boolean isCatalogAtStart ()
String getCatalogSeparator ()
boolean supportsSchemasInDataManipulation ()
boolean supportsSchemasInProcedureCalls ()
boolean supportsSchemasInTableDefinitions ()
boolean supportsSchemasInIndexDefinitions ()
boolean supportsSchemasInPrivilege ()
Definitions
boolean supportsCatalogsInDataManipulation ()
boolean supportsCatalogsInProcedureCalls ()
boolean supportsCatalogsInTableDefinitions ()
boolean supportsCatalogsInIndexDefinitions ()
boolean supportsCatalogsInPrivilege ()
Definitions
boolean supportsPositionedDelete ()
boolean supportsPositionedUpdate ()
boolean supportsSelectForUpdate ()
boolean supportsStoredProcedures ()
boolean supportsSubqueriesInComparisons ()
boolean supportsSubqueriesInExists ()
boolean supportsSubqueriesInIns ()
boolean supportsSubqueriesInQuantifieds ()
boolean supportsCorrelatedSubqueries ()
boolean supportsUnion ()
boolean supportsUnionAll ()
boolean supportsOpenCursorsAcrossCommit ()
boolean supportsOpenCursorsAcrossRollback ()
boolean supportsOpenStatementsAcrossCommit ()
boolean supportsOpenStatementsAcross ()
Rollback
int getMaxBinaryLiteralLength ()
int getMaxCharLiteralLength ()
int getMaxColumnNameLength ()
int getMaxColumnsInGroupBy ()
int getMaxColumnsInIndex ()
int getMaxColumnsInOrderBy ()
int getMaxColumnsInSelect ()
int getMaxColumnsInTable ()
int getMaxConnections ()
int getMaxCursorNameLength ()
int getMaxIndexLength ()
int getMaxSchemaNameLength ()
int getMaxProcedureNameLength ()
int getMaxCatalogNameLength ()
int getMaxRowSize ()
boolean doesMaxRowSizeIncludeBlobs ()
int getMaxStatementLength ()
int getMaxStatements ()
int getMaxTableNameLength ()
int getMaxTablesInSelect ()
int getMaxUserNameLength ()
int getDefaultTransactionIsolation ()
boolean supportsTransactions ()
boolean supportsTransactionIsolationLevel (int level)
boolean supportsDataDefinitionAndData ()
ManipulationTransactions
boolean supportsDataManipulation ()
TransactionsOnly
boolean dataDefinitionCausesTransaction ()
Commit
boolean dataDefinitionIgnoredIn ()
Transactions
ResultSet getProcedures (String catalog, String schemaPattern, String procedureNamePattern)
ResultSet getProcedureColumns (String catalog, String schemaPattern, String procedureNamePattern, String columnNamePattern)
ResultSet getTables (String catalog, String schemaPattern, String tableNamePattern, String types[])
ResultSet getSchemas ()
ResultSet getCatalogs ()
ResultSet getTableTypes ()
ResultSet getColumns (String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern)
ResultSet getColumnPrivileges (String catalog, String schema, String table, String columnNamePattern)
ResultSet getTablePrivileges (String catalog, String schemaPattern, String tableNamePattern)
ResultSet getBestRowIdentifier (String catalog, String schema, String table, int scope, boolean nullable)
ResultSet getVersionColumns (String catalog, String schema, String table)
ResultSet getPrimaryKeys (String catalog, String schema, String table)
ResultSet getImportedKeys (String catalog, String schema, String table)
ResultSet getExportedKeys (String catalog, String schema, String table)
ResultSet getCrossReference (String primaryCatalog, String primarySchema, String primaryTable, String foreignCatalog, String foreignSchema, String foreignTable )
ResultSet getTypeInfo ()
ResultSet getIndexInfo (String catalog, String schema, String table, boolean unique, boolean approximate)
As you can see in the table, the DatabaseMetaData object gives information about the functionality and limitation of the underlying DBMS. An important set of information that is very useful for an application programmer includes the methods describing schema details of the tables in the database, as well as table names, stored procedure names, and so on.

An example of using the DatabaseMetaData objects from a Java application is the development of multi-tier, scaleable applications. A Java application can query if the underlying database engine supports a particular feature. If it does not, Java can call alternate methods to perform the task. This way, the application will not fail if a feature is not available in the DBMS.

At the same time, the application will exploit advanced functionality whenever they are available. This is what some experts call "interoperable and yet scaleable." Interoperability is needed for application tools also--especially for general-purpose design and query tools based on Java that must interact with different data sources. These tools have to query the data source system to find out the supported features and proceed accordingly. The tools might be able to process information faster with data sources that support advanced features, or they may be able to provide the user with more options for a feature-rich data source.

ResultSetMetaData

Compared to the DatabaseMetaData, the ResultSetMetaData object is simpler and has fewer methods. But these will be more popular with application developers. The ResultSetMetaData, as the name implies, describes a ResultSet object. Table 44.5 lists all of the methods available for the ResultSetMetaData object.
Table 44.5 ResultSetMetaData Methods
Return Type Method Name Parameter
Int getColumnCount ()
boolean isAutoIncrement (int column)
boolean isCaseSensitive (int column)
boolean isSearchable (int column)
boolean isCurrency (int column)
int isNullable (int column)
boolean isSigned (int column)
int getColumnDisplaySize (int column)
String getColumnLabel (int column)
String getColumnName (int column)
String getSchemaName (int column)
int getPrecision (int column)
int getScale (int column)
String getTableName (int column)
String getCatalogName (int column)
int getColumnType (int column)
String getColumnTypeName (int column)
boolean isReadOnly (int column)
boolean isWritable (int column)
boolean isDefinitelyWritable (int column)
Return Values
int columnNoNulls = 0
int columnNullable = 1
int ColumnNullable Unknown = 2
As you can see from the previous table, the ResultSetMetaData object can be used to find out about the types and properties of the columns in a result set. You need to use methods such as getColumnLabel() and getColumnDisplaySize() even in normal application programs. Using these methods will result in programs that handle result sets generically, thus assuring uniformity across various applications in an organization as the names and sizes are taken from the database itself.

Before you leave this chapter, also look at the exception handling facilities offered by JDBC.

The SQLExceptions Class

The SQLException class in JDBC provides a variety of information regarding errors that occurred during a database access. The SQLException objects are chained so a program can read them in order. This is a good mechanism, as an error condition can generate multiple errors and the final error might not have anything to do with the actual error condition. By chaining the errors, you can actually pinpoint the first error. Each SQLException has an error message and vendor-specific error code. Also associated with a SQLException is a SQLState string that follows the XOPEN SQLState values defined in the SQL specification. Table 44.6 lists the methods for the SQLException class.
Table 44.6 SQLExceptions Methods
Return Type Method Name Parameter
SQLException SQLException (String reason, String SQLState, int vendorCode)
SQLException SQLException (String reason, String SQLState)
SQLException SQLException (String reason)
SQLException SQLException ()
String getSQLState ()
int getErrorCode ()
SQLException getNextException ()
void setNextException (SQLException ex)

The SQLWarnings Class

Unlike the SQLException class, the SQLWarnings class does not cause any commotion in a Java program. The SQLWarnings are tagged to the object whose method caused the warning. So you should check for warnings using the getWarnings() method that is available for all objects. Table 44.7 lists the methods associated with the SQLWarnings class.
Table 44.7 SQLWarnings Methods
Return Type Function Name Parameter
SQLWarning SQLWarning (String reason, String SQLState, int vendorCode)
SQLWarning SQLWarning (String reason, String SQLState)
SQLWarning SQLWarning (String reason)
SQLWarning SQLWarning ()
SQLWarning getNextWarning ()
void setNextWarning (SQLWarning w)