The paper is organized as follows. In section 1, we review the SQLJ framework for program development and its benefits. Section 2 provides a technical overview of the SQLJ language, including the use of complex Java host expressions as bind parameters, SQLJ iterators for querying data, and calls to stored procedures. We illustrate through a detailed example the steps involved in developing a SQLJ program, showing how to have the SQLJ translator check all the static SQL statements against a database schema. Next, we demonstrate the use of dynamic SQL, and show how SQLJ interoperates seamlessly with JDBC code, allowing dynamic SQL statements written in JDBC to coexist with the static ones in SQLJ. Finally, we describe the process of defining and running a SQLJ stored procedure in the Oracle8i data server. We conclude with a comparison of the SQLJ API with the JDBC interface, and summarize the benefits of SQLJ and Oracle's SQLJ strategy.
Translation of SQLJ source
files with the SQLJ translator. This 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.
SQLJ can perform Steps 1 to 3 by transparently invoking the SQLJ translator, a Java compiler, and then a profile customizer. At translation time, the translator can optionally check the SQL syntax and semantics against a user schema, and verify the type compatibility of host variables with SQL types for all static SQL statements in the program. In general, the SQLJ runtime is able to use any JDBC driver for execution, and will work even with a non-JDBC implementation. The Oracle SQLJ runtime can use either Oracle's JDBC/OCI or JDBC thin drivers, as specified by the user. Details of the JDBC/OCI and JDBC thin drivers from Oracle can be found in [4].
CREATE TYPE OWNER_SET AS TABLE OF VARCHAR(20)
OWNERS OWNER_SET ) NESTED TABLE OWNERS STORE AS OWNERS_TAB
Let us design an application that can: (1) update the duration of a project, (2) list the open projects that are yet to be completed, (3) show the time to complete all open projects, (4) calculate which projects are due this month, etc. We will implement each of these functions using a different feature of SQLJ. Below is the skeleton of the Java class ProjDemo for this application. Following Java's file naming rules, the file containing this public class must be named ProjDemo.sqlj.
public static void main(String[] args) {
dbConnect(); // logon to the database
String projName = "That Project";
updateDuration(projName, numDays); // update project duration
listOpenProjects(); // list open projects using named iterator
listOwners(projName); // list owners using positional iterator
getMaxDeadline(); // get max deadline via PL/SQL procedure
projectsDue(dueThisMonth ); // list due projects via dynamic SQL/JDBC
Date deadline = projDeadline(projName); // get deadline via SQLJ stored function
System.err.println( "Error running ProjDemo: " + e );
... Definitions of the above methods ...
We will gradually fill in the method definitions of the ProjDemo class in the following sections.
#sql iterator ProjIter (String name, int id, Date deadline); // declares Java type
#sql { CREATE UNIQUE INDEX projid_index ON projects (id) }; // executes SQL
public static void updateDuration(String projName, int numDays) throws SQLException {
#sql { UPDATE projects SET duration = duration + :numDays
SQLJ also supports the use of qualified names and complex Java host expressions, which must appear in parentheses after the colon prefix, as shown in the example below.
#sql { UPDATE PROJECTS SET duration = :(getNewDuration(id)) WHERE ID = :id };
Here, getNewDuration(id) is a Java method call that returns a numeric value denoting the updated duration of the project. At runtime, this expression is evaluated in Java, and then passed to the SQL statement as an input bind parameter.
All standard JDBC types, such as boolean, byte, short, int, String, byte[], Integer, Double, java.sql.Date etc. are valid host expression types in SQLJ. Additionally, Oracle's SQLJ translator supports the use of Oracle7 and Oracle8 types such as ROWID, CLOB, BLOB, as well as Object and REF types. Details on use of the Oracle8 types may be found in [3] and [4].
#sql iterator ProjIter (String name, int id, Date deadline);
This type declaration can be placed where a Java class definition can appear, for example, after the imports in the file ProjDemo.sqlj. During translation of this SQLJ program, the above declaration generates a Java class named ProjIter with three special column accessor methods: name(), id(), and deadline(), that return String, int, and java.sql.Date values respectively, and are used to access the fetched column values. Other methods of the ProjIter class are similar to those found on JDBC result sets, such as next() to iterate over fetched rows, and close() to release the resources held by the iterator.
After an iterator type is defined, a SQLJ program can declare instances of this iterator type, and populate it using a SQL query. During execution, the SQLJ runtime matches the names of the column accessors in the iterator with the SQL column names in a case-insensitive way. Note that column aliases must be used for those SQL columns whose names are not valid Java identifiers. We now illustrate these concepts in the method listOpenProjects() for our example:
public static void listOpenProjects() throws SQLException {
ProjIter projs = null; // Declare the iterator instance
// Populate the iterator with a SQL query
#sql projs = { SELECT start_date + duration as deadline, name, id
FROM projects WHERE start_date + duration >= sysdate
while (projs.next()) { // Loop through the result rows
// Access data via column accessors
System.out.println("Project named '" + projs.name() + "' id " +
projs.id() + " completes on " + projs.deadline());
Notice that the columns selected by the query do not match the iterator accessors in order, but have the same names. An alias named deadline denotes the completion time of a project, as computed by adding its start_date to its duration.
#sql iterator OwnerIter (String);
As for named iterators, this declaration generates a Java class named OwnerIter, but it does not have any special methods for column access; however, internally it encodes the query shape. Column data may be accessed by position only, through traditional FETCH..INTO syntax. The same benefits of strong typing as for named iterators are applicable for positional iterators also. The only difference is in the way column data is accessed, named access being more flexible and less error-prone in some cases, while FETCH..INTO may be adequate and more convenient in others. The choice is left to the SQLJ user, depending on the requirements of his/her program. Below we define the method listOwners() for our example using a positional iterator owners of type OwnerIter:
public static void listOwners(String projName) throws SQLException {
OwnerIter owners = null; // Declare the iterator instance
#sql owners = { SELECT * // Populate iterator with SQL query
FROM THE(SELECT (p.owners) FROM projects p
while (true) { // Loop though the results
#sql { FETCH :owners INTO :ownerName }; // FETCH implicitly gets next row
if (owners.endFetch()) break; // Check if no more rows
System.out.println(ownerName); // Else print data
owners.close(); // Close the iterator
Observe that the termination condition in the while loop for the FETCH statement is detected by calling the endFetch() method on the owners iterator, and this condition must always be checked before fetched data is accessed. This method is available only for positional iterators, and not for named ones. Positional and named iterators are separate Java entities, and the two paradigms cannot be mixed for the same iterator.
We can define a stored procedure named MAX_DEADLINE using PL/SQL in the Oracle database, as follows:
CREATE OR REPLACE PROCEDURE MAX_DEADLINE (deadline OUT DATE) IS
BEGIN SELECT MAX(start_date + duration) INTO deadline FROM projects;
Then, we can call this stored procedure from SQLJ, as in the method getMaxDeadline() below.
public static void getMaxDeadline() throws SQLException {
#sql { CALL MAX_DEADLINE(:OUT maxDeadline) }; // CALL syntax for stored procedures
System.out.println("Last project completes on " + maxDeadline);
OUT and INOUT parameter modes must be declared explicitly for stored procedures, functions, and anonymous blocks. The syntax for a function call is different from that of a procedure call, in that it uses the VALUES construct instead of CALL. For example, if we defined a PL/SQL function GET_MAX_DEADLINE returning a DATE instead of the stored procedure MAX_DEADLINE with an out parameter, the call to this function would appear in SQLJ as:
#sql maxDeadline = { VALUES(GET_MAX_DEADLINE()) }; // VALUES syntax for stored functions
Anonymous blocks in SQLJ statements are placed within the curly braces using BEGIN..END syntax.
public static void dbConnect() throws Exception
{ DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());// register driver
String url = "jdbc:oracle:oci8:@"; // Using JDBC/OCI driver to connect
String user = "scott"; String pwd = "tiger"; // Logon user id and password
DefaultContext.setDefaultContext( // Initialize SQLJ default context
new DefaultContext(url, user, pwd, false)); // with autoCommit off
This connection is used to execute all #sql statements that do not have a context tag, as in the ProjDemo examples above. To enable SQL checking at translation time, database connection information can also be specified on the SQLJ command-line, or more conveniently, in a SQLJ properties file. Below we show an example of SQLJ command-line invocation that uses the JDBC/OCI driver and the scott/tiger schema during translation to check static SQL operations on the default context:
sqlj -user=scott/tiger -url=jdbc:oracle:oci8:@ ProjDemo.sqlj
For flexibility, SQLJ supports many other options, such as for specification of the JDBC driver class used at translation time. Most options have common default values, e.g., the driver is preset to oracle.jdbc.driver.OracleDriver.
This declaration is expanded by the SQLJ translator to a Java class named ProjDb, of which an instance can be declared and initialized with a database connection as follows:
ProjDb myProjDb; // Declare instance of ProjDb connection context class
myProjDb = new ProjDb(url, user, pwd, true); // Initialize with autoCommit on
Subsequently, this connection context instance may be used in an embedded SQL operation as follows:
#sql [myProjDb] { UPDATE ... }; // Execute SQL on myProjDb connection context
The SQLJ translator also supports SQL checking on multiple connection contexts at translate time, through command-line options that are (optionally) tagged with the connection context class name. An example of such use is:
sqlj -user=scott/tiger -user@ProjDb=roger/lion -url=jdbc:oracle:oci8:@ ProjDemo.sqlj
Such an invocation makes the SQLJ translator use two different schemas for checking SQL operations: scott/tiger for those that use the default context, and roger/lion for the SQL executed on instances of the ProjDb connection context class.
java.sql.Connection conn = ...; // Create JDBC connection
ProjDb pdb = new ProjDB(conn); // Use to initialize SQLJ connection context
Conversely, it is also possible to extract a JDBC connection object from a SQLJ connection context instance. This feature is illustrated below, where we define the method projectsDue() using dynamic SQL via JDBC statements and connections:
public static void projectsDue(boolean dueThisMonth) throws SQLException {
// get JDBC connection from previously initialized SQLJ DefaultContext
Connection conn = DefaultContext.getDefaultContext().getConnection();
String query = "SELECT name, start_date + duration FROM projects " +
"WHERE start_date + duration >= sysdate "; // Query open projects
if (dueThisMonth) // Add condition to check month due
query += " AND to_char(start_date + duration, 'Month') " +
" = to_char(sysdate, 'Month') "; // Extract and compare month from dates
PreparedStatement pstmt = conn.prepareStatement(query);
ResultSet rs = pstmt.executeQuery();
System.out.println("Project: " + rs.getString(1) + " Deadline: " + rs.getDate(2));
Similar conversions as for database connections are also supported between JDBC result sets and SQLJ iterators:
ProjIter projs; // Declare iterator instance
#sql projs = { SELECT ... }; // Initialize it
java.sql.ResultSet rsProjs = projs.getResultSet(); // Get its JDBC result set
Likewise, we can instantiate a SQLJ iterator object using a JDBC result set. The example below shows such a conversion to the SQLJ named iterator projs. In this case, the column names in the SQL query must match the accessors in the iterator.
java.sql.ResultSet rs = ...; // Create and initialize a JDBC result set
#sql projs = {CAST :rs}; // Cast the result set to a SQLJ iterator
Notice the use of the CAST operator above, which is special syntax provided for conversion of JDBC result sets to iterators.
CREATE OR REPLACE JAVA SOURCE NAMED "ProjUtil" AS
public static Date getDeadline (String projName) {
// Note: connection is automatic for server-side execution of SQLJ programs
#sql { SELECT start_date + duration INTO :completionDate FROM projects
WHERE name = :projName }; // single-row query by key project name
} catch (SQLException e) { return null; } } }
Once this SQLJ function has been created successfully, it can be invoked from SQL statements. But first a wrapper has to be defined for mapping the Java invocation signature to SQL. This wrapper definition is based on PL/SQL syntax, e.g.,
CREATE OR REPLACE FUNCTION getDeadline(projName VARCHAR2) RETURN DATE
AS LANGUAGE JAVA NAME 'ProjUtil.getDeadline(java.lang.String) return java.sql.Date';
Then, the stored function or procedure can be invoked from both client-side and server-side SQLJ code, just like any other stored PL/SQL function or procedure. Thus, we can define the method projDeadline() using the above SQL wrapper:
public static Date projDeadline(String projName) throws SQLException {
#sql deadline = { VALUES(getDeadline(:IN projName)) };
Besides the inherent power of Java's portable development and flexible deployment model, the SQLJ application development framework provides many other important advantages. The SQLJ language and runtime are being standardized by ANSI, so that SQLJ programs written for one vendor's database can be easily adapted to another's. By design, SQLJ supports code portability not only at the source level through standard syntax, but also at the level of binaries, since it adds vendor-specific customizations to the binary profile files. Additionally, a Reference Implementation of SQLJ has been developed at Oracle using Java, and its source code is available freely to the public and to other database vendors. SQLJ is blazing a new path in its domain, in terms of its openness, robustness, flexibility, extensibility, standardization, and integration with tools. As the industry leader, Oracle has further reinforced the power of SQLJ through tightly-integrated support for it on the Oracle8i data server, allowing SQLJ procedures and functions to be stored in the database and invoked from SQL, PL/SQL, and Java. Oracle-specific datatypes and SQL extensions are fully accessible in SQLJ through binary profile customization, without compromising standards compliance. Oracle8 types such as LOBs, objects, and collections are also easily manipulated in SQLJ [3]. Oracle's JDeveloper tool provides an integrated development environment with built-in support for editing, translation, and debugging of SQLJ code. Further improvements in integration, tools, and performance are on the way.