ONJava.com -- The Independent Source for Enterprise Java
oreilly.comSafari Books Online.Conferences.

advertisement

AddThis Social Bookmark Button

DML Statements
Pages: 1, 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.