SQLJ is an ISO

International Organization for Standardization

The International Organization for Standardization , widely known as ISO, is an international standard-setting body composed of representatives from various national standards organizations. Founded on February 23, 1947, the organization promulgates worldwide proprietary, industrial and commercial...

 standard (ISO/IEC 9075-10) for embedding SQL

SQL

SQL is a programming language designed for managing data in relational database management systems ....

 statements in Java

Java (programming language)

Java is a programming language originally developed by James Gosling at Sun Microsystems and released in 1995 as a core component of Sun Microsystems' Java platform. The language derives much of its syntax from C and C++ but has a simpler object model and fewer low-level facilities...

 programs.

Whereas JDBC provides an API

Application programming interface

An application programming interface is a source code based specification intended to be used as an interface by software components to communicate with each other...

, SQLJ consists of a language

Programming language

A programming language is an artificial language designed to communicate instructions to a machine, particularly a computer. Programming languages can be used to create programs that control the behavior of a machine and/or to express algorithms precisely....

 extension. Thus programs containing SQLJ must be run through a preprocessor

Preprocessor

In computer science, a preprocessor is a program that processes its input data to produce output that is used as input to another program. The output is said to be a preprocessed form of the input data, which is often used by some subsequent programs like compilers...

 (the SQLJ translator) before they can be compiled.

Advantages and disadvantages

Some advantages of SQLJ over JDBC include:

• SQLJ commands tend to be shorter than equivalent JDBC programs.
• SQL syntax can be checked at compile time. The returned query results can also be checked strictly.
• Preprocessor might generate static SQL which performs better than dynamic SQL because query plan is created on program compile time, stored in database and reused at runtime. Static SQL can guarantee worst case reply time and access plan stability. IBM DB2 supports static SQL use in SQLJ programs.

Disadvantages include:

• SQLJ requires a preprocessing step.
• Many IDE
  Integrated development environment
  An integrated development environment is a software application that provides comprehensive facilities to computer programmers for software development...
  
  s do not have SQLJ support.
• SQLJ lacks support for most of the common persistence frameworks, such as Hibernate
  Hibernate (Java)
  Hibernate is an object-relational mapping library for the Java language, providing a framework for mapping an object-oriented domain model to a traditional relational database...
  
  .

Examples

The following examples compare SQLJ syntax with JDBC usage.

Multi-row query

JDBC	SQLJ
PreparedStatement stmt = conn.prepareStatement( "SELECT LASTNAME" + " , FIRSTNME" + " , SALARY" + " FROM DSN8710.EMP" + " WHERE SALARY BETWEEN ? AND ?"); stmt.setBigDecimal(1, min); stmt.setBigDecimal(2, max); ResultSet rs = stmt.executeQuery; while (rs.next) { lastname = rs.getString(1); firstname = rs.getString(2); salary = rs.getBigDecimal(3); // Print row... } rs.close; stmt.close;	sql private static iterator EmployeeIterator(String, String, BigDecimal); ... EmployeeIterator iter; sql [ctx] iter = { SELECT LASTNAME , FIRSTNME , SALARY FROM DSN8710.EMP WHERE SALARY BETWEEN :min AND :max }; do { #sql { FETCH :iter INTO :lastname, :firstname, :salary }; // Print row... } while (!iter.endFetch); iter.close;

Single-row query

JDBC	SQLJ
PreparedStatement stmt = conn.prepareStatement( "SELECT MAX(SALARY), AVG(SALARY)" + " FROM DSN8710.EMP"); rs = stmt.executeQuery; if (!rs.next) { // Error—no rows found } maxSalary = rs.getBigDecimal(1); avgSalary = rs.getBigDecimal(2); if (rs.next) { // Error—more than one row found } rs.close; stmt.close;	sql [ctx] { SELECT MAX(SALARY), AVG(SALARY) INTO :maxSalary, :avgSalary FROM DSN8710.EMP };

INSERT

JDBC	SQLJ
stmt = conn.prepareStatement( "INSERT INTO DSN8710.EMP " + "(EMPNO, FIRSTNME, MIDINIT, LASTNAME, HIREDATE, SALARY) " + "VALUES (?, ?, ?, ?, CURRENT DATE, ?)"); stmt.setString(1, empno); stmt.setString(2, firstname); stmt.setString(3, midinit); stmt.setString(4, lastname); stmt.setBigDecimal(5, salary); stmt.executeUpdate; stmt.close;	sql [ctx] { INSERT INTO DSN8710.EMP (EMPNO, FIRSTNME, MIDINIT, LASTNAME, HIREDATE, SALARY) VALUES (:empno, :firstname, :midinit, :lastname, CURRENT DATE, :salary) };

External links

• IBM Redbook: DB2 for z/OS and OS/390: Ready for Java