JDBC 4.0 and Oracle JDeveloper for J2EE Development

By Deepak Vohra
  • Instant online access to over 7,500+ books and videos
  • Constantly updated with 100+ new titles each month
  • Breadth and depth in over 1,000+ technologies
  1. JDBC 4.0

About this book

Data retrieval and storage is one of the most common components of J2EE applications. JDBC (Java Database Connectivity) is the Java API for accessing a SQL relational database and adding, retrieving, and updating data in the database.

Oracle JDeveloper is a developer-friendly integrated development environment (IDE) for building service-oriented applications using the latest industry standards for Java, XML, web services, and SQL. It supports the complete development lifecycle with integrated features for modeling, coding, debugging, testing, profiling, tuning, and deploying applications.

This book is about developing Java/J2EE applications with a database component in Oracle JDeveloper (version 10.1.3). It covers the practical aspects of JDBC (version 4.0); it will teach application developers about setting the environment for developing various JDBC-based J2EE applications and the procedure to develop JDBC-based J2EE applications. It will also explore the new features added in JDBC 4.0.

Publication date:
April 2008
Publisher
Packt
Pages
444
ISBN
9781847194305

 

Chapter 1. JDBC 4.0

The Java Database Connectivity API is used to access a SQL database from a Java application. JDBC also supports tabular data sources, such as a spreadsheet. We will constrain our discussion to SQL relational databases. Using JDBC API, SQL statements can be run in a database. JDBC started as JDBC 1.0 API; JDBC 1.0 covered the basics of establishing a connection with a database, running SQL statements, retrieving values from result sets, and using transactions. JDBC 2.0 introduced scrollable result sets, JDBC methods to update a result set or a database table, batch updates, and SQL3 data types such as, BLOB, CLOB, Array, Ref, and Struct. JDBC 3.0 introduced savepoints, connection pooling of prepared statements, multiple open ResultSet objects, BOOLEAN data type, and an interface for parameter metadata and for retrieving database metadata. JDBC 4.0 specifications added some new features, which we will discuss in this chapter.

The JDBC API provides various interfaces and classes for accessing a database; creating tables in the database; and adding, updating, deleting data, in the database tables. In the following sections, we will discuss some of the JDBC classes and interfaces. We will also discuss the new methods added to these classes or interfaces, in JDBC 4.0 specifications. To run a JDBC 4.0 application, install a RDBMS database such as the open-source MySQL database or the commercial Oracle database. A JDBC driver class is required to establish a connection with the database. JDBC drivers are vendor-specific. A JDBC driver class implements the java.sql.Driver interface.

DriverManager Class

The DriverManager class is used to obtain a connection with a database. A JDBC driver is required to be loaded before obtaining a connection with the database. In JDBC 3.0, a JDBC driver can be loaded either by specifying it in the jdbc.drivers system property, or by using the Class.forName() method. We require invoking the Class.forName() method by loading the Oracle JDBC driver, oracle.jdbc.OracleDriver, using JDBC 3.0.

Class.forName("oracle.jdbc.OracleDriver");

In JDBC 4.0 specifications, the DriverManager class has added support to getConnection() and getDrivers() methods, for the Java SE (Service Provider) mechanism. By using these methods, JDBC drivers may be loaded automatically. The Class.forName() method is not required to be invoked. Loading drivers using the Java SE Service Provider mechanism will be discussed in the Automatic SQL Driver Loading section.

A JDBC connection is represented by a java.sql.Connection object, and may be obtained from a DriverManager by calling the overloaded static getConnection() methods. The getConnection() method is listed in following table:

getConnection() Method

Description

getConnection(String url )

Obtains a connection with the specified database URL.

getConnection(String url, Properties properties)

Username and password may be specified in the Properties Hashtable.

getConnection(String url, String user, String password)

Obtains a connection with a URL username, and password.

For example, a connection with the Oracle database may be obtained as shown below:

String url="jdbc:oracle:thin:@localhost:1521:ORCL";
Connection connection = DriverManager.getConnection(url, "oe", "pw");
 

DriverManager Class


The DriverManager class is used to obtain a connection with a database. A JDBC driver is required to be loaded before obtaining a connection with the database. In JDBC 3.0, a JDBC driver can be loaded either by specifying it in the jdbc.drivers system property, or by using the Class.forName() method. We require invoking the Class.forName() method by loading the Oracle JDBC driver, oracle.jdbc.OracleDriver, using JDBC 3.0.

Class.forName("oracle.jdbc.OracleDriver");

In JDBC 4.0 specifications, the DriverManager class has added support to getConnection() and getDrivers() methods, for the Java SE (Service Provider) mechanism. By using these methods, JDBC drivers may be loaded automatically. The Class.forName() method is not required to be invoked. Loading drivers using the Java SE Service Provider mechanism will be discussed in the Automatic SQL Driver Loading section.

A JDBC connection is represented by a java.sql.Connection object, and may be obtained from a DriverManager by calling the overloaded static getConnection() methods. The getConnection() method is listed in following table:

getConnection() Method

Description

getConnection(String url )

Obtains a connection with the specified database URL.

getConnection(String url, Properties properties)

Username and password may be specified in the Properties Hashtable.

getConnection(String url, String user, String password)

Obtains a connection with a URL username, and password.

For example, a connection with the Oracle database may be obtained as shown below:

String url="jdbc:oracle:thin:@localhost:1521:ORCL";
Connection connection = DriverManager.getConnection(url, "oe", "pw");
 

Connection Interface


The Connection interface represents a connection with the database. SQL statements may be run in a connection session by using a Statement object. A Connection object is in auto-commit mode, by default. In the auto-commit mode, changes are committed to the database after an SQL statement is executed. The auto-commit mode can be modified by calling the setAutoCommit(boolean autoCommit) method. For example, auto-commit may be set to false :

connection.setAutoCommit(false);

If auto-commit is set to false, it would be required to commit changes by calling the commit() method:

connection.commit();

A Connection object can be set to read-only by calling the setReadOnly() method:

connection.setReadOnly(true);

If a Connection object is not required, close the connection by calling the close() method:

connection.close();

The following table discusses the methods in JDBC 4.0 that have been added to the Connection interface.

Method

Description

createArrayOf()

Creates a java.sql.Array object. java.sql.Array is the Java mapping for the SQL data type, ARRAY. The SQL3 data type ARRAY stores an array in a column.

createBlob()

Creates a Blob object.

createClob()

Creates a Clob object.

createNClob()

Creates an NClob object.

createSQLXML()

Creates a SQLXML object.

createStruct()

Creates a Struct object.

isValid()

Tests the validity of a connection.

getClientInfo()

Overloaded method returns a client info property, or a list of client info properties. Client info represents information, such as user name and application name about the client.

setClientInfo()

Overloaded method sets client info.

 

Transactions


A transaction is a group of one or more statements run as a unit. If the default value of auto-commit is set to true, then each Statement that would be run represents a transaction. After each statement is run, changes to the database are made with the auto-commit set to true. Set the auto-commit to false, if a developer requires a group of statements to be run together. Changes to the database are not made till each of the statement has run. If auto-commit is set to false, the changes to the database are committed with the commit() method. The commit() method commits the SQL statements run after the previous commit to the database was made. The group of statements run between two consecutive commits to the database represents a transaction. The rollback() method rolls back the changes made in the current transaction. A transaction may be required to be rolled back, if an error or a SQLException is generated.

connection.rollback();

While one transaction is modifying a database table, another transaction could be reading from the same table. The type of read can be dirty-read, a non-repeatable read, or a phantom read. A dirty-read occurs when a row has been modified by a transaction, but has not been committed, and is being read by a different transaction. If the transaction that modifies the row rolls back the transaction, then the value retrieved by the second transaction would be erroneous. A non-repeatable transaction occurs when one transaction reads a row while the other transaction modifies it. The first transaction re-reads the row obtaining a different value. A phantom read occurs when one transaction retrieves a result set with a WHERE condition, while the other transaction adds a row that meets the WHERE condition. The first transaction re-runs to generate a result set that has an additional row. The default transaction level can be obtained with the getTransactionLevel() method:

int transactionLevel=connection. getTransactionIsolation();

The different transaction isolation levels are listed in following table:

Transaction Isolation Level

Description

TRANSACTION_NONE

Transactions are not supported.

TRANSACTION_READ_COMMITTED

Dirty-reads cannot be done. Non-repeatable reads and phantom reads can be done.

TRANSACTION_REPEATABLE_READ

Dirty reads and non-repeatable reads cannot be done. Phantom reads can be done.

TRANSACTION_SERIALIZABLE

Dirty-reads, non-repeatable reads and phantom reads cannot be done.

The transaction isolation level can be set with the setTransactionIsolation(int level) method:

connection.setTransactionIsolation(level);
 

Savepoint Interface


Savepoint is a new interface in JDBC 3.0 specification. A Savepoint is a point within a transaction up to which the changes made in the transaction are rolled back, if the transaction is rolled back with the rollback() method. All changes before the savepoint are implemented when a transaction is rolled back. A savepoint is set with the overloaded setSavepoint() method:

Savepoint savepoint=connection.setSavepoint();
Savepoint savepoint=connection.setSavepoint("savepointName");

The getSavepointId() method returns the savepoint id, and the getSavepointName() method returns the savepoint name.

 

Statement Interface


The Statement interface runs SQL statements in a database and returns the result sets. A Statement object is obtained from a Connection object with the overloaded createStatement() method. Before enumerating the different createStatement() methods, we will discuss about the result set type, result set concurrency, and result set holdability. There are three result set types:

  1. 1. TYPE_FORWARD_ONLY

  2. 2. TYPE_SCROLL_INSENSITIVE

  3. 3. TYPE_SCROLL_SENSITIVE

The TYPE_FORWARD_ONLY result set is not scrollable. Its cursor moves only in the forward direction. The rows in the result set satisfies the query, either at the time when the query is executed, or when the rows are retrieved.

The TYPE_SCROLL_INSENSITIVE result set is scrollable. The rows in the result set do not reflect the changes made in the database. The rows in the result set satisfy the query, either at the time when the query is executed, or when the rows are retrieved.

The TYPE_SCROLL_SENSITIVE result set is scrollable, and reflects the changes made to the database while the result set is open.

Result set concurrency specifies the level of updatability. There are two concurrency levels:

  1. 1. CONCUR_READ_ONLY

  2. 2. CONCUR_UPDATABLE

CONCUR_READ_ONLY is the default concurrency level. The CONCUR_READ_ONLY concurrency specifies a result set that is not updatable, and CONCUR_UPDATABLE concurrency specifies a result set that is updatable.

Holdability specifies that the result set objects are to be kept open when the commit() method is invoked. There are two holdability values:

  1. 1. HOLD_CURSORS_OVER_COMMIT

  2. 2. CLOSE_CURSORS_AT_COMMIT

If HOLD_CURSORS_OVER_COMMIT is specified, the result set objects (that is cursors) are kept open after the commit() method is called. If CLOSE_CURSORS_AT_COMMIT is specified, the result set objects are closed at the commit() method.

The different createStatement() methods, which are used to create a Statement object from a Connection object are discussed in following table:

Create Statement Method

Description

createStatement()

A Statement object is created with result set of type TYPE_FORWARD_ONLY, and of concurrency CONCUR_READ_ONLY.

createStatement(int resultSetType, int resultSetConcurrency)

A Statement object is created with the specified result set type and result set concurrency. Implementation dependent, resultSetHoldability is used.

createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability)

A Statement object is created with the specified result set type, concurrency, and holdability.

Different execute() methods are available to run an SQL statement that may return multiple results. The execute(String sqlStatement) method runs an SQL statement and returns a boolean, which indicates whether the first result is a ResultSet object, or an update count. If true is returned, the first result is a ResultSet object. If false is returned, the first result is an update count. If the first result is a ResultSet object, then the ResultSet object can be obtained with the getResultSet() method. If the first result is an update count, then the update count can be obtained with the getUpdateCount() method:

Statement stmt=connection.createStatement();
boolean resultType=stmt.execute("SQL Statement");
if(resultType==true)
ResultSet resultSet=stmt.getResultSet();
else
int updateCount=stmt.getUpdateCount();

Multiple results can be returned by the execute() method. To obtain additional results, invoke the getMoreResults() method. The return value of the getMoreResults() method is similar to that of the execute() method. JDBC 3.0 introduced the getMoreResults(int) method to specify whether the current result set should be closed before opening a new result set. The getMoreResults(int) method parameter value can be CLOSE_ALL_RESULTS, CLOSE_CURRENT_RESULT, or KEEP_CURRENT_RESULT. If the parameter value is CLOSE_ALL_RESULTS, then all the previously opened ResultSet objects would be closed. If the value is CLOSE_CURRENT_RESULT, only the current ResultSet object is closed. If the value is KEEP_CURRENT_RESULT, the current ResultSet object is not closed.

The setQueryTimeout(int) method specifies the timeout, in seconds, for a Statement object to execute. The executeQuery(String sql) executes an SQL query and returns a single ResultSet object. The executeUpdate(String sql) method executes an SQL statement, which is either a DML (INSERT, UPDATE, or DELETE) statement or a DDL statement. If the SQL string is a DML statement, the executeUpate(String) method returns the number of rows modified. If the SQL string is a DDL statement, the method returns the value, "0". SQL statements can also be run in a batch with the executeBatch() method. Add SQL commands to run a batch with the addBatch(String sql) method:

stmt.addBatch("SQL command");
stmt.executeBatch();

The executeBatch() method returns an int[] value of update counts. The batch SQL commands can be cleared with the clearBatch() method. If a Statement object is not being used, it is closed automatically. It is recommended to close the Statement object with the close() method:

stmt.close();

When a Statement object is closed, the database and the JDBC resources associated with that object are also closed. Further, the ResultSet object associated with the Statement object is also closed.

In JDBC 4.0, the new methods discussed in following table have been added to the Statement interface:

Method

Description

isClosed()

Tests, if the Statement object has been closed.

isPoolable()

Tests, if the Statement object is poolable.

setPoolable()

Sets the Statement object as poolable. By default, a Statement object is not set to poolable. The method is only a hint to the statement pooling implementation. Statement pooling provides a better management for statement pooling resources.

 

ResultSet Interface


A ResultSet is a table of data, which is a database result set. The result set types, concurrency and holdability were discussed in the previous section. A ResultSet object can be created to scroll, update, and keep the cursors open, when a commit is done:

Statement stmt=connection.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE, ResultSet.HOLD_CURSORS_OVER_COMMIT);
ResultSet rs=stmt.execute("sql");

A ResultSet has a cursor, which points to the current row. Initially, the cursor points before the first row. The next() method moves the cursor to the next row. The previous() method shifts the cursor to the previous row. The ResultSet interface provides different methods to position the cursor. If the ResultSet is scrollable, then the result set type is TYPE_SCROLL_INSENSITIVE, or TYPE_SCROLL_SENSITIVE and the cursor can be shifted to a specified position. Some of the methods to position a ResultSet cursor are listed in following table:

ResultSet Method

Description

absolute(int row)

Positions the cursor to the specified row. Index for the First row is 1. If the index is a - ve number, then the cursor is positioned with respect to the end of the result set. -1 index, positions the cursor to the last row. If the index is more than the number of rows in the ResultSet, then the cursor is positioned at the end of the ResultSet. If the -ve index is less than the number of rows, then the cursor is positioned before the first row. The method returns the value as true, if the cursor is in the ResultSet.

afterLast()

Positions the cursor after the last row.

beforeFirst()

Positions the cursor before the first row. SQLException is generated, if the ResultSet is TYPE_FORWARD_ONLY

first()

Positions the cursor on the first row in the ResultSet. Returns the value as true, if cursor is on a valid row.

last()

Positions the cursor on the last row in the ResultSet.

relative(int rows)

Positions the cursor to a relative number of rows from the current row. If the relative position is before or after the current row, the cursor is positioned before or after the current row.

For an updatable result set, the method moveToInsertRow() moves the cursor to the insert row, which is a buffer, to insert a new row. The cursor can be shifted back to the current row with the method, moveToCurrentRow(). The ResultSet interface has methods, which are used to obtain the position of the cursor, and are listed in following table:

Method Name

Description

isAfterLast()

Returns true, if the cursor's position is after the last row.

isFirst()

Returns true, if the cursor's position is in the first row.

isLast()

Returns true, if the cursor's position is in the last row.

isBeforeFirst()

Returns true, if the cursor's position is before the first row.

The ResultSet column values are obtained with the help of getter methods. The ResultSet interface has a 'getter' method for each of the Java data types that map to the database data type. If the database data type is mappable to the Java data type, the Java data type is returned. A getter method with a column index position and column name are included for each of the data types. The getter method with the column index position is more efficient. An int column value is retrieved with the index position, and a String column value is retrieved with the column name as follows:

ResultSet rs;
Int intColumnValue=rs.getInt(1);
String stringColumnValue=rs.getString("column name");

The ResultSet interface has updater methods to update column values in a row. An 'updater' method is included for each of the Java data types that map to the database data type. If the ResultSet is updatable, then the column values in a row can be updated, or a new row can be added. To update a row, move the cursor to the row to be updated. For example, shift the cursor to the tenth row. Update a column value with an updater method. For example, update a String column, column1 to the value col1val. Also update the row in the database:

rs.absolue(10);
rs.updateString("column1", "col1val");
rs.updateRow();

The method updateRow() updates the database. To add a new row, shift the cursor to the insert row with the moveToInsertRow() method. Add column values with the updater methods, and insert a row in the database with the insertRow() method. Shift the cursor to the current row with the moveToCurrentRow() method:

rs.moveToInsertRow();
rs.updateString(1, "JDBC4.0");
rs.updateInt(2,16);
rs.updateBoolean(3, true);
rs.insertRow();
rs.moveToCurrentRow();

The current row in a ResultSet can be deleted with the deleteRow() method. A ResultSet object is automatically closed and the associated resources are released when the Statement object that had created the ResultSet object is being closed. However, it is recommended to close the ResultSet object using the close() method.

rs.close();

In JDBC 4.0, the methods discussed in following table have been added to the ResultSet interface:

Method

Description

getHoldability()

Returns the holdability of the ResultSet object.

getRowId()

Overloaded method returns the row id of the specified column.

updateRowId()

Overloaded method updates the row id for the specified RowId of an object.

getNClob()

Overloaded method returns the specified column as an NClob object.

isClosed()

Returns a Boolean value to indicate if the ResultSet object is closed.

getNString()

Overloaded method returns the specified column as a String object, which is used with NCHAR, NVARCHAR and LONGNVARCHAR columns.

getNCharacterStream()

Overloaded method returns the specified column value as a java.io.Reader object, which is used with NCHAR, NVARCHAR and LONGNVARCHAR columns.

updateNString()

Overloaded method updates the specified column with the specified String value, which is used with NCHAR, NVARCHAR and LONGNVARCHAR columns.

updateNCharacterStream()

Overloaded method updates the specified column with the specified character stream, and the specified String value. It is used with NCHAR, NVARCHAR and LONGNVARCHAR columns.

getSQLXML()

Overloaded method returns the specified column as an SQLXML object. SQLXML Java data type is discussed in a later section, in this chapter.

updateSQLXML()

Overloaded method updates the specified column with the specified SQLXML value.

updateNClob()

Overloaded method updates the specified column with the specified Reader object.

The updateObject() method in the ResultSet interface has been modified to support the new data types, NClob and SQLXML in JDBC 4.0. The updater methods in the table do not update the underlying database. To update the database, the insertRow() or updateRow() method is required to be invoked.

 

PreparedStatement Interface


A PreparedStatement object represents a precompiled SQL statement. The PreparedStatement interface extends the Statement interface. The precompiled SQL statement has IN parameters for which values are being set with the setter methods of the PreparedStatement interface. A 'setter' method is included for each of the Java data types that map to a SQL data type. The JDBC driver converts the Java data type to an SQL data type. The IN parameter values are set with parameter index. For example, update a Catalog table with the following definition using PreparedStatement :

CatalogId NUMBER
Journal VARCHAR(255)
Publisher VARCHAR(255)
Title VARCHAR(255)
Author VARCHAR(255)

Set Publisher column value to Oracle Publishing, and Journal column values to Oracle Magazine, where CatalogId is 1, referred to the code below:

PreparedStatement pstmt = connection.prepareStatement("UPDATE CATALOG SET Journal=? AND Publisher=? WHERE CatalogId=?");
pstmt.setString(1, "Oracle Magazine");
pstmt.setString(2, "Oracle Publishing");
pstmt.setInt(3, 1);
pstmt.executeUpdate();

If the database supports statement pooling, PreparedStatement objects are pooled by default. In JDBC 4.0, the methods discussed in the following table have been added to the PreparedStatement interface:

Method

Description

setRowId()

Sets the specified parameter to the specified RowId value. The driver converts the value to the SQL type ROWID.

setNString()

Sets the specified parameter to the specified String value. The driver converts the value to NCHAR, NVARCHAR, or LONGNVARCHAR SQL date type.

setNClob()

Overloaded method sets the specified parameter to the specified NClob object or Reader object. The driver converts the value to SQL type NCLOB.

setNCharacterStream()

Overloaded method sets the specified parameter to the specified Reader object.

setSQLXML()

Sets the specified parameter to the specified SQLXML value. The driver converts the value to the SQL type XML.

 

Database Metadata


Different RDBMS databases in combination with the database-specific JDBC drivers usually support, and implement features differently. It also supports different SQL data types. An application that is used with different databases would be required to obtain database-specific information. For example, an application could be required to retrieve information about all the SQL data types, which are being supported with a database. An application that implements batch updates would be required to find out if a database supports batch updates. The DatabaseMetaData interface represents the database metadata. The database metadata is obtained from the Connection object:

DatabaseMetaData metadata = currentConnection.getMetaData();

The SQL data type supported by a database can be obtained using the getTypeInfo() method:

ResultSet resultSet=metadata.getTypeInfo();

To find out if a database supports batch update, invoke the supportsBatchUpdates() method:

metadata.supportsBatchUpdates();

To find out if a database supports transactions, invoke the supportsTransactions() method, and to find out if a database supports savepoints, invoke the supportsSavepoints() method:

metadata.supportsTransactions();
metadata.supportsSavepoints();

Support for a ResultSet type can be checked using the supportsResultSetType() method, while support for a concurrency type, in combination with a result set type, can be checked with the supportsResultSetConcurrency() method. Support for a result set holdability can be checked with the supportsResultSetHoldability() method:

metadata.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE);
metadata.supportsResultSetConcurrency(ResultSet. TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
metadata.supportsResultSetHoldability(ResultSet. CLOSE_CURSORS_AT_COMMIT);

The database metadata also includes information about the different SQL clauses supported by the database. Support for the GROUP BY clause is checked with the supportsGroupBy() method; support for SELECT FOR UPDATE is checked with the supportsSelectForUpdate() method; support for UNION clause is checked with the supportsUnion() method; support for ALTER TABLE with add column is checked with the supportsAlterTableWithAddColumn() method; and support for mixed case SQL identifiers is checked with the storesMixedCaseIdentifiers() method. Also, the maximum number of columns that can be specified in a SELECT statement is obtained with the getMaxColumnsInSelect() method.

The database metadata also provides information about the JDBC driver and the database. The database product name, the database major version, the driver major version, the driver name, the driver version, and the JDBC major version supported by the driver are obtained as follows:

String database=metadata.getDatabaseProductName();
int databaseMajorVersion=metadata.getDatabaseMajorVersion();
int driverMajorVersion=metadata.getDriverMajorVersion();
String driverName=metadata.getDriverName();
int driverVersion=metadata.getDriverVersion();
int jdbcMajorVersion=metadata.getJDBCMajorVersion();

Metadata about a database table is obtained with the getTables(String catalog,String schemaPattern,String tableNamePattern,String[] types) method. The parameter, catalog, is a catalog name in the database. SchemaPattern is the Schema pattern. TableNamePattern is the table name pattern and the types represents the table type. Table types include TABLE, VIEW, SYSTEM TABLE, GLOBAL TEMPERORY, LOCAL TEMPERORY, ALIAS, and SYNONYM. Obtain all the tables of type, TABLE:

String[] names = {"TABLE"};
ResultSet tables = metadata.getTables(null,"%", "%", names);

Obtain the table name and table schema from the table's metadata:

while (tables.next()) {
String tableName = tables.getString("TABLE_NAME");
String tableSchema = tables.getString("TABLE_SCHEM");
}

Metadata about the columns can be obtained with the getColumns(String catalog,String schemaPattern,String tableNamePattern,String columnNamePattern) method. Obtain the column's metadata for the table name obtained from the table's metadata:

ResultSet columns = metadata.getColumns(null, "%", tableName, "%");

Obtain the column name, column type, column size, and column nullable:

while (columns.next()) {
String columnName = columns.getString("COLUMN_NAME");
String datatype = columns.getString("TYPE_NAME");
int datasize = columns.getInt("COLUMN_SIZE");
int nullable = columns.getInt("NULLABLE");
}

The procedures in the database can be obtained from the getProcedures(String catalog,String schemaPattern, String procedureNamePattern) method:

ResultSet procedures=metadata.getProcedures(null,"%", "%");

Obtain the procedure name, procedure type, and procedure schema:

while (procedures.next())
{
String procedureName = procedures.getString("PROCEDURE_NAME");
String procedureSchema = procedures.getString("PROCEDURE_SCHEM");
String procedureType = procedures.getString("PROCEDURE_TYPE");
}

In JDBC 4.0, the methods discussed in the following table have been added to the DatabaseMetaData interface:

Method

Description

getRowIdLifetime()

Indicates if the database supports SQL data type ROWID, and the duration for which a RowId object is valid. The value returned is one of the following:

RowIdLifetime.ROWID_UNSUPPORTED.

RowIdLifetime.ROWID_VALID_

OTHER.

RowIdLifetime.ROWID_VALID_SESSION.

RowIdLifetime.ROWID_VALID_TRANSACTION.

RowIdLifetime.ROWID_VALID_FOREVER.

autoCommitFailureClosesAllResultSets()

Indicates if all the ResultSets are closed, and if an SQLException is generated for an autocommit that was set as true.

getFunctions()

Retrieves a ResultSet of system and user functions in the specified catalog.

Functions and Procedures are outside the scope of this book.

getFunctionColumns()

Retrieves a ResultSet of the system and the user parameters for a specified catalog.

getClientInfoProperties()

Retrieves a ResultSet of the client info properties supported by the JDBC driver.

supportsStoredFunctionsUsingCallSyntax()

Indicates if the database supports the invoking functions using the CALL syntax.

The getSchemas() method in the DatabaseMetaData interface has been overloaded to support a catalog name and a schema pattern.

 

JDBC Exceptions


SQLException is the main Exception that is generated in a JDBC application. The detail of an SQL exception can be obtained from an SQLException object using the SQLException methods, some of which are discussed in following table:

Method

Description

getMessage()

Returns a textual description of the error.

getSQLState()

Returns a SQLState for the SQLException.

getErrorCode()

Returns the implementation-specific error code for the SQLException object.

getCause()

Returns the cause of the SQLException or null, if the cause is not specified or not known.

getNextException()

Returns an exception chained to the SQLException. All the chained exceptions can be retrieved by invoking the getNextException() method recursively. Returns null, if no chained exception occurs.

getMessage()

Returns a textual description of the error.

When an SQLException occurs, it is likely that one or more SQLExceptions chained to it, have also occurred. The chained exceptions can be retrieved by invoking the getNextException() method recursively, until the method returns null. The cause of an SQLException can be retrieved using the getCause() method. The chained causes can be also be retrieved by invoking the getCause() method recursively, until the value, null, is returned.

If SQLException is generated output the exception message using the getMessage() method, output the exception causes using the getCause() method recursively, and retrieve exceptions chained to the exception using the getNextException() method recursively:

catch(SQLException e)
{
while(e != null)
{
System.out.println("SQLException Message:" + e.getMessage());
Throwable t = e.getCause();
while(t != null)
{
System.out.println("SQLException Cause:" + t);
t = t.getCause();
}
e = e.getNextException();
}
}

Some of the subclasses in the SQLException class are listed in following table:

SQLException

Description

javax.sql.rowset.RowSetWarning

Database warning on a RowSet object.

javax.sql.rowset.serial.SerialException

Indicates an error in the serialization and de-serialization of SQL types such as: BLOB, CLOB, STRUCT, ARRAY.

Java.sql.SQLWarning

Database access warning.

Java.sql.DataTruncation

Indicates data truncation.

Java.sql.BatchUpdateException

Represents an error in a batch update operation.

JDBC 4.0 has added support for categorization of SQLExceptions and enhanced support for chained SQLExceptions, which we will discuss in a later section.

 

New Features in JDBC 4.0


JDBC 4.0 specification was made available in December 2006. Most databases provide at least a partial support for the JDBC 4.0 specification, in their JDBC drivers. JDBC 4.0 specification is implemented in JDK 6.0. Some of the new features of JDBC 4.0 specification, and the database support for JDBC 4.0 specification are discussed in the following sections.

Automatic SQL Driver Loading

JDBC 4.0 has facilitated the loading of a JDBC driver. In JDBC 3.0, a JDBC driver is loaded with the Class.forName(String) method. The Oracle JDBC driver is loaded in the following manner:

Class.forName("oracle.jdbc.OracleDriver");

In JDBC 4.0, a JDBC driver is loaded automatically with the Java Standard Edition Service Provider mechanism. The JDBC driver is loaded when the java.sql.DriverManager.getConnection() method is invoked. To load a JDBC driver with the Service Provider mechanism, JDBC 4.0 drivers should include the META-INF/services/java.sql.Driver file. In the java.sql.Driver file, specify the JDBC driver class to load. If the oracle.jdbc.OracleDriver is to be loaded then specify the following line in the java.sql.Driver file:

oracle.jdbc.OracleDriver

Multiple driver classes can be specified in a java.sql.Driver file, each on a separate line. A list of JDBC drivers available to a DriverManager can be obtained with the getDrivers() method:

Enumeration<Driver> drivers=DriverManager.getDrivers();

A JDBC connection can be obtained using the getConnection() method of the DriverManager class:

String url="jdbc:oracle:thin:@localhost:1521:ORCL";
Connection connection = DriverManager.getConnection(url,"oe", "pw");

Enhanced Data Type Support

JDBC 4.0 has added support for some new SQL data types. The ROWID SQL data type, which identifies a row in a table, is mapped to the java.sql.RowId Java data type. The Reader method, readRowId() has been added to the SQLInput interface, and the writer method, writeRowId(), has been added to the SQLOutput interface to read, and write ROWID values.

In JDBC 3.0, JDBC drivers supported only Unicode character set. SQL: 2003 standard has added support for SQL types, NCAHR, NVARCHAR, LONGVARCHAR, and NCLOB in which values are encoded using the National Character Set (NCS). The National Character Set SQL data type values were converted to the Unicode Character Set values with a JDBC 3.0 driver. The NCS data types can be more suitable if extensive character processing operations are required. Support for National Character Set database data types, NCHAR, NVARCHAR, LONGNVARCHAR, and NCLOB have been added in JDBC 4.0.

Setter methods, setNString(), setNCharacterStream(), and setNClob() have been added to the PreparedStatement and CallableStatement interfaces. Getter method, getNString(), getNCharacterStream(), and getNClob() have been added to the CallableStatement and ResultSet interfaces. Updater methods, updateNString(), updateNCharacterStream(), and updateNClob() have been added to the ResultSet interface. To create a NClob object, createNClob() method has been added to the Connection interface. Reader methods, readNString() and readNClob() have been added to the SQLInput interface to read the NCHAR, NVARCHAR, LONGNVARCHAR, and NCLOB values. Writer methods, writeNClob() and writeNString() have been added to the SQLOutput interface to write the NCHAR, NVARCHAR, LONGNVARCHAR, and NCLOB values.

In JDBC 4.0, support for BLOB and CLOB SQL data types have been enhanced. To create java.sql.Blob and java.sql.Clob objects, methods such as createBlob() and createClob() have been added to the Connection interface. In the PreparedStatement and CallableStatement interfaces, setBlob() method has been overloaded to set the Blob values from an InputStream, and setClob() method has been overloaded to set the Clob values from a Reader. In the ResultSet interface, the updater method, updateBlob(), has been overloaded to update a column from an InputStream and the updater method, updateClob(), has been overloaded to update a column from a Reader. To free resources in Blob and Clob objects, a method, free(), has been added to the Blob and Clob interfaces.

The setAsciiStream, setBinaryStream, and setCharacterStream methods in the CallableStatement and PreparedStatement interfaces have been overloaded. These interfaces have been overloaded to support the length parameter of type long in addition to the length parameter of type int. The length parameter specifies the length in bytes or characters of the InputStream or Reader object. Also, the setAsciiStream, setBinaryStream, and setCharacterStream methods in the CallableStatement and PreparedStatement interfaces have been overloaded with versions without the length parameter.

The setBlob() method in the CallableStatement and PreparedStatement interfaces has been overloaded with the other two methods to set parameter values from the InputStream object, one with a length parameter for the length of the binary stream, and the other without a length parameter. If the InputStream length does not match the specified length, an SQLException is generated. The setClob() method in the CallableStatement and PreparedStatement interfaces has been overloaded with other two methods to set parameter values from the Reader object, one with a length parameter for the number of characters in the Reader object and the other without a length parameter.

If the number of characters in the Reader object does not match the specified length, an SQLEception is generated. Similar to the setter methods in the PreparedStatement/CallableStatement, the updateAsciiStream, updateBinaryStream, updateBlob, updateCharacterStream, and updateClob methods in the ResultSet interface have been overloaded. Unlike the setBlob and setClob methods of the PreparedStatement and CallableStatement interfaces, the updateBlob and updateClob methods of the ResultSet interface do not generate an SQLException, if the InputStream/Reader length does not match the specified length.

SQL: 2003 XML Data Type Support

The SQL: 2003 standard supports a new data type, XML, for storing XML documents. With the XML data type, an XML document can be stored in a database table column similar to the other data types. JDBC 4.0 supports the SQL: 2003 standard. The java.sql.SQLXML object is the Java mapping for the database type, XML. Prior to the SQLXML Java data type, an XML type column value could be retrieved only as a String or CLOB, which did not include the functionality to access different nodes in an XML document.

An XML type database column can be mapped to a Java data type with the help of SQLXML data type. In JDBC 4.0 specification, a java.sql.Connection object has the provision to create an SQLXML object that initially does not have any data. The data can be added with the setString() method or the setBinaryStream(), setCharacterStream(),and setResult() methods. An SQLXML object can be retrieved from a ResultSet or a CallableStatement by using the overloaded getSQLXML() method. The data in an SQLXML object can be retrieved by using the getString() method or the getBinaryStream(), getCharacterStream(), and getSource() methods. An SQLXML object can be stored in a database table column of type XML, which is similar to any other data type using the setSQLXML() method of the PreparedStatement interface.

SQL Server 2005 EXPRESS supports the XML data type whereas, SQL Server 2000 does not. IBM's DB2 UDB V 9 also supports the XML data type. To find out if a database supports the XML data type, obtain the database metadata from the Connection object:

DatabaseMetaData metadata= connection.getMetaData();

The data types are supported with the getTypeInfo() method, as shown below:

ResultSet rs=metadata.getTypeInfo();

Iterate over the data type result set and output the TYPE_NAME column, as shown below:

System.out.println("TYPE_NAME:"+rs.getString("TYPE_NAME"));

For SQL Server 2005 and IBM's DB2 UDB v9, the XML TYPE_NAME is output:

TYPE_NAME: XML

In the following subsections, the procedures to create an XML document, store it in a database that supports the XML data type, and retrieve it from the database will be discussed.

Generating an XML Document

We will discuss the procedure to create and initialize an SQLXML object. Import the java.sql package, and the javax.xml.stream package:

import java.sql.*;
import javax.xml.stream.*;

The Java representation of an XML document in a database table is the SQLXML object. Create an SQLXML object from the Connection object with the createSQLXML() method, as shown below:

SQLXML sqlXML=connection.createSQLXML();

An SQLXML object can be initialized using one of the setString(), setBinaryStream(), setCharacterStream(), or setResult() methods. An SQLXML object can be initiated using the setResult() method and the StAXResult class. Create an XMLStreamWriter object from a StAXResult object, as shown below:

StAXResult staxResult = sqlXML.setResult(StAXResult.class);
XMLStreamWriter xmlStreamWriter = staxResult.getXMLStreamWriter();

The SQLXML object becomes non-writable after the setResult()method is invoked. Add the start of an XML document with the writeStartDocument(String,String) method, as shown below:

xmlStreamWriter.writeStartDocument("UTF-8","1.0");

The encoding and version of the XML document is specified in the writeStartDocument method. Add the start of an element with the writeStartElement(String localName) method, as shown below:

xmlStreamWriter.writeStartElement("catalog");

Add the element attributes by using the writeAttribute(String localName, String value) method. Add an element of text by using the writeCharacters(String text) method. Each start element would have a corresponding end element tag. Add an end element by using the writeEndElement() method. The writeEndElement() method does not specify the element name as the writeStartElement(String) method:

xmlStreamWriter.writeEndElement();

Add end of the document by using the writeEndDocument() method:

xmlStreamWriter.writeEndDocument();

A SQLXML object can also be initiated using the SAXResult class. Create a SAXResult object using the setResult() method of the SQLXML interface. Subsequently, obtain the ContentHandler result using the getHandler() method:

SAXResult saxResult = sqlXML.setResult(SAXResult.class);
ContentHandler contentHandler= saxResult.getHandler();

Specify the start of an XML document using the startDocument() method:

contentHandler.startDocument();

Specify the start of an element using the startElement(String uri,String localName,String qName,Attributes atts) method in which the parameter uri specifies the element namespace, parameter localName specifies the element local name, parameter qName specifies the element qualified name and parameter atts of type Attributes specifies the element attributes. An Attributes object can be created using the org.xml.sax.helpers.AttributesImpl class, which implements the Attributes interface. An attribute can be added to the AttributesImpl object using the addAttribute(String uri, String localName, String qName, String type, String value) method:

AttributesImpl.AttributesImpl() attrs=new AttributesImpl();
attrs.addAttribute("","","journal","StringType","OracleMagazine");
contentHandler.startElement("","","catalog",attrs);

The end of an element is specified with the endElement(String uri,String localName,String qName) method. Also specify the end of the document with the endDocument() method:

contentHandler.endElement("","","catalog");
contentHandler.endDocument();

An SQLXML object can also be initiated using the setCharacterStream() method. Create a Writer object from the SQLXML object using the setCharacterStream() method. Create a BufferedReader object from an input XML file. Read from the BufferedReader, and output to the Writer object:

Writer writer= sqlXML.setCharacterStream();
BufferedReader bufferedReader = new BufferedReader(new FileReader(new File("C:/catalog.xml")));
String line= null;
while((line = bufferedReader.readLine() != null) {
writer.write(line);
}

The SQLXML object becomes non-writable after the setCharacterStream() method is invoked. An XML document can also be added to an SQLXML object with the setString() method, as shown below:

sqlXML.setString("xmlString");

The SQLXML object becomes non-writable after invoking the setString() method. If the setString(), setBinaryStream(), setCharacterStream(), or setResult() method is invoked on an SQLXML object that has been previously initiated, a SQLException is generated. If any of the setBinaryStream(), setCharacterStream(), or setResult() methods are invoked more than once, a SQLException is generated, and the previously returned InputStream, Writer, or Result object is not effected.

Storing an XML Document

The SQLXML Java data type is stored in an XML document, just like any other Java data type. Create a database table with an XML type column. Run the SQL statement to create a database table, and obtain a Statement object from the Connection object, as shown below:

Statement stmt=connection.createStatement();

Create a database table, Catalog with an XML type column, as shown below:

stmt.executeUpdate("CREATE Table Catalog(CatalogId int, Catalog XML)");

Create a PreparedStatement object to add values to a database table, as shown in the following listing:

PreparedStatement statement=connection.prepareStatement("INSERT INTO CATALOG(catalogId, catalog) VALUES(?,?)");

Set the int value with the setInt() method and the SQLXML value with the setSQLXML() method, as shown below:

stmt.setInt(1, 1);
stmt.setSQLXML(2, sqlXML);

Update the database with the executeUpdate() method:

stmt.executeUpdate();

Retrieving an XML Document

An XML database data type row is retrieved as an SQLXML Java data type. Create a PreparedStatement for a SELECT query, as shown below:

PreparedStatement stmt=connection.prepareStatement("SELECT * FROM CATALOG WHERE catalogId=?");

Specify the catalogId value for which an XML document is to be retrieved:

stmt.setInt(1, 1);

Obtain a result set with the executeQuery() method:

ResultSet rs=stmt.executeQuery();

Obtain the SQLXML object for the catalog column of type XML, as shown below:

SQLXML sqlXML=rs.getSQLXML("Catalog");

Output the XML document in the SQLXML object by using the getString() method:

System.out.println(sqlXML.getString());

Accessing an XML Document Data

The XMLStreamReader interface can be used to read an XML document with an event iterator. An XMLStreamReader object is obtained from a SQLXML object, as shown below:

InputStream binaryStream = sqlXML.getBinaryStream();
XMLInputFactory factory = XMLInputFactory.newInstance();
XMLStreamReader xmlStreamReader = factory.createXMLStreamReader(binaryStream);

The SQLXML object becomes non-readable after calling the getBinaryStream() method. The next event is obtained by using the next() method, as shown below:

while(xmlStreamReader.hasNext())
{
int parseEvent=xmlStreamReader.next();
}

The next() method returns an int value that corresponds to an XMLStreamConstants constant, which represents an event type. Some of the return values of the next() method are listed in following table:

Event Type

Description

ATTRIBUTE

Specifies an attribute.

CDATA

Specifies a Cdata.

CHARACTERS

Text.

COMMENT

An XML document comment.

NOTATION_DECLARATION

Specifies a notation declaration.

START_DOCUMENT

Specifies the start of a document

START_ELEMENT

Specifies the start of an element.

END_ELEMENT

Specifies the end of an element.

ENTITY_DECLARATION

Specifies an entity declaration.

ENTITY_REFERENCE

Specifies an entity reference.

NAMESPACE

Specifies a namespace declaration.

SPACE

Specifies an ignorable white space.

If the return value is ELEMENT, then the local name, prefix, and namespace can be obtained by using the getLocalName(), getPrefix(), and getNamespaceURI() methods, as shown below:

System.out.println("Element Local Name: "+xmlStreamReader.getLocalName());
System.out.println("Element Prefix: "+xmlStreamReader.getPrefix());
System.out.println("Element Namespace:"+xmlStreamReader.getNamespaceURI());

The attribute count in an element is obtained by using the getAttributeCount() method. Iterate over the attributes and obtain the attribute local name by using the getAttributeLocalName() method, the attribute value with the getAttributeValue() method, the attribute prefix with the getAttributePrefix() method, and the attribute namespace with the getAttributeNamespace() method:

for(int i=0; i<xmlStreamReader.getAttributeCount();i++){
System.out.println("Attribute Prefix:"+xmlStreamReader.getAttributePrefix(i));
System.out.println("Attribute Namespace:"+xmlStreamReader.getAttributeNamespace(i));
System.out.println("Attribute Local Name:"+xmlStreamReader.getAttributeLocalName(i));
System.out.println("Attribute Value:"+xmlStreamReader.getAttributeValue(i));
SQL 2003 XML data type supportXML document data, accessing}

Support for Wrapper Pattern

Some vendor-specific JDBC resources that provide nonstandard JDBC methods are wrapped for architectural reasons. Such JDBC resources can be unwrapped to access instances with the wrapper pattern. Support for wrapper pattern is implemented in the Wrapper interface. With the Wrapper interface, resources that are wrapped as proxy classes can be accessed. The objective of the Wrapper interface is to provide a standard method to access vendor-specific extensions inside standard JDBC objects, such as, Connections, Statements, and ResultSets. The Wrapper interface is extended by the following interfaces:

  • java.sql.Connection

  • java.sql.DataSource

  • java.sql.ResultSet

  • java.sql.Statement

  • java.sql.DatabaseMetaData

  • java.sql.ResultSetMetaData

  • java.sql.ParameterMetaData

The Wrapper interface provides the methods isWrapperFor(Class<?>) and unwrap(Class<?>). The unwrap() method takes an interface as a parameter, and returns an Object that implements the interface. The object that is returned is either the object found to implement the specified interface, or a proxy for that object. The isWrapperFor() method returns a boolean. This method is used to find out if an instance implements the specified interface, or if an instance is a wrapper for an object that implements the specified interface. If the object implements the specified interface the value returned is true. If the object is a wrapper for the specified interface, the isWrapperFor()method is invoked recursively on the wrapped object. If the object does not implement the interface and is not a wrapper for the interface, the value returned is false. The unwrap() method should be invoked, if the isWrapperFor() method returns true.

Create an object of type, java.sql.PreparedStatement and check if the object is a wrapper for the Oracle JDBC specific interface, oracle.jdbc.OraclePreparedStatement using the isWrapperFor() method. If the object is a wrapper for the interface, create an instance of the oracle.jdbc.OraclePreparedStatement JDBC interface using the unwrap() method:

String url="jdbc:oracle:thin:@localhost:1521:ORCL";
Connection connection = DriverManager.getConnection(url,"oe", "pw");
String sql="INSERT INTO CATALOG(catalogId, journal) VALUES(?,?)"
java.sql.PreparedStatement stmt = connection.prepareStatement(sql);
Class class = Class.forName("oracle.jdbc.OraclePreparedStatement");
if(stmt.isWrapperFor(class))
{
OraclePreparedStatement ops = (OraclePreparedStatement)stmt.unwrap(class);
ops.defineColumnType(2, oracle.jdbc.OracleTypes.VARCHAR,4000);
}

Enhancements in SQLException

An error in interaction with the datasource is represented with the SQLException class. JDBC 4.0 has enhanced support for navigation of chained SQLExceptions with the iterator() method in the SQLException class. A chained SQLException is an Exception that is linked with other Exceptions. The iterator() method iterates over the chained exceptions and the chained causes. Chained exceptions can be retrieved and iterated over (without having to invoke the getNextException() and getCause() methods recursively) using the enhanced For-Each loop introduced in J2SE 5. When an SQLException is generated using the For-Each loop, the chained exceptions can be output as shown below:

catch(SQLException sqlException)
{
for(Throwable e : sqlException )
{
System.out.println("Error encountered: " + e);
}
}

In JDBC 4.0, four constructors have been added to the SQLException class with the Throwable cause as one of the parameters. The getCause() method can return non-SQLExceptions. In JDBC 4.0, three new categories of SQLExceptions have been added, which are as follows:

  • SQLTransientException

  • SQLNonTransientException

  • SQLRecoverableException

Categorization of the SQLExceptions facilitates the portability of error handling code. SQLTransientException and SQLNonTransientException classes have subclasses, which map to common SQLState class values. SQLState class provides JDBC application's return code information about the most recently executed SQL statement. The return code is sent by the database manager after the completion of each SQL statement. The SQLState class values are defined in the SQL: 2003 specification.

A SQLTransientException indicates that the operation that generates the exception could succeed, if retried. Subclasses of the SQLTransientException class are discussed in following table:

SQLException

SQLState Class Value

Description

SQLTransient

ConnectionException.

08

Represents that a connection operation that failed could succeed, if the operation is retried.

SQLTransaction

RollbackException.

40

Represents that a current Statement was rolled back.

SQLTimeoutException.

Does not correspond to a standard SQLState.

Represents that a Statement has timed out.

SQLNonTransientException indicates the operation, which generates the exception that will not succeed without the cause of the SQLException being rectified. Subclasses of the SQLNonTransientException are discussed in following table:

SQLException

SQLState Class Value

Description

SQLFeatureNotSupported Exception.

0A

Represents that a JDBC driver does not support a feature.

SQLNonTransientConnection Exception.

08

Represents that a connection operation that failed will not succeed if retried, without the cause of the exception being corrected.

SQLDataException.

22

Represents various data errors including non allowable conversion and division by 0.

SQLIntegrityConstraint

ViolationException.

23

Represents an integrity constraint exception.

SQLInvalidAuthorization SpecException.

28

Represents an authorization exception.

SQLSyntaxErrorException.

42

Represents an error in the SQL syntax.

The SQLRecoverableException indicates that the operation that throws the Exception can succeed, if the application performs some recovery steps and retries the entire transaction(or the transaction branch in the case of a distributed transaction). The recovery steps include at the least, closing the current connection and obtaining a new connection.

A new subclass of the SQLException class, SQLClientInfoException, has been added in the JDBC 4.0 specification. The SQLClientInfoException is generated, if one or more client info properties could not be set on a Connection. The SQLClientInfoException also lists the client info properties, which were not set. Some databases that do not allow multiple client info properties to be set atomically can generate the SQLClientInfoException exception after one or more client info properties have been set. The client info properties that were not set can be retrieved by using the getFailedProperties() method.

Connection Management

Connection pooling improves the performance and scalability of the connections by providing a cache of the connections that are reusable across client sessions. Connection pooling reduces the overhead of opening, initializing and closing connections. One of the drawbacks of the connection pooling is that when a connection in a connection pool becomes stale and unusable, the application performance is reduced. JDBC 3.0 specification did not have the provision to track connection state. Connection state tracking has been added to the Connection interface in the JDBC 4.0 to find out if a connection is valid. The isValid(int timeout) method returns true, if the connection is valid. The isValid() method validates a connection with a SQL query, or another mechanism. If a connection is not valid, the connection can be closed, thus reducing the accumulation of unusable connections. The Connection object conn can be closed, if it is not in use:

if(!conn.isClosed())
if(!conn.isValid())
conn.close();

Connection state tracking and closing of invalid connections are implemented by the connection pool manager. Another drawback of connection pooling has been that one or more connections assigned from a connection pool in a web or application server can bog down an application. JDBC 3.0 does not have the provision to identify the connections that use the excess of CPU time. JDBC 4.0 has added the setClientInfo() and getClientInfo() methods to the Connection interface using which, client specific information can be specified on a Connection object, when a Connection is assigned to an application. Client specification information includes user name and application name. The DatabaseMetaData interface in JDBC 4.0 provides a new method, getClientInfoProperties(). Client info properties supported by a JDBC driver can be obtained using the getClientInfoProperties() method. When one or more connections bog down the application, the getClientInfo() method can be used to identify which connections could be causing the reduction in performance. Some standard client info properties that a JDBC driver can support are discussed in the following table:

Client Info Property

Description

ApplicationName.

The name of the application that is using the connection.

ClientUser.

The name of the user.

ClientHostname.

The hostname on which the application is running.

Similar to connection pooling, JDBC 4.0 provides Statement pooling to reduce the overheads of opening, initiating, and closing Statement objects. Frequently used Statement objects can be pooled using the setPoolable(boolean poolable) method. The isPoolable() method is used to check if a Statement object is poolable. The Statement object, stmt can be pooled, if poolable:

if(stmt.isPoolable())
stmt.setPoolable(true);

Scalar Functions

Most databases support numeric, string, time, date, system, and conversion functions on the scalar values. SQL statements run using the Statement object, and can include the scalar functions using the JDBC escape syntax. JDBC 4.0 provides some new scalar function, which are discussed in the following table:

Scalar Function

Description

CHAR_LENGTH CHARACTER_LENGTH

Returns the length of a string expression.

CURRENT_DATE

Returns the current date.

CURRENT_TIME

Returns the current time.

CURRENT_TIMESTAMP

Returns the current timestamp.

EXTRACT

Extracts a field from a datetime value.

OCTET_LENGTH

Returns the length of a string expression in octets (bytes).

JDBC 4.0 support in Oracle Database

Support for JDBC 4.0 specification is a JDBC driver feature, and not a database feature. Oracle Database 11g JDBC drivers support JDBC 4.0 specification. Add the ojdbc6.jar file to the CLASSPATH environment variable to use the JDBC 4.0 features. JDK 6.0 is required for JDBC 4.0 support. Oracle database 11g JDBC drivers can be used with the Oracle database 9i and the later versions. Oracle database 11g JDBC drivers support all the JDBC 4.0 features except the SQLXML Java data type that is used to access the SQL data type XML. Oracle database 11g JDBC drivers support the wrapper pattern to access non-standard Oracle JDBC resources. Oracle extensions to the JDBC are available in the oracle.jdbc package.

The oracle.jdbc.OracleStatement interface can be unwrapped using the unwrap() method to create a oracle.jdbc.OracleStatement object. As the Statement interface extends the Wrapper interface, create a Statement object from a Connection object, conn. Check if the Statement object is a wrapper for the oracle.jdbc.OracleStatement interface using the isWrapperFor() method. Obtain a OracleStatement object from the interface using the unwrap() method to use the methods of the OracleStatement interface:

Statement stmt = conn.createStatement();
Class class = Class.forName("oracle.jdbc.OracleStatement");
if(stmt.isWrapperFor(class))
{
OracleStatement oracleStmt = (OracleStatement)stmt.unwrap(class);
oracleStmt.defineColumnType(1, Types.NUMBER);
}

Oracle database 11g JDBC drivers support the enhanced chained exceptions in the JDBC 4.0 SQLException class. JDBC 4.0 has added a distinction between the permanent errors and transient errors. Permanent errors are errors that occur in the correct operation of the database system and continue to recur, until the cause of the error is removed. Transient errors are errors occurring due to the failure of some segment of the system, or due to timeouts, and these may not recur if the operation that generated the error is retried. Oracle database 11g JDBC drivers support the different categories of SQLException.

Oracle database 11g JDBC drivers support the ROWID SQL data type. Each table in an Oracle database has a ROWID pseudocolumn that identifies a row in a table. The SQL data type of the ROWID column is ROWID. Usually a rowid uniquely identifies a row in a database. But rows in different tables that are stored in a cluster may have the same rowid. Rowids should not be used as the primary key for a database table. If a row is deleted and reinserted using an Import or Export utility, its rowid may get modified. If a row is deleted, its rowid can be assigned to a row added later. The ROWID pseudocolumn can be used in the SELECT and WHERE clauses. Rowid values have the following applications:

  1. 1. Rowids are the fastest way to access a row in a database table.

  2. 2. Rowids are unique identifiers for rows in a table.

  3. 3. Rowids represent how rows are stored in a table.

A ROWID column value can be retrieved using the getter methods in the ResultSet and CallableStatement interfaces. Retrieve the ROWID column value for the current row in a ResultSet object, rs, as shown below:

java.sql.RowId rowid=rs.getRowId();

A RowId object is valid till the identified row is not deleted. A RowId duration of the validity can be obtained using the getRowIdLifetime() method of the DatabaseMetaData interface. The duration of validity can be one of the int values in the following table:

int Value

Description

ROWID_UNSUPPORTED

Databases do not support the ROWID SQL data type.

ROWID_VALID_TRANSACTION

Valid for the duration of transaction in which it is created.

ROWID_VALID_SESSION

Valid for the duration of a session in which it is created across all transactions.

ROWID_VALID_FOREVER

Valid across all sessions.

ROWID_VALID_OTHER

Validity not known.

A RowId value can be used with a parameterized PreparedStatement to set a parameter value with a RowId object. A RowId value can also be used with an updatable ResultSet to update a column with a RowId object.

Oracle database 11g JDBC drivers support the National Character Set (NCS) data types NCHAR, NVARCHAR, LONGNVARCHAR, and NCLOB. Oracle database 11g drivers also support Large Object data types (LOBs). The Connection interface provides createBlob, createClob, and createNClob methods to create Blob, Clob, and NClob objects. Create a Blob object as shown below:

String url="jdbc:oracle:thin:@localhost:1521:ORCL";
Connection connection = DriverManager.getConnection(url,"oe", "pw");
Blob aBlob = connection.createBlob();

The LOB objects created do not contain any data. Data can be added using the setter methods in the Blob, Clob, and NClob interfaces. To add data to the Blob object, obtain an OutputStream object from the Blob object:

OutputStream outputStream=aBlob.setBinaryStream(1);

LOB objects can be used as input parameters with a PreparedStatement object using the setBlob, setClob, and setNClob methods. The Blob object created previously can be set as a parameter value on a PreparedStatement object, pstmt, as follows:

pstmt.setBlob(2,aBlob);

For an updatable ResultSet, the updateBlob, updateClob, and updateNClob methods can be used to update a Blob, Clob, or NClob column value. Update a ResultSet object, rs, of column type, BLOB, with the Blob object already created:

rs.updateBlob(3,aBlob);

Blob, Clob, and NClob data can be retrieved using the getBlob, getClob, and getNClob methods in the ResultSet and CallableStatement interfaces. Retrieve a Blob object corresponding to a BLOB column from a ResultSet object, rs :

Blob blob=rs.getBlob(2);

Either the entire data in a Blob object can be retrieved using the getBinaryStream() method, or the partial data in the Blob object can be retrieved using the getBinaryStream(long pos,long length) method. Here, the parameter, pos, specifies the offset position for start of data retrieval and the length parameter specifies the length in bytes of the data to be retrieved. Retrieve 100bytes of data from the Blob object that was created previously with an offset position of 200:

InputStream inputStream = aBlob.getBinaryStream(200, 100);

LOBs are valid at least for the duration of the transaction in which it is created. For long running transactions, it can be better to release LOB resources using the free() method:

aBlob.free();

JDBC 4.0 support in MySQL Database

MySQL database server provides support for the JDBC 4.0 specification in the Connector/J 5.1 JDBC drivers. MySQL's Connector/J 5.1 supports the JDBC 4.0 features, listed below:

  1. 1. Auto-registration of the JDBC driver with the DriverManager via the J2SE Service Provider mechanism.

  2. 2. Connection validity check using the isValid() method of the Connection interface.

  3. 3. Categorized SQLExceptions based on recoverability or retry-ability, and class of the underlying error.

  4. 4. Unwrapping of MySQL-specific extensions for JDBC.

  5. 5. Support for SQLXML. MySQL database does not support the SQL: 2003 SQL data type XML. JDBC 4.0 specification does not specify that the SQL data type to store a SQLXML object is required to be the SQL: 2003 XML data type. MySQL's Connector/J 5.1 JDBC driver supports the SQLXML Java data type.

  6. 6. Support for setting per-connection client info using the setClientInfo() method of the Connection interface.

  7. 7. Support for National Character Set data types NCHAR, NVARCHAR, LONGNVARCHAR, and NCLOB.

JDBC 4.0 support in IBM's DB2 UDB

IBM Data Server Driver for JDBC and SQLJ Version 4.0 supports the JDBC 4.0 specification. To use the JDBC 4.0 features, add the db2jcc4.jar file to the CLASSPATH environment variable. The JDBC 4.0 driver name is,"IBM Data Server Driver for JDBC and SQLJ" instead of the previous, "IBM DB2 JDBC Universal Driver Architecture". IBM Data Server Driver for JDBC and SQLJ supports most of the JDBC 4.0 features.

JDBC support has been added for the JDBC 4.0 java.sql.RowId interface for updating and retrieving data in ROWID columns. JDBC support has also been added for the java.sql.SQLXML interface for storing, updating, and retrieving data in XML columns. The IBM Data Server Driver for JDBC and SQLJ supports the following client info properties to identify the client connections:

  1. 1. ApplicationName

  2. 2. ClientAccountingInformation

  3. 3. ClientHostname

  4. 4. ClientUser

IBM Data Server Driver for JDBC and SQLJ supports the new SQLException subclasses, in the JDBC 4.0 specification. It also supports chained exceptions. The wrapper pattern is supported to access vendor-specific resources. The following IBM Data Server Driver for JDBC and SQLJ-specific interfaces in the com.ibm.db2.jcc package extend the Wrapper interface:

  1. 1. DB2Connection

  2. 2. DB2BaseDataSource

  3. 3. DB2SimpleDataSource

  4. 4. DB2Statement

  5. 5. DB2ResultSet

  6. 6. DB2DatabaseMetaData

 

Example Connection using a JDBC 4.0 Driver


We will connect with a database using a JDBC 4.0 driver. We will use Java DB as the example database. Java DB is Sun's version of the open-source Apache Derby database. Java DB is a lightweight (only 2MB), yet fully transactional, secure, and standards-based component. It also supports the SQL, JDBC, and Java EE standards. Java DB is a 100% Java technology database, and since Java is portable across platforms, Java DB can be run on any platform and its applications can be migrated to other open standard databases. Java DB is packaged with JDK 6. Therefore, all that is required to install Java DB is to install JDK 6. We connect with Java DB database using the JDBC 4.0 driver. Create a Java application, JDBCConnection.java, in a directory, C:/JavaDB, and add the directory to the CLASSPATH system environment variable. Java DB can be started in the embedded mode or as network server. Embedded mode is used to connect to the Java DB from a Java application running in the same JVM as the Java DB database. Java DB as a network server is used to connect with the database from different JVMs across the network. We will start Java DB in embedded mode from the JDBCConnection.java application. We will load the JDBC 4.0 driver automatically using the Java SE Service Provider mechanism. For automatic loading of the JDBC driver, we need to add the Java DB JDBC 4.0 driver JAR file, C:/Program Files/Sun/JavaDB/lib/derby.jar to the CLASSPATH variable. Java DB provides a batch script, setEmbeddedCP.bat in the bin directory to set the CLASSPATH for the embedded mode. Run the setEmbeddedCP script from the directory from which the JDBCConnection.java application is to be run as follows:

"C:\Program Files\Sun\JavaDB\bin\setEmbeddedCP.bat"

JAR file, derby.jar is added to the CLASSPATH. The derby.jar file includes a directory structure, META-INF/services/, and a file, java.sql.Driver, in the services directory. The java.sql.Driver file specifies the following JDBC driver class for the Java DB that is to be loaded automatically using the Java SE Service Provider mechanism:

org.apache.derby.jdbc.AutoloadedDriver

In the JDBCConnection.java application, specify the connection URL for the Java DB database. Create a new database instance by specifying the database name, demodb, and the create attribute as, true:

String url="jdbc:derby:demodb;create=true";

Connect with the Java DB database using the getConnection() method of the DriverManager class:

Connection conn = DriverManager.getConnection(url);

The DriverManager automatically loads the JDBC 4.0 driver class, org.apache.derby.jdbc.AutoloadedDriver, which is specified in the java.sql.Driver file using the Java SE Service Provider mechanism. The JDBC driver is not required to be loaded using the Class.forName() method using a JDBC 3.0 driver. The JDBCConnection.java application is listed below:

import java.sql.*;
public class JDBCConnection
{
public void connectToDatabase()
{
try
{
String url="jdbc:derby:demodb;create=true";
Connection conn = DriverManager.getConnection(url);
System.out.println("Connection Established");
}
catch (SQLException e)
{
System.out.println(e.getMessage());
}
}
public static void main(String[] argv)
{
JDBCConnection jdbc = new JDBCConnection();
jdbc.connectToDatabase();
}
}

Other Relational Database Management Systems (RDBMS) databases provide a JDBC 4.0 driver that can be connected using the JDBC 4.0 driver, as discussed for the Java DB database. The connection URLs, and JDBC 4.0 drivers for some of the commonly used databases are discussed in following table:

Database

Connection URL

JDBC 4.0 Driver

MySQL (4.1, 5.0, 5.1 and the 6.0 alpha).

jdbc:mysql://localhost:3306/test

MySQL Connector/J 5.1

(http://dev.mysql.com/downloads/connector/j/5.1.html) META-INF/services/java.sql.Driver is required to be added to the CLASSPATH as it is not included in the driver JAR file.

Oracle Database (9.01 and later).

jdbc:oracle:thin:@localhost:1521:ORCL

Oracle Database 11g JDBC Driver's (http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_111060.html) java.sql.Driver file is included in the JDBC JAR file, ojdbc6.jar.

IBM DB2 for Linux UNIX and Windows.

jdbc:db2://localhost:50000/SAMPLE

IBM Data Server Driver for JDBC and SQLJ version 4.0's (https://www14.software.ibm.com/webapp/iwm/web/reg/download.do?source=swg-informixfpd&S_PKG=dl&lang=en_US&cp=UTF-8) java.sql.Driver file is not included in the JDBC JAR file, db2jcc4.jar.

SQL Server Database (MS SQL Server 6.5 - 2005 with all Service Packs).

jdbc:inetdae7:localhost:1433

i-net MERLIA JDBC 4.0 driver for MS SQL Database's (http://www.inetsoftware.de/products/jdbc/mssql/merlia/) java.sql.Driver file is not included in the JDBC JAR file, Merlia.jar.

 

Summary


In this chapter, you have been introduced to the JDBC API and the new features in JDBC 4.0 specification. The DriverManager class is used to connect with a database. The Connection interface represents a database connection. The Savepoint interface represents a savepoint in a transaction. This is the point up to which the changes would be rolled back, if the transaction is rolled back. The Statement interface is used to run static SQL statements. The ResultSet interface represents the result set table generated by running SQL statements on a database. The PreparedStatement represents a compiled SQL statement that can be run more than once with different parameters. The DatabaseMetadata interface represents the database metadata, such as, table name, schema name, column name, and column type. The SQLException class represents the database access error or other SQL errors. JDBC 4.0 specification provides some new features to facilitate the development of JDBC applications. Support for new SQL data types has been added. The new features include, automatic SQL Driver loading, SQL: 2003 XML Data Type support, support for wrapper pattern, enhancements in SQLException, and enhanced connection management.

About the Author

  • Deepak Vohra

    Deepak Vohra is consultant and principle a member of the NuBean software company. He is a Sun Certified Java Programmer (SCJP) and Sun Certified Web Component Developer (SCWCD) and has worked in the fields of XML, Java programming, and J2EE for over 10 years. He is the coauthor of the Apress book Pro XML Development with Java Technology. Deepak is the author of several Packt Publishing books, including Processing XML documents with Oracle JDeveloper 11g and Java EE Development with Eclipse. Deepak is also a Docker Mentor and has published four other Docker-related books.

    Browse publications by this author
Book Title
Access this book, plus 7,500 other titles for FREE
Access now