SQLJ

SQLJ is a working title for efforts to combine Java and SQL. It was a common effort started around 1997 by engineers from IBM, Oracle, Compaq, Informix, Sybase, Cloudscape and Sun Microsystems.

It consists of the three parts: 0, 1 and 2. Part 0 describes the embedding of SQL statements into Java programs. SQLJ part 0 is the basis for part 10 of the SQL:1999 standard, a.k.a. SQL Object Language Bindings (SQL/OLB).^[1] SQLJ parts 1 and 2 describes the converse possibility to use Java classes (routines and types) from SQL statements. Parts 1 and 2 are the basis for part 13 of the SQL standard, SQL Routines and Types Using the Java Programming Language (SQL/JRT).

"SQLJ" is commonly used to refer to just SQLJ part 0, usually when it is contrasted with other means of embedding SQL in Java, like JDBC.

ANSI and ISO standards

SQLJ part 0: ANSI X3.135.10-1998, "Database Language SQL—Part 10: Object Language Bindings (SQL/OLB)"
SQLJ part 1: ANSI NCITS 331.1-1999, "SQLJ—Part 1: SQL Routines Using the Java Programming Language"
SQLJ part 2: ANSI NCITS 331.2-2000, "SQLJ—Part 2: SQL Types Using the Java Programming Language"

Part 0 was updated for JDBC 2.0 compatibility and ratified by ISO in 2000. The last two parts were combined when submitted to ISO. Part 2 was substantially rewritten for the ISO submission because the ANSI version was not formal enough for a specification, being closer to the style of a user manual. The combined version was ratified in 2002.^[1]

ISO/IEC 9075-10:2000, Information technology—Database languages—SQL—Part 10: Object Language Bindings (SQL/OLB)
ISO/IEC 9075-13:2002, Information technology—Database languages—SQL—Part 13: SQL Routines and Types Using the Java Programming Language (SQL/JRT).

SQLJ part 0

The SQLJ part 0 specification largely originated from Oracle, who also provided the first reference implementation.^[1]

In the following SQLJ is a synonym for SQLJ part 0.

Whereas JDBC provides an API, SQLJ consists of a language extension. Thus programs containing SQLJ must be run through a preprocessor (the SQLJ translator) before they can be compiled.

Advantages

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 access plan stability. IBM DB2 supports static SQL use in SQLJ programs.

Disadvantages

SQLJ requires a preprocessing step.
Many IDEs do not have SQLJ support.
SQLJ lacks support for most of the common persistence frameworks, such as Hibernate.
Oracle 18c (12.2) has desupported SQLJ in the 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();

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 };

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) };

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)
};

References

1 2 3 Jim Melton (2003). Advanced SQL: 1999. Morgan Kaufmann. pp. 352-364. ISBN 978-1-55860-677-7.

ANSI and ISO standards

SQLJ part 0

Advantages

Disadvantages

Examples

See also

References

Further reading

External links