AddThis Social Bookmark Button


Listen Print

Using Static and Dynamic SQL in SQLJ

by Jason Price
08/09/2001

SQLJ is an ISO and ANSI standard that was developed by many large corporations including Oracle, Sun Microsystems, IBM, Compaq, Informix, and Sybase. You can view the latest developments on the SQLJ standard at the SQLJ.org Web site. You can download trial versions of Oracle SQLJ and other Oracle software from Oracle's Technet Web site.

Here's an example that shows you the power of SQLJ when compared with JDBC. The following two code snippets retrieve the name and price columns from a database table containing a list of products (the product table).

First, the JDBC code snippet:

int v_id = 1;
String v_name = null;
float v_price = 0.0;
PreparedStatement prepared_statement = connection.prepareStatement(
  "SELECT name, price FROM products WHERE id = ?"
);
prepared_statement.setInt(1, v_id);
ResultSet result_set = prepared_statement.executeQuery();
while (result_set.next()) {
  v_name = result_set.getString(1);
  v_price = result_set.getFloat(2);
  System.out.println("Name = " + v_name + ", price = " + v_price);
}
result_set.close();
prepared_statement.close()

Next, the SQLJ code snippet:

int v_id = 1;
String v_name = null;
float v_price = 0.0;
#sql {
  SELECT name, price INTO :v_name, :v_price FROM products WHERE id = :v_id
};
System.out.println("Name = " + v_name + ", price = " + v_price);

See how the SQLJ code is much shorter and easier to understand? Also, SQLJ can check the SQL statement and variables for correctness, helping you trap errors in your programs before you run them.

You can use SQLJ to embed SQL statements in many types of Java programs, including stand-alone applications, applets, servlets, JavaServer Pages (JSP), Enterprise JavaBeans (EJB), and Oracle Java Stored Procedures.

Visit java.oreilly.com for a complete list of O'Reilly books about Java technologies.

In this article, I'll show you an example of a complete SQLJ program and how to compile and run that program. In addition, I'll introduce you to an exciting new feature of SQLJ Version 9i: adding dynamic SQL to SQLJ programs.

A "Hello World" Program for SQLJ

Many programming books introduce new languages using a variation of the "Hello World" program. The classic "Hello World" program has a simple objective: to display the words "Hello World" on the screen. My version of the "Hello World" program uses SQLJ statements to connect to the database and retrieve the current date, which is then displayed for you to see. First, I'll show you the code required for this "Hello World" program. Then I'll show you how to compile and run it. By convention, SQLJ programs use the file extension .sqlj, so the "Hello World" program will be named HelloWorld.sqlj.

The HelloWorld.sqlj Program

The HelloWorld.sqlj program, shown in Example 1, performs the following tasks:

  1. Connects to the database.
  2. Retrieves the current date from the database.
  3. Displays a message containing the current date obtained in the previous step.
  4. Disconnects from the database.

Example 1 lists the code for the HelloWorld.sqlj program.

Example 1. HelloWorld.sqlj

/*
   The program HelloWorld.sqlj illustrates how to connect to a
   database, and display the words "Hello World" along with
   the current date.
*/

// import required packages
import java.sql.Date;
import java.sql.SQLException;
import oracle.sqlj.runtime.Oracle;

public class HelloWorld {

  public static void main(String [] args) {

    java.sql.Date current_date;

    try {

      // connect to the database
      Oracle.connect(
        "jdbc:oracle:thin:@localhost:1521:orcl",
        "scott",
        "tiger"
      );

      // get the current date from the database
      #sql { SELECT sysdate INTO :current_date FROM dual };

      // display message
      System.out.println("Hello World! The current date is " +
        current_date);

    } catch ( SQLException e ) {

      System.err.println("SQLException " + e);

    } finally {
      try {

        // disconnect from the database
        Oracle.close();

      } catch ( SQLException e ) {
        System.err.println("SQLException " + e);
      }
    }

  } // end of main()

}

Most of the statements in this program are no different than those in a regular Java program. Let's look at the lines specific to SQLJ:

import java.sql.*;
import oracle.sqlj.runtime.Oracle;

The java.sql packages contain the classes, libraries, and interfaces for SQLJ programs. The program imports the java.sql.Date and java.sql.SQLException classes, which are described later. The oracle.sqlj.runtime.Oracle package contains the runtime methods for establishing connections to the database, including the Oracle.connect() and Oracle.close() methods.

Visit oracle.oreilly.com for a complete list of O'Reilly books about Oracle technologies.

The following program line declares an object named current_date:

java.sql.Date current_date;

The current_date object is declared using the java.sql.Date class. This is the SQL version of the java.util.Date class. I used the SQL version because the program will later retrieve the current date from the database and store it in the current_date object.

The main body of the program is contained within a try/catch block. All SQLJ statements must be contained within a try/catch block in order to trap any database errors that may occur. Such blocks may appear as follows:

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

  System.err.println("SQLException " + e);

}

If a database error occurs, then an exception of type java.sql.SQLException will be raised, and the catch block will be entered. In the example shown here, the catch block displays the details of the exception. Let's look at the statements inside the try block. The first one connects to the database:

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

Related Reading

Java Programming with Oracle SQLJ

Java Programming with Oracle SQLJ
By Jason Price

Table of Contents
Index
Sample Chapter

Read Online--Safari Search this book on Safari:
 

Code Fragments only

The Oracle.connect() method is used to connect to the database. The first parameter in the method call is known as the database URL and specifies the name and location of the database, along with the name of the driver to use when accessing the database. In the example shown here, the database URL is set to jdbc:oracle:thin:@localhost:1521:orcl, which specifies that a connect request is being made to a database instance named orcl, running on the machine localhost, using the Oracle JDBC Thin driver.

Next, a SQL statement is embedded into the program as follows:

#sql { SELECT sysdate INTO :current_date FROM dual };

This SQL statement retrieves the current date from the database and stores it in the current_date object. The colon indicates that current_date is a Java object defined in the program. The dual table used in this SQL statement is a table that is always present in an Oracle database; it is a dummy table that generally contains one row and allows you to use the Oracle built-in functions like sysdate, which returns the current date. This date is then displayed in the "Hello World" greeting by means of the following statement:

System.out.println("Hello World! The current date is " +
  current_date);

Disconnection from the database, performed using a call to the Oracle.close() method, is handled within a finally block, which is executed before the program exits:

} finally {
  try {

    // disconnect from the database
    Oracle.close();

  } catch ( SQLException e ) {
    System.err.println("SQLException " + e);
  }
}

It is considered good programming practice to disconnect from the database within a finally block.

Compiling and Running HelloWorld.sqlj

The HelloWorld.sqlj file contains embedded SQL statements that are contained within SQLJ program statements. Having written the program, your next step is to translate the SQLJ statements contained in the HelloWorld.sqlj file into calls to the SQLJ runtime libraries. This translation is performed using the sqlj command line utility. The sqlj command line utility translates the .sqlj file into a .java file, and then compiles the .java file into a .class file using the Java compiler.

The following command invokes sqlj to translate and compile the HelloWorld.sqlj file:

sqlj HelloWorld.sqlj

The sqlj command line utility translates HelloWorld.sqlj into HelloWorld.java and then calls the Java compiler to compile HelloWorld.java into HelloWorld.class. The HelloWorld.class file may then be executed using the java command line utility:

java HelloWorld

The program should display the following line (your date will be different):

Hello World! The current date is 2000-11-10

Static and Dynamic SQL in SQLJ

A static SQL statement is one in which the database tables and columns referenced in the statement are known when the program is written. It is likely that all, or most, of the SQL statements you will use in a program will be static. The previous SQLJ examples showed how to embed a static SQL statement using SQLJ. A dynamic SQL statement allows the table and column names referenced by the SQL statement to be set when the program runs. Version 9i of SQLJ allows you to embed both static and dynamic SQL statements in your SQLJ programs. With SQLJ Version 8.1.7 and below, you can embed static SQL directly and use JDBC statements to handle dynamic SQL.

The best way to learn how to use dynamic SQL using SQLJ 9i is to examine an example. The following example generates a dynamic SQL statement that selects the first_name, last_name, and phone column values from a database table called customers, where the id column is equal to 1:

int id = 1;
String first_name = null;
String last_name = null;
String phone = null;

String table = "customers";
String query = "id = " + id;
String column = "first_name";

#sql {
  SELECT
    :{column}, last_name, phone
  INTO
    :first_name, :last_name, :phone
  FROM
    :{table}
  WHERE
    :{query}
};

The table variable holds the name of the table (customers), the query variable holds the details of the WHERE clause ("id = 1"), and the column variable holds the name of a column used in the query (first_name). You can see that the dynamic parts of the SQL statement are placed within curly braces, which are in turn prefixed by a colon. These constructs are known as meta bind expressions. At runtime, the meta bind expressions in the previous statement are evaluated and their values are substituted into the final statement, which then appears as follows:

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

The full syntax for a meta bind expression is as follows:

:{ bind_expression [:: database_replacement ] }

The syntax elements are as follows:

bind_expression
Specifies a Java String value, or an expression that evaluates to a Java String value, and may be used to specify a table name, column name, or other items.

database_replacement
Specifies a database table, column, WHERE clause, etc., which is used by SQLJ when translating the dynamic SQL statement. This is optional, but it enables SQLJ to perform compile-time semantic checking of your statement.

The following example is derived from the first example in this section. The difference is that I've added database replacements, which would allow SQLJ to perform compile-time semantic checking of the statement:

#sql {
  SELECT
    :{column :: first_name}, last_name, phone
  INTO
    :first_name, :last_name, :phone
  FROM
    :{table :: customers}
  WHERE
    :{query :: id = 1}
};

So, if you were to invoke the sqlj command line utility using the user option to have it check the semantics of your SQL statements, it would use the database replacements when checking the dynamic SQL statement. In effect, the statement that it checks will be as follows:

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

Database replacements are useful, because the SQLJ compiler has no way of executing your code to determine which values it will actually supply at runtime. By using database replacements, you can specify reasonable values to use when checking the semantics of the statement at compile-time. Of course, the database replacement could be anything and needn't match what your statement actually does at runtime.

Finding Out More

I hope you've enjoyed reading this article and are interested in learning more about SQLJ. In addition to my book, Java Programming with Oracle SQLJ, there are several sources for more information on SQLJ, including Oracle's Technet Web site and the SQLJ Web site.

Jason Price is currently employed as a product manager with Oracle. He has contributed to many products at Oracle, including the database server, the application server, and several CRM applications. Prior to working at Oracle, he was employed as a consultant at Logica PLC in London, a systems integration and technical consulting firm. He is a certified Oracle DBA and Application Developer, and he has over ten years of experience in the software industry. When Jason is not working or writing, he enjoys walking, running, and working out. He holds a Bachelor of Science degree (with honors) in Physics from the University of Bristol, England. Jason currently resides in California.

O'Reilly & Associates will soon release (September 2001) Java Programming with Oracle SQLJ.