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