Wikipedia

SQLJ: Difference between revisions

Browse history interactively
← Previous edit
Content deleted Content added
VisualWikitext
inform about static SQL
m Removing link(s) Wikipedia:Articles for deletion/IBM Developer closed as soft delete (XFDcloser)
 
(51 intermediate revisions by 28 users not shown)
Line 1:
{{short description|Combination of SQL and Java within programs}}
'''SQLJ''' is an [[International Organization for Standardization|ISO]] standard (ISO/IEC 9075-10) for embedding [[SQL]] statements in [[Java (programming language)|Java]] programs.
'''SQLJ''' is a working title for efforts to combine [[Java (programming language)|Java]] and [[SQL]]. It was a common effort started around 1997 by engineers from [[IBM]], [[Oracle Corporation|Oracle]], [[Compaq]], [[Informix Corporation|Informix]], [[Sybase]], [[Apache Derby|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, aka [[SQL/OLB|SQL Object Language Bindings]] (SQL/OLB).<ref name="Melton2003">{{cite book|author=Jim Melton|title=Advanced SQL: 1999|url=https://archive.org/details/advancedsqlunder00melt_845|url-access=limited|year=2003|publisher=Morgan Kaufmann|isbn=978-1-55860-677-7|pages=[https://archive.org/details/advancedsqlunder00melt_845/page/n357 352]-364}}</ref> 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/JRT|SQL Routines and Types Using the Java Programming Language]] (SQL/JRT).
Whereas [[JDBC]] provides an [[Application programming interface|API]], SQLJ consists of a [[programming language | language]] extension. Thus programs containing SQLJ must be run through a [[preprocessor]] (the SQLJ translator) before they can be compiled.
 
"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.<ref name="Melton2003"/>
 
* 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.<ref name="Melton2003"/>
 
In the following SQLJ is a synonym for SQLJ part 0.
 
Whereas [[JDBC]] provides an [[Application programming interface|API]], SQLJ consists of a [[programming language|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 guarentee worst case reply time andguarantee access plan stability. IBM DB2 supports static SQL use in SQLJ programs.
 
=== Disadvantages ===
 
Disadvantages include:
* SQLJ requires a preprocessing step.
* Many [[Integrated Development Environment|IDE]]s do not have SQLJ support.
* SQLJ lacks support for most of the common persistence frameworks, such as [[Hibernate (Java)|Hibernate]].
* Oracle 18c (12.2) has desupported SQLJ in the database.
 
== =Examples ===
 
The following examples compare SQLJ syntax with JDBC usage.
 
{|class="wikitable" style="width:100%"
{| border="1"
|+ Multi-row query
! width="50%" | JDBC
! width="50%" | JDBC!!width=50%|SQLJ
|- style="vertical-align: top"
|-
|<syntaxhighlight lang="Java">
| colspan="2" align="center" | Multi-row query
PreparedStatement stmt = conn.prepareStatement(
|- valign="top"
"SELECT LASTNAME"
|
+ " , FIRSTNME"
PreparedStatement stmt = conn.prepareStatement(
+ " , SALARY"
"SELECT LASTNAME"
+ " ,FROM FIRSTNMEDSN8710.EMP"
+ " ,WHERE SALARY BETWEEN ? AND ?");
stmt.setBigDecimal(1, min);
+ " FROM DSN8710.EMP"
stmt.setBigDecimal(2, max);
+ " WHERE SALARY BETWEEN ? AND ?");
ResultSet rs = stmt.executeQuery();
stmt.setBigDecimal(1, min);
while (rs.next()) {
stmt.setBigDecimal(2, max);
lastname = rs.getString(1);
ResultSet rs = stmt.executeQuery();
firstname = rs.getString(2);
while (rs.next()) {
lastnamesalary = rs.getStringgetBigDecimal(13);
// Print row...
firstname = rs.getString(2);
}
salary = rs.getBigDecimal(3);
rs.close();
// Print row...
stmt.close();
}
</syntaxhighlight>
rs.close();
|<syntaxhighlight lang="sql">
stmt.close();
#sql private static iterator EmployeeIterator(String, String, BigDecimal);
|
...
#sql private static iterator EmployeeIterator(String, String, BigDecimal);
EmployeeIterator iter;
...
#sql [ctx] iter = {
EmployeeIterator iter;
SELECT LASTNAME
#sql [ctx] iter = {
, FIRSTNME
SELECT LASTNAME
, FIRSTNMESALARY
FROM , SALARYDSN8710.EMP
WHERE SALARY BETWEEN :min AND :max
FROM DSN8710.EMP
};
WHERE SALARY BETWEEN :min AND :max
do {
};
do #sql {
#sql {FETCH :iter
INTO :lastname, :firstname, :salary
FETCH :iter
};
INTO :lastname, :firstname, :salary
// Print row...
};
} while (!iter.endFetch());
// Print row...
} while (!iter.endFetchclose());
</syntaxhighlight>
iter.close();
|-
| colspan="2" align="center" | Single-row query
|- valign="top"
|
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
};
|-
| colspan="2" align="center" | INSERT
|-
|- valign="top"
|
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)
};
|}
 
{|class="wikitable" style="width:100%"
|+ Single-row query
!width=50%|JDBC!!width=50%|SQLJ
|- style="vertical-align:top"
|<syntaxhighlight lang="Java">
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();
</syntaxhighlight>
|<syntaxhighlight lang="sql">
#sql [ctx] {
SELECT MAX(SALARY), AVG(SALARY)
INTO :maxSalary, :avgSalary
FROM DSN8710.EMP
};
</syntaxhighlight>
|}
 
{|class="wikitable" style="width:100%"
|+ INSERT
!width=50%|JDBC!!width=50%|SQLJ
|- style="vertical-align:top"
|<syntaxhighlight lang="Java">
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();
</syntaxhighlight>
|<syntaxhighlight lang="sql">
#sql [ctx] {
INSERT INTO DSN8710.EMP
(EMPNO, FIRSTNME, MIDINIT, LASTNAME, HIREDATE, SALARY)
VALUES
(:empno, :firstname, :midinit, :lastname, CURRENT DATE, :salary)
};
</syntaxhighlight>
|}
 
== See also ==
* [[Embedded SQL]]
* [[Language Integrated Query]] (LINQ)
 
== References ==
{{reflist}}
 
== Further reading ==
* Connie Tsui, [http://www.ibm.com/developerworks/data/library/techarticle/0302tsui/0302tsui.html Considering SQLJ for Your DB2 V8 Java Applications], IBM developerworks, 13 Feb 2003
* Owen Cline, [http://www.ibm.com/developerworks/data/library/techarticle/dm-0412cline/ Develop your applications using SQLJ], IBM developerworks, 16 Dec 2004
* {{cite book|author=Jason Price|title=Java Programming With Oracle SQLJ|year=2001|publisher=O'Reilly Media|isbn=978-0-596-00087-5|url-access=registration|url=https://archive.org/details/javaprogrammingw00jaso}}
 
== External links ==
* [http://www.redbooks.ibm.com/abstracts/sg246435.html IBM Redbook: DB2 for z/OS and OS/390: Ready for Java]
* [https://docs.oracle.com/en/database/oracle/oracle-database/18/jsqlj/sqlj-developers-guide.pdf Oracle SQLJ Developers Guide]
 
{{DEFAULTSORT:Sqlj}}
[[Category:Java platform|Database Connectivity]]
[[Category:ApplicationDatabase programming interfacesAPIs]]
[[Category:SQL data access]]
[[Category:Databases]]
 
[[de:SQLJ]]
[[es:SQLJ]]
[[it:SQLJ]]
[[ja:SQLJ]]
[[ru:SQLJ]]
Retrieved from "https://en.wikipedia.org/wiki/SQLJ"