Up to this point, you have seen only code fragments. Now you will see some samples.

The first sample code creates the table COFFEES; the second one inserts values into the table and prints the results of a query. The third application creates the table SUPPLIERS, and the fourth populates it with values. After you have run this code, you can try a query that is a join between the tables COFFEES and SUPPLIERS, as in the fifth code example. The sixth code sample is an application that demonstrates a transaction and also shows how to set placeholder parameters in a PreparedStatement object using a for loop.

Because they are complete applications, they include some elements of the Java programming language we have not shown before in the code fragments. We will explain these elements briefly here.

Putting Code in a Class Definition

In the Java™ programming language, any code you want to execute must be inside a class definition. You type the class definition in a file and give the file the name of the class with .java appended to it. So if you have a class named MySQLStatement, its definition should be in a file named MySQLStatement.java.

Importing Classes to Make Them Visible

The first thing to do is to import the packages or classes you will be using in the new class. The classes in our examples all use the java.sql package (the JDBC™ API), which becomes available when the following line of code precedes the class definition:

import java.sql.*;

The star ( * ) indicates that all of the classes in the package java.sql are to be imported. Importing a class makes it visible and means that you do not have to write out the fully qualified name when you use a method or field from that class. If you do not include " import java.sql.*; " in your code, you will have to write " java.sql. " plus the class name in front of all the JDBC fields or methods you use every time you use them. Note that you can import individual classes selectively rather than a whole package. Java does not require that you import classes or packages, but doing so makes writing code a lot more convenient.

Any lines importing classes appear at the top of all the code samples, as they must if they are going to make the imported classes visible to the class being defined. The actual class definition follows any lines that import classes.

Using the main Method

If a class is to be executed, it must contain a static public main method. This method comes right after the line declaring the class and invokes the other methods in the class. The keyword static indicates that this method operates on a class level rather than on individual instances of a class. The keyword public means that members of any class can access this method. Since we are not just defining classes to be used by other classes but instead want to run them, the example applications in this chapter all include a main method.

Using try and catch Blocks

Something else all the sample applications include is try and catch blocks. These are the Java programming language's mechanism for handling exceptions. Java requires that when a method throws an exception, there be some mechanism to handle it. Generally a catch block will catch the exception and specify what happens (which you may choose to be nothing). In the sample code, we use two try blocks and two catch blocks. The first try block contains the method Class.forName, from the java.lang package. This method throws a ClassNotFoundException, so the catch block immediately following it deals with that exception. The second try block contains JDBC methods, which all throw SQLExceptions, so one catch block at the end of the application can handle all of the rest of the exceptions that might be thrown because they will all be SQLException objects.

Retrieving Exceptions

JDBC lets you see the warnings and exceptions generated by your DBMS and by the Java compiler. To see exceptions, you can have a catch block print them out. For example, the following two catch blocks from the sample code print out a message explaining the exception:

try {
	// Code that could generate an exception goes here.
	// If an exception is generated, the catch block below
	// will print out information about it.
} catch(SQLException ex) {
	System.err.println("SQLException: " + ex.getMessage());
}

try {
	Class.forName("myDriverClassName");
} catch(java.lang.ClassNotFoundException e) {
	System.err.print("ClassNotFoundException: "); 
	System.err.println(e.getMessage());
}

If you were to run CreateCOFFEES.java twice, you would get an error message similar to this:

SQLException: There is already an object named 'COFFEES'
in the database.
Severity 16, State 1, Line 1

This example illustrates printing out the message component of an SQLException object, which is sufficient for most situations.

There are actually three components, however, and to be complete, you can print them all out. The following code fragment shows a catch block that is complete in two ways. First, it prints out all three parts of an SQLException object: the message (a string that describes the error), the SQL state (a string identifying the error according to the X/Open SQLState conventions), and the vendor error code (a number that is the driver vendor's error code number). The SQLException object ex is caught, and its three components are accessed with the methods getMessage , getSQLState , and getErrorCode .

The second way the following catch block is complete is that it gets all of the exceptions that might have been thrown. If there is a second exception, it will be chained to ex, so ex.getNextException is called to see if there is another exception. If there is, the while loop continues and prints out the next exception's message, SQLState, and vendor error code. This continues until there are no more exceptions.

try {
	// Code that could generate an exception goes here.
	// If an exception is generated, the catch block below
	// will print out information about it.
} catch(SQLException ex) {
	System.out.println("\n--- SQLException caught ---\n");
	while (ex != null) {
		System.out.println("Message:   "
                                   + ex.getMessage ());
		System.out.println("SQLState:  "
                                   + ex.getSQLState ());
		System.out.println("ErrorCode: "
                                   + ex.getErrorCode ());
		ex = ex.getNextException();
		System.out.println("");
	}
}

If you were to substitute the catch block above into the code and run it after the table COFFEES had already been created, you would get the following printout:

--- SQLException caught ---
Message:  There is already an object named 'COFFEES' in the database.
Severity 16, State 1, Line 1
SQLState: 42501
ErrorCode:   2714

The vendor error code is specific to each driver, so you need to check your driver documentation for a list of error codes and what they mean.

Retrieving Warnings

SQLWarning objects are a subclass of SQLException that deal with database access warnings. Warnings do not stop the execution of an application, as exceptions do; they simply alert the user that something did not happen as planned. For example, a warning might let you know that a privilege you attempted to revoke was not revoked. Or a warning might tell you that an error occurred during a requested disconnection.

A warning can be reported on a Connection object, a Statement object (including PreparedStatement and CallableStatement objects), or a ResultSet object. Each of these classes has a getWarnings method, which you must invoke in order to see the first warning reported on the calling object. If getWarnings returns a warning, you can call the SQLWarning method getNextWarning on it to get any additional warnings. Executing a statement automatically clears the warnings from a previous statement, so they do not build up. This means, however, that if you want to retrieve warnings reported on a statement, you must do so before you execute another statement.

The following code fragment illustrates how to get complete information about any warnings reported on the Statement object stmt and also on the ResultSet object rs :

Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select COF_NAME from COFFEES");
while (rs.next()) {
	String coffeeName = rs.getString("COF_NAME");
	System.out.println("Coffees available at the Coffee Break:  ");
	System.out.println("    " + coffeeName);
	SQLWarning warning = stmt.getWarnings();
	if (warning != null) {
		System.out.println("\n---Warning---\n");
		while (warning != null) {
			System.out.println("Message: "
                                           + warning.getMessage());
			System.out.println("SQLState: "
                                           + warning.getSQLState());
			System.out.print("Vendor error code: ");
			System.out.println(warning.getErrorCode());
			System.out.println("");
			warning = warning.getNextWarning();
		}
	}
	SQLWarning warn = rs.getWarnings();
	if (warn != null) {
		System.out.println("\n---Warning---\n");
		while (warn != null) {
			System.out.println("Message: "
                                           + warn.getMessage());
			System.out.println("SQLState: "
                                           + warn.getSQLState());
			System.out.print("Vendor error code: ");
			System.out.println(warn.getErrorCode());
			System.out.println("");
			warn = warn.getNextWarning();
		}
	}
}

Warnings are actually rather uncommon. Of those that are reported, by far the most common warning is a DataTruncation warning, a subclass of SQLWarning. All DataTruncation objects have an SQLState of 01004, indicating that there was a problem with reading or writing data. DataTruncation methods let you find out in which column or parameter data was truncated, whether the truncation was on a read or write operation, how many bytes should have been transferred, and how many bytes were actually transferred.