Development Steps with SQLJ
Here's what you need to do to develop and run a SQLJ application:
- Translate the SQLJ source files with the SQLJ translator. This process generates Java files with calls to the SQLJ runtime, as well as binary SQLJ profile files that contain information about the static SQL statements present in the SQLJ source.
- Compile the Java code with the Java compiler. After compilation, the generated profiles are customized for using database-specific data types, extensions, and features.
- Run the application, using the SQLJ runtime library and database-specific JDBC driver.
For example, if your main .sqlj file defines class MyClass, then the source filename must be MyClass.sqlj. The translator produces the MyClass.java source file, and the compiler produces the MyClass.class class file. Also, both the translator and the compiler produce the profile-keys class, MyClass_SJProfileKeys.class. The translator names iterator classes and connection context classes according to how you declare them. For example, if you declare an iterator, MyIter, there will be a MyIter.class class file.
Here are the requirements for deploying SQLJ:
- SQLJ runtime library
- JDBC driver, e.g. JDBC/ODBC bridge, Oracle JDBC/OCI driver, Oracle Thin JDBC driver, DB2 JDBC driver, etc.
- JVM where the SQLJ program will execute
Now let's compare SQLJ with JDBC, and SQLJ/JDBC with PL/SQL.
SQLJ vs. JDBC
SQLJ was developed to complement the dynamic JDBC SQL model with a static SQL model. Unlike the ODBC and JDBC dynamic models, the static model provides strong type checking at application translation time. This entails not only compile-time checking of the SQL syntax and of the type compatibility of the host variables with the SQL statements in which they are used, but also of the correctness of the query itself with respect to the definition of tables, views, stored procedures, etc. in the database schema. Because all SQL is compiled, SQLJ could be a vehicle for better performance.
SQLJ Code vs. JDBC Code: For SQL statements with input arguments, SQLJ classes are often shorter than the equivalent dynamic SQL (JDBC) calls, because SQLJ uses host variables to pass arguments to SQL statements, while JDBC requires a separate statement to bind each argument and to retrieve each result.
Here is the SQLJ code fragment for a single-row SELECT statement...
String vName; int vSalary; String vJob; Java.sql.Timestamp vDate;
...
#sql { SELECT Ename, Sal
INTO :vName, :vSalary
FROM Emp
WHERE Job = :vJob and HireDate = :vDate };
and here is the JDBC code fragment for the same single-row SELECT statement:
String vName; int vSalary; String vJob; Java.sql.Timestamp vDate;
...
PreparedStatement stmt = connection.prepareStatement(
"SELECT Ename, Sal " +
"INTO :vName, :vSalary " +
"FROM Emp " +
"WHERE Job = :vJob and HireDate = :vDate");
stmt.setString(1, vJob);
stmt.setTimestamp(2, vDate);
ResultSet rs = stmt.executeQuery();
rs.next();
vName = rs.getString(1);
vSalary = rs.getInt(2);
rs.close();
As you can see, embedding SQL statements directly in a Java program produces much more concise and easy-to-read code than JDBC. Therefore, SQLJ reduces development time and maintenance costs in Java applications with database access. SQLJ programs could easily interact with JDBC code in the same source file to allow dynamic SQL calls, or you could use PL/SQL blocks within SQLJ statements for that. In addition, Oracle 9i added direct support for dynamic SQL in SQLJ code.
Java (SQLJ and JDBC) vs. PL/SQL in Oracle Databases: Java in Oracle database applications does not replace PL/SQL. Java and PL/SQL complement each other. Java (SQLJ/JDBC) enjoys the following advantages over PL/SQL:
- Java offers significant performance advantages for computationally expensive routines. According to Oracle performance tuning expert Guy Harrison, Software Architect with Quest Software, Inc., Java stored procedures are 5 to 100 times (!) faster, depending on the math operations and data types used in the procedure.
- Theoretically, Java stored procedures could be more easily converted to run against other databases.
- Java procedures could be deployed at any tier in a complex application: on the client, on an application server in the middle tier, or in the database server itself.
Java (SQLJ/JDBC) also has its share of disadvantages compared to PL/SQL:
- PL/SQL is tightly integrated with the Oracle database server. Oracle has been extending and polishing PL/SQL for more than 20 years while Java was just introduced in Oracle 8i in 1998.
- PL/SQL datatypes are equivalent to Oracle-native datatypes, so there is no datatype conversion required. On the other hand, JDBC interposes a generic layer between Java code and SQL, and SQLJ is one more layer.
- PL/SQL outperforms Java for database-centric routines: PL/SQL stored procedures run 1.5 times (for OLTP) to 2.5 times (for batch process) faster than Java procedures. In addition, Guy Harrison's tests show that Java procedures use more CPU power than PL/SQL. CPU overhead could be interpreted as a result of having to work with a longer code path and additional data type conversion from Oracle to Java.
A "Best of Both Worlds" Solution
Oracle provides an ideal environment for taking advantage of the strengths of both PL/SQL and Java languages. On the one hand, PL/SQL programs can transparently call SQLJ and JDBC stored procedures, allowing you to build component-based EJB and CORBA applications. Existing Java class libraries could be easily utilized and incorporated into database code development by using trivial PL/SQL call specifications. On the other hand, Java programs can call PL/SQL stored procedures, functions, and anonymous blocks via JDBC or SQLJ.
In particular, SQLJ provides syntax for calling...
- Stored procedures—e.g., to call the UpdateSalary procedure with the CALL operator:
#sql { CALL UpdateSalary };
- Functions—e.g., to call the GetName() function using the VALUES operator:
String name;
#sql { name = { VALUES GetName() };
or by using the SET operator:
String name;
#sql { SET :name = GetName() };
- Anonymous PL/SQL blocks:
#sql { [DECLARE ...] BEGIN ... END; };
It is important to note that the decision to use a particular language should be influenced not only by performance—although that's one of the major factors considered nowadays in application development—but also by programmer productivity, available expertise, and portability. Luckily for database developers, it's not necessary to choose one language over the other. You can easily mix and match both Java (SQLJ and JDBC) and PL/SQL stored routines in one database to get a "best of both worlds" solution.
In my next article, I explore the main components of SQLJ programming and show you how to use them with some sample code.
Boris Milrud has 10 years of experience as a software developer. He is a Senior Database Engineer with Callidus Software, Inc. located in San Jose CA. He is specializing in all aspects of Oracle database software development including database design, programming, optimization and tuning. Reach him at milrud@hotmail.com.
Do you have any interesting SQLJ experience you would like to share? Please feel free to contact Boris Milrud directly at 
