JDBC 2.0 Fundamentals

Contents

  1. Introduction

  2. A Complete Example

  3. Connecting a Java Program to a Database

  4. Statements, ResultSets, and Interacting with a Database

  5. Prepared Statements

  6. Java-SQL Type Equivalence

  7. JDBC Exception Types and Exception Handling

  8. Metadata

  9. Escape Syntax and Scalar Functions

  10. Stored Procedures

  11. Transactions

  12. Batch Update Facility

  13. Scrollable Result Sets

  14. LOBs

  15. SQL Conformance

  16. The JDBC 2.0 Optional Package and J2EE

  17. Using JDBC with JavaServer Pages

Cloudscape

  1. Cloudscape Installation and Setup

  2. Starting and Stopping Cloudscape

  3. SQL Primer

  4. Resources

Introduction to JDBC

JDBC is a Java API that documents a standard framework for dealing with tabular and, generally, relational data. JDBC 2.0 makes extensive use of SQL.

To query a database, Java apps pass a Driver to the DriverManager and then obtain a Connection. A Statement, PreparedStatement, or CallableStatement is then created and used to update the database or execute a query. A query returns a ResultSet containing the requested data, which is retrieved by Type. DatabaseMetaData and ResultSetMetaData classes are available to provide information about a database or a ResultSet.

JDBC 2.0

The JDBC 2.0 API is broken into two parts: the core API, which this course discusses, and the JDBC 2.0 Optional Package. The JDBC 2.0 core API adds only a few more classes than the previous version. Where there are significant changes are in the areas of performance and support for SQL3 (SQL-99) datatypes.

New functionality has also been added:

  1. Scrollable result sets
  2. Batch updates
  3. Programmatic inserts, deletes, and updates
  4. Performance hints
  5. Character streams for international Unicode
  6. Full precision for java.math.BigDecimal values
  7. Support for time zones in Date, Time, and Timestamp values

4J Cafe Example

  1. Load the JDBC driver

    2. Drivers enables JDBC classes to communicate with data sources. Here's the standard method for dynamically loading a driver:

    Class.forName( DriverClassName);

    A standard JDBC Compliant driver should also create a new instance of the driver class with this code. Unfortunately, in practice this does not work for all cases. For that reason, the exercises use the following code:

    Class.forName(DriverClassName).newInstance();

    While this code will create an additional object in many cases, the code required to determine whether an instance was created, and to create a new instance if not, generally outweighs that extra cost. Fortunately, the garbage collector eventually cleans up the unreferenced object and the DriverManager does not register the driver twice.

    Drivers can also be specified from the command line via the jdbc.drivers system property, but this method requires the driver(s) to be in the classpath at compile time:

    java -Djdbc.drivers=DriverClassName AJavaApp

    The specific DriverClassName used in this course for connecting to Cloudscape in the recommended set up is:

    COM.cloudscape.core.RmiJdbcDriver

  2. Connect to a data source

    3. The driver supplies methods to make a Connection, but requires a specific type of URL, which uses the jdbc protocol. The generalized form is:

    jdbc:<subprotocol>:<subname>

    See URLs in General Use and JDBC URLs in Getting Started with the JDBC API for more information.

    The URL required takes the following form:

    jdbc:cloudscape:rmi:jGuru;create=true

    Using the DriverManager class, you request a Connection using the passed URL and the DriverManager selects the appropriate driver; here, only the Cloudscape driver is loaded. For Cloudscape's driver, this is the actual point at which the database is created due to the 'create=true' URL attribute, which will be dropped for later connections.

    Here's the standard form of the Connection request:

    Connection con = DriverManager.getConnection( URL, Username, Password );

    This form is best for portability even in cases where Username and Password are empty strings ( "" ) due to a database default or, say, text files acting as ODBC data sources, which cannot make use of such attributes.

Creating a Table

While the Connection class has a number of capabilities, in order to use DDL or Data Manipulation Language ( DML ) SQL statements, a Statement object is required. So, the next step is to ask the Connection for a Statement object:

Statement stmt = con.createStatement();
At this point, the program can begin to do some actual work. To store the data, the example creates a table named JJJJData in the jGuru database. Following is the SQL statement to do that, which includes the columns needed for each data item. SQL keywords are capitalized in the sample for better visibility, but this is a programmer preference and not necessary in your code. 

CREATE TABLE JJJJData (
   Entry      INTEGER      NOT NULL,
   Customer   VARCHAR (20) NOT NULL,
   DOW        VARCHAR (3)  NOT NULL,
   Cups       INTEGER      NOT NULL,
   Type       VARCHAR (10) NOT NULL,
   PRIMARY KEY( Entry )
                      )
The program code to do this is: 

  stmt.executeUpdate( "CREATE TABLE JJJJData ("  +
         "Entry      INTEGER      NOT NULL, "    +
         "Customer   VARCHAR (20) NOT NULL, "    +
         "DOW        VARCHAR (3)  NOT NULL, "    +
         "Cups       INTEGER      NOT NULL, "    +
         "Type       VARCHAR (10) NOT NULL,"     +
         "PRIMARY KEY( Entry )"                  +
                                            ")" );
Notice that no terminator is supplied for the actual SQL statement. The various databases use different terminators, and portability is promoted by using none in the listed code. Instead, the task of inserting the proper terminator is delegated to the driver.

The code also indicates to the database that none of the columns may be NULL, mostly to avoid a sometimes troublesome area for SQL newcomers, and defines a primary key to identify each row.

Inserting Information into a Database

Now that the table has been created, the data can be entered using the SQL INSERT statement: 

INSERT INTO JJJJData VALUES ( 1, 'John', 'Mon', 1, 'JustJoe' )
INSERT INTO JJJJData VALUES ( 2, 'JS',   'Mon', 1, 'Cappuccino' )
INSERT INTO JJJJData VALUES ( 3, 'Marie', 'Mon', 2, 'CaffeMocha' )
...
In the example program, an array named SQLData contains the actual values, with each element in a form like this: 

"(1,  'John',   'Mon', 1, 'JustJoe')"
The program code corresponding to the INSERT statements above is:

stmt.executeUpdate( "INSERT INTO JJJJData VALUES " + SQLData[i] );

Insert the following data:

Entry Customer DOW Cups Type
1 John Mon 1 JustJoe
2 JS Mon 1 Cappuccino
3 Marie Mon 2 CaffeMocha
4 Anne Tue 8 Cappuccino
5 Holley Tue 2 MoJava
6 jDuke Tue 3 Cappuccino
7 Marie Wed 4 Espresso
8 JS Wed 4 Latte
9 Alex Thu 3 Cappuccino
10 James Thu 1 Cappuccino
11 jDuke Thu 4 JustJoe
12 JS Fri 9 Espresso
13 John Fri 3 Cappuccino
14 Beth Fri 2 Cappuccino
15 jDuke Fri 1 Latte

Step by Step

To briefly review the discussion so far: First, any JDBC program loads a JDBC driver and creates a URL using the jdbc protocol ( including an attribute to create the database here ). At that point, the program can connect to the database. Next, the returned Connection object is asked for a Statement. The specific example for this section then uses SQL statements passed to the driver to create and populate the JJJJData table.

The exercise for this section includes the source code for a complete application to create the table JJJJData and insert the required rows.

Exercise

  1. Creating and Populating a Table

Retrieving Information from a Database

To retrieve information from a database, you send SQL SELECT statements to the database via the Statement.executeQuery method, which returns the requested information as rows of data in a ResultSet object. A default ResultSet is examined row by row using ResultSet.next() ( to position to the next row ) and ResultSet.getXXX() to obtain individual column data.

Consider, for example, how to obtain the maximum number of cups of coffee consumed by a 4J Cafe customer in one day. In terms of SQL, one way to get the maximum value is to sort the table by the Cups column in descending order using the ORDER BY clause. The first row in the returned ResultSet contains the largest value for Cups. All columns are selected so that the program can report and verify that the data was entered into the table as expected. Use the SQL statement: 

SELECT Entry, Customer, DOW, Cups, Type 
  FROM JJJJData 
  ORDER BY Cups DESC
In a program, execute the SQL statement with: 

      ResultSet result = stmt.executeQuery(
        "SELECT Entry, Customer, DOW, Cups, Type " +
        "FROM JJJJData " +
        "ORDER BY Cups DESC");

Data Navigation

ResultSet.next() returns a boolean: true if there is a next row and false if not (meaning the end of the data/set has been reached). Conceptually, a pointer or cursor is positioned just before the first row when the ResultSet is obtained. Invoking next() moves to the first row, then the second and so on. To get the first row, the one with the most Cups, takes some special handling:

if( result.next() )
The if-statement collects the data. After that, a loop

while(result.next())
is used, to allow the program to continue to the end of the data.

Data Extraction

Once positioned at a row, the application can get the data on a column-by-column basis using the appropriate ResultSet.getXXX method. Here are the methods used in the example to collect the data, as well as code to sum the Cup column for each row: 

      iEntry = result.getInt("Entry");
      Customer = result.getString("Customer");
      DOW = result.getString("DOW");
      Cups = result.getInt("Cups");
      TotalCups += Cups;  // increment total
      Type = result.getString("Type");

The program uses standard out for reporting with System.out.println().

If all goes well, the output shows that:

JS consumed the most coffee, 9 Espressos on Friday!

The total cups of coffee consumed was 48.

The row by row output is:

12 JS Fri 9 Espresso
4 Anne Tue 8 Cappuccino
11 jDuke Thu 4 JustJoe
8 JS Wed 4 Latte
7 Marie Wed 4 Espresso
13 John Fri 3 Cappuccino
9 Alex Thu 3 Cappuccino
6 jDuke Tue 3 Cappuccino
14 Beth Fri 2 Cappuccino
5 Holley Tue 2 MoJava
3 Marie Mon 2 CaffeMocha
15 jDuke Fri 1 Latte
10 James Thu 1 Cappuccino
2 JS Mon 1 Cappuccino
1 John Mon 1 JustJoe

Note that the ResultSet is ordered by Cups only. Therefore, there is no guarantee of the order for entries with the same number of cups. For example, the entries with 3 cups for John, Alex, and jDuke may appear in any order. All three entries will come after entries with 4 or more cups and before entries with 2 or fewer cups (remember that descending order was requested), but that's all that really can be said.

The exercise for this section includes the source code for a complete application to examine the JJJJData table and generate the report.

Exercise

  1. Data Retrieval

In concluding this section, remember that:

  1. JDBC is portable.

    2. The driver name and URL, user, and password data have been hard-coded here to keep things simple. By substituting variables for this information, these programs will run with any JDBC Compliant driver.

  2. All of the code and material presented in this section applies to and runs under JDK 1.1 and JDBC 1.2 with the proper driver.

    3. From this point on, however, the course assumes that JDK 1.3 and JDBC 2.0 are available (but most of the material runs happily under JDK 1.2 as well).

Connecting a Java Program to a Database

A Connection object represents and controls a connection to a database. Connection basics have already been discussed in Connecting to the Database; this section clarifies a few points, mentions the various areas that a Connection controls, and presents two exercises that demonstrate a general method to provide the information required to connect successfully.

While everything in JDBC depends on the capabilities of the database and the JDBC driver, in general, you can have multiple connections to the same database and/or connections to multiple databases. The DriverManager class handles driver registration and provides methods for obtaining a Connection. Note that all DriverManager methods are static; there's no need to create an instance.

One of the first steps in obtaining a Connection is often the most frustrating: how to set up that @#$!!!@# database URL? As mentioned earlier, the basics look very clean jdbc:<subprotocol>:<subname>, with the <subprotocol>: identifying the machine or server and <subname> essentially identifying the database. In practice, the content depends on the specific driver and can be bewildering, ranking along with classpath problems in producing "no suitable driver" errors. Consider the Cloudscape URL used in the previous examples: 

jdbc:cloudscape:rmi:jGuru
which translates into 

jdbc:    <subprotocol>:      <subname>
jdbc:     cloudscape:rmi:     jGuru
This is fairly straightforward, primarily because the client and the server run on the same machine. Similar URLs are often seen with drivers below a type 4, because there is some other setup involved and the information required to locate a server is obtained from the setup information.

Even here, things are not always as they seem. Most DBMS engines that support remote (and even local) connections do so using a TCP/IP (Transmission Control Protocol/Internet Protocol) port. Actually, even Cloudscape does with the cloudscape:rmi: subprotocol; run netstat after starting Cloudscape and you will see it listening on port 1099. Like any other socket program, the DBMS engine is free to decide what port it wants to use. While TCP/IP is generally the norm, other communication protocols may be used. DB2, for example, can also use APPC (Advanced Program to Program Communication) on several platforms.

When applications attempt to connect to a network or internet server, identification/location information must be provided. The general JDBC way is to use //host:port/subsubname, where host is an IP address or DNS (Domain Name Service) or other locatable name. Check your driver/database documentation for the default port, and remember that a system administrator can decide to use a different one. Here the database becomes the subsubname and the driver writer is free to allow additional attributes in their own syntax. Using Cloudscape as an example again, this code is used to create the database: 

jdbc:cloudscape:rmi:jGuru;create=true
The ;create=true portion is an attribute using Cloudscape syntax. The moral is: review the documentation for your driver and database.

A Connection is automatically closed when it is garbage collected, but cautious programmers always close the Connection explicitly to directly determine that and when this occurs and to conserve resources. Note that while the API specifically says that closing a Connection "releases... database and JDBC resources immediately," the JDBC recommendation is to explicitly close Connections and Statements.

Connection, like other important areas of the JDBC API, is an Interface. Many programmers wonder where the objects come from since an Interface can't be instantiated. Short answer: the JDBC driver implements the interface and returns real objects when requested. This also explains why an application compiles perfectly and then may have numerous problems at runtime: code is compiled against the standard interface, and only gets the real thing once the program and driver are loaded and running.

Areas Controlled by the Connection Interface

Most of the preceding section relates to setup for DriverManager's getConnection() methods. The Connection itself is responsible for several areas including:

  • Creating Statement, PreparedStatement, and CallableStatement (used with stored procedures) instances.

  • Obtaining DatabaseMetadata objects.

  • Controlling transactions via the commit() and rollback() methods.

  • Setting the isolation level involved in transactions.

There's even a method to obtain any SQL statement in a given database's native dialect, appropriately named nativeSQL(). Several of these areas are discussed in later sections of the course.

Before moving on, the new DataSource class introduced in the JDBC 2.0 Optional Package should be mentioned. The specification recommends DataSource as the means for obtaining a Connection and actually talks about deprecating the current DriverManager / Connection method. While the JDBC programmer should be aware of this movement, and may even use it--most commonly in a J2EE environment,--it would be very surprising to see the DriverManager approach abandoned anytime soon.

Generalizing Connection Information

It should be evident from the above discussion of information needed to obtain a Connection object that hardcoding the information is not a rewarding decision. The following exercises provide two methods of obtaining this information--using a ResourceBundle and/or getting it directly from the end user--in two common programming scenarios.

You may wonder if the "sa" and "admin" that the exercises set for userID and password are Cloudscape defaults or just magic. The answers are that, out of the box, authentication/security is not enabled for Cloudscape; you have to set it up yourself. Otherwise it just ignores invalid arguments and attributes. These effective dummies have been included to give the feel of the JDBC standard Connection arguments from the beginning. This should again underscore the importance of reviewing your driver and database documentation. The second answer is that, in programming, as in many other areas, there may be mirrors, but there ain't no magic.

Exercises

  1. Generalizing Connection Information - Batch

  2. Generalizing Connection Information - Interactive

Statements, ResultSets, and Interacting with a Database

A Statement object is a container or transport mechanism to send/execute (normally) SQL statements and retrieve any results via its associated Connection. As mentioned in Areas Controlled by the Connection Interface, there are three types of Statements, including Prepared Statements and Callable Statements, both of which are subinterfaces of Statement. As noted earlier, you do not create a new instance of Statement, but instead, request the associated Connection to create one: 

Statement stmt = con.createStatement();

The execute series are the most often used of Statement's methods:

  • executeQuery() is used to execute SQL statements that return a single ResultSet.

  • executeUpdate() is used to execute SQL statements that modify a table or values of columns in a table and return the number of rows affected (which is zero in the case of DDL statements).

  • execute() can be used to execute any type of SQL statement, but is intended for those that can return multiple results or values. execute() is not discussed further in the course.

To allow the most flexibility to work with various databases and data sources, JDBC places no restriction on the kinds of SQL statements that a Statement can send. In fact, if the data source can understand it (and this is a programmer responsibility ), the statements don't even have to be SQL, which raises some interesting possibilities. However, a driver that claims to be JDBC Compliant must support at least ANSI SQL-92 Entry Level capabilities.

A Statement should automatically be closed when the Connection is garbage collected, but you should close it yourself as soon as it is no longer needed. The JDBC recommendation is to always close the Statement explicitly.

Modifying Data

Update has a specific meaning to programmers and, indeed, to SQL, so executeUpdate() is probably an unfortunate name for a method that is used to execute DML ( INSERT, UPDATE, and DELETE) statements as well as DDL statements such as CREATE TABLE, DROP TABLE, and ALTER TABLE. Regardless, it is used for all of these; in fact, as a rule of thumb, use it for anything that does not return a ResultSet.

JDBC defines types to match SQL data types. These must be appropriate to the data to avoid technical problems, unanticipated results, and to promote job retention. See Java-SQL Type Equivalence for further information on the available and appropriate types.

executeUpdate() returns an int containing the affected row count for INSERT, UPDATE, or DELETE statements, or zero for SQL statements that do not return anything, like DDL statements.

Exercise

  1. Using executeUpdate()

Database Queries

executeQuery() is used for Statements that return a ResultSet, basically a SELECT statement.

The default ResultSet object returned by executeQuery() has a cursor that moves forward only, by use of the next() method. It should be noted that executeQuery() always returns a non-null ResultSet. Newcomers often try to determine if rows were returned by comparing the ResultSet to null. Short of driver error, this never happens. next() returns a boolean value, which is true if another row is available and false if the ResultSet is exhausted. You may use an if statement if you anticipate that only one row will be returned. Otherwise a while loop is the norm: 

int iCount = 0;
while( myResultSet.next() )
{
  // retrieve column data
  // do something with it
  iCount++;
}
if( iCount == 0 )
{
  System.out.println(
    "myResultSet returned no data.");
}
else
if( bNoErrorsOrExceptionsOrEarlyTerminations )
{
  System.out.println(
    "All rows from myResultSet were processed.");
}

Columns should be read from left to right (the same order as in the SELECT) statement and can be obtained by column name or index. Using an index is preferred for efficiency ( and goes 1,2,3... not 0,1,2,3...) whereas column names may lead to more understandable code. Databases and drivers may vary, but for portability you should expect that in a default ResultSet you may only get a row, and even a column from that row, exactly once.

ResultSet's getXXX() methods are used to retrieve column data. JDBC defines types to match the SQL data types and there is a getXXX() method for each. See Java-SQL Type Equivalence for further information on the available and appropriate types.

A Statement only keeps one ResultSet open at a time and often reuses the same ResultSet for new data. You should be sure to get all the data required from the ResultSet before executing another query via its associated Statement. A Statement should automatically close() the ResultSet on re-execution and on Statement.close(), but you may want to close the ResultSet yourself as soon as its data is no longer needed. Cautious programmers may always close the ResultSet explicitly.

A ResultSet can also return metadata, which is information about the ResultSet itself and the data it contains. This is discussed further in ResultSet Metadata.

Exercise

  1. Selecting Data and Presenting Information

Prepared Statements

A PreparedStatement is a subinterface of Statement that offers several benefits:

  • The contained SQL is sent to the database and compiled or prepared beforehand. From this point on, the prepared SQL is sent and this step is bypassed. The more dynamic Statement requires this step on every execution. Depending on the DB engine, the SQL may be cached and reused even for a different PreparedStatement and most of the work is done by the DB engine rather than the driver.

  • A PreparedStatement can take IN parameters, which act much like arguments to a method, for column values.

  • PreparedStatements deal with data conversions that can be error prone in straight ahead, built on the fly SQL; handling quotes and dates in a manner transparent to the developer, for example.

Note: The SQL3 types, in general, assume usage of prepared statements for DML.

Here are two examples of setting up and obtaining prepared statements: 

pstmtU = con.prepareStatement( 
  "UPDATE myTable SET myStringColumn = ? " +
    "WHERE myIntColumn = ?"  );

pstmtQ = con.prepareStatement( 
  "SELECT myStringColumn FROM myTable " +
    "WHERE myIntColumn = ? ");

The question marks are stand-ins for values to be set before statement execution and are called parameter markers. These are referred to by number, starting from 1, in left to right order. PreparedStatement's setXXX() methods are used to set the IN parameters, which remain set until changed. Again, see Java-SQL Type Equivalence for information on the available types. Here's an example for setting the parameters in the previous statements: 

pstmtU.setString( 1, "myString" );
pstmtU.setInt( 2, 1024 );
pstmtU.executeUpdate();

pstmtQ.setInt( 1, 1024 );
pstmtQ.executeQuery();

You can also prepare a statement that has no parameters. Note that PreparedStatement has its own version of the execute method series, which have no arguments, due to setting the parameters. Remember that PreparedStatement inherits from Statement and includes all of Statement's functionality. In general, consider prepared statements when a query is run multiple times and only the values of the same columns change or the same query is run repeatedly.

Exercise

  1. Using Prepared Statements

Java-SQL Type Equivalence

JDBC defines Types to provide generic SQL types for conversion to standard Java types. In general, it's straightforward to determine the types and methods needed. The following two tables show the normal ResultSet methods used to get each data type. Typically the setXXX() methods follow the same patterns.

Common SQL Types--Standard Retrieval Methods

SQL Type Java Method
BIGINT getLong()
BINARY getBytes()
BIT getBoolean()
CHAR getString()
DATE getDate()
DECIMAL getBigDecimal()
DOUBLE getDouble()
FLOAT getDouble()
INTEGER getInt()
LONGVARBINARY getBytes()
LONGVARCHAR getString()
NUMERIC getBigDecimal()
OTHER getObject()
REAL getFloat()
SMALLINT getShort()
TIME getTime()
TIMESTAMP getTimestamp()
TINYINT getByte()
VARBINARY getBytes()
VARCHAR getString()

For display purposes, ResultSet.getString() can be also be used on the above types, with the possible exception of OTHER.

SQL3 Types--Retrieval Methods

SQL Type Java Method
ARRAY getArray()
BLOB getBlob()
CLOB getClob()
DISTINCT getUnderlyingType()
REF getRef()
STRUCT (castToStruct)getObject()
JAVA_OBJECT (castToObjectType)getObject()

ResultSet.getObject() can be also be used on any of the listed types in both tables.

This may seem very clear and basic, but the professional programmer should spend some time reading both Mapping SQL data types into Java and Mapping SQL and Java Types. In particular, review the table Conversions by ResultSet.getXXX() Methods to see the variety of options available.

"How do I get the type into the database in the first place?" is a question frequently asked regarding the SQL3 types that use Locators, due to unfortunate gaps in the documentation. The best general answer to this is to look at the corresponding class (for example, Blob for BLOB), and look to the getXXX() methods for materializing the data for clues to which setXXX() methods to use, generally with PreparedStatement. For Blob, these are getBinaryStream() and getBytes(), hence setBinaryStream() and setBytes(). See LOBs and the section's associated exercises for more information and example code.

JDBC Exception Types and Exception Handling

"I don't want to think about it." That's probably an honest developer's response to queries regarding exception/error handling in general, which is difficult to do properly and usually unrewarding. It is also critical to production quality applications.

The exercises in this course highlight specific JDBC areas and make no pretensions about being production quality. At the same time, a level of exception handling has been present, starting with the first exercise. However, that level has not been complete and it's time to remedy that with an introduction to the three types of SQLExceptions.

Note that a fourth type, BatchUpdateException was added in JDBC 2.0, which is discussed in Batch Update Facility.

SQL Exceptions

Many of the methods in the java.sql package throw an SQLException , which requires a try/catch block like any other Exception. Its purpose is to describe database or driver errors (SQL syntax, for example). In addition to the standard getMessage() inherited from Throwable, SQLException has two methods which provide further information, a method to get (or chain) additional exceptions and a method to set an additional exception.

  • getSQLState() returns an SQLState identifier based on the X/Open SQL specification. Your DBMS manuals should list some of these or see Resources for information to find SQLStates.

  • getErrorCode() is provided to retrieve the vendor-specific error code.

  • getNextException() retrieves the next SQLException or null if there are no more. Many things can go wrong between your program and the database. This method allows tracking all problems that occur.

  • setNextException() allows the programmer to add an SQLException to the chain.

These methods should be fairly straightforward. Typical catch code would look similar to the following: 

    try
    { 
       // some DB work
    } // end try
    catch (  SQLException SQLe)
    {
      while( SQLe != null)
      {
        // do handling

        SQLe = SQLe.getNextException();
      }
    } // end catch

Tip: Programmers are often perplexed by syntax errors, which seem to refer to some invisible operation, like "ungrok found at line 1, position 14." Consistently reporting the output of Connection.nativeSQL(yourQueryString) in exception handlers will clarify matters.

SQL Warnings

An SQLWarning is a subclass of SQLException, but is not thrown like other exceptions. The programmer must specifically ask for warnings. Connections, Statements, and ResultSets all have a getWarnings() method that allows retrieval. There is also a clearWarnings() method to avoid duplicate retrievals. The SQLWarning class itself only adds the methods getNextWarning() and setNextWarning().

An SQLWarning is very similar to traditional compiler warnings: something not exactly right occurred, but its effect was not severe enough to end processing. Whether it is important enough to investigate depends on the operation and context. An example of an SQLWarning is mentioned in the Scrollable Result Sets section.

Statements clear warnings automatically on the next execution. ResultSets clear warnings every time a new row is accessed. The API documentation is silent regarding Connection; to be cautious, issue clearWarnings() after warnings are obtained.

Typical code for obtaining SQLWarnings looks similar to this: 

    try
    { 
      ...

      stmt = con.createStatement();
      sqlw = con.getWarnings();
      while( sqlw != null)
      {
        // handleSQLWarnings

        sqlw = sqlw.getNextWarning();
      }
      con.clearWarnings();

      stmt.executeUpdate( sUpdate );
      sqlw = stmt.getWarnings();
      while( sqlw != null)
      {
        // handleSQLWarnings

        sqlw = sqlw.getNextWarning();
      }
    } // end try
    catch ( SQLException SQLe)
    {
      ...
    } // end catch

Data Truncation

DataTruncation is sort of an oddball subclass of SQLWarning. If it occurs on a read, an SQLWarning is issued, if it occurs on a write/update, an SQLException is thrown. In practice, it is only a concern on write/update operations, and therefore handled as an SQLException, which always has an SQLState of 01004.

Data truncation basically means that less information was read or written than requested. Some databases/drivers will accept data that is larger than a column can contain, truncate the data, write the truncated data, and then happily report, via a DataTruncation SQLException "You gave me too much data, but I handled it."

The DataTruncation class includes the following methods for information about the truncated data: getDataSize(), getIndex(), getParameter(), getRead(), and getTransferSize().

Sample Error Test Outcomes

The following is a set of actual error information, as returned from Cloudscape, UDB2/NT, and DB2/400, resulting from data specifically prepared to exhibit problems. See this section's exercise for details.

  • DELETE FROM JJJJTee
      WHERE Entry = 97

    CS Result:
    0 rows processed.

    UDB2/NT Result:
    0 rows processed.

    DB2/400 Result:
    DELETE FROM JJJJTee WHERE Entry = 97
    Statement Warnings:
    [SQL0100] Row not found for DELETE.
    SQL State: 02000
    Vendor Error Code: 100
    0 rows processed.

  • INSERT INTO JJJJTee
      VALUES (25, 'Rosa', 'Petite', 'Blue')

    CS Result:
    INSERT INTO JJJJTee VALUES (25, 'Rosa', 'Petite', 'Blue')
    problems with executeUpdate:
    The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint.
    SQL State: 23500
    Vendor Error Code: 20000

    UDB2/NT Result:
    INSERT INTO JJJJTee VALUES (25, 'Rosa', 'Petite', 'Blue')
    problems with executeUpdate:
    [IBM][CLI Driver][DB2/NT] SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because they would produce duplicate rows for a table with a primary key, unique constraint, or unique index. SQLSTATE=23505
    SQL State: 23505
    Vendor Error Code: -803

    DB2/400 Result:
    INSERT INTO JJJJTee VALUES (25, 'Rosa', 'Petite', 'Blue')
    problems with executeUpdate:
    [SQL0803] Duplicate key value specified.
    SQL State: 23505
    Vendor Error Code: -803

  • UPDATE JJJJTee
      SET TColor = 'Black'
      WHERE TColor = 'Appetite'

    CS Result:
    0 rows processed.

    UDB2/NT Result:
    0 rows processed.

    DB2/400 Result:
    UPDATE JJJJTee SET TColor = 'Black' WHERE TColor = 'Appetite'
    Statement Warnings:
    [SQL0100] Row not found for UPDATE.
    SQL State: 02000
    Vendor Error Code: 100
    0 rows processed.

  • DROP TABLE IDontExist

    CS Result: DROP TABLE IDontExist
    problems with executeUpdate:
    Table 'IDONTEXIST' does not exist.
    SQL State: 42X05
    Vendor Error Code: 20000

    UDB2/NT Result: DROP TABLE IDontExist
    problems with executeUpdate:
    [IBM][CLI Driver][DB2/NT] SQL0204N "userID.IDONTEXIST" is an undefined name. SQLSTATE=42704
    SQL State: 42S02
    Vendor Error Code: -204

    DB2/400 Result:
    DROP TABLE IDontExist
    problems with executeUpdate:
    [SQL0204] IDONTEXIST in JGURU type *FILE not found.
    SQL State: 42704
    Vendor Error Code: -204

  • UPDATE JJJJTee
      SET TSize = 'Small     Doppelganger'
      WHERE TSize = 'Small'

    CS Result:
    UPDATE JJJJTee SET TSize = 'Small     Doppelganger' WHERE TSize = 'Small'
    problems with executeUpdate:
    Non-blank characters were found while truncating string 'Small     Doppelganger' from length 22 to length 10.
    SQL State: 22001
    Vendor Error Code: 20000

    UDB2/NT Result:
    UPDATE JJJJTee SET TSize = 'Small     Doppelganger' WHERE TSize = 'Small'
    problems with executeUpdate:
    [IBM][CLI Driver][DB2/NT] SQL0433N Value "Small     Doppelganger" is too long. SQLSTATE=22001
    SQL State: 22001
    Vendor Error Code: -433

    DB2/400 Result:
    UPDATE JJJJTee SET TSize = 'Small     Doppelganger' WHERE TSize = 'Small'
    problems with executeUpdate:
    [SQL0404] Value for column or variable TSIZE too long.
    SQL State: 22001
    Vendor Error Code: -404

  • UPDATE JJJJTee
      SET TSize = 'Small                 '
      WHERE TSize = 'Small'

    CS Result:
    3 rows processed.

    UDB2/NT Result:
    3 rows processed.

    DB2/400 Result:
    3 rows processed.

  • DROP TSBLE BadSQL

    CS Result:
    DROP TSBLE BadSQL
    problems with executeUpdate:
    Syntax error: Encountered "TSBLE" at line 1, column 6.
    SQL State: 42X01
    Vendor Error Code: 20000

    UDB2/NT Result:
    DROP TSBLE BadSQL
    problems with executeUpdate:
    [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "TSBLE" was found following "DROP ". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601
    SQL State: 42601
    Vendor Error Code: -104

    DB2/400 Result:
    DROP TSBLE BadSQL
    problems with executeUpdate:
    [SQL0104] Token TSBLE was not valid. Valid tokens: DISTINCT DATA.
    SQL State: 42601
    Vendor Error Code: -104

Exercise

  1. Handling SQLExceptions and SQLWarnings

Metadata

Metadata is data (or information) about data. JDBC allows the programmer to discover a large amount of information about a database and any given ResultSet via metadata classes.

Database Metadata

In order to discover information about a database, a DatabaseMetaData object must be obtained. Once a program has obtained a valid Connection, this code gets a metadata object: 

  DatabaseMetaData dbmd = con.getMetaData();

The good news is that, at that point, you just call methods for the desired information. Most of the bad news is:

  • There are approximately 150 methods in the DatabaseMetaData class. Clearly, mastering (or even being aware of) the available information is a major task. However, a scan of the API can help.

  • Many of the methods return ResultSets, which the programmer has to step through to get the specific information.

  • Several of the methods, including those that return information about database and table components, use confusing name patterns. Depending on the database, the information may be in upper, lower, or mixed case, and the patterns are case-sensitive. As a result, there are methods that need to be called to discover how the information is stored before attempting to get the information.

While this can be discouraging, the most common DatabaseMetaData information, like database name, driver name, version, maximum connections available, SQL conformance, and so on, is easily obtained. Many programs won't need such information at all. Note that a given DBMS may not provide information for all of the methods, so check returned objects for nulls or empty strings.

Links are provided to programs in the course that use DatabaseMetaData in the "Exercises" section below. Go to the program "Solution" portion of each exercise and scan on DatabaseMetaData for example usage.

Exercises

  1. Generalizing Connection Information - Batch

  2. Determining Available Scalar Functions

  3. Using Batch Updates

  4. Paging with Scrollable ResultSets

ResultSet Metadata

In order to discover information about a given ResultSet, a ResultSetMetaData object must be obtained. Once a program has obtained a valid ResultSet, this code gets a metadata object: 

  ResultSetMetaData rsmd = rs.getMetaData();

The ResultSetMetaData class is more manageable than DatabaseMetaData, with around 25 methods. Using ResultSetMetaData, an application can discover the number of columns returned, an individual column's suggested display size, column names, column types, and so on. Note that a given DBMS may not provide information for all of the methods, so check returned objects for nulls or empty strings.

Links are provided to programs in the course that use ResultSetMetaData in the "Exercises" section below. Go to the program "Solution" portion of each exercise and scan on ResultSetMetaData for example usage.

Exercises

  1. Generalizing Connection Information - Batch

  2. Generalizing Connection Information - Interactive

  3. Selecting Data and Presenting Information

  4. Paging with Scrollable ResultSets

Escape Syntax and Scalar Functions

Most databases provide scalar functions (sometimes referred to as built in functions) that can be used to perform an operation on the specific value of a column, or even to provide the value of a built-on-the-fly column. The JDBC specification supports the various math, string, system, time and date, and type conversion functions specified by the X/Open Call Level Interface (CLI), and JDBC Compliant drivers must as well, if the underlying DBMS supports the functionality. The names of these functions should match the X/Open names, although this is not always the case. Scalar functions can be valuable for their functionality or to shift work to the database from your application.

JDBC provides these methods to determine the scalar functions: getNumericFunctions(), getStringFunctions(), getSystemFunctions(), getTimeDateFunctions(), and two versions of supportsConvert(). The getXXXFunctions() methods return the function names in a comma delimited String.

Because different databases use differing syntax for scalar function invocation, JDBC defines a specific escape syntax. The JDBC driver should understand this syntax and map it to the proper syntax for the underlying database. Escapes are also used for LIKE characters, date and time literals, stored procedure calls and outer joins. The escape for scalar functions is fn. The actual function name, along with any arguments, are enclosed in curly braces, as { fn <scalar function()> }.

Scalar functions are normally used with columns in an SQL statement. For example, the PI() numeric function can be used as:

UPDATE myTable
SET circularVal = squared * { fn PI() }
...

or

SELECT { fn concat( string, "bean" ) }
...

Consult your DBMS manuals for supported functionality.

Exercise

  1. Determining Available Scalar Functions

Stored Procedures

Stored procedures are user-generated functions or procedures that, once registered with the database, can be called by client applications. They can be very valuable because they shift work to the server and reduce coding, particularly with complex operations. Unfortunately, there is no standard for manner of, requirements for, or even language for creating stored procedures. And not all databases support them. Given this state of affairs, there is no way to create a generally useful exercise, so this section is limited to a discussion of and code snippets for invoking stored procedures using the JDBC standard method. Of course, creating a stored procedure is a one-time operation, and you are normally told the name and type of parameters required.

MetaData Support

There are several DatabaseMetaData methods that return information about the support that a particular data provides for stored procedures.

  • supportsStoredProcedures() determines if the DBMS supports JDBC standard stored procedure escape syntax.

  • getProcedures() returns a list of available stored procedures, while getProcedureColumns() describes parameters and results.

  • getProcedureTerm() informs the programmer of the vendor's preferred name for stored procedures.

Parameter INs and OUTs

When invoked, as with standard methods or functions, a stored procedure can receive zero or more arguments or parameters, referred to as IN parameters. They can return a ResultSet, update count, result parameter, and/or zero or more OUT parameters. In addition, a stored procedure can have INOUT parameters, in which case a value is sent in and a different value is returned in the same variable. IN, OUT, and INOUT parameters are all enclosed in a parenthetical expression and distinguished only by number, which corresponds to the order of parameter marker (?--the question mark) appearance, starting with 1, not zero.

Escape Syntax

As mentioned in Escape Syntax and Scalar Functions, stored procedures require JDBC escape syntax for standard invocation. Again, the driver handles the actual mapping. The basic format consists of call sp_name or ? = call sp_name with optional parameters, all enclosed in curly braces. Several example forms are shown below and are discussed in more detail in the following paragraphs.

A - takes no parameters and returns nothing, a ResultSet or a row count: 

     { call sp_A }
B - single parameter and returns a result parameter. Assumes int result parameter and a String IN parameter: 

     { ? = call sp_B( ? ) }
C - multiple parameters and returns nothing, a ResultSet or a row count. Assumes int IN, OUT, and INOUT parameters: 

     { call sp_C( ? ? ? ) }

CallableStatement

To actually send the request for stored procedure execution to the database, you use a CallableStatement, which extends PreparedStatement. When creating the CallableStatement, the escape syntax discussed above is used in quotes or as a String variable. You should be sure to be especially careful to get the syntax right; you're just sending a String.

A - 

CallableStatement cstmt = 
   con.prepareCall( "{ call sp_A }" );
B - 

CallableStatement cstmt = 
   con.prepareCall( "{ ? = call sp_B( ? ) }" );
C - 

CallableStatement cstmt = 
   con.prepareCall( "{ call sp_C( ? ? ? ) }" );

Setup, Invocation, and Value Retrieval

Before invoking a stored procedure, the parameter markers must be matched up with variables and types. See Java-SQL Type Equivalence for type information.

  • IN parameters are set using the setXXX() methods inherited from PreparedStatement.

  • OUT parameters must be registered, using one of the CallableStatement.registerOutParameter() methods.

  • INOUT parameters must be both set and registered.

The actual invocation will, as usual, use executeQuery(), executeUpdate(), or execute() depending on the expected result.

A - 

CallableStatement cstmt = 
   con.prepareCall( "{ call sp_A }" );

For no return:

cstmt.execute(); // could use executeUpdate()

For returned ResultSet:

ResultSet rs = cstmt.executeQuery();

For returned update count:

int iUC = cstmt.executeUpdate();

B - 

CallableStatement cstmt = 
   con.prepareCall( "{ ? = call sp_B( ? ) }" );


// int result parameter
cstmt.registerOutParameter( 1, Types.INTEGER );
// String IN parameter
cstmt.setString( 2, "M-O-O-N" );

cstmt.execute(); // could use executeUpdate()

int iRP = cstmt.getInt( 1 );

C - 

CallableStatement cstmt = 
   con.prepareCall( "{ call sp_C( ? ? ? ) }" );

Setup:

// set int IN parameter
cstmt.setInt( 1, 333 );

// register int OUT parameter
cstmt.registerOutParameter( 2, Types.INTEGER );

// set int INOUT parameter
cstmt.setInt( 3, 666 );
// register int INOUT parameter
cstmt.registerOutParameter( 3, Types.INTEGER );

For no return

( other than OUT and INOUT: ) 

cstmt.execute(); // could use executeUpdate()

// get int OUT and INOUT
int iOUT = cstmt.getInt( 2 );
int iINOUT = cstmt.getInt( 3 );

For returned ResultSet:

ResultSet rs = cstmt.executeQuery();

// get int OUT and INOUT
int iOUT = cstmt.getInt( 2 );
int iINOUT = cstmt.getInt( 3 );

For returned update count:

int iUC = cstmt.executeUpdate();

// get int OUT and INOUT
int iOUT = cstmt.getInt( 2 );
int iINOUT = cstmt.getInt( 3 );

All of this is detailed work, but the pattern should be clear.

Transactions

In SQL terms, a transaction is one or more statements that comprise a logical unit of work (LUW). This means that, in some sense, everything is a transaction. Normally, however, the term transaction is used to mean an all-or-nothing series of operations; that is, everything should complete successfully or nothing should.

The classic example of a transaction is withdrawing money from one bank account and depositing it in another. If only the withdrawal completes, money is lost. Another example is debits and credits in a double entry accounting system: both the debit and credit must complete. A third, which is seen in this section's exercise, is to ensure that a set of INSERTs, UPDATEs, or DELETEs all complete with no errors.

While some SQL dialects have specific begin and end transaction statements, in general a transaction starts at the beginning of a program and continues until the statement(s) is (are) committed. At that point, a new transaction begins. This is the model used by JDBC. A JDBC driver's default is to autocommit, meaning that the result of every SQL statement is permanent as soon as it is executed. This is why the course hasn't had to be concerned with transactions so far, and is perfectly acceptable in many cases.

Note: In autocommit mode, the commit occurs on Statement completion. When a Statement returns a ResultSet, the Statement is not complete until the last row has been retrieved or the ResultSet is closed.

Connection's setAutoCommit(bo olean autoCommit) method is the key to handling transactions. Use Connection.setAutoCommit(true) to have every statement committed; use Connection.setAutoCommit(false) for programmatic transaction control. This method can be invoked at will and, if necessary, multiple times in a program. After invoking Connection.setAutoCommit(false), Connection.commit(), and Connection.rollback() are used to control LUWs ( yes, this should be LUsW, just as indexes should be indices, but when in Rome...).

Commit

Once autocommit is set to false, all database DML statements can be seen as temporary until they are committed. JDBC supports commitment with the Connection.commit() method. That's basically all that is needed to permanently put everything since the last commit (or rollback) to the database, although occasionally timing can be tricky. In the past, many databases set a type of lock, even for reads, that prevented other users from accessing the same data. Automatic exclusive read locks are fairly rare at this point in time, but a good slogan from then that is still applicable is "commit early and commit often." Just not too early, of course.

Note: DDL statements in a transaction may be ignored or may cause a commit to occur. The behavior is DBMS dependent and can be discovered by use of DatabaseMetaData.dataDefinitionCausesTransactionCommit() and DatabaseMetaData.dataDefinitionIgnoredInTransactions(). One way to avoid unexpected results is to separate DML and DDL transactions.

Rollback

Connection.rollback() is used to remove operations performed since the previous commit or rollback. Use this method when an exception occurs or when the program detects some error condition or error in the data.

Concurrency

Most DBMSes allow multiple users to operate on the data at the same time. Some times developers do not take enough care with database concurrency issues. (These developers often have adventurous days and at least one exciting conversation with the boss when data starts disappearing or other odd things happen to the database.) The level and type of concurrency also has an impact on performance.

JDBC recognizes the following Transaction Isolation Levels, which control concurrency:

  • TRANSACTION_NONE
  • TRANSACTION_READ_COMMITTED
  • TRANSACTION_READ_UNCOMMITTED
  • TRANSACTION_REPEATABLE_READ
  • TRANSACTION_SERIALIZABLE

Use a Connection getTransactionIsolation() and setTransactionIsolation (int level) methods to determine and set the desired isolation level. A JDBC driver has an isolation level default, usually that of the underlying database. Not all databases support all of the above settings.

Appropriate and effective concurrency handling is extremely important in database operations and many applications simply do not get it right. Unfortunately, a complete discussion would require almost a course in itself, so look to your DBMS vendor's information and see Resources.

Typical Transaction Code

Here is an example of typical transaction handling code: 

    con.setAutoCommit( false );
    ...
    bError = false;
    try
    {
       for( ... )
      {
        // validate data, set bError true if error
        if( bError )
        {
          break;
        }

        stmt.executeUpdate( ... );
      }

      if( bError ) 
      { 
        con.rollback(); 
      }
      else 
      { 
        con.commit(); 
      }

    } // end try
    catch ( SQLException SQLe)
    {
      con.rollback();
      ...
    } // end catch
    catch ( Exception e)
    {
      con.rollback();
      ...
    } // end catch

Exercise

  1. Using Transactions

Batch Update Facility

The Batch Update Facility is new in JDBC 2.0 and allows multiple statements to be sent to the database as a unit, which can lead to improved performance. Be aware that drivers are not required to implement the functionality and those that do may not implement it in a way that is more efficient than normal submissions. Even so, there is little extra effort required to use batch updates, other than reporting, and the potential gain may well be worthwhile. You can determine driver support by the DatabaseMetaData.supportsBatchUpdates() method.

JDBC 2.0 Statements are created with an automatically associated list of commands. The methods addBatch(), clearBatch(), and executeBatch() are provided to manipulate and execute the list. executeBatch() returns an array of ints which provide completion or error information for each SQL statement executed. The JDBC recommendation is to set autocommit to false when using batch updates "for proper error handling." Doing so also allows all the benefits of transaction processing.

The int values that can be returned in the update counts array are:

  • -3

    • --Operation error. A driver has the option to stop at the first error and throw a BatchUpdateException or to report the error and continue. This value is only seen in the latter case.

  • -2

    • --The operation was successful, but the number of rows affected is unknown.

  • Zero

    • --DDL statement or no rows affected by the operation.

  • Greater than zero

    • --Operation was successful, number of rows affected by the operation.

Typical Batch Update Code

Here is an example of typical batch update: 

try
{
    con.setAutoCommit( false );
    ...
    bError = false;
    stmt.clearBatch();

    // add SQL statements
    stmt.addBatch( sUpdate1 );
    stmt.addBatch( sUpdate2 );
    stmt.addBatch( sUpdate3 );

    // execute the statements
    aiupdateCounts = stmt.executeBatch();

} // end try

// catch blocks
...

finally
{
    // determine operation result
    for (int i = 0; i < aiupdateCounts.length; i++)
    {
      iProcessed = aiupdateCounts[i];
      if( iProcessed > 0 ||
          iProcessed == -2
        )
      {
        // statement was successful
        ...
      }
      else
      {
        // error on statement
        bError = true;
        break;
      }
    } // end for

    if( bError ) 
    { 
      con.rollback(); 
    }
    else 
    { 
      con.commit(); 
    }
} // end finally

Handling BatchUpdateException

Statement.executeBatch() can throw a BatchUpdateException, which is a subclass of SQLException. Its only additional method is getUpdateCounts(), which allows the programmer to obtain the array of update counts for reporting. You would already be aware that the batch had problems by virtue of catching the exception. One oddity of BatchUpdateException is that it provides no chaining method for other BatchUpdateExceptions, and only inherits SQLException.getNextException(). Code using the Batch Update Facility should also catch and handle SQLExceptions.

Typical BatchUpdateException Handler

Here's an example of handling a batch update exception: 

  catch( BatchUpdateException bue )
  {
    bError = true;
    aiupdateCounts = bue.getUpdateCounts();

    SQLException SQLe = bue;
    while( SQLe != null)
    {
      // do exception stuff

      SQLe = SQLe.getNextException();
    }
  } // end BatchUpdateException catch
  catch( SQLException SQLe )
  {
    ...

  } // end SQLException catch

Exercise Note: UDB2/NT returns false from DatabaseMetaData.supportsBatchUpdates(). The following exercise has been tested against Cloudscape and DB2/400.

Exercise

  1. Using Batch Updates

Scrollable Result Sets

To this point, all ResultSets have been used in a sequential manner, obtaining rows from beginning to end using ResultSet.next(). As discussed in Statements, ResultSets, and Interacting with a Database and seen throughout the course, ResultSets are obtained via Statements, normally with the method executeQuery. The Statements so far have been created with 

stmt = con.createStatement();
which was the only method available in JDBC 1.0. In JDBC 2.0, a new method exists which allows the creation of scrollable and/or updatable ResultSets: 

createStatement( 
   int resultSetType, 
   int resultSetConcurrency )
resultSetType can be

  • ResultSet.TYPE_FORWARD_ONLY--This is the default and the same as in JDBC 1.0: forward movement only, columns can generally only be read once. When ResultSet.next() returns false, the ResultSet data is no longer available, and generally closed automatically.

  • ResultSet.TYPE_SCROLL_INSENSITIVE allows creation of a ResultSet in which the cursor can move backwards, forwards, and at random. This is static data: Any changes made in the database to the rows selected in the current ResultSet are invisible. That is, the ResultSet is insensitive to to data modification.

  • ResultSet.TYPE_SCROLL_SENSITIVE allows creation of a ResultSet in which the cursor can move backwards, forwards, and at random. This provides a dynamic view of the data: Any changes made in the database to the rows selected in the current ResultSet are visible. That is, the ResultSet is sensitive to to data modification.

resultSetConcurrency can be

  • ResultSet.CONCUR_READ_ONLY - This is the default and the same as in JDBC 1.0.

  • ResultSet.CONCUR_UPDATABLE allows programmatic data changes via new ResultSet methods and positioning capabilities.

Updatable ResultSets have both advantages and drawbacks, but are not discussed further in this course. For more information, see the trail in The Java Tutorial or Section 3.3 of the Advanced Tutorial from the online version of the The JDBC Tutorial and Reference, Second Edition book.

Note that the type of ResultSet requested, even when supported by the driver, may not be returned. The driver should issue an SQLWarning on the Connection if this is the case. In addition, DatabaseMetaData.supportsResultSetType() can be used to determine the types of ResultSets supported by a driver and ResultSet.getType() provides the type of the actual ResultSet returned. See Requesting Features That Are Not Supported for details.

A scrollable ResultSet is obtained like any other, normally via Statement.executeQuery(). However, with a scrollable ResultSet the following methods are available:

  • absolute()
  • afterLast()
  • beforeFirst()
  • first()
  • getRow()
  • isAfterLast()
  • isBeforeFirst()
  • isFirst()
  • isLast()
  • last()
  • moveToCurrentRow()--effectively valid only with an updatable ResultSet.
  • moveToInsertRow()--valid only with an updatable ResultSet.
  • previous()
  • relative()

Usage Notes

Driver capabilities and implementation levels for scrollable ResultSets vary, sometimes dramatically. Check the documentation. Here are a few other factors, and by no means all, to consider when using scrollable ResultSets:

  • A scrollable ResultSet, just like a nonscrollable one, is positioned before the first row upon retrieval.

  • A Statement is considered complete when all rows have been retrieved. This occurs when ResultSet.next() retrieves the last row. Some drivers take this to mean committing the Statement at that point when autocommit is on. The outcome is that the ResultSet is closed and an SQLException is thrown on the next attempted access. For portability, set autocommit to false.

  • ResultSet.getRow() may return zero at certain, or even all, positions. Among other things, this means that usage of the valued ResultSet.last(), ResultSet.getRow() sequence to obtain the number of rows is not reliable across databases, or even drivers for the same database.

  • ResultSet.absolute() throws an SQLException if passed zero.

  • ResultSet.relative() should not change the cursor position if passed zero. However, at least one vendor calls ResultSet.absolute() from ResultSet.relative() without checking for a zero value. Consider the potential (and experienced by the author) outcome.

Exercise

  1. Paging with Scrollable ResultSets

LOBs

JDBC 2.0 includes classes for handling several SQL3 data types. This section discusses LOBs or Large OBjects. Two types of LOBs are defined: BLOBs--Binary Large OBjects and CLOBs--Character Large OBjects.

From the perspective of classic relational database theory, a Clob is a marginal type--a lot of characters--and a Blob isn't really a type at all; all that is known is that the Blob contains some number of bytes, which could be anything. It should be clear that this tends to defeat the notion of data independence, particularly when there are other very acceptable methods for using a database to track what are essentially graphics, audio, or other types of binary files. Notice that there is no mechanism to prevent you from, say, writing an audio file to what is supposed to be an image Blob, or to know the name of the original source, or any number of similar considerations.

Locators

An SQL Locator type is similar in concept to a pointer or other information that keeps track of an entity. JDBC developers don't have to deal with locators, but it is helpful to understand the concept, because a locator is really what a JDBC driver expects to find in an Array, Blob, orClob column. That is, the actual data is not brought down in the ResultSet, just the locator. You specifically ask for the LOB data to be returned as needed, which is called materializing the data. Clearly this is more efficient than bringing down unknown quantities of bytes for each column. The actual data is stored to and retrieved from 'somewhere else' by the DBMS.

Clob

A Clob is a JDBC interface mapping for an SQL CLOB. A Clob is obtained by one of the getClob() methods of a ResultSet or CallableStatement. A Clob has methods to get a substring of the data, the length of the data, and the position of another Clob or a String in the current Clob. These methods work without materializing the data. To materialize the data, one can use getAsciiStream() or getCharacterStream() (for a Unicode stream) and then construct usable objects from the returned stream.

For Clob storage use setClob() from a PreparedStatement or updateObject() from an updatable ResultSet. This is where most discussions end, with the example basically retrieving a Clob from one row and putting it to another row in the same or a different table.

But how does a Clob get populated in the first place? There's a clue in the Clob getAsciiStream() and getCharacterStream() methods: use PreparedStatement's setAsciiStream() or setCharacterStream() methods to populate the Clob.

Blob

A Blob is a JDBC interface mapping for an SQL BLOB. A Blob is obtained by the getBlob() methods of a ResultSet or CallableStatement. A Blob has methods to get its number of bytes and to determine the starting position of another Blob or an array of bytes in the current Blob. These methods work without materializing the data. To materialize the data, you can use getBinaryStream() or getBytes() ( for part or all of the Blob ) and then construct usable objects from the returned stream or byte array.

For Blob storage use setBlob() from a PreparedStatement or updateObject from an updatable ResultSet. Again, this is where most discussions end, with the example retrieving a Blob from one row and putting it to another row in the same or a different table.

How does Blob data get there in the first place? Again, look at the Blob methods, this time getBinaryStream() and getBytes(): use PreparedStatement's setBinaryStream() or setBytes() methods to populate the Blob.

Exercises

  1. Storing an Image in a Blob

  2. Retrieving and Displaying an Image from a Blob

SQL Conformance

The base requirement for a JDBC Compliant driver is that it must support the ">ANSI SQL-92 Entry Level, which is essentially Level 2 of SQL-89. The following is a non-exhaustive list of SQL-92 Entry Level functionality beyond the basic SELECT, INSERT, UPDATE, and DELETE statements:

  • Multiple tables in the from clause.

  • Data types: characterType, decimalType, integerType, smallintType, floatType, realType, doublePrecisionType, and numericType.

  • Simple SQL expressions: and, or, not, like, =, <>, arithmetic functions, joins, group bys, having, order by clauses, and aggregate functions (such as sum, count, max, min.)

  • Simple table and column descriptors: tableName, columnName.

  • Unique and Primary Key constraints in table descriptors.

  • Check constraints in column descriptors.

  • Support for correlated subqueries and EXISTS subqueries.

  • Full support for Distinct in functions.

  • Union is supported.

The DatabaseMetaData methods supportsANSI92EntryLevelSQL(), supportsANSI92IntermediateSQL(), and supportsANSI92FullSQL() are provided to allow runtime discovery of the SQL conformance level, and therefore the capabilities, of a particular database and driver. A JDBC Compliant driver must return true for supportsANSI92EntryLevelSQL().

In addition, the level of ODBC defined SQL grammar support can be determined by the DatabaseMetaData methods supportsMinimumSQLGrammar(), supportsCoreSQLGrammar(), and supportsExtendedSQLGrammar(). A JDBC Compliant driver must return true for supportsMinimumSQLGrammar(). In case you are wondering, ODBC is not a purely Microsoft standard. Tables showing the SQL grammar levels can be found at AcuODBC SQL Conformance. More ODBC information is available at ODBC Version 3.51.

Depending on the application, you might return a message that certain functionality is not supported or use a different algorithm to provide the functionality based on the SQL Level or grammar type supported by the driver and its underlying DBMS.

The JDBC 2.0 Optional Package and J2EE

In JDBC 2.0, there are actually two packages. The second package, known as The JDBC 2.0 Optional Package (javax.sql), includes a DataSource interface, Connection pooling, Distributed Transactions, and Rowsets.

JDBC and the JDBC 2.0 Optional Package are a part of the Java 2 Platform, Enterprise Edition.

Using JDBC with JavaServer Pages(JSP)

This section of the course briefly discusses some aspects of using JDBC with JSP as mentioned in JavaServer Pages Fundamentals.

This course focused on the core JDBC 2.0 API. However, connection pooling is not only desirable, but a practical necessity with JSP, and it makes sense to discuss some of the JDBC 2.0 Optional Package features. The JDBC 2.0 Optional Package Binary can be obtained from the JDBC Download Page. For more information about DataSource, ConnectionPoolDataSource, and PooledConnection, you are again referred to the first three sections of The JDBC 2.0 Optional Package by Maydene Fisher.

As discussed in the article, the backbone code for a program using a DataSource and a PooledConnection is: 

import javax.naming.*;
import javax.sql.*;

...

Context context = new InitialContext(); 
DataSource ds = (DataSource)ctx.lookup( 
            "jdbc/DataSource" ); 
Connection con = ds.getConnection( 
            "userID", "password" );

...

finally
{ 
  if( con != null ) { con.close(); }
}

Remember that the javax.naming package is part of the Java Naming and Directory Interface (JNDI) and is included in JDK 1.3. Also, remember that a connection pool class overrides the close() method and marks the Connection as available. That's why it is important to ensure that close() is invoked. Otherwise, the pool considers the Connection to be in use and creates a new Connection the next time one is requested, losing the benefit of pooling.

The reason that the code above can be so straightforward is that the DataSource, pooled or not, is expected to be set up by a DBA, with tools provided by the DBMS vendor. Unfortunately, this expectation makes it almost impossible to create a generally useful exercise, because each vendor can provide different methods to accomplish this task. For a discussion on setting up a DataSource using JNDI, see sections 3.7 through 3.7.3 of the Advanced Tutorial.

The JSP version of the above application code is very similar: 

<%@ page import="javax.naming.*, javax.sql.*" %>

...

<%

Context context = new InitialContext(); 
DataSource ds = (DataSource)ctx.lookup( 
            "jdbc/DataSource" ); 
Connection con = ds.getConnection( 
            "userID", "password" );

...

finally
{ 
  if( con != null ) { con.close(); }
}

%>

In an application that displays information, it makes sense to create a bean that handles queries, and possibly returns QueryRows or similar objects on request that contain the columns for a row and/or report the columns in a single String, as required. The bean could either take the DataSource or a Connection as an argument, depending on the preferred level of control and separation of functionality.

Assuming paged displays, the relative approach taken in the exercise for Scrollable Result Sets can be used, or you could obtain a row count for the retrieval and then use absolute row and page positioning. For more ideas and a package devoted to JSP scrolling, see the Pager Tag Library. Refer to the JavaServer Pages Fundamentals short course and Resources for further ideas and information about implementing JSP.

Cloudscape Installation and Setup

The Cloudscape database, as included in the Java 2 SDK Enterprise Edition (J2SE), is used for the majority of the JDBC 2.0 Fundamentals course. As of this writing, the version included is Cloudscape 3.0.4. The first step is to obtain the J2EE SDK and documentation for your platform, which is available from the Downloads & Specifications section of the J2EE Home page. Be sure to read and follow the Installation Instructions.

If you prefer to review the documentation online, see J2EE Documentation. For the Cloudscape online documentation, see Cloudscape 3.0.1 Documentation.

If your only purpose for downloading J2EE is to use Cloudscape for the course, you do not need to make the changes to the userconfig script as suggested in the Installation Instructions. You do need to perform the following steps:

  • Set the environment variable J2EE_HOME. On NT, this is the drive letter and parent directory for the J2EE SDK, for example:

    set J2EE_HOME=D:\j2ee

  • Set the environment variable JAVA_HOME. On NT, this is the drive letter and parent directory for J2SE, for example:

    set JAVA_HOME=D:\j2se

Use your own locations and names for the J2EE and J2SE directories. From this point on, J2EE_HOME and JAVA_HOME refers to these directories.

  • To avoid classpath issues, copy and rename the following jars from J2EE_HOME/lib/cloudscape to JAVA_HOME/jre/lib/ext:

    • cloudscape.jar
    • client.jar rename to cs_client.jar
    • RmiJdbc.jar rename to cs_RmiJdbc.jar
    • tools.jar rename to cs_tools.jar

You do not have to rename the jars (and you can use different names if you like) but doing so will avoid any conflicts due to the common names used. Feel free to set up things differently, but the above steps provide the fewest changes for proper client and server operation. Note that this setup bypasses J2EE and simply allows use of Cloudscape. If you want to use portions of J2EE in conjunction with exercises from the course, you need to modify the name of the database used to jGuru OR edit the exercise programs and resource bundles to use the default CloudscapeDB database instead.

For more information on Cloudscape, see the downloaded documentation under Cloudscape DBMS at J2EE_HOME/doc/cloudscape/index.html. Not all of the documentation applies to the special version included in the J2EE download. Because this course focuses on standards, there will not be much more discussion in the way of database specifics, as setup is different for each one. Refer to the documentation for your own database and drivers for this type of information and starting/stopping the database. However, a brief discussion about starting and stopping Cloudscape is in order.

Starting and Stopping Cloudscape

Make sure that Cloudscape has been installed and set up as discussed in Cloudscape Installation and Setup.

  • To start Cloudscape, use the command line. The actual entry (be sure to make the appropriate substitution for J2EE_HOME and slashes or backslashes as required) is:

    J2EE_HOME/bin/cloudscape -start

  • It is especially important for database integrity to properly shutdown Cloudscape. Do not use CTRL-C or other end process/termination methods unless absolutely unavoidable. You must stop Cloudscape from the command line as well. In general, this means starting up another window/shell and typing the following command (again, make the appropriate substitution for J2EE_HOME and slashes or backslashes as required ):

    J2EE_HOME/bin/cloudscape -stop

SQL Primer

This section serves as an SQL refresher to help you along with the exercises. It is not meant to be a tell-all resource for SQL. It takes you through the basic commands necessary for CRUD operations.

  • C--Create

  • R--Read

  • U--Update

  • D--Delete

Creating Tables

Use the CREATE TABLE statement when you want to create a table. Because creating tables is such an important operation, it requires minimum conformance. However, some datasources, such as Text ODBC sources, only support the simplest column elements, with little or no constraint support. 

CREATE TABLE <table name>
  (<column element> [, <column element>]...)

A column element is of the form: 

<column name> <data type>
   [DEFAULT <expression>]
   [<column constraint> [, <column constraint>]...]

A column constraint is of the form: 

NOT NULL |
  UNIQUE |
  PRIMARY KEY

Example: 

CREATE TABLE java (
   version_name varchar (30),
   major_version int,
   minor_version int,
   release_date date);

Use the DROP TABLE statement when you want to drop a table. Like CREATE TABLE, it requires minimum conformance. 

DROP TABLE <table name>

Accessing Columns

Use the SELECT statement when you want to retrieve a set of columns. The set may be from one or more tables, and you can specify the criteria to determine which rows to retrieve. Most of the clauses are available with minimum conformance. Additional capabilities are available with the core grammar. 

SELECT [ALL | DISTINCT] <select list>
   FROM <table reference list>
   WHERE <search condition list>
   [ORDER BY <column designator> [ASC | DESC]
          [, <column designator> [ASC | DESC]]...]

The select list usually contains a comma-separated list of columns or an '*' to select all of them. 

SELECT version_name, release_date from java;

If your driver supports core compliance, you can also use the GROUP BY, HAVING, and UNION clauses of SELECT.

To perform a join operation so that you can get results from multiple tables, the WHERE clause needs to provide the criteria. In the event the same column name is used in multiple tables, you can preface the column name with the table name, followed by a period. 

SELECT employee_id, employee_name, 
       department_table.department_id, department_name
FROM   employee_table, department_table
WHERE  employee_table.department_id =
       department_table.department_id;

You can also specify aliases to use for the tables in the from clause to avoid cumbersome names or if you are a poor typist: 

SELECT employee_id, employee_name, 
       d.department_id, department_name
FROM   employee_table e, department_table d
WHERE  e.department_id =
       d.department_id;

Storing Information

Use the INSERT statement when you want to insert rows. It too can provide different capabilities depending upon the conformance level supported. 

INSERT INTO <table name>
  [(<column name> [, <column name>]...)]
  VALUES (<expression> [, <expression>]...)


INSERT INTO java VALUES
  ('2.0Beta', 2, 0, 'Aug-1-1997');

If the core grammar is supported, you can use a SELECT clause to load multiple rows at a time.

Use the UPDATE statement when you want to update rows. It requires the minimum grammar. 

   UPDATE <table name>
   SET <column name = {<expression> | NULL}
    [, <column name = {<expression> | NULL}]...
   WHERE <search condition>

Use the DELETE statement when you want to remove rows. It requires the minimum grammar. 

DELETE FROM <table name>
   WHERE <search condition>