Java with SQL

Embedding SQL in Java with SQLJ

Oracle permits developers to write efficient and compact programs with the use of SQLJ. SQLJ and built on top of JDBC, permits application developers to embed SQL statements in Java programs and to run a preprocessor to translate SQLJ to Java code which makes JDBC calls to the database by using a JDBC driver from any vendor. The SQLJ programs can be developed and debugged by using Oracle's JDeveloper that is a standard Java development tool. Since the SQLJ runtime environment runs on top of the

JDBC driver, it can be deployed by using the similar configuration as JDBC programs-namely, both JDBC and OCI driver with middle-tier applications and the thin JDBC driver for Java applets. The biggest benefit you gain by using SQLJ is raised productivity and manageability of Java code through providing the subsequent:

•Code which is significantly compact compared to JDBC

•With the use of typed cursors strong types queries

•To  recognize  errors  at  an  earlier  stage  Compile-time  checking  of  SQL  statements  and quickly deploy and debug applications.

You can use SQLJ programs via following these generic steps.

1.    Write a SQLJ Java program
2.    Run the SQLJ program by the SQLJ translator. Translator preprocesses the SQLJ program and produce standard Java code with JDBC calls.
3.    Compile the Java code
4.    Run the Java program.

An example of JDBC Program

Java.sql.CallableStatement stmt;
Connection conn;
ResultSet res;

/* Declare the objects for a callable statement, connection, and the result set*/
Conn = DriverManager.getConnection ("jdbc:default");

/* An Initialize the connection object with the default connection*/

Stmt = conn. PrepareStatement
("SELECT ename FROM emp WHERE ecity =? AND deptno =? *);

/* An Inituakuze the connection object with the default connection */

Stmt = conn.prepareStatement
("SELECT ename FROM emp WHERE ecity =? AND deptno =?");

/* Prepare the statement to execute */

Stmt.setString (1, city_p);
Stmt.setInteger (2, deptno_p);

/* It is using positional parameters to set the variables*/

Res = stmt.executeQuery ( );

/* store the results in the result set and execute the query */

A Sample SQLJ Program

ResultSet res;
/* to store the result set define an object */

#sql res = (SELECT ename FROM emp

WHERE ecity = :city_p AND deptno = :deptno_p);

/* Pass the program by the SQLJ translator and execute the program. A result set is stored in "res" */

Oracle8I offers various features which make it an open and extensible platform for application development:

• An open protocol CORBA / IIOP
• (in addition to PL/SQL) Java as an open server programming language
• The capability to use data cartridges
• The Internet File System (IFS)