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:

Coffee Consumption at the jGuru Jive Java Jumphouse
"At the 4J Cafe, caffeine is our most important product"

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

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

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

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

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

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

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.

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	`get`UnderlyingType`()`
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.