SQLJ

from Wikipedia, the free encyclopedia

SQLJ is an obsolete working title for efforts to merge Java and SQL. SQLJ Part 0 defines the possibility of integrating SQL statements into Java programs. Part 0 has meanwhile been adopted as Part 10 Object Language Bindings (SQL / OLB) of the ISO / IEC 9075 standard for the SQL database language . Parts 1 and 2 describe the opposite way, namely to address Java classes (routines and types) in SQL statements. Part 1 and 2 were standardized as SQL Part 13 SQL Routines and Types Using the Java Programming Language in SQL.

In the following, the term SQLJ is a synonym for SQLJ Part 0.

With SQLJ, Embedded SQL is defined for Java. In contrast, JDBC provides an API .

format

The embedded SQL statements have the following format: 

 #sql [[<context>]] { <SQL-Anweisung> };

They start with the string #sql, can span several lines, and end with a semicolon. They can contain Java variables (: x) and expressions: (y + z).

Advantages and disadvantages

SQLJ has several advantages:

  • programming is much easier compared to JDBC , since SQLJ can directly access variables in the surrounding Java code.
  • In the case of SQLJ for IBM's DB2, there is better authentication control, since user rights are not checked on the basis of tables, but on the basis of bound SQLJ profiles (packages), so that program-oriented authorization is possible.
  • The SQLJ Translator checks the syntax and semantics of SQL statements. It is thus possible to check the statements against the database schema, whereby SQL errors can be discovered and corrected at an early stage. In contrast to this, when using JDBC, errors are only detected at runtime.

Disadvantages are:

  • SQLJ programs must be transformed into Java source code using a preprocessor
  • the SQLJ syntax is not recognized by many development environments
  • SQLJ is not supported by popular persistence frameworks such as Hibernate
  • As of early 2006, the preprocessor does not yet understand any elements of the Java syntax that were introduced with versions 1.4 ( assert ) and 5.0 ( generic types , Extended For Loop, etc.).
  • The preprocessor may load classes on which the class to be translated depends. This may cause class initializers to run, which may result in U. triggers side effects or increases the translation time.

Examples and comparison with JDBC

The following examples compare SQLJ syntax with JDBC calls:

JDBC SQLJ
query 
PreparedStatement stmt = conn.prepareStatement(
   "SELECT LASTNAME"
 + " , FIRSTNAME"
 + " , 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);
  // Zeile drucken...
}
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
};
while (true) {
  #sql {
    FETCH :iter
     INTO :lastname, :firstname, :salary
  };
  if (iter.endFetch()) break;
  // Zeile drucken...
}
iter.close();
Query that returns exactly one line 
PreparedStatement stmt = conn.prepareStatement(
    "SELECT MAX(SALARY), AVG(SALARY)"
  + " FROM DSN8710.EMP");
rs = statement.executeQuery();
if (!rs.next()) {
  // Fehler -- nichts gefunden
}
maxSalary = rs.getBigDecimal(1);
avgSalary = rs.getBigDecimal(2);
if (rs.next()) {
  // Fehler -- mehr als ein Treffer
}
rs.close();
stmt.close();

#sql [ctx] {
  SELECT MAX(SALARY), AVG(SALARY)
    INTO :maxSalary, :avgSalary
    FROM DSN8710.EMP
};
INSERT 
PreparedStatement 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)
};

Web links