SQLJ

sqlj

The SQLJ Utility (sqlj.exe ) is for Java developers seeking alternatives to JDBC for Oracle database access. This utility is roughly equivalent to the Pro*C and Pro*COBOL precompilers. Given that entire books are written on SQLJ, this book will detail the use of the SQLJ executable. This example requires a working version of the Java SDK, either version 1.3 or 1.4 on the client machine and the SQLJ Oracle installation.

SQLJ is an industry standard that provides an easier way for Java programmers to access databases via SQL. Without SQLJ, they are limited to JDBC result set processing, an error prone and time-consuming process. SQLJ enables developers to insert SQL statements directly into the Java code indicated by the #sql syntax:

#sql users = { select username from v$session };

The sqlj.exe file is used to convert SQLJ syntax to a Java source file and optionally, a class file that can be executed. And, after viewing the messy generated Java source file, it is easy to see the value of SQLJ. This is not a Java source that someone would want to maintain.

The following .sqlj file (Util_Sessions.sqlj) will be used as an example. This SQLJ source simply queries the database for the names of the users connected and displays them.

< Util_Sessions.sqlj

package com.rampant.sqljexample;

import java.sql.SQLException;

import oracle.sqlj.runtime.Oracle;

public class CurrentSessions {

#sql static public iterator userIterator(String username);

public static void main (String args[]) {

try {

Oracle.connect("jdbc:oracle:oci8:@ORCL92", "scott", "tiger");

userIterator users = null;

// retrieve the data into the iterator

#sql users = { select username from v$session };

while (users.next()) {

System.out.println(users.username());

}

catch (SQLException se) {

System.out.println("Error " + se.toString());

}

finally {

try {

Oracle.close();

}

catch (SQLException sqle) {

System.out.println("Cannot close connection");

}

At the operating system level, the Java environment must be configured. The following DOS commands ensure that JAVA_HOME is set and also appends the three zip files below to the CLASSPATH. These zip files are needed for the SQLJ executable to perform.

C:\oracle9i\bin>echo %JAVA_HOME%

c:\j2sdk1.4.0_01

C:\oracle9i\bin>SET CLASSPATH=%CLASSPATH%;%ORACLE_HOME%\sqlj\lib\translator.zip

C:\oracle9i\bin>SET CLASSPATH=%CLASSPATH%;%ORACLE_HOME%\sqlj\lib\runtime12.zip

C:\oracle9i\bin>SET CLASSPATH=%CLASSPATH%;%ORACLE_HOME%\jdbc\lib\classes12.zip

C:\oracle9i\bin>sqlj -compile=true -user=scott/tiger -status -ser2class -warn CurrentSessions.sqlj

[Translating]

[Reading file CurrentSessions]

[Translating file CurrentSessions]

[Compiling]

Here is the resulting Java file (CurrentSessions.java) as generated by the sqlj.exe. The SQLJ executable created a corresponding Java file that will be compiled and executed as part of our program. The SQLJ file is used only for the purpose of generating the Java file.

< CurrentSessions.java

/*@lineinfo:filename=CurrentSessions*//*@lineinfo:user-code*//*@lineinfo:1^1*/package com.rampant.sqljexample;

import java.sql.SQLException;

import oracle.sqlj.runtime.Oracle;

public class CurrentSessions {

/*@lineinfo:generated-code*//*@lineinfo:8^4*/

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

// SQLJ iterator declaration:

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

public static class userIterator

extends sqlj.runtime.ref.ResultSetIterImpl

implements sqlj.runtime.NamedIterator

{

public userIterator(sqlj.runtime.profile.RTResultSet resultSet)

throws java.sql.SQLException

{

super(resultSet);

usernameNdx = findColumn("username");

m_rs = (oracle.jdbc.OracleResultSet) resultSet.getJDBCResultSet();

}

private oracle.jdbc.OracleResultSet m_rs;

public String username()

throws java.sql.SQLException

{

return m_rs.getString(usernameNdx);

}

private int usernameNdx;

}

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

/*@lineinfo:user-code*//*@lineinfo:8^60*/

public static void main (String args[]) {

try {

Oracle.connect("jdbc:oracle:oci8:@ORCL92", "scott", "tiger");

userIterator users = null;

// retrieve the data into the iterator

/*@lineinfo:generated-code*//*@lineinfo:17^7*/

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

// #sql users = { select username from v$session };

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

{

// 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());

try {

__sJT_st = __sJT_ec.prepareOracleStatement(__sJT_cc,"0com..sqljexample.CurrentSessions","select username from v$session");

// execute query

users = new com..sqljexample.CurrentSessions.userIterator(new sqlj.runtime.ref.OraRTResultSet(__sJT_ec.oracleExecuteQuery(),__sJT_st,"0com..sqljexample.CurrentSessions",null));

} finally { __sJT_ec.oracleCloseQuery(); }

}

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

/*@lineinfo:user-code*//*@lineinfo:17^53*/

while (users.next()) {

System.out.println(users.username());

}

catch (SQLException se) {

System.out.println("Error " + se.toString());

}

finally {

try {

Oracle.close();

}

catch (SQLException sqle) {

System.out.println("Cannot close connection");

}

}/*@lineinfo:generated-code*/

Notice that the SQLJ process inserted some special syntax into the generated Java file. Developers should only maintain the sqlj file, however, and not be concerned with editing the sqlj generated Java file.

Once CurrentSessions.java is compiled into a class file, it can be executed. Notice from the output below that three users are connected to the database. This Java program was executed by simply invoking the entire class name at the operating system command line.

C:\oracle9i\bin>java com.rampant.sqljexample.CurrentSessions

SYSTEM

SYS

SCOTT

That is a very simple example, but in a few lines of code, a Java program was created that accessed the database and returned a list of connected users.

SQLJ provides the Java developer with an extensive list of command line options. These enable the Java developer to fully control the SQLJ development environment. These options can be viewed by typing sqlj at the command line with no additional parameters.

Conclusion

The advantages of SQLJ include:

· The automatic validation of SQL code. With JDBC, SQL and DML, statements are only validated at runtime and not compile time, since the SQL is only known as a String object to JDBC. SQLJ will not allow invalid SQL to compile and will inform the developer at development time instead of runtime.

· The readability of the Java code. SQLJ can accomplish the same tasks as JDBC with much fewer lines. In addition, it is much easier to read SQL and DML statements inline in Java.

The disadvantages to SQLJ include:

· The extra step needed to create compiled code to access the database. An extra step is one more opportunity for problems to occur.

The readability of the generated Java code.