Using Java to execute static and dynamic SQL

DB2 UDB for z/OS supports SQLJ and JDBC. In general, Java applications use SQLJ for static SQL and JDBC for dynamic SQL.

By using the Java programming language you gain the following key advantages:

• You can write an application on any Java-enabled platform and run it on any platform to which the Java Development Kit (JDK) is ported.

• You can develop an application once and run it anywhere, which offers the following potential benefits:

  • Reduced development costs

  • Reduced maintenance costs

  • Reduced systems managements costs

  • Flexibility in supporting diverse hardware and software configurations

SQLJ support

DB2 UDB for z/OS includes SQLJ, which provides support for embedding static SQL statements in Java applications and servlets. Servlets are application programs that are written in the Java programming language and that run on a Web server.

Because SQLJ coexists with JDBC, an application program can create a JDBC connection and then use that connection to run dynamic SQL statements through JDBC and embedded static SQL statements through SQLJ.

Background

A group of companies that includes Oracle, Hewlett-Packard, and IBM initially developed SQLJ to complement the dynamic SQL JDBC model with a static SQL model. “Open standards” on page 34 has information about SQLJ standards.

SQLJ example

The SQLJ coding to update the salary of any employee is as follows:

#sql [myConnCtxt] { UPDATE EMP
                      SET SALARY = :newSalary
                      WHERE EMPNO = :empID };

Compare the SQLJ coding to a similar example using a COBOL program in “Overview of static SQL” on page 185.

Advantages of using SQLJ

By using SQLJ you gain the following advantages:

• Portable applications across platforms and database management systems.

• Strong typing, with compile and bind-time checking to ensure that applications are well designed for the database. You can read about strong typing in “Chapter 7. Implementing your database design.”

• Superior performance, manageability, and authorization checking of static SQL.

• Improved programmer productivity and easier maintenance. In comparison with a JDBC application, the resulting program is typically shorter and easier to understand.

• Familiarity for programmers who use embedded SQL in other traditional programming languages.

JDBC support

DB2 UDB for z/OS supports applications that use JavaSoft JDBC interfaces to access DB2 data by using dynamic SQL. DB2 UDB for z/OS support for JDBC enables organizations to write Java applications that access local DB2 data or remote relational data on a server that supports DRDA.

Background

Sun Microsystems developed the JDBC specifications. The JDBC specifications define a set of APIs (based on ODBC) that allow Java applications to access relational data. The APIs provide a generic interface for writing platform-independent applications that can access any SQL database. The APIs are defined within 16 classes, and they support basic SQL functions for connecting to a database, running SQL statements, and processing results. Together, these interfaces and classes represent the JDBC capabilities by which a Java application can access relational data.

JDBC example

This example shows a portion of a JDBC program for keeping an inventory of books.

/*********************************************************/
/* Determine which table to update, then build SQL      */
/* statement dynamically.                               */
/*********************************************************/
String tableName = null;
Statement stmt = con.createStatement();

ResultSet rs = stmt.executeQuery("SELECT TYPE FROM BOOK_TYPES
  WHERE TITLE = " + bkTitle);
if (rs.next())
{
  if (rs.getString(1).equalsIgnoreCase("FICTION"))
   tableName = "FICTION_BOOKS";
  else
   tableName = "NON_FICTION_BOOKS";
/*********************************************************/
/* PREPARE and EXECUTE the statement                      */
/*********************************************************/
stmt.executeUpdate("UPDATE " + tableName + " SET INVENTORY = INVENTORY-1
  WHERE TITLE = " + bkTitle);
}
rs.close();
stmt.close();

Compare the JDBC coding with a similar example that uses a C program in “Writing dynamic SQL applications” on page 194 and an ODBC program in “Using ODBC to execute dynamic SQL” on page 196.

Advantages of using JDBC

DB2 UDB for z/OS support for JDBC offers a number of advantages for accessing DB2 data:

• JDBC combines the benefit of running your applications in a z/OS environment with the portability and ease of writing Java applications.

• The JDBC interface offers the ability to change between drivers and access a variety of databases without recoding your Java program.

• JDBC applications do not require precompiles or binds.

• JDBC provides a consistent interface for applications to query and retrieve system catalog information across the DB2 UDB family of database management systems. This capability reduces the need to write catalog queries that are specific to each database server.

Table 6.1 shows some of the major differences between SQLJ and JDBC.