1. What is SQLJ?

SQLJ is a technology that enables Java programs to access a database using embedded SQL statements. It operates at a higher level of abstraction and has a simpler, more concise syntax than JDBC.

In this tutorial, we will cover the basics of SQLJ, including its syntax, connection contexts, iterators, and executable statements.

2. SQLJ Statement Syntax

SQLJ statements begin with the #sql token. There are two types of SQLJ statements: declarations and executable statements.

Declarations are used to declare iterators and connection contexts, while executable statements are used to execute embedded SQL statements.

3. Connection Contexts

A connection context is used to establish a database connection. Instances of the connection context class are used to create database connections to a particular schema.

4. Iterators

An iterator is used to process multiple rows retrieved by a database query. There are two types of iterators: named and positional. Named iterators are defined by specifying the names and datatypes of the columns, while positional iterators are defined by specifying only the datatypes.

5. Declaration Statements

Declaration statements in SQLJ are used to declare iterators and connection contexts.

5.1 Declaring Connection Contexts

A connection context is declared using…

// TEMPLATE
#sql  context context_classname;

// EXAMPLE
#sql public context MyContext;
MyContext mctx1 = new MyContext("jdbc:ibm:greg@localhost:3001", "Greg", "eagle", false);

This declaration creates a connection context class, whose instances are used to create database connections to a particular schema.

5.2 Declaring Iterators

An iterator is declared using…

// TEMPLATE
#sql [modifiers] iterator class_name [implements_clause] [with_clause]
  (java_type column_name [, java_type column_name ...]);

// STEP 1: DECLARE NAMED ITERATOR CLASS
#sql private static iterator ProductIteratorNamedClass(
  int id,
  double price,
  String name,
  String description
);

This declaration creates an iterator class that can be used to process multiple rows retrieved by a database query.

6. Executable Statements

Executable statements are used to execute embedded SQL statements. An executable clause can have one of the following forms:

#sql [connection-context] {sql-statement};
#sql [connection-context,execution-context] {sql-statement};
#sql [execution-context] {sql-statement};

An execution context is used to control the execution of SQL statements. A connection context object maintains a JDBC connection on which SQL operations can be performed.

Executable statements can be used to perform various database operations such as SELECT, INSERT, UPDATE, and DELETE. For example:

#sql {
  INSERT INTO products (id, type_id, name, description, price)
  VALUES (:id, :type_id, :name, :description, :price)
};

6.1 Two Clause Types

SQLJ executable statements come in two forms, depending on whether the embedded SQL statement returns a value.

When an SQL statement doesn't return a value, the syntax is straightforward: #sql { SQL_statement }. The part after #sql is called the SQLJ clause, specifically a statement clause since it doesn't include a result expression.

If an SQL statement returns a result, you need to specify where to store it. SQLJ syntax allows this with: #sql result = { SQL_statement }. This is known as an assignment clause because it includes a result expression. A result can be anything from a simple variable to an iterator that takes columns of data from a multi-row SELECT.

6.2 Using Host Variables in SQLJ

SQLJ programs use host variables to share data between SQL statements and the rest of the Java code. A host variable is simply a Java variable declared in your program.

To use a host variable to store a database column value, its type must be compatible with the column type. For example, use a Java int variable to store an integer column value.

Host variables are used within host expressions, which link the variable to the SQLJ statement. The syntax is: :[mode] host_variable

The mode specifies how the SQLJ statement interacts with the host variable:

• IN: Read-only, the SQLJ statement can't change the value.
• OUT: The SQLJ statement writes a new value to the host variable.
• INOUT: The SQLJ statement can both read and write the host variable's value.

6.3 Using Iterators

To use an iterator, you need to declare the iterator class, declare an iterator object, populate the iterator object using a SELECT statement, read the rows from the iterator object, and close the iterator object.

// STEP 1: DECLARE NAMED ITERATOR CLASS (see above)

// STEP 2: DECLARE AN ITERATOR OBJECT OF THAT CLASS
ProductIteratorNamedClass product_iterator;

// STEP 3: POPULATE THE ITERATOR OBJECT
#sql [context] product_iterator = {
  SELECT
    id, name, description, price
  FROM
    products
  WHERE
    id <= 5
};

// STEP 4: READ THE ROWS FROM THE ITERATOR OBJECT
while (product_iterator.next(  )) {
  System.out.println("id = " + product_iterator.id(  ));
  System.out.println("name = " + product_iterator.name(  ));
  System.out.println("description = " + product_iterator.description(  ));
  System.out.println("price = " + product_iterator.price(  ));
}
// OR
ResultSet rs = product_iterator.getResultSet();

// STEP 5: CLOSE THE ITERATOR OBJECT
product_iterator.close();

6.4 Handling Exceptions

SQLJ executable statements must be contained in a try/catch statement. SQLJ executable statements throw exceptions of the java.sql.SQLException class.

6.5 SQLJ SELECT INTO

The SQL SELECT INTO statement is used to select a single row from a database table and store the specified column values in specified host variables.

// Declare host variables
int id = 2;
String first_name = null;
String last_name = null;
java.sql.Date dob = null;
String phone = null;

// Execute SQL query and store values in host variables
#sql [context] {
  SELECT
    first_name, last_name, dob, phone
  INTO
    :first_name, :last_name, :dob, :phone
  FROM
    customers
  WHERE
    id = :id
};

After the SELECT INTO statement is executed, the first_name, last_name, dob, and phone host variables contain the column values retrieved from the customers table.

Closing

By following this tutorial, you should now have a good understanding of the basics of SQLJ and how to use it to access a database from a Java program.

Further IBM Reading

1. Developing SQLJ applications
2. SQLJ application programming
3. SQLJ statement reference information

Contact Author

Connect with me on LinkedIn

Or email me at Gregory.Cernera@ibm.com