ONJava.com    
 Published on ONJava.com (http://www.onjava.com/)
 See this if you're having trouble printing code examples


Learning SQLJ

DML Statements

by Jason Price, author of Java Programming with Oracle SQLJ
02/06/2002

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.

These columns reference numerous SQL scripts, source code, and other files that are available for download at O'Reilly's Web site. For more specific information about the files used, or to get an introduction to SQLJ, read my first column, Setting Up Your Environment to Develop SQLJ Programs.

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:

// declare host variables
int id = 2;
String first_name = null;
String last_name = null;
java.sql.Date dob = null;
String phone = null;

// perform SELECT to get the customer details for
//  the customer #2 from the customers table
#sql {
  SELECT
    first_name, last_name, dob, phone
  INTO
    :first_name, :last_name, :dob, :phone
  FROM
    customers
  WHERE
    id = :id
};

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:

SELECT
  first_name, last_name, dob, phone
INTO
  :first_name, :last_name, :dob, :phone

The default mode for the host expressions in an INTO clause is OUT, meaning that the values for these four host variables are set by the statement.

The WHERE clause of the SELECT INTO statement uses the value stored in the id host variable to identify the row to be retrieved from the customers table:

WHERE
  id = :id

The default mode for a host variable in a WHERE clause is IN, meaning that the id value is read by the statement.

After the SELECT INTO statement has been executed, the first_name, last_name, dob, and phone host variables contain the column values retrieved from the customers table.

The previous SELECT statement relied on the default mode for each of the host variables. It's also possible to specify those modes explicitly, as in the following statement:

#sql {
  SELECT
    first_name, last_name, dob, phone
  INTO
    :OUT first_name, :OUT last_name, :OUT dob, :OUT phone
  FROM
    customers
  WHERE
    id = :IN id
};

Related Reading

Java Programming with Oracle SQLJJava Programming with Oracle SQLJ
By Jason Price
Table of Contents
Index
Sample Chapter
Full Description
Read Online -- Safari

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:

int number_of_products;

#sql {
  SELECT
    COUNT(*)
  INTO
    :number_of_products
  FROM
    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:

int new_quantity = 10;
int cust_id = 2;
int prod_id = 3;

#sql {
  UPDATE
    purchases
  SET
    quantity = :new_quantity
  WHERE
    purchased_by = :cust_id
  AND
    product_id = :prod_id
};

This example updates the quantity column for the row in the purchases table, where the product_id column is equal to 2.

Deleting Rows

The SQL DELETE statement is used to remove rows from a table. When a DELETE statement is used in a SQLJ executable statement, host expressions can appear in the WHERE clause. For example:

int cust_id = 2;

#sql {
  DELETE FROM
     customers
  WHERE
    id = :cust_id
};

This example deletes the row in the customers table, where the id column is equal to 2.

Inserting Rows

The SQL INSERT statement is used to add rows to a table. When an INSERT statement is used in a SQLJ executable statement, host expressions can appear in the VALUES clause. For example:

int id = 13;
int type_id = 1;
String name = "Life Story";
String description = "The Life and Times of Jason Price";
double price = 19.95;

#sql {
  INSERT INTO
    products (id, type_id, name, description, price)
  VALUES
    (:id, :type_id, :name, :description, :price)
};

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:

int id = 14;
double new_price = 10.95;
int prod_id = 4;

#sql {
  INSERT INTO products
    SELECT
      :id, type_id, name, description, :new_price
    FROM
      products
    WHERE
      id = :prod_id
};

Handling Database Null Values

Previously in this series:

DDL Statements and Transactions

Database Connections and SQLJ Statements

Setting Up Your Environment to Develop SQLJ Programs

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:

java.lang.Boolean
java.lang.Byte
java.lang.Short
java.lang.Integer
java.lang.Long
java.lang.Float
java.lang.Double

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:

// set the price to null
#sql {
  UPDATE
    products
  SET
    price = NULL
  WHERE
    id = 1
};

// retrieve the null price into price_var
#sql {
  SELECT
    price
  INTO
    :price_var
  FROM
    products
  WHERE
    id = 1
};

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.

double price_var2 = 0.0;
if (price_var != null) {
  price_var2 = price_var.doubleValue();
}

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.

SQLJ executable statements throw exceptions of the java.sql.SQLException class, so your try/catch statement should look as follows:

try {
  ...
} catch (SQLException e) {
  ...
}

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:

try {
  #sql {
    DELETE FROM
      customers
  };
} catch (SQLException exception) {
  System.out.println("SQLException " + exception);
}

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:

try {
  ...
} catch (SQLNullException null_exception) {
  System.out.println("SQLNullException " + null_exception);
} catch (SQLException exception) {
  System.out.println("SQLException " + exception);
}

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.


Read more Learning SQLJ columns.

Return to ONJava.com.

Copyright © 2009 O'Reilly Media, Inc.