Working with Databases in Java - Beginning Java Programming: The Object-Oriented Approach - page 322

MyEmp.sqlj SQLJ Preprocessor MyEmp.Java Java Compiler MyEmp.class

Database

figure 9-10

The preprocessor can perform syntax checks, Java/SQL type matching, and verify the query against
the database schema at design time. This will not only result in fewer errors at runtime, but will also
improve the performance of the query. Another key difference with JDBC is that SQL statements are
compiled at runtime.

Note that as illustrated in Figure 9-10, a Java program with embedded SQLJ statements should have
an *.sqlj filename extension. Both SQLJ and JDBC statements can be used in the same program.
This allows you to combine the benefits of static, pre-compiled SQL offered by SQLJ with the run-
time flexibility offered by JDBC.

The sqlj.runtime and sqlj.runtime.ref packages contain the Java runtime classes and inter-
faces used by SQLJ and should be imported at the start of the program. The JDBC java.sql pack-
age will also be imported to set up the JDBC connection and perform error handling:

import sqlj.runtime.*;
import sqlj.runtime.ref.*;
import java.sql.*;

A SQLJ program starts by loading a JDBC driver and creating a connection context (similar to a
JDBC connection object) as follows:

try {
System.out.println("Loading JDBC driver...");
Class.forName("com.mysql.jdbc.Driver");
DefaultContext.setDefaultContext(new DefaultContext(
"jdbc: mysql://localhost:3306/employeeschema"," root","mypassword123"));
} catch (Exception e) {
//do something
}

By default, all SQLJ statements will then be executed within the defined default connection context.

SQLJ statements start with an #sql delimiter and are terminated with a semicolon as follows:
#sql {<sql-statement>};

This allows the SQLJ preprocessor to easily recognize the SQL instructions. The SQLJ statement
itself can span multiple lines and may also include host variables and/or expressions. Host variables
are variables defined within the Java program and can be included in a SQLJ statement preceded by
a colon, such as the following: