Introduction to SQLJ

Want to write less code within your Java applications that deal with SQL calls to a database? Would you like your SQL statements to be checked during compilation and not runtime? Then SQLJ just may be what you need! SQLJ gives exactly what you need for quick development, with less code, ease of debugging, and automatic performance improvement.

This article is part of the SQLJ series. This article assumes you have basic knowledge of JDBC and some of its methods, as well as some SQL. If you want to refresh your JDBC knowledge, have a look this article—JDBC Basics.

What is SQLJ?

This article will give you all the necessary information on how to start coding SQLJ and get you started coding the Data Tier much quicker then ever. SQLJ is essentially embedded SQL (Structured Query Language, sometimes referred to as Sequel) placed into Java applications. SQLJ has been developed to facilitate the development of database-oriented projects, cutting down the development/debugging time significantly. When developing Java applications using JDBC, you are still left to test your SQL against the database.

Code trouble may come from syntax and other semantics that may not be correct, or simply column names may be misspelled, causing the cycle test/debug/code to repeat. Debugging is particular easy with SQLJ because you see the actually generated source and can quickly locate any bug.

By using SQLJ, your code becomes more maintainable and flexible. Sometimes, specifications for your application may change and that should not be unexpected. You often create complex SQL statements combining unions, joins, and multiple where clauses with dynamic values. However, if you use SQLJ, you will be pleased to see that your code is still readable even if it contains an SQL jungle. For example, with SQLJ you don’t need to concatenate your SQL statements; you can write as many SQL statements as you like as long as they are properly marked. If you use Oracle, you can use any dbms package, procedures, custom packages, and all those neat built-in functions right inside your Java application. If you are a PL/SQL developer, you might find this very helpful in developing an advanced software reusing your PL/SQL code and style.

Dynamic versus Static SQL

Unlike embedded static SQL, dynamic SQL may not be known until runtime. At this point, all the validation and parsing of the SQL statement sent to the database server from your application may generate an error. Optimization and analysis of the static SQL can be performed during compilation time, therefore significantly improving the performance of your application.

The Tasks that SQLJ Translator Performs

• Syntactic checking of the embedded SQL constructs
• Java and SQL data type checking
• Schema checking

Figure 1: SQLJ workflow

Setup

At this point, you may be asking, “What do I need for this to work?” You can use pretty much any Oracle Database starting from the 8th version. You must always remember that the database that you will use must have JVM (Java Virtual Machine) built in it, IBM AS/400 (OS 360), or what they now call I-Series IBM operating system has JVM.

For this article, I’ve used Oracle 8i and in the code examples you note that the connection and part of the code refer to Oracle libraries. Create/Drop scripts are available in the source file. Below is the sequence and a table that will be used in this article as example:

CREATE SEQUENCE customer_seq
   MINVALUE 1
   MAXVALUE 999999999999999
   START WITH  1
   INCREMENT BY 1
   CACHE 20; 
create table customer (
   ID number(12) PRIMARY KEY ,
   fullname varchar2(100)  NULL,
   street varchar2(100) null,
   city varchar2(100) null,
   Province varchar2(100) null,
   country varchar2(100) null
);
COMMIT;

Besides your database, you would need the following additional libraries to transform SQLJ:

translator.jar, runtime12.jar (oracle library)

Writing a SQLJ Application

Now that you have an understanding of setups, you can write a simple application that uses SQLJ to retrieve data from the fullname and street name data columns:

Code Listing 1.1

import java.sql.*;    // you need this import for SQLException
                      // and other classes from JDBC
import oracle.sqlj.runtime.Oracle;
public class SingleRowQuery extends Base {
   public static void main(String[] args) {
      try {
         connect();
         singleRowQuery(1);
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }
   public static void singleRowQuery(int id) throws SQLException {
      String fullname = null;
      String street = null;
      //OUT is actually the default for INTO host variables
      #sql {
         SELECT fullname,
            street INTO : OUT fullname,
            : OUT street FROM customer WHERE ID = :id};
      System.out.println("Customer with ID = " + id);
      System.out.println();
      System.out.println(fullname + " " + street);
   }
}

Code Explanation:

Next, you are going to look at the important parts of the code in Listing 1.1. The class Base deals with connection context and is available with this article.

import oracle.sqlj.runtime.Oracle;    // part of the translator.jar
                                      // that deals with all SQLJ
                                      // statements

Any method that has a SQLJ statement must always have throws SQLException; otherwise, the translator will not work. You will see why from the generated code (see Listing 1.2).

Now, move on to the data handling part. You maybe a little confused if you know PL/SQL. Why is “INTO” being used in this context? In this example, you are using a Select statement to save the values into two Host Variables by specifying INTO : OUT.

: OUT means the values that are stored in host variables
: IN means the variables read from; if you are using a method to get the value, you must enclose it with brackets like this: 🙁 this.getName() ).

: [mode] host_variable

Mode can only be OUT, IN, or both INOUT. Remember that OUT is write and IN is read; INOUT will do both.

Now that you are done with the code, you can dig more into the SQLJ specifics. As you can see, the SQLJ execution statement is not a regular string and your file must be have the *.sqlj extension; besides, this the statement has the following syntax:

#SQL {SQL statement};

These are the features that SQLJ supports:

• SQL DML (data modification language) statements such as SELECT, UPDATE, DELETE, and INSERT
  
• SQL transaction control statements such as COMMIT and ROLLBACK
  
• SQL DDL (data definition language) such as CREATE TABLE and DROP TABLE
  
• Calls to Oracle PL/SQL stored procedures, functions, and packages
  
• Session directives

Here is a sample for PL/SQL procedure call:

#SQL {

   //Procedure call

   CALL insertCustomerOrder(1234,44);

};

Here is another sample executable block assigning a host variable a value from PL/SQL:

#SQL {

   DECLARE

   loc_myid number := 1234;

   BEGIN

      SET :(:my_hostvariable) := loc_myid;

   END;

};

Translator Generated Code:

Move on from the developer-written code to the code that is generated by a translator. As you can see in code Listing 1.2, the generated code contains relevant imports for JDBC calls, results checking, sets fetchsize, oracle native execution of a query, and it also generates all exception handling and a few other checks. This is a great features that saves you time writing try { } catch{} blocks. The code generated is written in a good fashion even though it is quite ugly; it contains a method called prepareOracleStatement, which compiles the statement and therefore checks against schema.

Generated code is also optimized when compiled by javac into bytecode.

Code Listing 1.2

// The code is similar to what the original code contains; this

// is a start of the method.

// It contains local variable definitions.

…

//  ************************************************************

//  #sql { SELECT fullname, street

//         FROM customer

//         WHERE ID = :id };

//  ************************************************************

{

   // declare temps

  oracle.jdbc.OraclePreparedStatement __sJT_st = null;

   sqlj.runtime.ref.DefaultContext

      __sJT_cc = sqlj.runtime.ref.DefaultContext.getDefaultContext();

if (__sJT_cc==null)

   sqlj.runtime.error.RuntimeRefErrors.raise_NULL_CONN_CTX();

   sqlj.runtime.ExecutionContext.OracleContext __sJT_ec =

((__sJT_cc.getExecutionContext()==null) ?

sqlj.runtime.ExecutionContext.raiseNullExecCtx() :

__sJT_cc.getExecutionContext().getOracleContext());

   oracle.jdbc.OracleResultSet __sJT_rs = null;

   try {

      __sJT_st =

         __sJT_ec.prepareOracleStatement(

            __sJT_cc,”0SingleRowQuery”,

                     “SELECT fullname,ntttt street

                      FROM customer WHERE ID =  :1”);

      if (__sJT_ec.isNew())

      {

         __sJT_st.setFetchSize(2);

      }

      // set IN parameters

      __sJT_st.setInt(1,id);

      // execute query

      __sJT_rs = __sJT_ec.oracleExecuteQuery();

      if (__sJT_rs.getMetaData().getColumnCount() != 2)

sqlj.runtime.error.RuntimeRefErrors.raise_WRONG_NUM_COLS(

   2,__sJT_rs.getMetaData().getColumnCount());

      if (!__sJT_rs.next()) sqlj.runtime.error.RuntimeRefErrors.

         raise_NO_ROW_SELECT_INTO();

      // retrieve OUT parameters

      fullname = __sJT_rs.getString(1);

      street = __sJT_rs.getString(2);

      if (__sJT_rs.next()) sqlj.runtime.error.RuntimeRefErrors.

         raise_MULTI_ROW_SELECT_INTO();

   } finally { if (__sJT_rs!=null) __sJT_rs.close();

      __sJT_ec.oracleClose(); }

}

…

// printing the result goes here

Retrieving Multiple Rows

In any application, you need to be able to retrieve multiple rows from your database, or some other datasource. If you use SQLJ technology, this is quite an easy task.

In JDBC, you would use a ResultSet to populate with rows (references, to be precise). Using SQLJ, you would use what is called an Iterator to achieve the similar result. (If you know some PL/SQL, you can think of an iterator as a CURSOR REF.)

The following steps summarize iterator processing:

• Use the SQLJ declaration to define the iterator class.
  
• Declare an instance of the iterator.
  
• Populate the iterator instance with SELECT.
  
• Use the next() method of the iterator class to retrive the next row.
  
• Extract the column values from the current iterator row by using the iterator class methods.
  
• Deactivate or dispose the iterator instance by invoking the close() method.

Here is the format for creating an iterator in SQLJ:

#SQL iterator << iterator name >>

   (<< list of attributes declarations) };

Here is the format of instantiating an iterator. As you can see, it’s just another class:

iterator_class_name  instance_name;

// Now populating

#SQL  instance_name = {  select_statement };

Here is the full sample method for retrieving a list of customers:

Code Listing 1.3

//necessary imports

#sql iterator CustomerItr implements Scrollable

   ( int ID, String fullname, String street);

// this line will be translated into Java class called CustomerItr

// with the getters id(), fullname(), street()

//… some main method, connection handling

public static void printCustomers() throws SQLException {

      //declare our iterator object that will store the result

      CustomerItr itr;    // Instantiate an iterator

      //populate the iterator using a SELECT statement

      #sql itr = { SELECT id, fullname,street FROM customer };

      System.out.println(“IDtNametStreettn”);

         itr.afterLast();
      //read column values from the iterator

      while(itr.previous()) {

         System.out.print(itr.ID());

         System.out.print(“t”);

         System.out.print(itr.fullname());

         System.out.print(“t”);

         System.out.print(itr.street());

         System.out.println();

      }

   //close the iterator

   itr.close();

}

Note that the iterator’s parameters must be the same as column names referenced in the database.

Execution & Compilation of SQLJ

Once you have created your SQLJ files, you are going to want to compile and execute them. The following syntax demonstrates how to do this:

> sqlj << filename.sqlj >>

Compile *.sqlj file with proper libraries in your CLASSPATH results in the *.java and *.class file generated.

Conclusion

The future article would cover the Advanced features and aspects of SQLJ development. Writing short and clear code using a database backend is an essential part of the software development.The data tier is almost always present in any type of software architecture weather it be a Web application or a Forms-based application. If you are writing a small time project and don’t have a three-tier application design, SQLJ would be perfect for you.

SQLJ gives you the power of Rapid Application Development (RAD); it also saves time on correcting simple mistakes that always occur due to human error. Furthermore, SQLJ is a standard.

In the next article I will cover the advanced features of SQLJ which include BLOB,LOB datatypes for retrieving files from the database. I will also cover the usage of Java lanaguage within PL/SQL and invocation of Java stored procedures from SQLJ.

About the Author

Serge Bornow graduated from Seneca College in Programming and Analysis. He is currently a software developer for NetHandicap.com. He focuses most of his time on Java and .Net programming. Serge is a Java evangelist and has also been a beta tester for Sun Microsystems.