jGuru: JDBC 2.0 Fundamentals

Print-friendly Version

by

[About This Course| Exercises]

Course Outline

• Introduction to JDBC^TM
  • SQL
  • ODBC
  • The Java Programming Language and JDBC
  • JDBC 1.0
  • JDBC 2.0
• A Complete Example
  • Describing the Scenario
  • Creating a Database
  • Connecting to the Database
  • Creating a Table
  • Inserting Information into a Database
  • Step by Step
  • Retrieving Information from a Database
    • Data Navigation
    • Data Extraction
• Connecting a Java Program to a Database
  • Areas Controlled by the Connection Interface
  • Generalizing Connection Information
• Statements, ResultSets, and Interacting with a Database
  • Modifying Data
  • Database Queries
• Prepared Statements
• Java-SQL Type Equivalence
• JDBC Exception Types and Exception Handling
  • SQL Exceptions
  • SQL Warnings
  • Data Truncation
  • Sample Error Test Outcomes
• Metadata
  • Database Metadata
  • ResultSet Metadata
• Escape Syntax and Scalar Functions
• Stored Procedures
  • MetaData Support
  • Parameter INs and OUTs
  • Escape Syntax
  • CallableStatement
  • Setup, Invocation, and Value Retrieval
• Transactions
  • Commit
  • Rollback
  • Concurrency
  • Typical Transaction Code
• Batch Update Facility
  • Typical Batch Update Code
  • Handling BatchUpdateException
    • Typical BatchUpdateException Handler
• Scrollable Result Sets
  • Usage Notes
• LOBs
  • Locators
  • Clob
  • Blob
• SQL Conformance
• The JDBC 2.0 Optional Package and J2EE
• Using JDBC with JavaServer Pages

• Cloudscape Installation and Setup
• Starting and Stopping Cloudscape
• SQL Primer
  • Creating Tables
  • Accessing Columns
  • Storing Information
• Resources
  • Specific Information
  • Web Sites
  • Documentation and Specs
  • Books
  • SQL Resources
    • Web Sites
    • Books

Introduction to JDBC

JDBC is a Java API (Application Programming Interface) that documents a standard framework for dealing with tabular and, generally, relational data. While JDBC 2.0 begins a move to make SQL semi-transparent to the programmer, SQL is still the lingua franca of the standard database engines and represents a major industry victory in the effort to separate data from code. Before getting into the course proper, it's worth taking a few moments to provide some background on the movement from straight-ahead SQL to JDBC.

SQL

SQL is a standardized language used to create, manipulate, examine, and manage relational databases. This course will not extensively explain SQL, although a very basic SQL Primer and SQL Resources are provided.

However, you should understand the following:

• A database is essentially a smart container for tables.
• A table is a container comprised of rows.
• A row is (conceptually) a container comprised of columns.
• A column is a single data item having a name, type, and value.

While you should review the definitions and understand the important differences, initially you can use the following analogs: A database approximates a file system; a table approximates a file; a row approximates a record or structure; and a column approximates a field or variable. If these terms are unfamiliar, you should review some programming resources, particularly in the area of Input/Output (I/O) operations, before proceeding with the course.

Because SQL is an application-specific language, a single statement can be very expressive and can initiate high-level actions, such as sorting and merging, on an entire set of data. SQL was standardized in 1992 so that a program could communicate with most database systems without having to change the SQL commands. However, you must connect to a database before sending SQL commands, and each database vendor has a different interface to do so, as well as different extensions of SQL. Enter ODBC.

ODBC

ODBC (Open Database Connectivity), a C-based interface to SQL-based database engines, provides a consistent interface for communicating with a database and for accessing database metadata (information about the database system vendor, how the data is stored, and so on). Individual vendors provide specific drivers or "bridges" to their particular database management system. Consequently, thanks to ODBC and SQL, you can connect to a database and manipulate it in a standard way. It is no surprise that, although ODBC began as a PC standard, it has become nearly an industry standard.

Although SQL is well-suited for manipulating databases, it was not designed to be a general application language; rather, it was intended to be used only as a means of communicating with databases. Another more general and complete programming language is needed to host and feed SQL statements to a database and process results for data manipulation, visual display, or report generation. Unfortunately, you cannot easily write a program that will run on multiple platforms, even though the database connectivity standardization issue has been largely resolved. For example, if you wrote a database client in C++, you might have to totally rewrite the client for another platform; that is to say, your PC version would not run on a Macintosh. There are two reasons for this. First, C++ as a language is not portable because C++ is not completely specified (for example, how many bits does an int hold?). Second, and more importantly, support libraries such as network access and GUI (Graphical User Interface) frameworks are different on each platform. Enter the Java programming language and JDBC.

The Java Programming Language and JDBC

A Java program, written properly and according to specification, can run on any Java technology-enabled platform without recompilation. The Java programming language is completely specified and, by definition, a Java technology-enabled platform must support a known core of libraries. One such library is the java.sql package or JDBC, which you can think of as a portable version of ODBC, and is itself a major standard. Using the Java programming language in conjunction with JDBC provides a truly portable solution to writing database applications.

Note: While portable applications and a standard database interface are major achievements, keep in mind that, for historical, competitive, and sometimes nonsensical reasons, the various databases are not completely standardized. This may mean that you have to aim for a lowest common denominator in terms of capabilities or build-in adjustments for specific databases, even on the same platform. This problem remains whether you use standard SQL, ODBC, JDBC, or other solutions.

A JDBC driver is a class that implements the JDBC Driver interface and understands how to convert program (and typically SQL) requests for a particular database. Clearly, the driver is what makes it all work. There are four different driver types, which are discussed in the JDK (Java Development Kit) documentation at JDBC Driver Types. This course uses type 4 drivers because of their nearly zero installation requirements and dynamic nature. Another driver type may make more sense for your particular project. Most database vendors now provide drivers to implement the JDBC API for their particular systems. These are generally provided free of charge. Third party drivers are also available, ranging in cost from free to very expensive. For links to JDBC driver resources, see Specific Information and the other Resources.

JDBC 1.0

The JDBC 1.0 API provided the basic framework for data access, consisting primarily of the following interfaces and classes:

• Driver
• DriverManager
• Connection
• Statement
• PreparedStatement
• CallableStatement
• ResultSet
• DatabaseMetaData
• ResultSetMetaData
• Types

As you will see in this course, you 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. In general, the JDBC 2.0 core API adds a few more classes, but is primarily concerned with performance, class enhancements and functionality, and the new SQL3 (also known as SQL-99) datatypes.

The new functionality in the core API includes scrollable result sets, batch updates, programmatic inserts, deletes, and updates, performance hints, character streams for streams of internationalized Unicode characters, full precision for java.math.BigDecimal values and support for time zones in Date, Time, and Timestamp values.

At the time this course was prepared, the JDBC 3.0 draft was under review and planned to be included in the 1.4 release of the JDK.

A Complete Example

The first hands-on experience with JDBC in this course involves a basic but complete example to illustrate the overall concepts related to creating and accessing information in a database. The fundamental issues encountered when writing any database application are:

• Creating a database. A database can be created using tools supplied by the database vendor, or via SQL statements fed to the database from a Java program. Since there is normally a database administrator (of course, as a developer, this may be you), and not all JDBC drivers support database creation through Data Definition Language ( DDL), this topic will, in general, be left as DBMS (DataBase Management System) and driver specific. If you are interested in more details, there typically is a CREATE DATABASE statement, but be sure to review your DBMS SQL reference, as it is not part of the SQL standard, but is DBMS-dependent.
  
  
• Connecting to the database. This is the first job of the JDBC driver, and specific information must be passed to it. The basic information requirements are a Database URL (Universal Resource Locator), a User ID, and a Password. Depending on the driver, there may be many other arguments, attributes, or properties available. Here are two examples:

  

  Database Connection URL Attributes.

  

  AS/400 JDBC Properties.

• Creating a table. While the database contains tables, the tables are the actual components that contain data, in the form of rows and columns. Table creation is accomplished by the DDL CREATE TABLE statement. This statement has many options, some differing from vendor to vendor; again, be sure to review your DBMS SQL reference for specifics.
  
  
• Inserting information into a database. Again, data can be entered and maintained using database-specific tools, or with SQL statements sent programmatically. This course, as might be expected, will focus on using JDBC to send SQL statements to the database.
  
  
• Selectively retrieving information. After sending SQL commands to retrieve the data and using JDBC to get results into variables, program code works as with any other variables to display or manipulate that data.

Describing the Scenario

The initial task for this example requires setting up the structures and inserting data to track java (that is, coffee) intake at the jGuru Jive Java Jumphouse, better known to the initiated as the 4J Cafe. Then a report must be generated for 4J Cafe management that includes total coffee sales and the maximum coffee consumed by a customer in one day. Here's the data:

Creating a Database

As noted aboolean autoCommitve, database creation is DBMS-specific. To aid in understanding the example, an exception is made here to the basic course rule of adherence to JDBC standards. A database is created in Cloudscape by setting a database connection URL attribute that is passed to the driver. As you will see shortly, this attribute is: create=true. The named database, which is jGuru here, is created in the DBMS default directory. For the J2EE download as explained in Cloudscape Installation and Setup, this will be J2EE_HOME/Cloudscape. If the database already exists, Cloudscape creates a Connection, but then issues an SQLWarning.

Note: Keep in mind that this is Cloudscape's method and does not necessarily apply to any other DBMS. For example, to create the database on UDB2/NT, CREATE DATABASE jGuru was used; On DB2/400, first the command STRSQL was issued, then CREATE COLLECTION jGuru was used.

Connecting to the Database

There are always two steps to making a database connection using the DriverManager:

1. Load the JDBC driver.

   You must load a driver that enables the JDBC classes to communicate with a data source. In the initial examples, the driver class used with Cloudscape, RmiJdbcDriver, is hard-coded. 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.

   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.

   One obvious point that is often taken for granted: the use of URLs means that JDBC applications are more or less automatically network and internet enabled. Given that this sample uses Cloudscape's driver, 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. 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.

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.

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();

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 )
                      )

  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 )"                  +
                                            ")" );

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' )
...

"(1,  'John',   'Mon', 1, 'JustJoe')"

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

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

      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() )

while(result.next())

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:

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

2. Data Retrieval

In concluding this section, remember that:

1. JDBC is portable.

   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.

   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

jdbc:    <subprotocol>:      <subname>
jdbc:     cloudscape:rmi:     jGuru

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

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.

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

3. Generalizing Connection Information - Batch
4. 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

5. 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

6. 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

7. 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.

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

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

8. 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

9. Generalizing Connection Information - Batch
10. Determining Available Scalar Functions
11. Using Batch Updates
12. 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

13. Generalizing Connection Information - Batch
14. Generalizing Connection Information - Interactive
15. Selecting Data and Presenting Information
16. 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

17. 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 }

     { ? = call sp_B( ? ) }

     { 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 }" );

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

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 }" );

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

ResultSet rs = cstmt.executeQuery();

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( ? ? ? ) }" );

// 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 );

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

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

ResultSet rs = cstmt.executeQuery();

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

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

18. 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

19. 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();

createStatement( 
   int resultSetType, 
   int resultSetConcurrency )

• 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.

• 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.

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

20. 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.

The version of Cloudscape used in this course does not support the SQL3 data types. The following exercises were tested against UDB2/NT and DB2/400.

Exercises

21. Storing an Image in a Blob
22. 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. This course has covered the core API and functionality. The second package, known as The JDBC 2.0 Optional Package (javax.sql), includes a DataSource interface, Connection pooling, Distributed Transactions, and Rowsets. The article The JDBC 2.0 Optional Package by Maydene Fisher provides an overview of the additional functionality.

JDBC and the JDBC 2.0 Optional Package are integral parts of the Java 2 Platform, Enterprise Edition (J2EE ), which comprises a broad array of technologies for building enterprise-class server-side applications.

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 database administrator, 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, you must 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

See J2EE_HOME/doc/guides/ejb/html/Tools4.html#11919 for information about Cloudscape output on successful startup and shutdown.

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>

Resources

Specific Information

• Where can I find a comprehensive list of JDBC drivers, including the databases they support?
• Where can I find a list of the possible SQLStates returned by SQLException.getSQLState()?
• Where can I find online documentation for database xyz?
• What SQL learning and reference resources are available online?
• How can I design my servlet/JSP so that query results get displayed on several pages, like the results of a search engine?

Web Sites

• Sun Microsystems, JDBC Home Page
• jGuru's JDBC FAQ
• The Java Tutorial - JDBC Database Access
• Java Developer Connection Database Access Articles
• JDBC 2.0 Advanced Tutorial
• JDBC Performance Tips
• jGuru's JSP FAQ
• jGuru's Servlets FAQ

Documentation and Specs

The Java Technology site at Sun Microsystems includes Products and APIs.

• Sun Microsystems, JDBC API Documentation
• J2EE Documentation page

Books

• JDBC API Tutorial and Reference, Second Edition by Maydene Fisher, Dr. Rick Cattell, Graham Hamilton, Seth White and Mark Hapner (Addison Wesley ISBN 0201433281)
• Database Programming with JDBC and Java, Second Edition by George Reese (O'Reilly & Associates ISBN 1565926161) [sample chapter]

SQL Resources

Web Sites

• FIPS PUB 127-2: The Standard for Database Language SQL
• DB2 Home Page
• Oracle Home Page

Books

• SQL The Complete Reference by James R. Groff and Paul N. Weinberg (McGraw-Hill ISBN 0072118458)
• Joe Celko's SQL for Smarties : Advanced SQL Programming, Second Edition by Joe Celko (Morgan Kaufmann ISBN 1558605762)

Copyright 1996-2000 jGuru.com. A