Core Java Data Objects

JDBC (Java Database Connectivity ^[1] ) is a Java API for accessing relational database systems. Prominent examples of relational database management system (RDBMS) products include Oracle, IBM DB2, Microsoft SQL Server, Sybase or Interbase, as well as open -source RDBMS solutions like MySQL or PostgreSQL.

^[1] Sun's official position is that JDBC does not stand for Java Database Connectivity, possibly because of trademark issues with Microsoft's ODBC, Open Database Connectivity; however, it is the generally accepted assumption and a useful memory bridge to remember the acronym.

Other approaches for database systems included ISAM, hierarchical, or document-oriented databases, as well as X.500/LDAP repositories, and more recently, object databases. JDBC was not designed to access any of these non-relational database systems.

A relational database stores data entities in rows of tables, with fields of the entities stored as columns . Column values are constrained by an SQL data type, such as VARCHAR , INTEGER , TIMESTAMP , and so on. Most tables have one column designated as primary key, which uniquely identifies the row within the table. Relationships between entities are usually expressed by storing values of primary keys in columns of other entities, sometimes using foreign-key integrity constraints.

Relational databases and their usage through JDBC are closely modeled around a classical and simple client/server scenario: A client (e.g., an application using JDBC) sends an SQL request (e.g., a SELECT query statement) to a relational database server, which processes it and returns the result in the form of a set of rows.

A query is expressed in SQL, the standardized Structured Query Language. SQL-92 currently is commonly implemented, and SQL-99 standard is the latest release. The SQL language includes keywords for querying, inserting and updating data, among others.

Although the JDBC API as such is certainly object-oriented, using interfaces and classes, objects and methods , it is important to understand that it still is a (very thin) wrapper around SQL, which is procedural in nature. Persistent data in JDBC always comes in the row and column flavor, never directly as Java objects.

^[a] http://www.sqlj.org

12.1.1 Using JDBC fundamentals

The basic steps using JDBC are always similar and evolve around the following points:

1. Obtain a java.sql.Connection , either from a java.sql.DriverManager or from a javax.sql.DataSource . Using a DataSource is the preferred method and ensures portability to application servers using connection pools.

2. Obtain a java.sql.Statement from the Connection , or reuse a PreparedStatement or CallableStatement possibly created earlier.

3. Run SQL code, e.g., via the executeUpdate() method of Statement , or via the executeQuery() method, which returns a java.sql.ResultSet . SQL can contain both Data Definition Language commands (DDL) like CREATE TABLE , and so on, as well as, more commonly, Data Manipulation Language commands (DML) like INSERT , UPDATE , DELETE , and SELECT .

4. If required, read data from the ResultSet , possibly iterating over it with the next () method if more than one row is returned in the set by the database server. Alternatively, do something else with the ResultSet , such as a moveToInsertRow() , updateXYZ() , or insertRow() . This step may also be skipped if no data (of interest) was returned from the Statement .

5. Close the ResultSet , if one was used.

6. Close the Statement .

7. Close the Connection .

Furthermore, production code must do error handling via correct SQLException treatment (throws clause, catch and abort, catch and rethrow, and so on), as well as SQL warnings via the getWarnings() method of Connection , Statements , ResultSet , and so on.

The UML sequence diagram in Figure 12-1 illustrates the relationship between the major JDBC classes and interfaces.

12.1.2 JDBC history

The java.sql package, which provides the core of the JDBC API, was introduced as part of the core Java 1.1 API around January 1997. ^[2]

^[2] The original JDBC API (JDBC 1.0) was initially available as an add-on to Java 1.0.

In May 1998, Sun released JDBC 2.0 with version 1.2 of the Java 2 API and added a number of new classes to the java.sql package, including scrollable and updateable result sets, batch updates, BLOB and CLOB support, and support for storing objects in their serialized form in relational database fields. ^[3] It also introduced the javax.sql standard extension.

^[3] The ResultSet.getObject() and PreparedStatement.setObject() method do NOT perform any object/relational (O/R) mapping in any known JDBC implementation; they merely serialize and store a binary byte stream in BLOB or JAVA_OBJECT type columns, on which no query or referencing is possible.

The javax.sql package includes additional features such as the RowSet class for treating database query results such as JavaBeans, the DataSource interface used in connection with pooling and to obtain connections via JNDI. The package also provides support to allow JDBC clients to interact with the Java Transaction API (JTA) and perform units of work within existing (possibly distributed) transactions.

12.1.3 New features in the JDBC 3.0 specification

The latest JDBC specification at the time of this writing is version 3.0, which was finalized in October 2001, and is also included in the "Merlin" version 1.4 (JDK 1.4.x) of the Java 2 API. It does not aim at adding any revolutionary new concepts (such as perhaps O/R mapping, non-relational data sources, XML, or anything like that) to the JDBC API. It does, however, provide numerous detail enhancements and new capabilities that introduce more flexibility and control for developers. These include, in order of perceived importance, the following:

• New Savepoint interface, which contains new methods to set, release, or roll back a transaction to designated savepoints; e.g., Savepoint svpt1 = conn.setSavepoint("SAVEPOINT_1"); (... do some other work ... ) conn.rollback(svpt1);

• New configurable properties for the ConnectionPoolDataSource interface that can be used to describe how PooledConnection objects created by DataSource objects should be pooled. Furthermore, the specification documents show how prepared statements are pooled and reused by connection pools.

• A means of retrieving values from columns containing automatically generated values. After appending the Statement.RETURN_GENERATED_KEYS flag to the execute() , executeUpdate() , or prepareStatement() methods, the method Statement.getGeneratedKeys() can be called and retrieves the value of such a key, as a ResultSet object with a column for each automatically generated key.

• Methods to alter the data contained in BLOB and CLOB objects. Also added the updateBlob() , updateClob() , updateArray() , and updateRef() methods to the ResultSet interface.

• The data type java.sql.Types.DATALINK , allowing JDBC drivers to store and retrieve references to externally managed datafor example, data in a file outside the data source.

• Support for mapping SQL-99 user -defined types (UDTs), structured and distinct types to classes in the Java programming language. The future will tell whether relational database vendors will consistently implement this in their products and JDBC 3.0 drivers. JDO implementations could leverage this new feature and transparently map a persistent JDO-enhanced class to an SQL-99 UDT. Developers wishing to use this feature today at the JDBC level have to implement the SQLData interface and write to SQLOutput and SQLInput streams manually.

• Updated and new DatabaseMetaData methods for retrieving SQL-type hierarchies.

Other minor enhancements include:

• New interface ParameterMetaData , which describes the number, type, and properties of parameters to prepared statements.

• New method getMoreResults(int) to ResultSet , which takes an argument that specifies whether ResultSet objects returned by a Statement object should be closed before returning any subsequent objects.

• Methods to allow a string to identify the parameter to be set for a CallableStatement object.

• The ability to specify the holdability of a ResultSet (cursor in DB) object.

• The data type java.sql.Types.BOOLEAN (logically equivalent to BIT).

• Methods to retrieve the object referenced by a Ref object; also the ability to update a referenced object through the Ref object.

Last but not least, the JDBC 3.0 specification now includes a chapter clarifying the relationship between the JDBC SPI (Service Provider Interface) and the Java Connector architecture JCA. ^[4] JDBC driver vendors can now package their drivers as resource adapters and get all the benefits of pluggability, packaging, and deployment in Connector RAR File Format, and provide wrappers to implement the JCA system contracts. For more on JCA, see Chapter 8.

^[4] The Java Connector Architecture (JCA) defines a standard way to package and deploy a "resource adapter" that allows a J2EE container to integrate its connection, transaction, and security management with those of an external resource.

12.1.4 Vendor-specific JDBC API extensions

Although JDBC currently is the universally accepted API to access relational databases from Java, some vendors require API extensions that go beyond the JDBC standard. The generally accepted way of doing this is by casting an object returned from a standard JDBC method to a vendor-specific class, as in this example:

ResultSet resultSet; (...) java.sql.Blob blob = resultSet.getBlob("BINARY"); if(blob instanceof oracle.sql.BLOB) return ((oracle.sql.BLOB)blob).getBinaryOutputStream();