General Java

If you know SQL and Java, and you want to learn SQLJ, this series of articles is for you! I'm going to introduce SQLJ, the standard for embedding database SQL statements in Java programs.

You may be familiar with JDBC, an API for talking SQL with the database. SQLJ takes many ideas from JDBC further by providing a language interface for SQL statements in Java. This allows programmers to concentrate on what to say, not how to say it. Take the following SQLJ statement (here aName is a Java variable of type String, and emp is a database table with a numeric column sal and a character column ename):

#sql { UPDATE emp SET sal = sal * 1.1 WHERE ename = :aName };

If you're using JDBC, you'd have to write something along the following lines:

PreparedStatement st =
conn.prepareStatement("UPDATE emp SET sal = 3000 WHERE ename = ? ");
st.setString(1, aName);
st.executeUpdate();
st.close();

Brevity isn't the only benefit of SQLJ. An equally important feature is that you can check SQL statements against the database at translate time rather than at runtime, when a particular SQL statement may or may not get executed. Last but not least, since the SQL text of SQLJ programs is known ahead of time, it permits database vendors to use all of their compile-time performance tricks - such as compilation into the database.

You're probably thinking, however, that with all of these goodies there must be limitations. Actually, there's only one: SQLJ supports only static SQL statements. The names, shapes and types of SQL tables, views and procedures must be known and fixed ahead of time. The actual data sent to or received from the database is, of course, not fixed. In the example above the bind variable aName can take on any string value. Note that you couldn't use this variable to specify a table name, as in the following:

#sql { UPDATE :aName SET sal = sal * 1.1 }; // BAD! Not a static SQL statement.

So how can you write dynamic SQL programs if you have to? That's easy: just use JDBC. SQLJ actually provides nice interoperability features with JDBC.

Now that I've lured you into reading about SQLJ, here are the topics I'll discuss in the remainder of the article:

• The shape of SQLJ programs
• An introduction to the SQLJ translator
• Using Java host expressions in SQLJ statements
• An introduction to iterators - the strongly typed SQLJ counterpart to JDBC result sets

I'll also sprinkle in a few exercises to keep you busy until next month. Throughout the article, by the way, you'll find important tips. Even if you don't read anything else, read - and heed - these tips! You'll be glad you did and will save yourself time and frustration. Oh, and if you're wondering how to obtain and install SQLJ, check out the first sidebar.

Skeleton of an SQLJ Program
In this section I'll take a look at the essential components that every SQLJ program needs - in other words, the skeleton. One of the first lines in your SQLJ program will be:

import java.sql.SQLException;

If something goes wrong while you're running your SQLJ program, your SQLJ statements and any methods in the SQLJ runtime API throw a SQLException. Either declare that your program throws an SQLException, or put

try { ... } catch (SQLException exn) { ... }

blocks in your program.

What good is an SQL program without a database connection? (See the second sidebar for a summary of SQLJ syntax and usage.) Another important import line is the following:

import sqlj.runtime.ref.DefaultContext;

Before executing an SQLJ statement you had better connect to the database.

new oracle.jdbc.driver.OracleDriver();
DefaultContext.setDefaultContext
(new DefaultContext("jdbc:oracle:oci8:@", "scott", "tiger", false));

The first line creates an instance of your JDBC driver and - as a desired side effect - registers that driver with the JDBC DriverManager. Of course, if you don't use an Oracle JDBC driver, you'd use a different class name here. The second statement sets the SQLJ default connection. Your username - equivalent to the database schema you're connecting to - is "scott" and your password "tiger." The first argument to the DefaultContext() constructor is the JDBC URL. If you want to connect to a different database and/or through a different JDBC driver, you need to adjust the URL accordingly. Refer to your JDBC driver documentation for specifics. The last argument is the auto-commit flag. For serious database work you want to turn auto-commit off. Only at the end of the day will you decide whether to commit or roll back your work. How do you do that? With #sql {COMMIT}; or #sql {ROLLBACK}; of course!

It's also good style to close your connection context (by now it's rather obvious that's what we call connections in SQLJ) rather than leave the cleanup to your JVM's finalization. You can accomplish this with:

DefaultContext.getDefaultContext().close();

Are you still with me? Now pull all the pieces together into a file HiScotty.sqlj (see Listing 1).

Tip: Every SQLJ statement must be terminated with a semicolon ";"

Excercises

• What happens if you omit the semicolon ";" at the end of the SQLJ statement (after the closing curly brace)?
• What happens if you put a semicolon ";" at the end of the SQL statement (just before the closing curly brace)?

Meet Your Translator
Now you've got a file with a bunch of text in it - nothing to write home about. How do you bring this to life? One small step for you, one big leap for the translator:

sqlj HiScotty.sqlj

Yes, this translates your SQLJ source to a Java source and compiles it in the same fell swoop. This should - if everything goes okay - create some *.class files (and a *.ser file), and you can then issue:

java HiScotty

Even though you're familiar with .class files - the result of Java compilation - you'll be curious about these .ser files that the SQLJ translator produces. We also call them (serialized) profiles. They're serialized Java objects that contain all the information about the static SQL statements in your .sqlj source files, such as the SQL source text, the types and names of the host variables that occur in the SQL statement and what kind of SQL statement this is (a commit/rollback, a query, a DML statement and so on).

Without a database the SQLJ translator can perform only offline checking of your SQL code. If you want to get your database involved, that is, if you want SQLJ to perform online checking, then you must tell the translator how to connect to it (see Figure 1).

Specifically, you must supply a username (corresponding to the database schema you want to connect to) and a password:

sqlj -user=scott/tiger HiScotty.sqlj

Of course, you also want to be able to say which database you'd like to talk to and how - that is, using which driver and protocol. Because SQLJ uses JDBC underneath, this is accomplished by - yes, you guessed it! - a JDBC URL. Depending on the version of your SQLJ translator, this may already be set up (Oracle SQLJ, for example, uses "jdbc:oracle:oci8:@"), or you can provide it on the SQLJ command line with the -url= option. For example, you can use Oracle's thin (Type IV) JDBC driver as follows:

sqlj -user=scott/tiger

-url=jdbc:oracle:thin:@my_host:1521:my_oracle_sid HiScotty.sqlj

The -user and -url flags are two of the 46 or so option flags that SQLJ accepts. Issue sqlj -help to get an introduction to the most important ones.

Tip: The translator will try to check your SQLJ programs against the database if, and only if, you specify the -user option.

Excercises

• The translator will try to check your SQLJ programs against the database if, and only if, you specify the -user option.
• When run offline, SQLJ checks the legality of Java types used in SQL statements, and some (rather superficial) syntax. Show some errors caught by the translator offline.
• When run online, SQLJ additionally checks the shape of SELECTs and the type compatibility between SQL and Java, and - if you use the Oracle SQLJ translator - it asks the database to parse SQL DML statements. Show some errors caught by the translator online, but not offline.
• Show some errors caught only at runtime, but not at translation time.
  Exercise: In the examples above, the password was given on the command line. Usually, you want to avoid doing this:
  1. What happens if you omit the password in the -user option?
  2. You can use the sqlj.properties file for storing command line options used for sqlj invocation. Investigate the format of this file, and store the password information in it. What happens if an option is given in both the command line and the sqlj.properties file?

Cooler Than Host Variables: Host Expressions
As we already saw, host variables are Java variables prefixed with ":", placed inside the SQL statement, that can retrieve and/or send data values:

String aName = "SCOTT";
Double raise = new Double(1.08);
Double salary;

#sql { UPDATE emp SET sal = sal * :raise WHERE ename = :aName };
#sql { SELECT sal INTO :salary FROM emp WHERE ename = :aName };

But SQLJ is more flexible than that - you can use Java expressions instead of host variables. Just make sure that the host expression is enclosed between ":(" and ")" (see Listing 2).

Tip: All host expressions are evaluated once, and only once, from left to right (including side effects) before any values are sent to the database.

• A host expression in an INTO list must be able to receive a data value. Write a host expression that is not legal in an INTO list. What happens when you compile and run your program?
• Show that you can use SQL comments in SQL text (between { and }), and that you can use Java comments inside Java host expressions in SQLJ statements.
• The examples above use a "SELECT INTO" statement. What happens if no row meets the selection criteria? What if more than one row is selected? How would you have to write JDBC code to implement similar functionality?

Look, Ma! Result Sets Are Typed...and Are Called "Iterators"
When you execute a query in JDBC, it will return a java.sql.ResultSet. You then retrieve the rows in the result set through a processing loop. The next() method on the ResultSet returns true if another row is available. In this case, the row is retrieved and the individual columns can be accessed through getXxxx(column_number) calls, where Xxxx represents the Java type with which you want to retrieve the column, such as String, Int (for int), Double (for double) and so on.

SQLJ does not have the "amorphous" result sets of JDBC. SQLJ query results are always strongly typed - each column in the result has a particular Java type. To differentiate these "typed result sets" from the JDBC notion of ResultSet and from the SQL notion of cursor, we call them iterators. First, you declare an iterator type by specifying both the column types in Java and the column names. The names also serve as names of accessor functions, with which the column value is retrieved.

How do you get your iterator with all of these names and types? You declare it, of course!

#sql iterator Iter (String ename, Double sal);

This line creates a Java class declaration for the Iter class - right where you wrote it. This class has next() and close() methods - just like the java.sql.ResultSet. Instead of the getXxxx(column_name) accessors, however, your Iter class sports two fully customized, tailor-made, individualized accessor methods known as String ename() and Double sal(). A minor detail: you'll have the most success with this declaration if you put it where Java class declarations are permitted.

Let's declare ourselves an Iter.

Iter n;

And - better yet - populate it with the result from a query:

#sql n = { SELECT ename, sal FROM emp };

How do you use this iterator? Whaddayaknow, I told you all about these methods you find in Iter.

while (n.next()) {
System.out.println(n.ename()+" would like to make "+ (n.sal()*2));
}
n.close();

Questions, questions! You should now have a gazillion questions about iterators, such as:

• Where do you declare an iterator type? (Answer: Wherever you declare a Java class, and with the same Java class modifiers.)
• Does the order in the SELECT list matter? (Answer: No.)
• How do you match SQL and Java names? What about case-sensitive and case-insensitive names? (Answer: Names always match in a case-insensitive way.)
• Can you say "SELECT * FROM EMP"? (Answer: Yes.)
• What happens when you use computed columns? (Answer: You need to employ SQL aliasing to get a name match on the column.)

You're encouraged to tackle some of the exercises below to confirm the answers given here.

Tip: If you want to declare an iterator locally (as an inner class), I recommend that you declare it as follows.

#sql public static iterator IteratorName( ... );

You must always close() your iterators once you are done using them, or you will run out of cursors with which to connect to the database.

Exercises

• Does the order in the SELECT list matter? Write a SQLJ program using the example above, and run it. Now reverse the order of the columns. Which behavior do you expect? Run the modified program and test your hypothesis.

• Can you use the query SELECT * FROM EMP? Change the example to use this form of SELECT. What behavior do you expect? Run the program and verify your guess.
• Is it a good idea to use a wildcard in SELECT statements in a SQLJ program? If yes, why? If no, why not?
  1. How do you match SQL and Java names? Show that the case doesn't matter by changing the case of the column names in the iterator declaration. 2. How can you populate an Iter variable from a query with computed columns, such as SELECT 'BILL', 5000.0 FROM emp? Show that the case doesn't matter by changing the case of the column names in the SELECT statement. Also show that this is the case with case-sensitive column names.
  3. Which restrictions do you expect on column names in iterator declarations themselves? Show that SQLJ issues an error when these restrictions are violated.
  4. Which restrictions do you expect on column names in SELECT statements? When can SQLJ check these restrictions? Show that SQLJ can issue an error when these restrictions are violated.

Enough for today. Stay tuned! There's another important flavor of iterators that we need to talk about another time. So, next time, I'll consider how to call stored procedures and functions in the database and survey all of the Java types that are supported in SQLJ statements. Most of the SQLJ translator's mysteries still have to be unraveled as well. I'll also give a little history lesson about the numbering of SQLJ parts 0 through 2. In the meantime, keep your feedback, your answers to exercises and your questions rolling! I also encourage you to check out some of the SQLJ resources listed in the "SQLJ Resources" section.

SQLJ Resources

1. The SQLJ distributions (from www.sqlj.org and from (http://technet.oracle.com) contain runtime API documentation, papers, and - most important - demo programs.
2. The Oracle Technology Network site (http://technet.oracle.com) provides SQLJ (as well as JDBC) downloads, tutorials, examples, discussion groups, and the full Oracle SQLJ Developer's Guide and Reference.
3. Oracle's JDeveloper Java development environment has supported SQLJ since version 1.1. Another vendor is said to be providing integrated SQLJ support in their Java development environment soon. Keep a lookout for that.
4. Morisseau-Leroy, N. et al. (1999). Oracle 8i SQLJ Programming. Osborne/McGraw-Hill. Other SQLJ books are in the works.
5. The ANSI SQLJ standard ANSI X3.135.10-1998, SQL Part 10: Object Language Binding (SQL/OLB) can be ordered from www.ansi.org.