SQLJ

From Wikipedia, the free encyclopedia

SQLJ is an ISO standard (ISO/IEC 9075-10) for embedding SQL statements in Java programs.

Unlike JDBC, SQLJ is not an API but a language extension. Thus, SQLJ programs must be run through a preprocessor (the SQLJ translator) before they can be compiled.

Some advantages of SQLJ over JDBC:

  • SQLJ tend to be shorter than equivalent JDBC programs.
  • SQL statements syntax can be checked at compile time.

There are some disadvantages:

  • SQLJ requires a preprocessing step.
  • Many IDEs do not have SQLJ support.
  • There is no SQLJ support for most of the common persistence frameworks, such as Hibernate.

[edit] Examples

The following examples compare SQLJ syntax with JDBC usage.

JDBC SQLJ
Multi-row query 
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
};
while (true) {
  #sql {
    FETCH :iter
     INTO :lastname, :firstname, :salary
  };
  if (iter.endFetch()) break;
  // Print row...
}
iter.close();
Single-row query 
PreparedStatement stmt = conn.prepareStatement(
    "SELECT MAX(SALARY), AVG(SALARY)"
  + " FROM DSN8710.EMP");
rs = statement.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 
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)
};

[edit] External links