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.
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");
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.
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.
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:
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.
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();
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:
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}
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);
}
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 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:
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). |
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. Rowids are the fastest way to access a row in a database table.
2. Rowids are unique identifiers for rows in a table.
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:
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. Auto-registration of the JDBC driver with the
DriverManagervia the J2SE Service Provider mechanism.2. Connection validity check using the
isValid()method of theConnectioninterface.3. Categorized
SQLExceptionsbased on recoverability or retry-ability, and class of the underlying error.4. Unwrapping of MySQL-specific extensions for JDBC.
5. Support for
SQLXML. MySQL database does not support the SQL: 2003 SQL data typeXML. JDBC 4.0 specification does not specify that the SQL data type to store aSQLXMLobject is required to be the SQL: 2003XMLdata type. MySQL's Connector/J 5.1 JDBC driver supports theSQLXMLJava data type.6. Support for setting per-connection client info using the
setClientInfo()method of theConnectioninterface.7. Support for National Character Set data types NCHAR, NVARCHAR, LONGNVARCHAR, and NCLOB.
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. ApplicationName
2. ClientAccountingInformation
3. ClientHostname
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. DB2Connection
2. DB2BaseDataSource
3. DB2SimpleDataSource
4. DB2Statement
5. DB2ResultSet
6. DB2DatabaseMetaData