An Introduction to JDBC, Part 2

Editor's Note: This is Part Two of a book excerpt from Java Enterprise in a Nutshell. This excerpt focuses on database connection, statements and results.

Connecting to the Database

The java.sql.Connection object, which encapsulates a single connection to a particular database, forms the basis of all JDBC data-handling code. An application can maintain multiple connections, up to the limits imposed by the database system itself. A standard small office or web server Oracle installation can support 50 or so connections, while a major corporate database could host several thousand. The DriverManager.getConnection( ) method creates a connection:

Connection con = DriverManager.getConnection("url", "user", "password");

You pass three arguments to getConnection( ): a JDBC URL, a database username, and a password. For databases that don't require explicit logins, the user and password strings should be left blank. When the method is called, the DriverManager queries each registered driver, asking if it understands the URL. If a driver recognizes the URL, it returns a Connection object. Because the getConnection( ) method checks each driver in turn, you should avoid loading more drivers than are necessary for your application.

The getConnection( ) method has two other variants that are less frequently used. One variant takes a single String argument and tries to create a connection to that JDBC URL without a username or password, or with a username and password embedded in the URL itself. The other version takes a JDBC URL and a java.util.Properties object that contains a set of name/value pairs. You generally need to provide at least username=value and password=value pairs.

When a Connection has outlived its usefulness, you should be sure to explicitly close it by calling its close( ) method. This frees up any memory being used by the object, and, more importantly, it releases any other database resources the connection may be holding on to. These resources (cursors, handles, and so on) can be much more valuable than a few bytes of memory, as they are often quite limited. This is particularly important in applications such as servlets that might need to create and destroy thousands of JDBC connections between restarts. Because of the way some JDBC drivers are designed, it is not safe to rely on Java's garbage collection to remove unneeded JDBC connections.

The JDBC 2.0 standard extension, discussed later in this chapter, provides a facility for connection pooling, whereby an application can maintain several open database connections and spread the load among them. This is often necessary for enterprise-level applications, such as servlets, that may be called upon to perform tens of thousands of database transactions a day.

Statements

Once you have created a Connection, you can begin using it to execute SQL statements. This is usually done via Statement objects. There are actually three kinds of statements in JDBC:

Statement

Represents a basic SQL statement

PreparedStatement

Represents a precompiled SQL statement, which can offer improved performance

CallableStatement

Allows JDBC programs complete access to stored procedures within the database itself

We're just going to discuss the Statement object for now; PreparedStatement and CallableStatement are covered in detail later in this chapter.

To get a Statement object, call the createStatement( ) method of a Connection:

Statement stmt = con.createStatement(  );

Once you have created a Statement, use it to execute SQL statements. A statement can either be a query that returns results or an operation that manipulates the database in some way. If you are performing a query, use the executeQuery( ) method of the Statement object:

ResultSet rs = stmt.executeQuery("SELECT * FROM CUSTOMERS");

Here we've used executeQuery( ) to run a SELECT statement. This call returns a ResultSet object that contains the results of the query (we'll take a closer look at ResultSet in the next section).

Statement also provides an executeUpdate( ) method, for running SQL statements that don't return results, such as the UPDATE and DELETE statements. executeUpdate( ) returns an integer that indicates the number of rows in the database that were altered.

If you don't know whether a SQL statement is going to return results (such as when the user is entering the statement in a form field), you can use the execute( ) method of Statement. This method returns true if there is a result associated with the statement. In this case, the ResultSet can be retrieved using the getResultSet( ) method and the number of updated rows can be retrieved using getUpdateCount( ):

Statement unknownSQL = con.createStatement(  );

if(unknownSQL.execute(sqlString)) {

 ResultSet rs = unknownSQL.getResultSet(  );

 // display the results

} 

else {

 System.out.println("Rows updated: " + unknownSQL.getUpdateCount(  ));

}

It is important to remember that a Statement object represents a single SQL statement. A call to executeQuery( ), executeUpdate( ), or execute( ) implicitly closes any active ResultSet associated with the Statement. In other words, you need to be sure you are done with the results from a query before you execute another query with the same Statement object. If your application needs to execute more than one simultaneous query, you need to use multiple Statement objects. As a general rule, calling the close( ) method of any JDBC object also closes any dependent objects, such as a Statement generated by a Connection or a ResultSet generated by a Statement, but well-written JDBC code closes everything explicitly.

Multiple Result Sets

It is possible to write a SQL statement that returns more than one ResultSet or update count (exact methods of doing so vary depending on the database). The Statement object supports this functionality via the getMoreResults( ) method. Calling this method implicitly closes any existing ResultSet and moves to the next set of results for the statement. getMoreResults( ) returns true if there is another ResultSet available to be retrieved by getResultSet( ). However, the method returns false if the next statement is an update, even if there is another set of results waiting farther down the line. To be sure you've processed all the results for a Statement, you need to check that getMoreResults( ) returns false and that getUpdateCount( ) returns -1.

We can modify the previous execute( ) example to handle multiple results:

Statement unknownSQL = con.createStatement(  );

unknownSQL.execute(sqlString);

while (true) { 

 rs = unknownSQL.getResultSet(  );

 if(rs != null)

 // display the results

 else

 // process the update data

  

 // Advance and quit if done

 if((unknownSQL.getMoreResults(  ) == false) && 

 (unknownSQL.getUpdateCount(  ) == -1))

 break; 

}

Statements that return multiple results are actually quite rare. They generally arise from stored procedures or SQL implementations that allow multiple statements to be executed in a batch. Under SyBase, for instance, multiple SELECT statements may be separated by newline (\n) characters.