Oracle8i SQLJ Developer's Guide and Reference Release 8.1.5 A64684-01 |
|
The best way to summarize the SQLJ executable statement features and functionality discussed to this point is by examining short but complete programs. This section presents two such examples.
The first example, presented one step at a time and then again in its entirety, uses a SELECT INTO
statement to perform a single-row query of two columns from a table of employees. If you want to run the example, make sure to change the parameters in the connect.properties
file to settings that will let you connect to an appropriate database.
The second example, slightly more complicated, will make use of a SQLJ iterator for a multi-row query.
Import any JDBC or SQLJ packages you will need.
You will need at least some of the classes in the java.sql
package:
import java.sql.*;
You may not need all of the java.sql
package, however. Key classes there are java.sql.SQLException
and any classes that you refer to explicitly (for example, java.sql.Date
, java.sql.ResultSet
).
You will need the following package for the Oracle
class, which you typically use to instantiate DefaultContext
objects and establish your default connection:
import oracle.sqlj.runtime.*;
If you will be using any SQLJ runtime classes directly in your code, import the following packages:
import sqlj.runtime.*; import sqlj.runtime.ref.*;
If your code does not use any SQLJ runtime classes directly, however, it will be sufficient to have them in your CLASSPATH
as described in "Set the PATH and CLASSPATH".
(Key runtime classes include AsciiStream
, BinaryStream
, and ResultSetIterator
in the sqlj.runtime
package, and DefaultContext
in the sqlj.runtime.ref
package.)
Declare the SimpleExample
class with a constructor that uses the static Oracle.connect()
method to set the default connection. This also registers the Oracle JDBC drivers. If you are using a non-Oracle JDBC driver, you must add code to register it (as mentioned in the code comments below).
This uses a signature of connect()
that takes the URL, username, and password from the file connect.properties
. An example of this file is in the directory [Oracle Home]/sqlj/demo
and also in "Set Up the Runtime Connection".
public class SimpleExample { public SimpleExample() throws SQLException { /* If you are using a non-Oracle JDBC driver, add a call here to DriverManager.registerDriver() to register your driver. */ // Set default connection (as defined in connect.properties). Oracle.connect(getClass(), "connect.properties"); }
(The main()
method is defined below.)
Create a main()
that calls the SimpleExample
constructor and then sets up a try/catch
block to handle any SQL exceptions thrown by the runExample()
method (which performs the real work of this application).
public static void main (String [] args) { try { SimpleExample o1 = new SimpleExample(); o1.runExample(); } catch (SQLException ex) { System.err.println("Error running the example: " + ex); } }
(The runExample()
method is defined below.)
Create a runExample()
method that performs the following:
main()
method for processing.
SELECT
statement and selects the data into the host variables.
void runExample() throws SQLException { System.out.println( "Running the example--" ); // Declare two Java host variables-- Float salary; String ename; // Use SELECT INTO statement to execute query and retrieve values. #sql { SELECT Ename, Sal INTO :ename, :salary FROM Emp WHERE Empno = 7499 }; // Print the results-- System.out.println("Name is " + ename + ", and Salary is " + salary); } } // Closing brace of SimpleExample class
This declares salary
and ename
as Java host variables. The SQLJ clause then selects data from the Ename
and Sal
columns of the Emp
table and places the data into the host variables. Finally, the values of salary
and ename
are printed out.
Note that this SELECT
statement could select only one row of the Emp
table because the Empno
column in the WHERE
clause is the primary key of the table.
This section presents the entire SimpleExample
class from the previous step-by-step sections. Because this is a single-row query, no iterator is required.
// Import SQLJ classes: import sqlj.runtime.*; import sqlj.runtime.ref.*; import oracle.sqlj.runtime.*; // Import standard java.sql package: import java.sql.*; public class SimpleExample { public SimpleExample() throws SQLException { /* If you are using a non-Oracle JDBC driver, add a call here to DriverManager.registerDriver() to register your driver. */ // Set default connection (as defined in connect.properties). Oracle.connect(getClass(), "connect.properties"); } public static void main (String [] args) throws SQLException { try { SimpleExample o1 = new SimpleExample(); o1.runExample(); } catch (SQLException ex) { System.err.println("Error running the example: " + ex); } } void runExample() throws SQLException { System.out.println( "Running the example--" ); // Declare two Java host variables-- Float salary; String ename; // Use SELECT INTO statement to execute query and retrieve values. #sql { SELECT Ename, Sal INTO :ename, :salary FROM Emp WHERE Empno = 7499 }; // Print the results-- System.out.println("Name is " + ename + ", and Salary is " + salary); } }
The next example will build on the previous example by adding a named iterator and using it for a multiple-row query.
First, declare the iterator class. Use object types Integer
and Float
instead of primitive types int
and float
wherever there is the possibility of null values.
#sql iterator EmpRecs( int empno, // This column cannot be null, so int is OK. // (If null is possible, Integer is required.) String ename, String job, Integer mgr, Date hiredate, Float sal, Float comm, int deptno);
Later, when needed, instantiate the EmpRecs
class and populate it with query results.
EmpRecs employees; #sql employees = { SELECT Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno FROM Emp };
Then use the next()
method of the iterator to print the results.
while (employees.next()) { System.out.println( "Name: " + employees.ename() ); System.out.println( "EMPNO: " + employees.empno() ); System.out.println( "Job: " + employees.job() ); System.out.println( "Manager: " + employees.mgr() ); System.out.println( "Date hired: " + employees.hiredate() ); System.out.println( "Salary: " + employees.sal() ); System.out.println( "Commission: " + employees.comm() ); System.out.println( "Department: " + employees.deptno() ); System.out.println(); }
Finally, close the iterator when you are done.
employees.close();
This example uses a named iterator for a multiple-row query that selects several columns of data from a table of employees.
Aside from use of the named iterator, this example is conceptually similar to the previous single-row query example.
// Import SQLJ classes: import sqlj.runtime.*; import sqlj.runtime.ref.*; import oracle.sqlj.runtime.*; // Import standard java.sql package: import java.sql.*; // Declare a SQLJ iterator. // Use object types (Integer, Float) for Mgr, Sal, And Comm rather // than primitive types to allow for possible null selection. #sql iterator EmpRecs( int empno, // This column cannot be null, so int is OK. // (If null is possible, Integer is required.) String ename, String job, Integer mgr, Date hiredate, Float sal, Float comm, int deptno); // This is the application class. public class EmpDemo1App { public EmpDemo1App() throws SQLException { /* If you are using a non-Oracle JDBC driver, add a call here to DriverManager.registerDriver() to register your driver. */ // Set default connection (as defined in connect.properties). Oracle.connect(getClass(), "connect.properties"); } public static void main(String[] args) { try { EmpDemo1App app = new EmpDemo1App(); app.runExample(); } catch( SQLException exception ) { System.err.println( "Error running the example: " + exception ); } } void runExample() throws SQLException { System.out.println("\nRunning the example.\n" ); // The query creates a new instance of the iterator and stores it in // the variable 'employees' of type 'EmpRecs'. SQLJ translator has // automatically declared the iterator so that it has methods for // accessing the rows and columns of the result set. EmpRecs employees; #sql employees = { SELECT Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno FROM Emp }; // Print the result using the iterator. // Note how the next row is accessed using method 'next()', and how // the columns can be accessed with methods that are named after the // actual database column names. while (employees.next()) { System.out.println( "Name: " + employees.ename() ); System.out.println( "EMPNO: " + employees.empno() ); System.out.println( "Job: " + employees.job() ); System.out.println( "Manager: " + employees.mgr() ); System.out.println( "Date hired: " + employees.hiredate() ); System.out.println( "Salary: " + employees.sal() ); System.out.println( "Commission: " + employees.comm() ); System.out.println( "Department: " + employees.deptno() ); System.out.println(); } // You must close the iterator when it's no longer needed. employees.close() ; } }