APPENDIX C: JDBC

 What Is JDBC?

JDBC is a Java API for executing SQL statements. (As a point of interest, JDBC is a trademarked name and is not an acronym; nevertheless, JDBC is often thought of as standing for "Java Database Connectivity".) It consists of a set of classes and interfaces written in the Java programming language. JDBC provides a standard API for tool/database developers and makes it possible to write database applications using a pure Java API.

Using JDBC, it is easy to send SQL statements to virtually any relational database. In other words, with the JDBC API, it isn't necessary to write one program to access a Sybase database, another program to access an Oracle database, another program to access an Informix database, and so on. One can write a single program using the JDBC API, and the program will be able to send SQL statements to the appropriate database. And, with an application written in the Java programming language, one also doesn't have to worry about writing different applications to run on different platforms. The combination of Java and JDBC lets a programmer write it once and run it anywhere.

Java, being robust, secure, easy to use, easy to understand, and automatically downloadable on a network, is an excellent language basis for database applications. What is needed is a way for Java applications to talk to a variety of different databases. JDBC is the mechanism for doing this.

JDBC extends what can be done in Java. For example, with Java and the JDBC API, it is possible to publish a web page containing an applet that uses information obtained from a remote database. Or an enterprise can use JDBC to connect all its employees (even if they are using a conglomeration of Windows, Macintosh, and UNIX machines) to one or more internal databases via an Intranet. With more and more programmers using the Java programming language, the need for easy database access from Java is continuing to grow.

MIS managers like the combination of Java and JDBC because it makes disseminating information easy and economical. Businesses can continue to use their installed databases and access information easily even if it is stored on different database management systems. Development time for new applications is short. Installation and version controls are greatly simplified. A programmer can write an application or an update once, put it on the server, and everybody has access to the latest version. And for businesses selling information services, Java and JDBC offer a better way of getting out information updates to external customers.

What Does JDBC Do?

Simply put, JDBC makes it possible to do three things: establish a connection with a database send SQL statements process the results.

The following code fragment gives a basic example of these three steps:

Connection con=DriverManager.getConnection("jdbc:odbc:wombat","login",
"password");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table1");
while (rs.next()) {
int x = rs.getInt("a");
String s = rs.getString("b");
float f = rs.getFloat("c");
}

Connection

A Connection object represents a connection with a database. A connection session includes the SQL statements that are executed and the results that are returned over that connection. A single application can have one or more connections with a single database, or it can have connections with many different databases.

Opening a Connection

The standard way to establish a connection with a database is to call the method DriverManager.getConnection. This method takes a string containing a URL. The DriverManager class, referred to as the JDBC management layer, attempts to locate a driver than can connect to the database represented by that URL. The DriverManager class maintains a list of registered Driver classes, and when the method getConnection is called, it checks with each driver in the list until it finds one that can connect to the database specified in the URL. The Driver method connect uses this URL to actually establish the connection.

A user can bypass the JDBC management layer and call Driver methods directly. This could be useful in the rare case that two drivers can connect to a database and the user wants to explicitly select a particular driver. Normally, however, it is much easier to just let the DriverManager class handle opening a connection.

The following code exemplifies opening a connection to a database located at the URL "jdbc:odbc:wombat" with a user ID of "oboy" and "12Java" as the password :

String url = "jdbc:odbc:wombat";
Connection con = DriverManager.getConnection(url, "oboy", "12Java");

Sending SQL Statements

Once a connection is established, it is used to pass SQL statements to its underlying database. JDBC does not put any restrictions on the kinds of SQL statements that can be sent; this provides a great deal of flexibility, allowing the use of database-specific statements or even non-SQL statements. It requires, however, that the user be responsible for making sure that the underlying database can process the SQL statements being sent and suffer the consequences if it cannot. For example, an application that tries to send a stored procedure call to a DBMS that does not support stored procedures will be unsuccessful and generate an exception. JDBC requires that a driver provide at least ANSI SQL-2 Entry Level capabilities in order to be designated JDBC COMPLIANTTM. This means that users can count on at least this standard level of functionality.

JDBC provides three classes for sending SQL statements to the database, and three methods in the Connection interface create instances of these classes. These classes and the methods which create them are listed below:

1.Statement- -created by the method createStatement. A Statement object is used for sending simple SQL statements.

2.PreparedStatement- -created by the method prepareStatement. A PreparedStatement object is used for SQL statements that take one or more parameters as input arguments (IN parameters). PreparedStatement has a group of methods which set the value of IN parameters, which are sent to the database when the statement is executed. Instances of PreparedStatement extend Statement and therefore include Statement methods. A PreparedStatement object has the potential to be more efficient than a Statement object because it has been pre-compiled and stored for future use.

3.CallableStatement- -created by the method prepareCall. CallableStatement objects are used to execute SQL stored procedures- -a group of SQL statements that is called by name, much like invoking a function. A CallableStatement object inherits methods for handling IN parameters from PreparedStatement; it adds methods for handling OUT and INOUT parameters.

The following list gives a quick way to determine which Connection method is appropriate for creating different types of SQL statements:

createStatement method is used for simple SQL statements (no parameters) prepareStatement method is used for SQL statements with one or more IN parameters simple SQL statements that are executed frequently prepareCall method is used for call to stored procedures

 
DriverManager

 The DriverManager class is the management layer of JDBC, working between the user and the drivers. It keeps track of the drivers that are available and handles establishing a connection between a database and the appropriate driver. In addition, the DriverManager class attends to things like driver login time limits and the printing of log and tracing messages.

For simple applications, the only method in this class that a general programmer needs to use directly is DriverManager.getConnection. As its name implies, this method establishes a connection to a database. JDBC allows the user to call the DriverManager methods getDriver, getDrivers, and registerDriver as well as the Driver method connect, but in most cases it is better to let the DriverManager class manage the details of establishing a connection.

Statement

A Statement object is used to send SQL statements to a database. There are actually three kinds of Statement objects, all of which act as containers for executing SQL statements on a given connection: Statement, PreparedStatement, which inherits from Statement, and CallableStatement, which inherits from PreparedStatement. They are specialized for sending particular types of SQL statements: a Statement object is used to execute a simple SQL statement with no parameters; a PreparedStatement object is used to execute a precompiled SQL statement with or without IN parameters; and a CallableStatement object is used to execute a call to a database stored procedure.

The Statement interface provides basic methods for executing statements and retrieving results. The PreparedStatement interface adds methods for dealing with IN parameters; CallableStatement adds methods for dealing with OUT parameters.

Creating Statement Objects

Once a connection to a particular database is established, that connection can be used to send SQL statements. A Statement object is created with the Connection method createStatement, as in the following code fragment:

Connection con = DriverManager.getConnection(url, "sunny", "");
Statement stmt = con.createStatement();

The SQL statement that will be sent to the database is supplied as the argument to one of the methods for executing a Statement object:

ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table2");

Executing Statements Using Statement objects

The Statement interface provides three different methods for executing SQL statements, executeQuery, executeUpdate, and execute. The one to use is determined by what the SQL statement produces.

The method executeQuery is designed for statements that produce a single result set, such as SELECT statements.

The method executeUpdate is used to execute INSERT, UPDATE, or DELETE statements and also SQL DDL (Data Definition Language) statements like CREATE TABLE and DROP TABLE. The effect of an INSERT, UPDATE, or DELETE statement is a modification of one or more columns in zero or more rows in a table. The return value of executeUpdate is an integer indicating the number of rows that were affected (referred to as the update count). For statements such as CREATE TABLE or DROP TABLE, which do not operate on rows, the return value of executeUpdate is always zero.

The method execute is used to execute statements that return more than one result set, more than one update count, or a combination of the two. Because it is an advanced feature that most programmers will never need, it is explained in its own section later in this overview.

All of the methods for executing statements close the calling Statement object's current result set if there is one open. This means that one needs to complete any processing of the current ResultSet object before re-executing a Statement object.

It should be noted that the PreparedStatement interface, which inherits all of the methods in the Statement interface, has its own versions of the methods

executeQuery, executeUpdate and execute. Statement objects do not themselves contain an SQL statement; therefore, one must be provided as the argument to the Statement.execute methods. PreparedStatement objects do not supply an SQL statement as a parameter to these methods because they already contain a precompiled SQL statement. CallableStatement objects inherit the PreparedStatement forms of these methods. Using a query parameter with the

PreparedStatement or CallableStatement versions of these methods will cause an SQLException to be thrown.

Statement Completion

When a connection is in auto-commit mode, the statements being executed within it are committed or rolled back when they are completed. A statement is considered complete when it has been executed and all its results have been returned. For the method executeQuery, which returns one result set, the statement is completed when all the rows of the ResultSet object have been retrieved. For the method executeUpdate, a statement is completed when it is executed. In the rare cases where the method execute is called, however, a statement is not complete until all of the result sets or update counts it generated have been retrieved.

Some DBMSs treat each statement in a stored procedure as a separate statement; others treat the entire procedure as one compound statement. This difference becomes important when auto-commit is enabled because it affects when the method commit is called. In the first case, each statement is individually committed; in the second, all are committed together.

Closing Statement Objects

Statement objects will be closed automatically by the Java garbage collector. Nevertheless, it is recommended as good programming practice that they be closed explicitly when they are no longer needed. This frees DBMS resources immediately and helps avoid potential memory problems.

ResultSet

A ResultSet contains all of the rows which satisfied the conditions in an SQL statement, and it provides access to the data in those rows through a set of get methods that allow access to the various columns of the current row. The ResultSet.next method is used to move to the next row of the ResultSet, making the next row become the current row.

The general form of a result set is a table with column headings and the corresponding values returned by a query. For example, if your query is SELECT a, b, c FROM Table1, your result set will have the following form:

a b c
-------- --------- --------
12345 Cupertino CA
83472 Redmond WA
83492 Boston MA

The following code fragment is an example of executing an SQL statement that will return a collection of rows, with column 1 as an int, column 2 as a String, and column 3 as an array of bytes:

java.sql.Statement stmt = conn.createStatement();
ResultSet r = stmt.executeQuery("SELECT a, b, c FROM Table1");
while (r.next())
{
// print the values for the current row.
int i = r.getInt("a");
String s = r.getString("b");
float f = r.getFloat("c");
System.out.println("ROW = " + i + " " + s + " " + f);}

Rows and Cursors

A ResultSet maintains a cursor which points to its current row of data. The cursor moves down one row each time the method next is called. Initially it is positioned before the first row, so that the first call to next puts the cursor on the first row, making it the current row. ResultSet rows are retrieved in sequence from the top row down as the cursor moves down one row with each successive call to next. A cursor remains valid until the ResultSet object or its parent Statement object is closed.

In SQL, the cursor for a result table is named. If a database allows positioned updates or positioned deletes, the name of the cursor needs to be supplied as a parameter to the update or delete command. This cursor name can be obtained by calling the method getCursorName.

Note that not all DBMSs support positioned update and delete. The DatabaseMetaData.supportsPositionedDelete and supportsPositionedUpdate

methods can be used to discover whether a particular connection supports these operations. When they are supported, the DBMS/driver must ensure that rows selected are properly locked so that positioned updates do not result in update anomalies or other concurrency problems.

Columns

The getXXX methods provide the means for retrieving column values from the current row. Within each row, column values may be retrieved in any order, but for maximum portability, one should retrieve values from left to right and read column values only once.

Either the column name or the column number can be used to designate the column from which to retrieve data. For example, if the second column of a ResultSet object rs is named "title" and stores values as strings, either of the following will retrieve the value stored in that column:

String s = rs.getString("title");
String s = rs.getString(2);
 
Note that columns are numbered from left to right starting with column 1. Also, column names used as input to getXXX methods are case insensitive.

The option of using the column name was provided so that a user who specifies column names in a query can use those same names as the arguments to getXXXmethods. If, on the other hand, the select statement does not specify column names (as in "select * from table1" or in cases where a column is derived), column numbers should be used. In such situations, there is no way for the user to know for sure what the column names are.

In some cases, it is possible for a SQL query to return a result set that has more than one column with the same name. If a column name is used as the parameter to a getXXX method, getXXX will return the value of the first matching column name. Thus, if there are multiple columns with the same name, one needs to use a column index to be sure that the correct column value is retrieved. It may also be slightly more efficient to use column numbers.

Information about the columns in a ResultSet is available by calling the method ResultSet.getMetaData. The ResultSetMetaData object returned gives the number, types, and properties of its ResultSet object's columns. If the name of a column is known, but not its index, the method findColumn can be used to find the column number.

Data Types and Conversions

For the getXXX methods, the JDBC driver attempts to convert the underlying data to the specified Java type and then returns a suitable Java value. For example, if the getXXX method is getString, and the data type of the data in the underlying database is VARCHAR, the JDBC driver will convert VARCHAR to Java String. The return value of getString will be a Java String object.

The following table shows which JDBC types a getXXX method is allowed to retrieve and which JDBC types (generic SQL types) are recommended for it to retrieve. A small x indicates a legal getXXX method for a particular data type; a large X indicates the recommended getXXX method for a data type. For example, any getXXX method except getBytes or getBinaryStream can be used to retrieve the value of a LONGVARCHAR, but getAsciiStream or getUnicodeStream are recommended, depending on which data type is being returned. The method getObject will return any data type as a Java Object and is useful when the underlying data type is a database-specific abstract type or when a generic application needs to be able to accept any data type. Use of ResultSet.getXXX methods to retrieve common JDBC data types. An "x" indicates that the getXXX method may legally be used to retrieve the given JDBC type. An "X" indicates that the getXXX method is recommended for retrieving the given JDBC type.

PreparedStatement

The PreparedStatement interface inherits from Statement and differs from it in two ways:

1.Instances of PreparedStatement contain an SQL statement that has already been compiled. This is what makes a statement "prepared."

2.The SQL statement contained in a PreparedStatement object may have one or more IN parameters. An IN parameter is a parameter whose value is not specified when the SQL statement is created. Instead the statement has a question mark ("?") as a placeholder for each IN parameter. A value for each question mark must be supplied by the appropriate setXXX method before the statement is executed.

Because PreparedStatement objects are precompiled, their execution can be faster than that of Statement objects. Consequently, an SQL statement that is

executed many times is often created as a PreparedStatement object to increase efficiency. Being a subclass of Statement, PreparedStatement inherits all the functionality of Statement. In addition, it adds a whole set of methods, which are needed for setting the values to be sent to the database in place of the placeholders for IN parameters. Also, the three methods execute, executeQuery, and executeUpdate are modified so that they take no argument. The Statement forms of these methods (the forms that take an SQL statement parameter) should never be used with a PreparedStatement object.

Creating PreparedStatement Objects

The following code fragment, where con is a Connection object, creates a PreparedStatement object containing an SQL statement with two placeholders for IN parameters:

PreparedStatement pstmt = con.prepareStatement("UPDATE table4 SET m = ? WHERE x = ?");

The object pstmt now contains the statement "UPDATE table4 SET m = ? WHERE x = ?", which has already been sent to the DBMS and been prepared for execution.

Passing IN Parameters

Before a PreparedStatement object is executed, the value of each ? parameter must be set. This is done by calling a setXXX method, where XXX is the appropriate type for the parameter. For example, if the parameter has a Java type of long, the method to use is setLong. The first argument to the setXXX methods is the ordinal position of the parameter to be set, and the second argument is the value to which the parameter is to be set. For example, the following code sets the first parameter to 123456789 and the second parameter to 100000000:

pstmt.setLong(1, 123456789);
pstmt.setLong(2, 100000000);

Once a parameter value has been set for a given statement, it can be used for multiple executions of that statement until it is cleared by a call to the method clearParameters.

In the default mode for a connection (auto-commit enabled), each statement is commited or rolled back automatically when it is completed.

The same PreparedStatement object may be executed multiple times if the underlying database and driver will keep statements open after they have been committed. Unless this is the case, however, there is no point in trying to improve performance by using a PreparedStatement object in place of a Statement object.

Using pstmt, the PreparedStatement object created above, the following code illustrates setting values for the two-parameter placeholders and executing pstmt 10 times. As stated above, for this to work, the database must not close pstmt. In this example, the first parameter is set to "Hi" and remains constant. The second parameter is set to a different value each time around the for loop, starting with 0 and ending with 9.

pstmt.setString(1, "Hi");
for (int i = 0; i < 10; i++) {
pstmt.setInt(2, i);
int rowCount = pstmt.executeUpdate();
}