Introduction to SQLJ

Want to write less code within your Java applications that deal with SQL calls to a database? Would you like your SQL statements to be checked during compilation and not runtime? Then SQLJ just may be what you need! SQLJ gives exactly what you need for quick development, with less code, ease of debugging, and automatic performance improvement.

This article is part of the SQLJ series. This article assumes you have basic knowledge of JDBC and some of its methods, as well as some SQL. If you want to refresh your JDBC knowledge, have a look this article—JDBC Basics.

What is SQLJ?

This article will give you all the necessary information on how to start coding SQLJ and get you started coding the Data Tier much quicker then ever. SQLJ is essentially embedded SQL (Structured Query Language, sometimes referred to as Sequel) placed into Java applications. SQLJ has been developed to facilitate the development of database-oriented projects, cutting down the development/debugging time significantly. When developing Java applications using JDBC, you are still left to test your SQL against the database.

Code trouble may come from syntax and other semantics that may not be correct, or simply column names may be misspelled, causing the cycle test/debug/code to repeat. Debugging is particular easy with SQLJ because you see the actually generated source and can quickly locate any bug.

By using SQLJ, your code becomes more maintainable and flexible. Sometimes, specifications for your application may change and that should not be unexpected. You often create complex SQL statements combining unions, joins, and multiple where clauses with dynamic values. However, if you use SQLJ, you will be pleased to see that your code is still readable even if it contains an SQL jungle. For example, with SQLJ you don't need to concatenate your SQL statements; you can write as many SQL statements as you like as long as they are properly marked. If you use Oracle, you can use any dbms package, procedures, custom packages, and all those neat built-in functions right inside your Java application. If you are a PL/SQL developer, you might find this very helpful in developing an advanced software reusing your PL/SQL code and style.

Dynamic versus Static SQL

Unlike embedded static SQL, dynamic SQL may not be known until runtime. At this point, all the validation and parsing of the SQL statement sent to the database server from your application may generate an error. Optimization and analysis of the static SQL can be performed during compilation time, therefore significantly improving the performance of your application.

The Tasks that SQLJ Translator Performs

Syntactic checking of the embedded SQL constructs

Java and SQL data type checking

Schema checking

Figure 1: SQLJ workflow

Setup

At this point, you may be asking, "What do I need for this to work?" You can use pretty much any Oracle Database starting from the 8th version. You must always remember that the database that you will use must have JVM (Java Virtual Machine) built in it, IBM AS/400 (OS 360), or what they now call I-Series IBM operating system has JVM.

For this article, I've used Oracle 8i and in the code examples you note that the connection and part of the code refer to Oracle libraries. Create/Drop scripts are available in the source file. Below is the sequence and a table that will be used in this article as example:

Code Explanation:

Next, you are going to look at the important parts of the code in Listing 1.1. The class Base deals with connection context and is available with this article.

import oracle.sqlj.runtime.Oracle; // part of the translator.jar
// that deals with all SQLJ
// statements

Any method that has a SQLJ statement must always have throws SQLException; otherwise, the translator will not work. You will see why from the generated code (see Listing 1.2).

Now, move on to the data handling part. You maybe a little confused if you know PL/SQL. Why is "INTO" being used in this context? In this example, you are using a Select statement to save the values into two Host Variables by specifying INTO : OUT.

: OUT means the values that are stored in host variables
: IN means the variables read from; if you are using a method to get the value, you must enclose it with brackets like this: :( this.getName() ).

: [mode] host_variable

Mode can only be OUT, IN, or both INOUT. Remember that OUT is write and IN is read; INOUT will do both.