Review of JDBC Basics

Before examining how we can use stored programs in JDBC , let's look at how JDBC supports database operations that don't include stored programs. These basic operations will serve as the foundation for JDBC that does use stored programs. If you are already familiar with JDBC, you might want to skip forward to "Using Stored Programs in JDBC," later in this chapter.

14.1.1. Installing the Driver and Configuring Your IDE

While the JDBC interface itself is part of native Java , to use JDBC with MySQL we will need to install a MySQL-aware JDBC driver. MySQL provides such a driver, Connector/J, which we can download from http://dev.mysql.com/downloads/connector/j.html. Installation is a simple matter of unpacking the contents of a .zip file or a tar archive to a convenient location on our hard drive.

To allow our Java programs to access the Connector/J archive, we need to add the Connector/J JAR (Java Archive) file to our system's CLASSPATH. For instance, if we unpacked the Connector/J files into a directory called C:MySQLConnectorJ, then our CLASSPATH might look like this:

Set CLASSPATH=C:MySQLConnectorJmysql-connector-java-3.1.10-bin.jar;.

Most Java IDEs require that we specify any required libraries in either a general or a project-specific dialog box. For example, in Eclipse, we can open the Properties dialog box for the project, select Java Build Path, click Add External JARs, then add the location of the Connector/J JAR file. Figure 14-1 shows the Eclipse dialog box for adding a required library.

14.1.2. Registering the Driver and Connecting to MySQL

Within our Java program we will normally import the java.sql package so that we don't have to fully qualify our references to JDBC classes, as shown in Example 14-1.

Example 14-1. Importing the java.sql package

Before we can connect to MySQL, we need to initialize the Connector/J driver. This is done with the static Class.forName( ) method, shown in Example 14-2. We can then create a Connection object that represents a specific MySQL connection by using DriverManager.getConnection( ) with an appropriately formatted URL. This also is shown in Example 14-2 .

Figure 14-1. Configuring Eclipse for Connector/J

Example 14-2. Connecting to a MySQL instance

The URL for the getConnection( ) method has the following (simplified) format:

jdbc:mysql://host[:port]/[database][?Name1=Value1][&Name2=Value2]...

The name/value pairs following the ? character typically include user and password together with other optional connection parameters (relating to the use of SSL, timeouts, etc.). You can find a full list of optional connection parameters in the Connector/J documentation at http://dev.mysql.com/doc/connector/. The following are examples of possible URLs:

jdbc:MySQL://localhost/?user=root

Connect to the MySQL server on the local host at the default port (3306) and connect to root (no password).

jdbc:MySQL://fred:3305/test?user=joe&password=joe1

Connect to the MySQL server on host fred at port number 3305. Connect as joe/joe1 to database test.

14.1.3. Issuing a Non-SELECT Statement

Now that we have created our connection object, we are ready to issue a SQL statement. The simplest way to execute a SQL statement that does not return a result set (such as INSERT, UPDATE, DELETE, or a DDL statement) is to use the createStatement( ) and executeUpdate( ) methods of the JDBC Connection interface.

The createStatement( ) method creates a reuseable Statement object. The executeUpdate( ) instance method of this Statement object can be used to execute the statement. Example 14-3 shows the use of the createStatement( ) and executeUpdate( ) methods to execute the SET AUTOCOMMIT=0 command.

Example 14-3. Issuing a SQL statement that returns no result set

In general, it's not a good idea to create statements in this way except for one-off SQL statements. For any statement that may be re-executed (perhaps with different parameters), we should use the PreparedStatement interface (see the "Using Prepared Statements" section later in this chapter).

14.1.4. Issuing a SELECT and Retrieving a Result Set

If our statement is a SELECT statement or another MySQL command that returns a result set, we can call the executeQuery( ) method of a Statement object. This creates a ResultSet object through which we can iterate in much the same way as we would iterate through the rows returned by a stored program cursor. This is, however, quite different programmatically from the way in which the java.util.Iterator interface is normally used to iterate through Java collections.

The next( ) method of the ResultSet object allows us to move to the next row in the result setthe very first call to next( ) will move to the first rowwhile getInt( ), getString( ), and other similar methods allow us to retrieve specific columns from the current row. Columns can be specified by name or by number. Example 14-4 shows us processing a simple query in JDBC.

Example 14-4. Processing a SELECT in JDBC

As with non-SELECT statements, we should use the PreparedStatement interface rather than Statement if there is a chance that we will re-execute the SQL (potentially with different parameters).

14.1.5. Getting Result Set Metadata

If we don't know the exact structure of the result when we write our code (perhaps the SQL is entered by the end user or dynamically generated by some other module), then we can create a ResultSetMetaData object that contains information about the structure of the ResultSet object. Example 14-5 shows the use of this interface to print a list of column names and data types being returned from a query. Take special note that the first metadata result column has an index of 1 where most Java programmers would assume it to be 0.

Example 14-5. Using the ResultSetMetaData object to get result set structure

14.1.6. Using Prepared Statements

Most Java applicationsparticularly those running in a middle tier such as in a J2EE- compliant application serverre-execute SQL statements many times during the life of a database session. While the "parameters" to the statement, such as WHERE clause arguments, might change, the SQL itself is usually executed many times. Prepared statements are statement objects that are permanently associated with a particular SQL statement. They can be re-executed with new parameters when required. Using a prepared statement results in reduced overhead for the MySQL server, since re-executing an existing statement takes less processing time than executing a new SQL statement.

Note that although the MySQL server supports a feature (since 4.1) called server-side prepared statements , and although the JDBC implementation of prepared statements may leverage the MySQL implementation, the prepared statements we are discussing here are a JDBC feature, and are not specific to any particular RDBMS or version of MySQL.

The PreparedStatement interface extends the Statement interface and therefore inherits methods from that interface. The primary extensions in the PreparedStatement interface relate to specifying parameters prior to execution so that the PreparedStatement instance can be re-executed in a new context.

To create a prepared statement, we use the prepareStatement( ) method of the Connection interface, providing a SQL string as the argument. Any variable portions of the SQL string are represented by the ? character. In Example 14-6 we create a prepared statement that includes a single parameter value representing a specific product identifier.

Example 14-6. Creating a prepared statement

Before each execution of the prepared statement, we need to provide values for all the parameters of the statement. The PreparedStatement interface provides setInt( ), setString( ), and other similar methods for doing this. Each method takes the parameter number as the first argument and a value of the appropriate data type as the second argument. For instance, in Example 14-7, we set the value of the product identifier that will be provided to the prepared statement defined in Example 14-6 to a value of 12. Take note again that the index of the first parameter is 1 and notas we might expect0.

Example 14-7. Setting a parameter value in a prepared statement

Now we can execute the prepared statement using its instance method executeQuery( ) if it is expected to return a result set, or executeUpdate( ) otherwise (see Example 14-8).

Example 14-8. Executing a prepared statement

Example 14-9 shows the prepared statement being declared, the parameter set, and a result set retrieved.

Example 14-9. PreparedStatement example

Of course, if we were only going to execute the prepared statement once, this would all be wasted effort. The point is that having created the prepared statement, we can execute it any number of times, feeding different parameters to the prepared statement each time. Example 14-10 illustrates this principle by executing the prepared statement in a loop to print descriptions of the first 10 product IDs.

Example 14-10. Executing a prepared statement repetitively

14.1.7. Handling Transactions

Although we can issue commands such as COMMIT, ROLLBACK, START TRANSACTION, and SET AUTOCOMMIT using the setUpdate( ) method of Statement or PreparedStatement objects, it is probably easier to perform transaction control using the methods provided by the Connection interface.

The Connection interface supports a setAutocommit( ) method, together with commit() and rollback( ) methods, which allow us to disable MySQL autocommit and to perform explicit commit and rollback operations within a connection. So a transaction in JDBC would look like this:

myConnection.setAutoCommit(false); /* transactional statements go in here */ myConnection.commit( );

14.1.8. Handling Errors

JDBC methods generally throw a SQLException if the SQL that is being issued results in a database error being generated. Classes that contain JDBC statements should therefore either use a throws clause to indicate that such an exception might be raised, or include the JDBC statements within a try/catch block.

Example 14-11 illustrates the first technique; the createDemoTables( ) method will throw a SQLException if a MySQL error occurs. It is up to the caller to catch that exception; otherwise, the unhandled exception might crash the Java program. This technique is recommended for generic or low-level database code that cannot interpret the exception within the context of the application. Pointless catching and re-throwing of exceptions is one of the cardinal sins of Java programming, because it leads to massive stack traces that just obscure what is actually causing the problem.

Example 14-11. Throwing a SQLException

Example 14-12 shows the alternative approach. Here, the JDBC calls are enclosed in a try/catch block that catches the SQLException and reports the error message. Since the exception is caught, the createDemoTables( ) method no longer needs to declare the tHRows clause. This technique should be used when the catch block is able to adequately deal with the error by logging it or handling it programmatically. The catch block may also re-throw the exception as an application exception that includes valuable context information with regard to what the application was trying to do when the SQL failed.

Example 14-12. Catching a SQLException

The getErrorCode( ) and getMessage( ) methods are typically used to report on the specifics of the database error concerned. However, the SQLException class inherits a lot of useful diagnostic methods from its super classes Exception and Throwable. In particular, printStackTrace( ) will print a stack trace for the exception to standard output, while getStackTrace( ) allows programmatic access to the trace.