SQLj support - IBM DB2

JDBC runs all the SQL statements dynamically, which means it prepares all the statements at runtime just before the execution. Some of the statements (for example, statements without parameters) can be prepared at compile time, which increases the performance of the application at runtime. SQLj gives this flexibility of preparing the statements at compile time. An SQLj program runs all the statements statically. The SQL statements can be embedded into the Java program using SQLj. Example gives the different syntaxes of how to embed a static SQL statement in a Java program.

SQLj syntax

Connection context in SQLj is equivalent to the Connection object in JDBC and required for executing any SQL statement. Execution context is required to get the information regarding the SQL statement before and after executing the SQL statement. A default connection context is used when no connection context is specified.

The SQLj program needs to be translated and customized first before compiling and running the program. Translation and customization create the package for embedded SQL statements in the program and store it in the database. These packages are used by the application at runtime.

Getting connection context
The SQLj program needs to connect to the database before executing any SQL statement. For getting a connection context, following these steps:

  1. Declare a class for connection context. This can be done using the following statement:
  2. #sql contextcontext-class-name
    This declaration creates a class with the name context-class-name. To use this class to create a context object, declaration must be global and should not be inside the class.
  3. Load the JDBC driver. You do this the same way we have done for the JDBC program.
  4. Invoke the constructor of the context class.
  5. The Example shows how to establish the connection in the SQLj program.

SQLj connection context

A Connection context for the SQLj program can also be created using the Connection object.

SQLj Connection Context from Connection object

Manipulating data
A connection context class object can be used to execute any SQL statement. To monitor and control the SQL statements while executing, we need to create the ExecutionContext class. An ExecutionContext class object can be created by calling the method get Execution Context of connection context. Some of the ExecutionContext methods are applicable before executing the SQL statement while some of them are applicable only after execution of the statement.

Below shows how to create the ExecutionContext object and use its method getUpdateCount to retrieve the number of rows deleted by the DELETE statement.

Creating ExecutionContext object

SQLj defines iterators for selecting multiple rows using the SELECT statement. Iterators are the SQLj equivalent of ResultSet in Java and cursors in other programming languages. To iterators, the application needs to create an iterator class by defining the iterator and an object of that class. The result of the SELECT statement can be assigned to this object. SQLj provides two types of iterators:

  • Named iterators
  • Positioned iterators

Named iterators
Named iterators identify a row by the name of the column in the result set. So while defining the named iterator, you need to specify the name of the columns and their data types selected by the SELECT statement. Below shows how to use the named iterator in an SQLj program.

Using named iterators

Positioned iterators
A positioned iterator identifies a row by its position in the result set. So while defining the position iterator, you need to give only the data types of the columns.Below code shows how to use the positioned iterator in SQLj.

Using positioned iterators

Updatable and scrollable iterators
Like JDBC, by default iterators in SQLj are read-only and cannot be moved backward. To define a scrollable iterator, you need to implement the sqlj.runtime.Scrollable while defining the iterator. Similar to defining the updatable cursor, you need to implement sqlj. runtime. ForUpdate while defining the iterator. Unlike JDBC, when defining the updatable iterator, you also need to specify the columns you would like to update.Below code gives the code snippet, which uses updatable iterators.

Updatable iterator

Similarly, you can define the holdability of the iterator by adding the clause with (holdability =true) while defining the iterator.

Batch updates with SQLj
SQLj supports batch updates the same way JDBC does. But unlike JDBC, SQLj allows you to add statements of different types (a different instance of the same statement, or a statement with a host expression) in the same batch. To create a batch in SQLj, an ExecutionContext is required. The batching can be enabled by calling setBatching method of ExecutionContext and setting the value to true.

The number of statements in the batch can be limited to a value by calling the method setBatchLimit of the ExecutionContext. Once this limit is set, batch will be automatically executed when this limit is reached. Batch can be explicitly executed by calling the executeBatch method of the ExecutionContext. Apart from that, a batch is executed implicitly if the batch contains a statement, which is incompatible with other statements in the same batch. In that case, a batch will be executed and a new batch is created for incompatible statements.Below code shows how to perform batch update.

Batch updates

When an error occurs while executing any of the batch statements, the remaining statements are executed and a Batch Update Exception is thrown after all of the statements have executed.

A savepoint can be created in an SQLj program using universal driver. A savepoint in an SQLj program can be created using the SAVEPOINT statement. Below code shows how to create a savepoint in an SQLj program.

Creating a savepoint in SQLj

XQuery and SQL/XML support
An XQuery can only be run dynamically. As SQLj runs every query statically, to run the XQuery in SQLj, we need to change the XQuery statement into an SQL/XML statement. An SQL/XML statement can be run statically.

SQL/XML equivalent of XQuery statement

select xmlquery('$d/movie[movie-details/country="US"]/heading/title' passing movies.info as "d") from movies

Below code shows the SQLj code to run the query

Running XQuery in SQLj

For retrieving XML values, an iterator can be defined either with Object data type argument or a new class, com. ibm. db2. jcc. DB2Xml object.

Exception handling
Exception handling can be done in the same way we handle exceptions in a JDBC program.

JDBC and SQLj can be used together in a single application. A connection object in JDBC is similar to the ConnectionContext in SQLj. A connection object can be retrieved from a ConnectionContext object and vice versa. A connection object from ConnectionContext can be retrieved using the method getConnection of ConnectionContext object. In the same way, an iterator in SQLj and the JDBC ResultSet can be retrieved from each other. To get an iterator from the ResultSet object, use the following command:

Before doing the cast, make sure that iterator definition is the same as the result set definition. For the named iterator, column name and the data types of the columns defined in the iterator should be the same as those of the result set. For a positioned iterator, the number of columns and data type should match those of the result set. Apart from that, properties such as scrollability, updatability, and holdability should match the definition of the iterator. For details about setting these properties for SQLj iterator, refer to “Updatable and scrollable iterators” .

A ResultSet object can be retrieved from the iterator by calling the getResultSet method of the iterator class.Below code shows how to get an iterator from the ResultSet object.

Creating an iterator from the ResultSet object

Similarly, an ExecutionContext in SQLj is equivalent to the Statement object in JDBC. In SQLj, ExecutionContext is used to get the information regarding the Query statement before and after execution; in a Java Statement, the object is used to do the same. Some of the equivalent functions are shown in below

Comparison between Statement and ExecutionContext

Comparison between Statement and ExecutionContext

Below it gives you a comparison between ResultSet object in JDBC and iterator in SQLJ.

Comparison between ResultSet and iterator

Comparison between ResultSet and iterator

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

IBM DB2 Topics