May 26, 2018
Hot Topics:

Introduction to SQLJ

  • October 13, 2005
  • By Serge Bornow
  • Send Email »
  • More Articles »

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 {
   loc_myid number := 1234;
      SET :(:my_hostvariable) := loc_myid;

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;
      __sJT_cc = sqlj.runtime.ref.DefaultContext.getDefaultContext();
if (__sJT_cc==null)
   sqlj.runtime.ExecutionContext.OracleContext __sJT_ec = 
((__sJT_cc.getExecutionContext()==null) ? 
sqlj.runtime.ExecutionContext.raiseNullExecCtx() : 
   oracle.jdbc.OracleResultSet __sJT_rs = null;
   try {
      __sJT_st =
                     "SELECT fullname,ntttt street
                      FROM customer WHERE ID =  :1");
      if (__sJT_ec.isNew())
      // set IN parameters
      // execute query
      __sJT_rs = __sJT_ec.oracleExecuteQuery();
      if (__sJT_rs.getMetaData().getColumnCount() != 2)
      if (!__sJT_rs.next()) sqlj.runtime.error.RuntimeRefErrors.
      // retrieve OUT parameters
      fullname = __sJT_rs.getString(1);
      street = __sJT_rs.getString(2);
      if (__sJT_rs.next()) sqlj.runtime.error.RuntimeRefErrors.
   } 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 };


      //read column values from the iterator
      while(itr.previous()) {
   //close the iterator

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.


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.

Page 2 of 2

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

By submitting your information, you agree that developer.com may send you developer offers via email, phone and text message, as well as email offers about other products and services that developer believes may be of interest to you. developer will process your information in accordance with the Quinstreet Privacy Policy.


We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Thanks for your registration, follow us on our social networks to keep up-to-date