DML Statements

This third column in my Learning SQLJ series explores how to add SQLJ statements to your Java programs that use embedded SQL Data Manipulation Language (DML) statements. DML statements may be used to retrieve and modify the contents of database tables. In this article, you will also learn how to process database null values and handle database exceptions.

Single Row Queries

The SQL SELECT INTO statement is used to select a single row from a database table and store the specified column values in specified host variables. The values in those host variables may then be accessed in the Java program.

The following example illustrates the use of a SELECT INTO statement to retrieve the first_name, last_name, dob, and phone columns from the customers table for the row where the id column is equal to 2. The values retrieved are stored in host variables:

In this example, five host variables are declared. These are named id, first_name, last_name, dob, and phone. The id variable is an int, which is compatible with the NUMBER database type used for the id column. The first_name, last_name, and phone variables are declared as Java String types. The String type is compatible with the VARCHAR2 database type used by these columns. The dob variable is declared as java.sql.Date, which is compatible with the DATE database type.

Next, the SELECT INTO statement uses four host expressions (identified by the colons) to store the values from the first_name, last_name, dob, and phone columns into the respective host variables:

The OUT mode in front of the first_name, last_name, dob, and phone variables indicate that the SELECT INTO statement will store values in those variables. The IN mode in front of the id variable indicates that the SELECT INTO statement will read the value stored in the id variable.

Built-in database functions can also be used in queries. The following example uses the COUNT() function to count the number of rows in the products table, and stores the result in a host variable named number_of_products:

Host variables and expressions can also be used in SQL UPDATE, DELETE, and INSERT statements. These statements are shown in the following three sections.

Updating Rows

The SQL UPDATE statement is used to modify rows in a table. When an UPDATE statement is used in a SQLJ executable statement, host expressions can appear in the SET and WHERE clauses. Here's an example:

A SELECT statement may also be used in conjunction with an INSERT statement. The rows returned from the SELECT statement are passed to the INSERT statement, which then adds those rows to a table. When SELECT and INSERT statements are used together in a SQLJ executable statement, host expressions can appear in the SELECT statement's column list and WHERE clause. For example:

Handling Database Null Values

Columns in a database table can be defined as being NULL or NOT NULL. NULL indicates that the column doesn't have to contain a value when a row is inserted into the table. NOT NULL indicates that the column value must be set. The default behavior, if you don't specify otherwise, is for Oracle to allow nulls.

Unfortunately, the Java numeric, logical, and bit types (int, float, boolean, and byte, for example) cannot be used to retrieve nulls from the database. So what do you do if a column that you want to select using a SQLJ statement may contain a null? The answer is that you must use the Java wrapper classes. A wrapper class is a Java class that allows you to define a wrapper variable, which can then be used to retrieve database nulls. These wrapper classes are contained in the java.lang package, and the following seven wrapper classes are defined:

The wrapper classes may be used to represent database null values for the various types of numbers, as well as for the boolean type. The following example declares a host variable using the java.lang.Double wrapper class:

java.lang.Double price_var;

Once declared, you can use wrapper variables as you would any other host variable. The following example sets the price column for product #1 to null using an UPDATE statement. The new price is then retrieved, and stored in price_var, using a SELECT INTO statement:

If you attempt to retrieve a database null like this into a regular Java double variable, you will get a SQL exception when the statement is executed. This is because double variables can't represent database nulls.

Wrapper variables implement accessor methods that you can use to get at the underlying value, when that value is not null. If the price column in the previous example contained an actual number rather than a null, the SELECT statement would have stored that value in the price_var wrapper variable. If you then wanted to convert that value to a Java double variable, you could use the wrapper class's doubleValue() method. The code in the following example checks to see if price_var contains a non-null value. If price_var does contain a non-null value, then the doubleValue() method is used to store that value in the double variable named price_var2.

The other wrapper classes contain similar methods for the other Java types. For example, java.lang.Float has floatValue(), and java.lang.Byte has byteValue().

Handling Exceptions

SQLJ executable statements must be contained in a try/catch statement, or they must be placed in a method that throws the required exception. If an exception is caused by a statement in the try clause, then the exception is thrown to the catch clause. Java will then attempt to locate an appropriate handler to process the exception. If the catch clause doesn't contain an appropriate handler, the exception is thrown up the call stack until a handler is found.

The try block will contain the SQLJ statements that may cause a java.sql.SQLException, the catch block should contain the statements that are executed when a java.sql.SQLException is raised. The following example shows a catch block that simply displays an error message in the event that the DELETE in the try block fails:

There is a subclass of java.sql.SQLException that you can use to handle database null exceptions more specifically:

java.sql.SQLNullException

This exception is thrown when a database null value is selected into a Java primitive type. If you use this subclass of java.sql.SQLException, you must place the handler for the subclass before the handler for SQLException, otherwise SQLException would handle SQLNullException. This is because SQLException is the superclass, and the subclass would be recognized as being an instance of the superclass.

The try/catch statement in the following example contains handlers for SQLNullException and SQLException. Notice that SQLNullException is placed before SQLException:

By placing the subclasses (which are more specific) first, you ensure that those handlers are executed if they are applicable. Only if neither subclass applies will the more general SQLException handler get control.

I hope you've enjoyed reading this third column on learning SQLJ. Next month, I'll show you how to add SQL Data Definition Language (DDL) statements to your programs to create database tables, and I'll also cover how to handle database transactions.

Jason Price
is currently employed as a product manager with Oracle Corporation. He has contributed to many products at Oracle, including the database server, the application server, and several CRM applications.