Chapter 2. DB2 connectivity options 45
2.15 SQLJ
Figure 2-17 SQLJ processing
Structured Query Language for Java (SQLJ) complements and extends JDBC.
See Figure 2-17. It enables quicker development cycles through a more concise
syntax and safer code through much more extensive preexecution checking. In
order to better understand how SQLJ complements JDBC, we briefly explain the
difference between them.
DB2 provides a JDBC driver to support the basic functions of Java applications
wanting to process relational data: establish a connection to a database, execute
SQL requests, and process the results. The JDBC API consists of a set of
classes and interfaces written in Java that provide a standard API for Java
application developers to implement database applications. This API provides for
portability across platforms and database systems. The value of the JDBC API is
that an application can run on any platform with a Java Virtual Machine (JVM).
The JDBC API only supports dynamic SQL, which means that its performance
can benefit greatly from dynamic statement caching in the database engine.
JDBC is defined as part of the J2EE platform or programming model.
The JDBC driver for DB2 also supports SQLJ. SQLJ, in contrast to JDBC, lets
you write embedded SQL when developing Java applications. SQLJ was initially
developed by a consortium of database vendors and has received wide
database vendor acceptance: IBM, Oracle, Sybase, Informix®, and Compaq.
Most importantly, the DB2 implementation of SQLJ provides support for true
static SQL execution, which plays to the strengths of DB2 in terms of
performance and security.
JAVA code
JAVA cod e
yyyyy yyyyy yyyyyyyy yyyyyyyyy
yyy yy yyyyyyy yyy yy yyyyyyy y
yyyyyyyyyyyyyy yyyyyyyyyyyyyy
yyyyy yyyyy yyyyyyyy yyyyyyyyy
yyy yy yyyyyyy yyy yy yyyyyyy y
yyyyyyyyyyyyyy yyyyyyyyyyyyyy
yyyyy yyyyy yyyyyyyy yyyyyyyyy
yyy yy yyyyyyy yyy yy yyyyyyy y
yyyyyyyyyyyyyy yyyyyyyyyyyyyy
010001001010101010100101
010101010100010010100001
010001001010101010100101
010101010100010010100001
010001001010101010100101
010101010100010010100001
010001001010101010100101
010101010100010010100001
010001001010101010100101
010101010100010010100001
010101010100010010100001
010001001010101010100101
010101010100010010100001
010001001010101010100101
SQLJ code
SQLJ code
xxxxxx xx xxxxx xxxx xxx xxx
xxxxxx xxxxx xxxxx xxxxxx xxx
xxxx xx xxxx xx xxxx x
xxxxxx xxxxx xxxxx x xxxxx xxx x
xxx xxx
xxxxxx xxxxx xxxxx xxxxxx xxx
xxxx xx xxxx xx xxxx x
xxxxxx xxxxx xxxxx x xxxxx xxx x
xxx xxx
xxxxxx xxxxx xxxxx xxxxxx xxx
xxxx xx xxxx xx xxxx x xxxxxxx xx
SQLJ source code
Java source code
Java executable (.class)
SQLJ
traslation
Java
co m pilation
Java source code
with
embedded SQLJ
calls
Java source
code
with JDBC calls
Java byte
code
with JD B C
calls
46 WebSphere for z/OS Connectivity Architectural Choices
As does JDBC, SQLJ also provides for application portability across platforms
and database systems, and SQLJ has been accepted as a standard by both
ANSI and ISO. Currently, SQLJ is not part of the J2EE platform. IBM advocates
for SQLJ to be included in the J2EE platform and the JDK. SQLJ parallels the
JDBC model and covers all the basic functionality.
What are the advantages and disadvantages of using SQLJ vs. JDBC? There
are strong compelling reasons for using SQLJ when developing enterprise class
applications. For example, SQLJ is more concise and is less complex to code
relative to JDBC. In Example 2-1 and Example 2-2, you can see the syntax
differences:
Example 2-1 JDBC syntax
java.sql.PreparedStatement ps =
con.prepareStatement("SELECT ADDRESS FROM EMP
WHERE NAME=?");
ps.setString(1, name);
java.sql.ResultSet rs = ps.executeQuery();
rs.next();
addr = rs.getString(1);
rs.close();
Example 2-2 SQLJ syntax
#sql [con] { SELECT ADDRESS INTO :addr
FROM EMP
WHERE NAME=:name };
Another advantage is that all the SQL checking in terms of syntax and type
mapping can be performed during program preparation instead of at run time. If
DB2 has a customized version of the SQLJ profile, then SQLJ provides for static
SQL execution, delivering better performance through reduced CPU resource
consumption. This is because with static execution you avoid the full cost of
prepare and run time checking.

Get WebSphere for z/OS Connectivity Architectural Choices now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.