Oracle8i JDBC Developer's Guide and Reference Release 8.1.5 A64685-01 |
|
This section contains a side-by-side comparison of two versions of the same sample code: one version is written in JDBC and the other in SQLJ. The objective of this section is to point out the differences in coding requirements between SQLJ and JDBC.
In the sample, two methods are defined: getEmployeeAddress()
which SELECT
s into a table and returns an employee's address based on the employee's number, and updateAddress()
which takes the retrieved address, calls a stored procedure, and returns the updated address to the database.
In both versions of the sample code, these assumptions have been made:
ObjectDemo.sql
SQL script (described below) has been run to create the schema in the database and populate the tables.
UPDATE_ADDRESS
, which updates a given address, exists.
addr
) passed to the updateAddress
method can be null.
Both versions of the sample code reference objects and tables created by the ObjectDemo.sql
script.
Following is a listing of the ObjectDemo.sql
script that creates the tables and objects referenced by the two versions of the sample code. The ObjectDemo.sql
script creates a person
object, an address
object, a typed table (persons
) of person
objects, and a relational table (employees
) for employee data.
/*** Using objects in SQLJ ***/ SET ECHO ON; /** /*** Clean up ***/ DROP TABLE EMPLOYEES / DROP TABLE PERSONS / DROP TYPE PERSON FORCE / DROP TYPE ADDRESS FORCE / /*** Create an address object ***/ CREATE TYPE address AS OBJECT ( street VARCHAR(60), city VARCHAR(30), state CHAR(2), zip_code CHAR(5) ) / /*** Create a person object containing an embedded Address object ***/ CREATE TYPE person AS OBJECT ( name VARCHAR(30), ssn NUMBER, addr address ) / /*** Create a typed table for person objects ***/ CREATE TABLE persons OF person / /*** Create a relational table with two columns that are REFs to person objects, as well as a column which is an Address object.***/ CREATE TABLE employees ( empnumber INTEGER PRIMARY KEY, person_data REF person, manager REF person, office_addr address, salary NUMBER ) / /*** insert code for UPDATE_ADDRESS stored procedure here / /*** Now let's put in some sample data Insert 2 objects into the persons typed table ***/ INSERT INTO persons VALUES ( person('Wolfgang Amadeus Mozart', 123456, address('Am Berg 100', 'Salzburg', 'AU','10424'))) / INSERT INTO persons VALUES ( person('Ludwig van Beethoven', 234567, address('Rheinallee', 'Bonn', 'DE', '69234'))) / /** Put a row in the employees table **/ INSERT INTO employees (empnumber, office_addr, salary) " + " VALUES (1001, address('500 Oracle Parkway', " + " 'Redwood City', 'CA', '94065'), 50000) / /** Set the manager and person REFs for the employee **/ UPDATE employees SET manager = (SELECT REF(p) FROM persons p WHERE p.name = 'Wolfgang Amadeus Mozart') / UPDATE employees SET person_data = (SELECT REF(p) FROM persons p WHERE p.name = 'Ludwig van Beethoven') / COMMIT / QUIT
Following is the JDBC version of the sample code, which defines methods to retrieve an employee's address from the database, update the address, and return it to the database. Note, the "TO
DO
s" in the comment lines indicate where you might want to add additional code to enhance the usefulness of the code sample.
import java.sql.*; import oracle.jdbc.driver.*; /** This is what we have to do in JDBC **/ public class SimpleDemoJDBC // line 7 { //TO DO: make a main that calls this public Address getEmployeeAddress(int empno, Connection conn) throws SQLException // line 13 { Address addr; PreparedStatement pstmt = // line 16 conn.prepareStatement("SELECT office_addr FROM employees" + " WHERE empnumber = ?"); pstmt.setInt(1, empno); OracleResultSet rs = (OracleResultSet)pstmt.executeQuery(); rs.next(); // line 21 //TO DO: what if false (result set contains no data)? addr = (Address)rs.getCustomDatum(1, Address.getFactory()); //TO DO: what if additional rows? rs.close(); // line 25 pstmt.close(); return addr; // line 27 } public Address updateAddress(Address addr, Connection conn) throws SQLException // line 30 { OracleCallableStatement cstmt = (OracleCallableStatement) conn.prepareCall("{ ? = call UPDATE_ADDRESS(?) }"); //line 34 cstmt.registerOutParameter(1, Address._SQL_TYPECODE, Address._SQL_NAME); // line 36 if (addr == null) { cstmt.setNull(2, Address._SQL_TYPECODE, Address._SQL_NAME); } else { cstmt.setCustomDatum(2, addr); } cstmt.executeUpdate(); // line 43 addr = (Address)cstmt.getCustomDatum(1, Address.getFactory()); cstmt.close(); // line 45 return addr; }
}
In the getEmployeeAddress()
method definition, you must pass the connection object to the method definition explicitly.
Prepare a statement that selects an employee's address from the employees
table on the basis of the employee number. The employee number is represented by a marker variable, which is set with the setInt()
method. Note that because the prepared statement does not recognize the "INTO
" syntax used in "SQL Program to Create Tables and Objects", you must provide your own code to populate the address (addr
) variable. Since the prepared statement is returning a custom object, cast the output to an Oracle result set.
Because the Oracle result set contains a custom object of type Address
, use the getCustomDatum()
method to retrieve it (the Address
object could be created by JPublisher). The getCustomDatum()
method requires you to use the factory method Address.getFactory()
to materialize an instance of an Address
object. Since getCustomDatum()
returns a Datum
, cast the output to an Address
object.
Note that the routine assumes a one-row result set. The "TO DO
s" in the comment statements indicate that you must write additional code for the cases where the result set contains either no rows or more than one row.
Close the result set and prepared statement objects, then return the addr
variable.
In the updateAddress()
definition, you must pass the connection object and the Address
object explicitly.
The updateAddress()
method passes an address to the database for update and fetches it back. The actual updating of the address is performed by the UPDATE_ADDRESS
stored procedure (the code for this procedure is not illustrated in this example).
Prepare an Oracle callable statement that takes an address object (Address
) and passes it to the UPDATE_ADDRESS
stored procedure. To register an object as an output parameter, you must know the object's SQL type code and SQL type name.
Before passing the address object (addr
) as an input parameter, the program must determine whether addr
has a value or is null. Depending on the value of addr
, the program calls different set
methods. If addr
is null, the program calls setNull()
, if it has a value, the program calls setCustomDatum()
.
Fetch the return result addr
. Since the Oracle callable statement returns a custom object of type Address
, use the getCustomDatum()
method to retrieve it (the Address
object could be created by JPublisher). The getCustomDatum()
method requires you to use the factory method Address.getFactory
to materialize an instance of an Address
object. Because getCustomDatum()
returns a Datum
, cast the output to an Address
object.
Close the Oracle callable statement, then return the addr
variable.
Note the following coding requirements for the JDBC version of the sample code:
getEmployeeAddress()
and updateAddress()
definitions must explicitly include the connection object.
_SQL_TYPECODE
and _SQL_NAME
of the factory objects that you are registering as output parameters.
JDBC programs have the potential of being expensive in terms of maintenance. For example, in the above code sample, if you add another WHERE
clause, then you must change the SELECT
string. If you append another host variable, then you must increment the index of the other host variables by one. A simple change to one line in a JDBC program might require changes in several other areas of the program.
Following is the SQLJ version of the sample code that defines methods to retrieve an employee's address from the database, update the address, and return it to the database.
import java.sql.*; /** This is what we have to do in SQLJ **/ public class SimpleDemoSQLJ // line 6 { //TO DO: make a main that calls this? public Address getEmployeeAddress(int empno) // line 10 throws SQLException { Address addr; // line 13 #sql { SELECT office_addr INTO :addr FROM employees WHERE empnumber = :empno }; return addr; } // line 18 public Address updateAddress(Address addr) throws SQLException { #sql addr = { VALUES(UPDATE_ADDRESS(:addr)) }; // line 23 return addr; } }
The getEmployeeAddress()
method does not require a connection object. SQLJ uses a default connection context instance, which would have been defined previously somewhere in your application.
The getEmployeeAddress()
method retrieves an employee address according to employee number. Use standard SQLJ SELECT
INTO
syntax to select an employee's address from the employee table if their employee number matches the one (empno
) passed in to getEmployeeAddress()
. This requires a declaration of the Address object (addr
) that will receive the data. The empno
and addr
variables are used as input host variables. (Host variables are sometimes also referred to as bind variables.)
The getEmployeeAddress()
method returns the addr
object.
The updateAddress()
method also uses the default connection context instance.
The address is passed to the updateAddress()
method, which passes it to the database. The database updates it and passes it back. The actual updating of the address is performed by the UPDATE_ADDRESS
stored function (the code for this function is not shown here). Use standard SQLJ function-call syntax to receive the address object (addr
) output by UPDATE_ADDRESS
.
The updateAddress()
method returns the addr
object.
Note the following coding requirements for the SQLJ version of the sample code:
_SQL_TYPECODE
, _SQL_NAME
, or factories.
SELECT
...INTO
is supported and OBDC-style escapes are not used.