Embedding SQL statements in your Java application

Static SQL statements in SQLJ are in SQLJ clauses. SQLJ clauses begin with #sql and end with a semicolon (;) character.

Before you create any SQLJ clauses in your Java™ application, import the following packages:

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

The simplest SQLJ clauses are clauses that can be processed and consist of the token #sql followed by an SQL statement enclosed in braces. For example, the following SQLJ clause may appear wherever a Java statement may legally appear:

#sql { DELETE FROM TAB };

The previous example deletes all the rows in the table named TAB.

In an SQLJ process clause, the tokens that appear inside the braces are either SQL tokens or host variables. All host variables are distinguished by the colon (:) character. SQL tokens never occur outside the braces of an SQLJ process clause. For example, the following Java method inserts its arguments into an SQL table:


public void insertIntoTAB1 (int x, String y, float z) throws SQLException 
{
    #sql { INSERT INTO TAB1 VALUES (:x, :y, :z) };
}

The method body consists of an SQLJ process clause containing the host variables x, y, and z.

In general, SQL tokens are case insensitive (except for identifiers delimited by double quotation marks), and can be written in upper, lower, or mixed case. Java tokens, however, are case sensitive. For clarity in examples, case insensitive SQL tokens are uppercase, and Java tokens are lowercase or mixed case. Throughout this topic, the lowercase null is used to represent the Java "null" value, and the uppercase NULL is used to represent the SQL "null" value.

The following types of SQL constructs may appear in SQLJ programs:

  • Queries For example, SELECT statements and expressions.
  • SQL Data Change statements (DML) For example, INSERT, UPDATE, DELETE.
  • Data statements For example, FETCH, SELECT..INTO.
  • Transaction Control statements For example, COMMIT, ROLLBACK, etc.
  • Data Definition Language (DDL, also known as Schema Manipulation Language) statements For example, CREATE, DROP, ALTER.
  • Calls to stored procedures For example, CALL MYPROC(:x, :y, :z)
  • Invocations of stored functions For example, VALUES( MYFUN(:x) )