SQLJ: Embedding SQL Commands in Java

SQLJ: Embedding SQL Commands in Java

In the previous subsections, we gave an overview of how SQL commands can be embedded in a traditional programming language, using the C language in our examples. We now turn our attention to how SQL can be embedded in an object-oriented programming language, in particular, the Java language. SQLJ is a stan-dard that has been adopted by several vendors for embedding SQL in Java. Historically, SQLJ was developed after JDBC, which is used for accessing SQL data-bases from Java using function calls. We discuss JDBC in Section 13.3.2. In this sec-tion, we focus on SQLJ as it is used in the Oracle RDBMS. An SQLJ translator will generally convert SQL statements into Java, which can then be executed through the JDBC interface. Hence, it is necessary to install a JDBC driver when using SQLJ. In this section, we focus on how to use SQLJ concepts to write embedded SQL in a Java program.

Before being able to process SQLJ with Java in Oracle, it is necessary to import sev-eral class libraries, shown in Figure 13.5. These include the JDBC and IO classes (lines 1 and 2), plus the additional classes listed in lines 3, 4, and 5. In addition, the program must first connect to the desired database using the function call getConnection, which is one of the methods of the oracle class in line 5 of Figure

     Figure 13.5 Importing classes needed for including SQLJ in Java programs in Oracle, and establishing a connection and default context.

     1) import   java.sql.*    ;

     2) import   java.io.* ;

     3) import   sqlj.runtime.* ;

     4) import   sqlj.runtime.ref.* ;

     5) import   oracle.sqlj.runtime.* ;

        ...     

     DefaultContext cntxt =

     oracle.getConnection("<url name>", "<user name>", "<password>", true) ;

DefaultContext.setDefaultContext(cntxt) ;

13.5. The format of this function call, which returns an object of type default context, is as follows:

public static DefaultContext

getConnection(String url, String user, String password,

Boolean autoCommit)

throws SQLException ;

For example, we can write the statements in lines 6 through 8 in Figure 13.5 to con-nect to an Oracle database located at the url <url name> using the login of <user name> and <password> with automatic commitment of each command, and then set this connection as the default context for subsequent commands.

In the following examples, we will not show complete Java classes or programs since it is not our intention to teach Java. Rather, we will show program segments that illustrate the use of SQLJ. Figure 13.6 shows the Java program variables used in our examples. Program segment J1 in Figure 13.7 reads an employee’s Ssn and prints some of the employee’s information from the database.

Notice that because Java already uses the concept of exceptions for error handling, a special exception called SQLException is used to return errors or exception con-ditions after executing an SQL database command. This plays a similar role to SQLCODE and SQLSTATE in embedded SQL. Java has many types of predefined exceptions. Each Java operation (function) must specify the exceptions that can be thrown—that is, the exception conditions that may occur while executing the Java code of that operation. If a defined exception occurs, the system transfers control to the Java code specified for exception handling. In J1, exception handling for an SQLException is specified in lines 7 and 8. In Java, the following structure

try {<operation>} catch (<exception>) {<exception handling code>} <continuation code>

is used to deal with exceptions that occur during the execution of <operation>. If no exception occurs, the <continuation code> is processed directly. Exceptions

Figure 13.6 Java program vari-ables used in SQLJ examples J1 and J2

          string dname, ssn , fname, fn, lname, ln, bdate, address ;

          char sex, minit, mi ;

          double salary, sal ;

          integer dno, dnumber ;

//Program Segment J1:

          ssn = readEntry("Enter a Social Security Number: ") ;

          try {

          #sql { select Fname, Minit, Lname, Address, Salary

Figure 13.7 Program segment J1, a Java program seg-ment with SQLJ.

          into :fname, :minit, :lname, :address, :salary

          from EMPLOYEE where Ssn = :ssn} ;

          } catch (SQLException se) {

          System.out.println("Social Security Number does not exist: " + ssn) ;

          Return ;

          }

          System.out.println(fname + " " + minit + " " + lname + " " + address       " " + salary)

that can be thrown by the code in a particular operation should be specified as part of the operation declaration or interface—for example, in the following format:

<operation return type> <operation name> (<parameters>)

throws SQLException, IOException ;

In SQLJ, the embedded SQL commands within a Java program are preceded by #sql, as illustrated in J1 line 3, so that they can be identified by the preprocessor. The #sql is used instead of the keywords EXEC SQL that are used in embedded SQL with the C programming language (see Section 13.2.1). SQLJ uses an INTO clause— similar to that used in embedded SQL—to return the attribute values retrieved from the database by an SQL query into Java program variables. The program variables are preceded by colons (:) in the SQL statement, as in embedded SQL.