Print-friendly Version

For Driver Writers

This document specifies the minimum that a driver must implement to be compliant with the JDBC API. The requirements are subdivided to correspond to the three major release of the JDBC API.

Compliance with the JDBC 3.0 API

The full list of requirements for compliance with the JDBC 3.0 API (which includes the JDBC 2.0 and 1.0 APIs) is in the JDBC 3.0 Specification, which is available for download.

The requirements for JDBC 2.0 and 1.0 compliance, provided here, are based on a chapter in the book JDBC API Tutorial and Reference, Second Edition: Universal Data Access for the Java 2 Platform, published by Addison Wesley.

As far as possible, there should be a standard JDBC API that works in a uniform way across all databases. To this end, JDBC imposes some requirements that apply to all drivers. However, it is unavoidable that different databases support different SQL features and provide different semantics for some operations. Consequently, JDBC allows some variations in particular situations.

This chapter outlines requirements and allowed variations, addresses some implementation issues, and lists security responsibilities for drivers

A.1 Requirements for All Drivers

This section covers the requirements for standard implementations of JDBC drivers. In general, a driver is not required to support a feature that its DBMS does not support.

A.1.1 Implement Methods in the Interfaces

All of the methods in the interfaces contained in the JDBC 1.0 API must be implemented so that they support at least ANSI SQL-92 Entry Level. Beyond this base level, which is always required, a driver does not have to support a feature if the DBMS for which it is written does not support that feature. For example, if a DBMS does not support SQL3 data types, implementing the interfaces that support the new types (Array, Blob, Clob, Ref, SQLData, SQLInput, SQLOutput, and Struct) is optional. Or if a DBMS does not support an extension feature, such as connection pooling, the interfaces that support connection pooling (ConnectionEventListener, ConnectionPoolDataSource, PooledConnection) are optional. If an individual method supports a feature that a DBMS does not include, that method may be implemented to throw an SQLException. See section A.2 for listings of what is required and optional in a driver implementation.

Metadata interfaces should be fully implemented. The purpose of the DatabaseMetaData interface is to tell users what a DBMS does and does not support, so every method should be implemented. The ResultSetMetaData interface should likewise always be fully implemented. A driver vendor that supplies a javax.sql.RowSet implementation should also supply a full implementation of the javax.sql.RowSetMetaData interface.

Note that the interfaces in the JDBC Optional Package API are an integral part of the JDBC 2.1 API, and a JDBC driver should implement them on the same basis as the JDBC 2.1 core API. The RowSet interface and the interfaces that support it, however, are not included in the following lists because they are implemented on top of JDBC and thus are not part of a driver's implementation.

JDBC drivers that support distributed transactions must support the XAConnection and XADataSource interfaces.

The following interfaces must always be fully implemented:

java.sql.Driver
java.sql.DatabaseMetaData
java.sql.ResultSetMetaData
 

The following interfaces must be implemented, but some methods in them are optional if the implementation of those methods depends on a feature that the DBMS does not support:

java.sql.CallableStatement
java.sql.Connection
java.sql.PreparedStatement
java.sql.ResultSet
java.sql.Statement

The following interfaces are optional (may be omitted) if a driver cannot support them:

java.sql.Array
java.sql.Blob
javax.sql.ConnectionEventListener
javax.sql.ConnectionPoolDataSource
java.sql.Clob
javax.sql.DataSource
javax.sql.PooledConnection
java.sql.Ref
java.sql.SQLData
java.sql.SQLInput
java.sql.SQLOutput
java.sql.Struct
javax.sql.XAConnection
javax.sql.XADataSource

The following classes and exceptions are already fully implemented in the JDBC 2.1 API:

java.sql.BatchUpdateException javax.sql.ConnectionEvent java.sql.DataTruncation java.sql.Date java.sql.DriverManager java.sql.DriverPropertyInfo java.sql.SQLException java.sql.SQLWarning java.sql.Time java.sql.Timestamp java.sql.Types

A test suite is provided with the JDBC API to help driver developers test whether their drivers conform to JDBC requirements. In addition to implementing all of the methods in the interfaces, a JDBC driver must also comply with the requirements presented in the following sections.

A.1.2 Implement a Static Initializer

Every Driver class should contain a special static block, sometimes referred to as a static initializer, that does two things when it is loaded:

1. Creates an instance of itself
2. Registers the newly-created instance by calling the method DriverManager.registerDriver

   This static initializer is demonstrated in the following code fragment.

public class MyDriver implements java.sql.Driver { static { java.sql.DriverManager.registerDriver(new MyDriver()); } . . . }

When the Driver class is implemented to do these two things, a user can load and register a JDBC driver with the DriverManager simply by calling the method Class.forName with the Driver class name as the argument.

Note that drivers loaded via the DataSource API should not automatically register with the DriverManager. The DriverManager and DriverPropertyInfo classes and the Driver interface may be deprecated in the future.

A.1.3 Support Extensions to SQL-92 Entry Level

Certain SQL features beyond SQL-92 Entry Level are widely supported and are desirable to include as part of the JDBC compliance definition so that applications can depend on the portability of these features. However, SQL-92 Transitional Level, the next higher level of SQL compliance defined by ANSI, is not widely supported. Even where Transitional Level semantics are supported, the syntax is often different across DBMSs.

Therefore, JDBC defines two kinds of extensions to SQL-92 Entry Level that must be supported by a JDBC Compliant driver:

• Selective Transitional Level syntax and semantics. Currently the only feature at this level that is required for JDBC compliance is the command DROP TABLE.
• An escape syntax that supports the Selective Transitional Level semantics. A driver should scan for and translate this escape syntax into DBMS-specific syntax. Note that these escapes need only be supported where the underlying database supports the corresponding Transitional Level semantics. Where appropriate, an escape syntax must be included for stored procedures, time and date literals, scalar functions, LIKE escape characters, and outer joins.

This ODBC-compatible escape syntax is, in general, not the same as has been adopted by ANSI in SQL-92 Transitional Level for the same functionality. In cases where all of the desired DBMSs support the standard SQL-92 syntax, the user is encouraged to use that syntax instead of these escapes. When enough DBMSs support the more advanced SQL-92 syntax and semantics, these escapes should no longer be necessary. In the meantime, however, JDBC drivers should support them.

A.1.4 Support Scalar Functions

Support for scalar functions needs some extra explanation. JDBC supports numeric, string, time, date, system, and conversion functions on scalar values. For those who want more detail, the Open Group CLI specification provides more information on the semantics of the scalar functions. The functions supported are listed below for reference.

If a DBMS supports a scalar function, the driver should also. Because scalar functions are supported by different DBMSs with slightly different syntax, it is the driver's job either to map them into the appropriate syntax or to implement the functions directly in the driver.

A user should be able to find out which functions are supported by calling metadata methods. For example, the method DatabaseMetaData.getNumericFunctions should return a comma separated list of the Open Group CLI names of the numeric functions supported. Similarly, the method DatabaseMetaData.getStringFunctions should return a list of string functions supported, and so on.

The scalar functions are listed by category:

NUMERIC FUNCTIONS

Function Name Function Returns ABS(number) Absolute value of number ACOS(float) Arccosine, in radians, of float ASIN(float) Arcsine, in radians, of float ATAN(float) Arctangent, in radians, of float ATAN2(float1, float2) Arctangent, in radians, of float2 / float1 CEILING(number) Smallest integer >= number COS(float) Cosine of float radians COT(float) Cotangent of float radians DEGREES(number) Degrees in number radians EXP(float) Exponential function of float FLOOR(number) Largest integer <= number LOG(float) Base e logarithm of float LOG10(float) Base 10 logarithm of float MOD(integer1, integer2) Remainder for integer1 / integer2 PI() The constant pi POWER(number, power) number raised to (integer) power RADIANS(number) Radians in number degrees RAND(integer) Random floating point for seed integer ROUND(number, places) number rounded to places places SIGN(number) -1 to indicate number is < 0; 0 to indicate number is = 0; 1 to indicate number is > 0 SIN(float) Sine of float radians SQRT(float) Square root of float TAN(float) Tangent of float radians TRUNCATE(number, places) number truncated to places places

STRING FUNCTIONS

Function Name Function Returns ASCII(string) Integer representing the ASCII code value of the leftmost character in string CHAR(code) Character with ASCII code value code, where code is between 0 and 255 CONCAT(string1, string2) Character string formed by appending string2 to string1; if a string is null, the result is DBMS-dependent DIFFERENCE(string1, string2) Integer indicating the difference between the values returned by the function SOUNDEX for string1 and string2 INSERT(string1, start, A character string formed by deleting length length, string2) characters from string1 beginning at start, and inserting string2 into string1 at start LCASE(string) Converts all uppercase characters in string to lowercase LEFT(string, count) The count leftmost characters from string LENGTH(string) Number of characters in string, excluding trailing blanks LOCATE(string1, Position in string2 of the first occurrence of string2[, start]) string1, searching from the beginning of string2; if start is specified, the search begins from position start. 0 is returned if string2 does not contain string1. Position 1 is the first character in string2. LTRIM(string) Characters of string with leading blank spaces removed REPEAT(string, count) A character string formed by repeating string count times REPLACE(string1, string2, Replaces all occurrences of string2 in string1 string3) with string3 RIGHT(string, count) The count rightmost characters in string RTRIM(string) The characters of string with no trailing blanks SOUNDEX(string) A character string, which is data source-dependent, representing the sound of the words in string; this could be a four-digit SOUNDEX code, a phonetic representation of each word, etc. SPACE(count) A character string consisting of count spaces SUBSTRING(string, start, A character string formed by extracting length length) characters from string beginning at start UCASE(string) Converts all lowercase characters in string to uppercase

TIME and DATE FUNCTIONS

Function Name Function Returns CURDATE() The current date as a date value CURTIME() The current local time as a time value DAYNAME(date) A character string representing the day component of date; the name for the day is specific to the data source DAYOFMONTH(date) An integer from 1 to 31 representing the day of the month in date DAYOFWEEK(date) An integer from 1 to 7 representing the day of the week in date; 1 represents Sunday DAYOFYEAR(date) An integer from 1 to 366 representing the day of the year in date HOUR(time) An integer from 0 to 23 representing the hour component of time MINUTE(time) An integer from 0 to 59 representing the minute component of time MONTH(date) An integer from 1 to 12 representing the month component of date MONTHNAME(date) A character string representing the month component of date; the name for the month is specific to the data source NOW() A timestamp value representing the current date and time QUARTER(date) An integer from 1 to 4 representing the quarter in date; 1 represents January 1 through March 31 SECOND(time) An integer from 0 to 59 representing the second component of time TIMESTAMPADD(interval, A timestamp calculated by adding count num- count, timestamp) ber of interval(s) to timestamp; interval may be one of the following: SQL_TSI_FRAC_SECOND, SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, or SQL_TSI_YEAR TIMESTAMPDIFF(interval, An integer representing the number of inter- timestamp1, timestamp2) val(s) by which timestamp2 is greater than timestamp1; interval may be one of the following: SQL_TSI_FRAC_SECOND, SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, or SQL_TSI_YEAR WEEK(date) An integer from 1 to 53 representing the week of the year in date YEAR(date) An integer representing the year component of date

SYSTEM FUNCTIONS

Function Name Function Returns DATABASE() Name of the database IFNULL(expression, value) value if expression is null; expression if expression is not null USER() User name in the DBMS

CONVERSION FUNCTIONS

Function Name Function Returns CONVERT(value, SQLtype) value converted to SQLtype where SQLtype may be one of the following SQL types: BIGINT, BINARY, BIT, CHAR, DATE, DECIMAL, DOUBLE, FLOAT, INTEGER, LONGVARBINARY, LONGVARCHAR, REAL, SMALLINT, TIME, TIMESTAMP, TINYINT, VARBINARY, or VARCHAR

A.1.5 Provide Locks for Positioned Updates and Deletes

The JDBC 2.l core API provides various methods for positioning a result set cursor, thereby making it easy to update or delete a particular row in a result set. The JDBC 1.0 API, however, provides only simple cursor support, which makes positioned updates and deletes a little more complicated.

When a query is executed with the method executeQuery, the result is a ResultSet object with a cursor pointing above the first row. This cursor will remain valid until the ResultSet object or its parent Statement object (the query that generated the result set) is closed. If a driver does not support the new cursor positioning methods, an application must get the name of the cursor associated with the current ResultSet object by calling the method ResultSet.getCursorName. This cursor name can then be used in positioned update or positioned delete statements.

Not all DBMSs support positioned updates and positioned deletes. An application can use the JDBC methods DatabaseMetaData.supportsPositionedUpdate and DatabaseMetaData.supportsPositionedDelete to determine whether a particular connection supports them. Since many DBMSs do not support "for update" in a SELECT statement (as in SELECT FOR UPDATE), drivers for these DBMSs will have to scan for this phrase and implement the intended semantics. The purpose of this syntax is to signal that the result set generated from a query will be used in a positioned update or positioned delete.

When positioned updates and deletes are supported, the DBMS/driver must ensure that rows selected are properly locked so that positioned updates do not result in update anomalies or other concurrency problems.

A.1.6 Support Multithreading

All operations on java.sql and javax.sql objects are required to be multithread safe. They must be able to cope correctly with having several threads simultaneously calling the same object. In other words, a statement execution in one thread should not block an execution in another thread. In particular, JDBC drivers should operate correctly when used from multiple threads.

An example of a specific use of multithreading is the way a long-running statement can be cancelled. This is done by using one thread to execute the statement and a second one to cancel it with the method Statement.cancel.

Even though it is expected that in practice most JDBC objects will be accessed in a single-threaded way, there needs to be support for multithreading.

Some database APIs, such as ODBC, provide mechanisms for allowing SQL statements to execute asynchronously. This allows an application to start up a database operation in the background and then handle other work (such as managing a user interface) while waiting for the operation to complete.

Since Java is a multithreaded environment, there seems to be no real need to provide support for asynchronous statement execution. Java programmers can easily create a separate thread if they wish to execute statements asynchronously with respect to their main thread.

Some drivers may allow more concurrent execution than others, but developers should be able to assume fully concurrent execution. If the driver requires some form of synchronization, then the driver should provide it. In this situation, the only difference visible to the developer should be that applications run with reduced concurrency.

For example, two Statement objects on the same connection can be executed concurrently, and their ResultSets can be processed concurrently (from the perspective of the developer). Some drivers will provide this full concurrency. Others may execute one statement and wait until it completes before sending the next one.

A.1.7 Throw Exceptions for Truncated Input Parameters

If input parameters are truncated, a DataTruncation exception should be thrown.

A.1.8 Use Default Behaviors for SQL3 Data Types

To be JDBC Compliant, a driver written for a DBMS that supports SQL3 data types must implement the default behaviors for SQL3 data types. The JDBC 2.1 core API specifies the default behaviors for SQL3 data types in two general areas: (1) their lifetime and (2) whether they are implemented using locators.

1. The lifetime of an instance of an SQL3 data type
   • Blob, Clob, and Array instances are valid for the duration of the transaction in which they were created.
   • Ref instances remain valid while the session, or connection on which they were created, is open.
   • Struct instances are valid as long as an application retains a reference to them.
2. Whether an instance uses a locator or materializes its data on the client. A locator, a temporary object residing on the client, is a logical pointer to a column value on the server. For example, a LOCATOR(CLOB) designates a CLOB instance stored as a column value. Operations on the LOCATOR(CLOB) affect the CLOB value on the server.
   • Blob, Clob, and Array instances are implemented as locators; that is, they are logical pointers to values on the server and do not materialize their data on the client.
   • Ref instances map an SQL data type that is already a logical pointer to a structured type; therefore, they do not materialize the structured type's data and also do not need to be implemented as locators.
   • Struct instances do materialize the data of the structured types they represent. A JDBC driver should materialize the attribute values of a structured type on the client before returning a reference to the Struct instance to an application.

Though not to be done casually, it is possible for a driver to override the defaults. For example, the duration of a Blob instance could be changed to be the session in which it was created instead of the transaction in which it was created. Or the implementation of a Struct instance could be changed to use a locator. Any such changes should be considered carefully, weighing the advantages of the new behavior against the disadvantage of making the implementation nonstandard, and therefore nonportable. The JDBC 2.1 API does not specify how to override the defaults.

A.2 Permitted Variants

Because of the variation in database functionality and syntax, JDBC allows some variation in driver implementations. The actual SQL used by one database may vary from that used by other databases. For example, different databases provide different support for outer joins. Also, the syntax for a number of SQL features may vary between databases.

The java.sql.DatabaseMetaData interface provides a number of methods with which a user can determine exactly which SQL features are supported by a particular database. It is the responsibility of the driver writer to be sure that the DatabaseMetaData methods return accurate information about what the DBMS does and does not support.

A.2.1 When Functionality Is Not Supported

Some variation is allowed for drivers written for databases that do not support certain functionality. For example, some databases do not support OUT parameters with stored procedures. In this case, the CallableStatement methods that deal with OUT parameters (registerOutParameter and the various CallableStatement.getXXX methods) would not apply, and they may be written so that when they are called, they throw an SQLException.

The following features introduced in the JDBC 2.1 core API are optional in drivers for DBMSs that do not support them. When a DBMS does not support a feature, the methods that support the feature may throw an SQLException. In the following list of optional features, the methods in parentheses are the DatabaseMetaData methods that indicate whether the DBMS and driver support the feature.

• scrollable result sets (DatabaseMetaData.supportsResultSetType)
• updatable result sets (DatabaseMetaData.supportsResultSetConcurrency)
• batch updates (DatabaseMetaData.supportsBatchUpdates)
• SQL3 data types (DatabaseMetaData.getTypeInfo)
• storage and retrieval of Java objects
  • DatabaseMetaData.getUDTs will return descriptions of the UDTs defined in a given schema
  • DatabaseMetaData.getTypeInfo will return descriptions of the data types available with the DBMS (STRUCT, DISTINCT, and JAVA_OBJECT are new type codes for objects in java.sql.Types)

A.2.2 Variation in Fundamental Properties

Variation is also permitted in some fundamental properties, such as transaction isolation levels. The default properties of the current database and the range of properties it supports can be obtained by calling DatabaseMetaData methods.

A.2.3 Adding Functionality

Database vendors who wish to expose additional functionality that is supported by their databases may create subclasses of existing JDBC classes and provide additional methods in the new subclasses. Thus the Foobah corporation might define a new Java type foobah.sql.FooBahStatement that inherits from the standard java.sql.Statement type but adds some new functionality.

A.3 Security Responsibilities of Drivers

Because JDBC drivers may be used in a variety of different situations, it is important that driver writers follow certain simple security rules to prevent applets from making illegal database connections.

These rules are unnecessary if a driver is downloaded as an applet because the standard security manager will prevent an applet driver from making illegal connections. However, JDBC driver writers should bear in mind that if their drivers are "successful," then users may start installing them as trusted parts of the Java environment, and must make sure that they are not abused by visiting applets. We therefore urge all JDBC driver writers to follow the basic security rules.

These rules apply at connection open time. This is the point when the driver and the virtual machine should check that the current caller is really allowed to connect to a given database. After a connection is opened, no additional checks are necessary.

Only native code drivers need to verify database access prior to connecting. Pure Java drivers can rely on the security manager.

The sections that follow discuss the basic security measures that drivers need to address.

A.3.1 Check Shared TCP Connections

If a JDBC driver attempts to open a TCP connection, then the open will be automatically checked by the Java security manager. The security manager will check to see if there is an applet on the current call stack and if so, will restrict the open to whatever set of machines that applet is allowed to call. So normally, a JDBC driver can leave TCP open checks up to the Java Virtual Machine.

However, if a JDBC driver wants to share a single TCP connection among several different database connections, then it becomes the driver's responsibility to make sure that each of its callers is really allowed to talk to the target database. For example, if a TCP connection is opened to the machine foobah for applet A, this does not mean that applet B should automatically be allowed to share that connection. Applet B may have no right whatsoever to access machine foobah.

Therefore, before allowing someone to re-use an existing TCP connection, the JDBC driver should check with the security manager that the current caller is allowed to connect to that machine. This can be done with the following code fragment:

SecurityManager security = System.getSecurityManager();
if (security != null) {
	security.checkConnect(hostName, portNumber);
}

The SecurityManager.checkConnect method will throw a java.lang.SecurityException if the connection is not permitted.

A.3.2 Check All Local File Access

If a JDBC driver needs to access any local data on the current machine, then it must ensure that its caller is allowed to open the target files. The following code fragment illustrates this:

SecurityManager security = System.getSecurityManager();
if (security != null) {
	security.checkRead(fileName);
}

The Security.checkRead method will throw a java.lang.SecurityException if the current caller is an applet that is not allowed to access the given file.

As with TCP connections, the driver need only be concerned with these security issues if file resources are shared among multiple calling threads and the driver is running as trusted code.

A.3.3 Assume the Worst

Some drivers may use native methods to bridge to lower-level database libraries. In these cases, it may be difficult to determine what files or network connections will be opened by the lower-level libraries.

In these circumstances the driver must make worst-case security assumptions and deny all database access to downloaded applets unless the driver is completely confident that the intended access is innocuous.

For example, a JDBC-ODBC bridge might check the meaning of ODBC data source names and only allow an applet to use those ODBC data source names that reference databases on machines to which the applet is allowed to open connections. But for some ODBC data source names, the driver may be unable to determine the hostname of the target database and must therefore deny downloaded applets access to these data sources.

In order to determine if the current caller is a trusted application or applet (and can therefore be allowed arbitrary database access), the JDBC driver can check to see if the caller is allowed to write an arbitrary file:

SecurityManager security = System.getSecurityManager();
if (security != null) {
	security.checkWrite("foobah");
}

A.4 Use SQLException for Exceptions

As just stated, if a DBMS does not support certain functionality, a method may be implemented so that it throws an SQLException.

There are cases where a Java RunTimeException and an SQLException might overlap. For example, if a method expects an argument to be a java.sql.Types constant and something else is supplied, the exception thrown could be an IllegalArgumentException or an SQLException. In such cases, it is recommended that the SQLException be thrown because that gives JDBC more consistent control over errors.

A.5 Suggested Implementations

A.5.1 Prefetch Rows

JDBC provides methods for retrieving individual columns within individual rows, a field at a time. It does not at present provide the means for prefetching rows in larger chunks. However, in order to reduce the number of interactions with the target database, it is recommended that drivers normally prefetch rows in suitable chunks.

New methods in the JDBC 2.1 core API set the number of rows to be fetched, but a driver may ignore this number, especially if it conflicts with optimizations on the part of the DBMS and/or driver. Similarly, drivers may also ignore the fetch direction indicated by new methods in the JDBC 2.1 core API.

A.5.2 Provide "Finalize" Methods for Applets

Users are advised to call the method close on Statement and Connection objects when they are done with them. However, some users will forget, and some code may get killed before it can close these objects. Therefore, if JDBC drivers have state associated with JDBC objects that need to get explicitly cleared up, they should provide finalize methods to take care of them. The garbage collector will call these finalize methods when the objects are found to be garbage, and this will give the driver a chance to close (or otherwise clean up) the objects. Note, however, that there is no guarantee that the garbage collector will ever run. If that is the case, the finalizers will not be called.

Driver writers should look carefully at the semantics used for finalization in Java. A good source of information on this is The Java Language Specification, by James Gosling, Bill Joy, and Guy Steele. Some care is required to ensure that the finalization process occurs in the right order regardless of the order in which the garbage collector deals with a driver's objects.

A.5.3 Avoid Implementation-dependent States

Some databases have restrictions that result in hidden dependencies between JDBC objects. For instance, two Statement objects may be open, but while the ResultSet object of one is in use, the other Statement object cannot be executed. This implies that an implementation-defined Statement state exists that is controlled via another Statement object.

The JDBC API does not define such states, and if at all possible, JDBC implementations should not introduce them. They hinder portability, and implementations containing them are not fully JDBC Compliant.