The Java TM Tutorial

| Start of Tutorial > Start of Trail > Start of Lesson | Search

Trail: JDBC Database Access
Lesson: JDBC Basics

## Creating Complete JDBC Applications

> Up to this point, you have seen only code fragments. Later in this section you will see sample programs that are complete applications you can run.

> 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 is made 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 `SQLException`s, 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 `CreateCoffees.java` 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

> SQLState is a code defined in X/Open and ANSI-92 that identifies the exception. Two examples of SQLState code numbers and their meanings follow:

> >     08001 -- No suitable driver
> >     HY011 -- Operation invalid at this time

> 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.

| Start of Tutorial > Start of Trail > Start of Lesson | Search