sql

What the JDBC^TM 4.0 API Includes

Versions

• The JDBC 3.0 API
• The JDBC 2.1 core API
• The JDBC 2.0 Optional Package API
  (Note that the JDBC 2.1 core API and the JDBC 2.0 Optional Package API together are referred to as the JDBC 2.0 API.)
• The JDBC 1.2 API
• The JDBC 1.0 API

Classes, interfaces, methods, fields, constructors, and exceptions have the following "since" tags that indicate when they were introduced into the Java platform. When these "since" tags are used in Javadoc^TM comments for the JDBC API, they indicate the following:

• Since 1.6 -- new in the JDBC 4.0 API and part of the Java SE platform, version 6
• Since 1.4 -- new in the JDBC 3.0 API and part of the J2SE platform, version 1.4
• Since 1.2 -- new in the JDBC 2.0 API and part of the J2SE platform, version 1.2
• Since 1.1 or no "since" tag -- in the original JDBC 1.0 API and part of the JDK^TM, version 1.1

NOTE: Many of the new features are optional; consequently, there is some variation in drivers and the features they support. Always check your driver's documentation to see whether it supports a feature before you try to use it.

NOTE: The class SQLPermission was added in the Java^TM 2 SDK, Standard Edition, version 1.3 release. This class is used to prevent unauthorized access to the logging stream associated with the DriverManager, which may contain information such as table names, column data, and so on.

What the java.sql Package Contains

• Making a connection with a database via the DriverManager facility
  • DriverManager class -- makes a connection with a driver
  • SQLPermission class -- provides permission when code running within a Security Manager, such as an applet, attempts to set up a logging stream through the DriverManager
  • Driver interface -- provides the API for registering and connecting drivers based on JDBC technology ("JDBC drivers"); generally used only by the DriverManager class
  • DriverPropertyInfo class -- provides properties for a JDBC driver; not used by the general user
• Sending SQL statements to a database
  • Statement -- used to send basic SQL statements
  • PreparedStatement -- used to send prepared statements or basic SQL statements (derived from Statement)
  • CallableStatement -- used to call database stored procedures (derived from PreparedStatement)
  • Connection interface -- provides methods for creating statements and managing connections and their properties
  • Savepoint -- provides savepoints in a transaction
• Retrieving and updating the results of a query
  • ResultSet interface
• Standard mappings for SQL types to classes and interfaces in the Java programming language
  • Array interface -- mapping for SQL ARRAY
  • Blob interface -- mapping for SQL BLOB
  • Clob interface -- mapping for SQL CLOB
  • Date class -- mapping for SQL DATE
  • NClob interface -- mapping for SQL NCLOB
  • Ref interface -- mapping for SQL REF
  • RowId interface -- mapping for SQL ROWID
  • Struct interface -- mapping for SQL STRUCT
  • SQLXML interface -- mapping for SQL XML
  • Time class -- mapping for SQL TIME
  • Timestamp class -- mapping for SQL TIMESTAMP
  • Types class -- provides constants for SQL types
• Custom mapping an SQL user-defined type (UDT) to a class in the Java programming language
  • SQLData interface -- specifies the mapping of a UDT to an instance of this class
  • SQLInput interface -- provides methods for reading UDT attributes from a stream
  • SQLOutput interface -- provides methods for writing UDT attributes back to a stream
• Metadata
  • DatabaseMetaData interface -- provides information about the database
  • ResultSetMetaData interface -- provides information about the columns of a ResultSet object
  • ParameterMetaData interface -- provides information about the parameters to PreparedStatement commands
• Exceptions
  • SQLException -- thrown by most methods when there is a problem accessing data and by some methods for other reasons
  • SQLWarning -- thrown to indicate a warning
  • DataTruncation -- thrown to indicate that data may have been truncated
  • BatchUpdateException -- thrown to indicate that not all commands in a batch update executed successfully

java.sql and javax.sql Features Introduced in the JDBC 4.0 API

• auto java.sql.Driver discovery -- no longer need to load a java.sql.Driver class via Class.forName
• National Character Set support added
• Support added for the SQL:2003 XML data type
• SQLException enhancements -- Added support for cause chaining; New SQLExceptions added for common SQLState class value codes
• Enhanced Blob/Clob functionality -- Support provided to create and free a Blob/Clob instance as well as additional methods added to improve accessiblity
• Support added for accessing a SQL ROWID
• Support added to allow a JDBC application to access an instance of a JDBC resource that has been wrapped by a vendor, usually in an application server or connection pooling environment.
• Availability to be notfied when a PreparedStatement that is associated with a PooledConnection has been closed or the driver determines is invalid

java.sql and javax.sql Features Introduced in the JDBC 3.0 API

• Pooled statements -- reuse of statements associated with a pooled connection
• Savepoints -- allow a transaction to be rolled back to a designated savepoint
• Properties defined for ConnectionPoolDataSource -- specify how connections are to be pooled
• Metadata for parameters of a PreparedStatement object
• Ability to retrieve values from automatically generated columns
• Ability to have multiple ResultSet objects returned from CallableStatement objects open at the same time
• Ability to identify parameters to CallableStatement objects by name as well as by index
• ResultSet holdability -- ability to specify whether cursors should be held open or closed at the end of a transaction
• Ability to retrieve and update the SQL structured type instance that a Ref object references
• Ability to programmatically update BLOB, CLOB, ARRAY, and REF values.
• Addition of the java.sql.Types.DATALINK data type -- allows JDBC drivers access to objects stored outside a data source
• Addition of metadata for retrieving SQL type hierarchies

java.sql Features Introduced in the JDBC 2.1 Core API

• Scrollable result sets--using new methods in the ResultSet interface that allow the cursor to be moved to a particular row or to a position relative to its current position
• Batch updates
• Programmatic updates--using ResultSet updater methods
• New data types--interfaces mapping the SQL3 data types
• Custom mapping of user-defined types (UDTs)
• Miscellaneous features, including performance hints, the use of character streams, full precision for java.math.BigDecimal values, additional security, and support for time zones in date, time, and timestamp values.

javax.sql Features Introduced in the JDBC 2.0 Optional Package API

• The DataSource interface as a means of making a connection. The Java Naming and Directory Interface^TM (JNDI) is used for registering a DataSource object with a naming service and also for retrieving it.
• Pooled connections -- allowing connections to be used and reused
• Distributed transactions -- allowing a transaction to span diverse DBMS servers
• RowSet technology -- providing a convenient means of handling and passing data

Custom Mapping of UDTs

1. Defining the SQL structured type or DISTINCT type in SQL
2. Defining the class in the Java programming language to which the SQL UDT will be mapped. This class must implement the SQLData interface.
3. Making an entry in a Connection object's type map that contains two things:
   • the fully-qualified SQL name of the UDT
   • the Class object for the class that implements the SQLData interface

When these are in place for a UDT, calling the methods ResultSet.getObject or CallableStatement.getObject on that UDT will automatically retrieve the custom mapping for it. Also, the PreparedStatement.setObject method will automatically map the object back to its SQL type to store it in the data source.

Package Specification

• Specification of the JDBC 4.0 API

Related Documentation

• Getting Started--overviews of the major interfaces

• Chapters on the JDBC API--from the online version of The Java Tutorial Continued

• JDBC^TMAPI Tutorial and Reference, Third Edition-- a complete reference and tutorial for the JDBC 3.0 API

@since 1.1

The javax.sql package provides for the following:

1. The DataSource interface as an alternative to the DriverManager for establishing a connection with a data source
2. Connection pooling and Statement pooling
3. Distributed transactions
4. Rowsets

Applications use the DataSource and RowSet APIs directly, but the connection pooling and distributed transaction APIs are used internally by the middle-tier infrastructure.

Using a DataSource Object to Make a Connection

These are the main advantages of using a DataSource object to make a connection:

• Changes can be made to a data source's properties, which means that it is not necessary to make changes in application code when something about the data source or driver changes.
• Connection and Statement pooling and distributed transactions are available through a DataSource object that is implemented to work with the middle-tier infrastructure. Connections made through the DriverManager do not have connection and statement pooling or distributed transaction capabilities.

Driver vendors provide DataSource implementations. A particular DataSource object represents a particular physical data source, and each connection the DataSource object creates is a connection to that physical data source.

A logical name for the data source is registered with a naming service that uses the Java Naming and Directory Interface^TM (JNDI) API, usually by a system administrator or someone performing the duties of a system administrator. An application can retrieve the DataSource object it wants by doing a lookup on the logical name that has been registered for it. The application can then use the DataSource object to create a connection to the physical data source it represents.

A DataSource object can be implemented to work with the middle tier infrastructure so that the connections it produces will be pooled for reuse. An application that uses such a DataSource implementation will automatically get a connection that participates in connection pooling. A DataSource object can also be implemented to work with the middle tier infrastructure so that the connections it produces can be used for distributed transactions without any special coding.

Connection Pooling and Statement Pooling

Connection pooling is totally transparent. It is done automatically in the middle tier of a Java EE configuration, so from an application's viewpoint, no change in code is required. An application simply uses the DataSource.getConnection method to get the pooled connection and uses it the same way it uses any Connection object.

The classes and interfaces used for connection pooling are:

• ConnectionPoolDataSource
• PooledConnection
• ConnectionEvent
• ConnectionEventListener
• StatementEvent
• StatementEventListener

If the connection pool manager supports Statement pooling, for PreparedStatements, which can be determined by invoking the method DatabaseMetaData.supportsStatementPooling, the connection pool manager will register as a StatementEventListener object with the new PooledConnection object. When the PreparedStatement is closed or there is an error, the connection pool manager (being a listener) gets a notification that includes a StatementEvent object.

Distributed Transactions

The classes and interfaces used for distributed transactions are:

• XADataSource
• XAConnection

The XAConnection interface is derived from the PooledConnection interface, so what applies to a pooled connection also applies to a connection that is part of a distributed transaction. A transaction manager in the middle tier handles everything transparently. The only change in application code is that an application cannot do anything that would interfere with the transaction manager's handling of the transaction. Specifically, an application cannot call the methods Connection.commit or Connection.rollback, and it cannot set the connection to be in auto-commit mode (that is, it cannot call Connection.setAutoCommit(true)).

An application does not need to do anything special to participate in a distributed transaction. It simply creates connections to the data sources it wants to use via the DataSource.getConnection method, just as it normally does. The transaction manager manages the transaction behind the scenes. The XADataSource interface creates XAConnection objects, and each XAConnection object creates an XAResource object that the transaction manager uses to manage the connection.

Rowsets

1. Event Notification
   • RowSetListener
     A RowSet object is a JavaBeans^TM component because it has properties and participates in the JavaBeans event notification mechanism. The RowSetListener interface is implemented by a component that wants to be notified about events that occur to a particular RowSet object. Such a component registers itself as a listener with a rowset via the RowSet.addRowSetListener method.

     When the RowSet object changes one of its rows, changes all of it rows, or moves its cursor, it also notifies each listener that is registered with it. The listener reacts by carrying out its implementation of the notification method called on it.

   • RowSetEvent
     As part of its internal notification process, a RowSet object creates an instance of RowSetEvent and passes it to the listener. The listener can use this RowSetEvent object to find out which rowset had the event.

2. Metadata
   • RowSetMetaData
     This interface, derived from the ResultSetMetaData interface, provides information about the columns in a RowSet object. An application can use RowSetMetaData methods to find out how many columns the rowset contains and what kind of data each column can contain.

     The RowSetMetaData interface provides methods for setting the information about columns, but an application would not normally use these methods. When an application calls the RowSet method execute, the RowSet object will contain a new set of rows, and its RowSetMetaData object will have been internally updated to contain information about the new columns.

3. The Reader/Writer Facility
   A RowSet object that implements the RowSetInternal interface can call on the RowSetReader object associated with it to populate itself with data. It can also call on the RowSetWriter object associated with it to write any changes to its rows back to the data source from which it originally got the rows. A rowset that remains connected to its data source does not need to use a reader and writer because it can simply operate on the data source directly.
   • RowSetInternal
     By implementing the RowSetInternal interface, a RowSet object gets access to its internal state and is able to call on its reader and writer. A rowset keeps track of the values in its current rows and of the values that immediately preceded the current ones, referred to as the original values. A rowset also keeps track of (1) the parameters that have been set for its command and (2) the connection that was passed to it, if any. A rowset uses the RowSetInternal methods behind the scenes to get access to this information. An application does not normally invoke these methods directly.

   • RowSetReader
     A disconnected RowSet object that has implemented the RowSetInternal interface can call on its reader (the RowSetReader object associated with it) to populate it with data. When an application calls the RowSet.execute method, that method calls on the rowset's reader to do much of the work. Implementations can vary widely, but generally a reader makes a connection to the data source, reads data from the data source and populates the rowset with it, and closes the connection. A reader may also update the RowSetMetaData object for its rowset. The rowset's internal state is also updated, either by the reader or directly by the method RowSet.execute.
   • RowSetWriter
     A disconnected RowSet object that has implemented the RowSetInternal interface can call on its writer (the RowSetWriter object associated with it) to write changes back to the underlying data source. Implementations may vary widely, but generally, a writer will do the following:
     • Make a connection to the data source
     • Check to see whether there is a conflict, that is, whether a value that has been changed in the rowset has also been changed in the data source
     • Write the new values to the data source if there is no conflict
     • Close the connection

The RowSet interface may be implemented in any number of ways, and anyone may write an implementation. Developers are encouraged to use their imaginations in coming up with new ways to use rowsets.

IMPORTANT NOTE: Code that uses API marked "Since 1.6" must be run using a JDBC technology driver that implements the JDBC 4.0 API. You must check your driver documentation to be sure that it implements the particular features you want to use.

Package Specification

• Specification of the JDBC 4.0 API

Related Documentation

• JDBC^TM API Tutorial and Reference, Third Edition:

@since 1.4

Table of Contents

• 1.0 Package Specification
• 2.0 Standard RowSet Definitions
• 3.0 Implementater's Guide
• 4.0 Related Specifications
• 5.0 Related Documentation

1.0 Package Specification

Note: The interface definitions provided in this package form the basis for all compliant JDBC RowSet implementations. Vendors and more advanced developers who intend to provide their own compliant RowSet implementations should pay particular attention to the assertions detailed in specification interfaces.

2.0 Standard RowSet Definitions

• JdbcRowSet - A wrapper around a ResultSet object that makes it possible to use the result set as a JavaBeans^TM component. Thus, a JdbcRowSet object can be a Bean that any tool makes available for assembling an application as part of a component based architecture . A JdbcRowSet object is a connected RowSet object, that is, it must continually maintain its connection to its data source using a JDBC technology-enabled driver ("JDBC driver"). In addition, a JdbcRowSet object provides a fully updatable and scrollable tabular data structure as defined in the JDBC 3.0 specification.

• CachedRowSet^TM> - A CachedRowSet object is a JavaBeans^TM component that is scrollable, updatable, serializable, and generally disconnected from the source of its data. A CachedRowSet object typically contains rows from a result set, but it can also contain rows from any file with a tabular format, such as a spreadsheet. CachedRowSet implementations must use the SyncFactory to manage and obtain pluggable SyncProvider objects to provide synchronization between the disconnected RowSet object and the originating data source. Typically a SyncProvider implementation relies upon a JDBC driver to obtain connectivity to a particular data source. Further details on this mechanism are discussed in the javax.sql.rowset.spi package specification.

• WebRowSet - A WebRowSet object is an extension of CachedRowSet that can read and write a RowSet object in a well formed XML format. This class calls an XmlReader object (an extension of the RowSetReader interface) to read a rowset in XML format. It calls an XmlWriter object (an extension of the RowSetWriter interface) to write a rowset in XML format. The reader and writer required by WebRowSet objects are provided by the SyncFactory in the form of SyncProvider implementations. In order to ensure well formed XML usage, a standard generic XML Schema is defined and published at http://java.sun.com/xml/ns/jdbc/webrowset.xsd.

• FilteredRowSet - A FilteredRowSet object provides filtering functionality in a programmatic and extensible way. There are many instances when a RowSet object has a need to provide filtering in its contents without sacrificing the disconnected environment, thus saving the expense of having to create a connection to the data source. Solutions to this need vary from providing heavyweight full scale SQL query abilities, to portable components, to more lightweight approaches. A FilteredRowSet object consumes an implementation of the Predicate interface, which may define a filter at run time. In turn, a FilteredRowSet object is tasked with enforcing the set filter for both inbound and outbound read and write operations. That is, all filters can be considered as bi-directional. No standard filters are defined; however, sufficient mechanics are specified to permit any required filter to be implemented.

• JoinRowSet - The JoinRowSet interface describes a mechanism by which relationships can be established between two or more standard RowSet implementations. Any number of RowSet objects can be added to a JoinRowSet object provided the RowSetobjects can be related in a SQL JOIN like fashion. By definition, the SQL JOIN statement is used to combine the data contained in two (or more) relational database tables based upon a common attribute. By establishing and then enforcing column matches, a JoinRowSet object establishes relationships between RowSet instances without the need to touch the originating data source.

Standard JDBC RowSet implementations may use these utility classes to assist in the serialization of disconnected RowSet objects. This is useful when transmitting a disconnected RowSet object over the wire to a different VM or across layers within an application.

1.0 SerialArray

2.0 SerialBlob

3.0 SerialClob

5.0 SerialDatalink

6.0 SerialJavaObject

7.0 SerialRef

8.0 SerialStruct

9.0 SQLInputImpl

10.0 SQLOutputImpl

Custom Mapping

A programmer defines the mapping by implementing the interface SQLData. For example, if an SQL structured type named AUTHORS has the attributes NAME, TITLE, and PUBLISHER, it could be mapped to a Java class named Authors. The Authors class could have the fields name, title, and publisher, to which the attributes of AUTHORS are mapped. In such a case, the implementation of SQLData could look like the following:

   public class Authors implements SQLData {
       public String name;
       public String title;
       public String publisher;

       private String sql_type;
       public String getSQLTypeName() {
           return sql_type;
       }

       public void readSQL(SQLInput stream, String type)
                                  throws SQLException  {
           sql_type = type;
           name = stream.readString();
           title = stream.readString();
           publisher = stream.readString();
       }

       public void writeSQL(SQLOutput stream) throws SQLException {
           stream.writeString(name);
           stream.writeString(title);
           stream.writeString(publisher);
       }
   } 

    java.util.Map map = new java.util.HashMap();
    map.put("SCHEMA_NAME.AUTHORS", Class.forName("Authors");

For a disconnected RowSet object, custom mapping can be done only when a Map object is passed to the method or constructor that will be doing the custom mapping. The situation is different for connected RowSet objects because they maintain a connection with the data source. A method that does custom mapping and is called by a disconnected RowSet object may use the Map object that is associated with the Connection object being used. So, in other words, if no map is specified, the connection's type map can be used by default.

Table of Contents

• 1.0 Package Specification
• 2.0 Service Provider Architecture
• 3.0 Implementer's Guide
• 4.0 Resolving Synchronization Conflicts
• 5.0 Related Specifications
• 6.0 Related Documentation

1.0 Package Specification

     http://www.syncml.org

For the the next level up, the writer checks to see if there are any conflicts, and if there are, it does not write anything to the data source. The problem with this concurrency level is that if another party has modified the corresponding data in the data source since the RowSet object got its data, the changes made to the RowSet object are lost. The RIOptimisticProvider implementation uses this level of synchronization.

At higher levels of synchronization, referred to as pessimistic concurrency, the writer take steps to avoid conflicts by setting locks. Setting locks can vary from setting a lock on a single row to setting a lock on a table or the entire data source. The level of synchronization is therefore a tradeoff between the ability of users to access the data source concurrently and the ability of the writer to keep the data in the RowSet object and its data source synchronized.

It is a requirement that all disconnected RowSet objects (CachedRowSet, FilteredRowSet, JoinRowSet, and WebRowSet objects) obtain their SyncProvider objects from the SyncFactory mechanism.

The reference implementation (RI) provides two synchronization providers.

• RIOptimisticProvider
  The default provider that the SyncFactory instance will supply to a disconnected RowSet object when no provider implementation is specified.
  This synchronization provider uses an optimistic concurrency model, assuming that there will be few conflicts among users who are accessing the same data in a database. It avoids using locks; rather, it checks to see if there is a conflict before trying to synchronize the RowSet object and the data source. If there is a conflict, it does nothing, meaning that changes to the RowSet object are not persisted to the data source.
• RIXMLProvider
  A synchronization provider that can be used with a WebRowSet object, which is a rowset that can be written in XML format or read from XML format. The RIXMLProvider implementation does no checking at all for conflicts and simply writes any updated data in the WebRowSet object to the underlying data source. WebRowSet objects use this provider when they are dealing with XML data.

If a WebRowSet object does not specify a provider in its constructor, the SyncFactory will give it an instance of RIOptimisticProvider. However, the constructor for WebRowSet is implemented to set the provider to the RIXMLProvider, which reads and writes a RowSet object in XML format.

See the SyncProvider class specification for further details.

Vendors may develop a SyncProvider implementation with any one of the possible levels of synchronization, thus giving RowSet objects a choice of synchronization mechanisms. A vendor can make its implementation available by registering the fully qualified class name with Sun Microsystems at jdbc@sun.com. This process is discussed in further detail below.

2.0 Service Provider Interface Architecture

2.1 Overview

The Service Provider Interface provides a pluggable mechanism by which SyncProvider implementations can be registered and then generated when required. The lazy reference mechanism employed by the SyncFactory limits unnecessary resource consumption by not creating an instance until it is required by a disconnected RowSet object. The SyncFactory class also provides a standard API to configure logging options and streams that may be provided by a particular SyncProvider implementation.

2.2 Registering with the SyncFactory

A third party SyncProvider implementation must be registered with the SyncFactory in order for a disconnected RowSet object to obtain it and thereby use its javax.sql.RowSetReader and javax.sql.RowSetWriter implementations. The following registration mechanisms are available to all SyncProvider implementations:

• System properties - Properties set at the command line. These properties are set at run time and apply system-wide per invocation of the Java application. See the section "Related Documentation" further related information.

• Property Files - Properties specified in a standard property file. This can be specified using a System Property or by modifying a standard property file located in the platform run-time. The reference implementation of this technology includes a standard property file than can be edited to add additional SyncProvider objects.

• JNDI Context - Available providers can be registered on a JNDI context. The SyncFactory will attempt to load SyncProvider objects bound to the context and register them with the factory. This context must be supplied to the SyncFactory for the mechanism to function correctly.

Details on how to specify the system properties or properties in a property file and how to configure the JNDI Context are explained in detail in the SyncFactory class description.

2.3 SyncFactory Provider Instance Generation Policies

The SyncFactory generates a requested SyncProvider object if the provider has been correctly registered. The following policies are adhered to when either a disconnected RowSet object is instantiated with a specified SyncProvider implementation or is reconfigured at runtime with an alternative SyncProvider object.

• If a SyncProvider object is specified and the SyncFactory contains no reference to the provider, a SyncFactoryException is thrown.

• If a SyncProvider object is specified and the SyncFactory contains a reference to the provider, the requested provider is supplied.

• If no SyncProvider object is specified, the reference implementation provider RIOptimisticProvider is supplied.

These policies are explored in more detail in the SyncFactory class.