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


Learning SQLJ

Database Connections and SQLJ Statements

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

This second column in my Learning SQLJ series explores how to connect to a database and embed SQL statements in your Java programs using SQLJ. 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.

SQLJ Programs

A SQLJ program, like any other Java program, is divided up into blocks, and a SQLJ statement may appear anywhere that a normal Java statement may appear. All SQLJ statements begin with the language token #sql in order to differentiate those statements from other Java program statements.

There are two types of SQLJ statements:

Declarations are used to declare iterators and connection contexts. Iterators are used to store result sets from SQL queries that may return more than one row. Connection contexts are used to establish database connections. Executable statements are used to execute embedded SQL statements and PL/SQL blocks. Any SQL statement that is supported by the JDBC driver may be embedded within a SQLJ executable statement. Executable statements may also contain host expressions, which are used to exchange information between the Java program and the database via Java variables.

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

In order to perform any type of database operation, your SQLJ program must first connect to the database, so without further ado, let's plunge in!

Database Connections

The easiest way to connect to a database is to use the connect() method defined in the class oracle.sqlj.runtime.Oracle. Recall from my first column that this is one of the classes that you must import into your SQLJ program.

The connect() Method

The connect() method accepts three parameters: a database username, a password, and a database URL. The syntax of a call to the connect() method is as follows:

Oracle.connect(URL, username, password);

The syntax elements are as follows:

URL
Identifies the database to which you want to connect, along with the driver you wish to use to make the connection. See the following section, titled "Database URL Structure," for details on this parameter.
username
Specifies your database username.
password
Specifies the password for your username.

The following example shows the connect() method being used to connect to a database:

Oracle.connect(
  "jdbc:oracle:thin:@localhost:1521:orcl",
  "fundamental_user",
  "fundamental_password"
);

In all cases, the connection to a database is made through Oracle Net8 (or above).

Database URL Structure

The database URL tells SQLJ where your database is located. The structure of the database URL is dependent on the brand of JDBC driver being used. In the case of the Oracle's JDBC drivers, the URL structure is as follows:

driver_name:@driver_specific_information

The syntax elements are as follows:

driver_name

Specifies the name of the Oracle JDBC driver that you want to use. This may be any one of the following:

jdbc:oracle:thin
Oracle JDBC Thin driver (for Oracle7 and above)
jdbc:oracle:oci
Oracle JDBC OCI driver (for Oracle9i and above)
jdbc:oracle:oci8
Oracle JDBC OCI8 driver (for Oracle8i and Oracle8)
jdbc:oracle:oci7
Oracle JDBC OCI7 driver (for Oracle7)

driver_specific_information

Specifies any driver-specific information required to connect to the database. This is dependent on the driver being used. In the case of the Oracle JDBC Thin driver, the driver-specific information may be specified in the following format:

host_name:port:database_SID

For all the Oracle JDBC drivers -- including the various OCI drivers -- the driver-specific information may be specified using an Oracle Net8 (or above) keyword-value pair, which may be specified in the following format:

(description=(address=(host=host_name)(protocol=tcp)(port=port)) (connect_data=(sid=database_sid)))

The syntax elements are as follows:

host_name
Specifies the name of the machine on which the database is running.
port
Specifies the port number on which the Net8 database listener waits for requests. 1521 is the default port number. Check with your Database Administrator (DBA) to ensure that this is correct value in your environment.
database_SID
Specifies the system identifier (SID) of the database instance to which you want to connect. Your DBA will be able to provide you with the correct database SID to use.

You may also use an Oracle Net8 (or above) TNSNAMES string -- for more information on this, speak with your DBA or consult the Oracle documentation.

The following example shows the connect() method being used to connect to a database using the Oracle OCI8 driver:

Oracle.connect(
  "jdbc:oracle:oci8:@(description=(address=(host=localhost)" +
    "(protocol=tcp)(port=1521))(connect_data=(sid=orcl)))",
  "scott",
  "tiger"
);

The Oracle JDBC Thin driver has the least amount of system resource requirements, and is generally used in lightweight, client-based programs such as Java applets. The Oracle JDBC Thin driver may be used to access Oracle7 databases and above.

The various Oracle JDBC OCI drivers require more system resources than the thin driver, but they are faster and are suitable for middle tier programs. The OCI driver is used to access Oracle9i databases, and above. The OCI8 driver is used for accessing Oracle8i and Oracle8 databases. The OCI7 driver is used for accessing Oracle7 databases.

Once you've made a connection to the database using the connect() method, you may execute SQLJ statements that contain embedded SQL operations.

Simple SQLJ Statements

In this section you will learn how to write simple SQLJ statements that use embedded SQL Data Manipulation Language (DML) statements. DML consists of the following types of statements:

You will also learn how SQLJ statements can share data with other Java statements in the program, through the use of host variables.

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; the statement type is determined by whether or not the embedded SQL statement returns a value.

If an embedded SQL statement does not return a value, the syntax of the SQLJ executable statement is as follows:

#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, and is 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.

If an embedded SQL statement does return a result, then 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 as follows:

#sql host_variable = { SQL_statement };

The syntax elements are as follows:

host_variable
Is a variable, declared in the Java program, which is used to store the value returned by the SQL statement. This may also be a Java array element or object attribute.
SQL_statement
Is 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 in a future column):

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, the ID of which 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 will return 0 if the product was found; otherwise, the function will return 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 that contains a result expression, such as the one shown here, 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 an SQLJ program retrieve the values stored in table columns into Java variables?" The answer is by using host variables and expressions.

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 convertible into a type that is compatible with the database column type. For example, if the database column contains an integer value, then you should use a Java host variable of type int. Table 1 lists some of the more common type mappings.

You can also use the oracle.sql.NUMBER type to represent numbers, which 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 Oracle rowids, which are pointers to rows in a table.

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 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 variables to the SQLJ executable statement. The syntax for a host expression that uses a host variable is as follows:

:[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
Your SQLJ statement may only read the value stored in the host variable (the value may not be changed).
OUT
Your SQLJ statement should write a new value to the host variable.
INOUT
Your 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; otherwise, the default mode is IN. The mode is not case sensitive, and therefore may be upper or lower case. 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 must be type-compatible with the corresponding Oracle data type.

In SQLJ statements, host expressions are always preceded by a colon (:) character. 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. The following two host expressions, which must be placed in brackets, 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

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

Java function named calc_new_price():

:((calc_new_price(1, 1.2)))

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

I hope you've enjoyed reading this column on how to connect to a database and embed SQL statements in your Java programs with SQLJ. Next month, I'll go over how to add SQLJ to Java programs that use embedded SQL Data Manipulation Language (DML) statements. See you then!

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.


Return to ONJava.com.

Copyright © 2009 O'Reilly Media, Inc.