SQLJ
SQLJ is an ISO standard (ISO/IEC 9075-10) for embedding SQL statements in Java programs.
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.
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 IDEs do not have SQLJ support.
- SQLJ lacks support for most of the common persistence frameworks, such as Hibernate.
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
};
do {
#sql {
FETCH :iter
INTO :lastname, :firstname, :salary
};
// Print row...
} while (!iter.endFetch());
iter.close();
|
| Single-row query | |
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 | |
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
Similar projects
- SQLG2 (some info on pure Java replacement of SQLJ)