3.3 Simple SQLJ Statements
In this section, you will learn how to write a program that contains simple SQLJ statements that use embedded SQL Data Manipulation Language (DML) statements. DML consists of the following types of statements:
-
SELECT
-
INSERT
-
UPDATE
-
DELETE
You will also learn how SQLJ statements can share data with other Java statements in the program through the use of host variables. At the end of this section, there is a complete example program that retrieves and modifies some of the data stored in the fundamental_user database schema.
3.3.1 The Form of a SQLJ Statement
A SQLJ executable statement is a program line that contains an embedded SQL statement. There are two possible types of executable statements, determined by whether or not the embedded SQL statement returns a value.
3.3.1.1 Statements that do not return a value
If an embedded SQL statement does not return a value, the syntax of the SQLJ executable statement is:
#sql { SQL_statement }; The syntax element is as follows:
- SQL_statement
-
Specifies any valid SQL statement.
The following SQLJ executable statement invokes a SQL INSERT statement to add a row to the customers table:
#sql { INSERT INTO customers (id, first_name, last_name, dob, phone) VALUES (1, 'John', 'Smith', '13-NOV-1970', '650-555-1212') }; Everything to the right of the #sql token in the syntax is the executable part of the SQLJ statement, known as the SQLJ clause. There are two types of SQLJ clauses. Because this SQLJ clause does not include a result expression, it is known as a statement clause.
3.3.1.2 Statements that return a value
If an embedded SQL statement does return a result, you need a way to specify where that result should be placed. SQLJ syntax accommodates this need. When a value is returned, the syntax for a SQLJ executable statement is:
#sql host_variable = { SQL_statement }; The syntax elements are as follows:
- host_variable
-
A variable, declared in the Java program, used to store the value returned by the SQL statement. This may also be a Java array element or object attribute.
- SQL_statement
-
Any valid SQL statement that returns a value.
An example of the type of SQL statement that returns a value is a call to a PL/SQL function. The following example uses an assignment clause to store the result returned by a call to a PL/SQL function (you will learn more about using PL/SQL in SQLJ later in this chapter):
int result; #sql result = { VALUES update_product_price_func(1, 2) }; The PL/SQL function update_product_price_func( ) is created by the fundamental_user_schema.sql script. It attempts to update the price column of the row in the products table whose id is equal to the first parameter in the function call. The price column is multiplied by the second parameter in the function call. The function returns 0 if the product was found, and otherwise returns 1. The value returned from this function is assigned by the SQLJ statement to the Java variable named result.
Recall that everything to the right of #sql is known as a SQLJ clause. A SQLJ clause such as the one shown here, containing a result expression, is known as an assignment clause. Assignment clauses are fine for storing the results of PL/SQL function calls, but one question I'm sure you're thinking is, "How does a SQLJ program retrieve the values stored in table columns into Java variables?" The answer is by using host variables and expressions.
3.3.2 Host Variables and Expressions
Host variables allow SQLJ programs to exchange information between the embedded SQL statements and the rest of the Java program. A host variable is any Java variable that is declared in the Java program. Host variables may be referenced within a SQLJ statement, and SQLJ takes care of the details of moving data back and forth between the SQL and Java environments.
When you use a host variable in a SQLJ statement to hold the value of a column retrieved from the database, the type of the host variable must be convertable into a type that is compatible with the database column type. For example, if the database column contains an integer value, you should use a Java host variable of type int. Table 3-1 lists some of the more common type mappings.
You can also use the oracle.sql.NUMBER type to represent numbers. This is useful if you are dealing with large numbers because there is no loss of precision like that which may occur when using the Java double or float types. The oracle.sql.ROWID type may be used to represent rowids (discussed in Chapter 2). For a full description of type mappings between Java and Oracle, see Appendix A.
In addition to using compatible types, you must ensure that the host variable is within the scope of the SQLJ statement in which the reference to the host variable is located.
Table 3-1. Common Java-to-Oracle type mappings
| Java type | Description | Oracle type |
| int | Integer values | INTEGER |
| double | Real numbers | NUMBER |
| String | Character strings | VARCHAR2 |
Host variables are placed within SQLJ host expressions; a host expression is the "glue" that binds the host variable to the SQLJ executable statement. The syntax for a host expression that uses a host variable is:
:[mode] host_variable
The syntax elements are as follows:
- mode (optional)
-
Specifies the mode of the host expression and may be set to one of the following:
- IN
-
The SQLJ statement may only read the value stored in the host variable (the value may not be changed).
- OUT
-
The SQLJ statement should write a new value to the host variable.
- INOUT
-
The SQLJ statement may both read and write the value of the host variable.
The default mode is OUT if the host expression is part of an INTO list, or if the expression is an assignment in a SET statement (the SET statement is discussed later in this chapter). Otherwise, the default mode is IN. The mode is not case-sensitive, and therefore may be upper- or lowercase. A space is required immediately after the mode.
- host_variable
-
The name of a Java variable in the program. The variable must be within scope and type-compatible with the corresponding Oracle data type.
In SQLJ statements, host expressions are always preceded by a colon (:). The following are examples of host expressions that could be used in SQLJ statements:
:IN variable1 :OUT variable2 :INOUT variable3
A host expression may also contain other Java storage constructs, including array elements and object attributes. These must be placed in parentheses. The following two host expressions show how to reference an object attribute and an array element, respectively:
:(object.attribute1) :(array[1])
You can also call Java functions from a host expression. For example, the following host expression calls a Java function named calc_new_price( ):
:((calc_new_price(1, 1.2)))
Assume that the Java function calc_new_price( ) multiplies the original price of a product (the ID of which is passed as the first parameter) by the value specified in the second parameter. The new price returned by the function acts as an input to the host expression.
3.3.3 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.
 | If a SELECT INTO statement returns more than one row or no rows, a runtime error is generated. I show you how to handle multiple-row queries later in this chapter. |
|
The following example illustrates a SELECT INTO statement that retrieves the first_name, last_name, dob, and phone columns from the customers table for the row in which the id column equals 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 is 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 the 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 }; The OUT modes 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 described in the following sections.
3.3.4 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 in which the product_id column is equal to 2.
3.3.5 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 in which the id column is equal to 2.
3.3.6 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 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 }; 3.3.7 Handling Database Null Values
In Chapter 2, you learned that 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. If you don't specify otherwise, Oracle allows nulls by default.
Unfortunately, the Java numeric, logical, and bit types (int, float, boolean, and byte, for example) can't retrieve nulls from the database. So what do you do if a column you want to select using a SQLJ statement may contain a null? 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; 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 get a SQL exception when the statement is executed. This happens because double variables can't represent database nulls.
Wrapper variables implement accessor methods that you 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, 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( ).
3.3.8 Handling Exceptions
In Chapter 1, you learned that SQLJ executable statements must be contained in a try/catch statement. If an exception is caused by a statement in the try clause, the exception is thrown to the catch clause. Java then attempts 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 like this:
try { ... } catch (SQLException e) { ... } The try block contains the SQLJ statements that may cause a java.sql.SQLException, and the catch block should contain the statements to be 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
-
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 handles SQLNullException. This is because SQLException is the superclass, and the subclass would be recognized as an instance of the superclass.
The try/catch statement in this 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 no subclass applies will the more general SQLException handler get control.
3.3.9 Using Data Definition Language Statements
Data Definition Language (DDL) statements are used to create and modify database objects, and consist of statements such as CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, CREATE SEQUENCE, and so forth. In a SQLJ program, you execute a DDL statement just as you would any other SQL statement: you enclose it within a #sql token. The following example shows the CREATE TABLE statement creating a table named addresses to be used to store customer addresses:
#sql { CREATE TABLE addresses ( id NUMBER CONSTRAINT addresses_pk PRIMARY KEY, customer_id NUMBER CONSTRAINT addresses_fk_customers REFERENCES customers(id), street VARCHAR2(255) NOT NULL, city VARCHAR2(255) NOT NULL, state CHAR(2) NOT NULL, country VARCHAR2(255) NOT NULL ) }; 
