Chapter 7. SQLJ

By now you've learned a number of SQL commands and worked with a database a bit, creating tables, entering data, and performing queries. All of this has been interactive at an SQL prompt in SQL*Plus great for learning but not very useful for a programmer. You've also learned how to program using PL/SQL which is great for discrete procedures and functions but not for building complete applications that a user can interact with. That is a task for a higher-level host language such as Java. In this and the remaining chapters, we'll learn how to write Java programs that interact with the database procedurally.

We start with SQLJ because it is the most direct approach. SQLJ allows you to imbed SQL statements directly in your Java code this is the easiest way to access a database from Java, especially for a programmer new to working with databases.

A translator (or precompiler) does the hard work for you by interpreting the SQLJ statements and converting them into standard Java and JDBC. The amount of code necessary to process result sets is much less with SQLJ than it is if we use JDBC directly.

Another advantage to SQLJ is that SQL statements are interpreted at compile time, so the precompiler can not only check their syntax but optionally, if the database is accessible, can also check to be sure they are consistent with the database schema.

The principal drawback to SQLJ is that it allows only static SQL statements. Because most of the SQL statements in an application are usually static, this is not too serious a shortcoming. It is possible to mix SQLJ and JDBC. Use SQLJ wherever possible and resort to JDBC whenever necessary. This can be somewhat messy, however, and it's best to stick to one or the other.

Another consideration that should be made is whether it is wise to introduce an additional tool (and dependency) in the development process. Depending on the size of the project and the amount of database programming required, the benefit of using SQLJ over JDBC may be outweighed by the added complexity of the environment, especially if JDBC is being used directly, as well.

In summary, SQLJ is the best and fastest way of adding simple database support to a program, especially if you do not have much experience with JDBC. If your needs are more demanding or you think you will need to use JDBC eventually, anyway, you probably should bite the bullet and go directly to JDBC from the start.