Release 8.1.5
A64684-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i SQLJ Developer's Guide and Reference Release 8.1.5 A64684-01 |
|
This chapter presents sample applications that highlight advanced JDBC features and Oracle extensions, including the following topics:
This section contains code samples that demonstrate basic JDBC features.
The JDBC drivers support the manipulation of data streams in both directions between client and server. The code sample in this section demonstrates this by using the JDBC OCI driver for connecting to a database, and inserting and fetching LONG data using Java streams.
import java.sql.*; // line 1 import java.io.*; class StreamExample { public static void main (String args []) throws SQLException, IOException { // Load the driver DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database // You can put a database name after the @ sign in the connection URL. Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger"); // It's faster when you don't commit automatically conn.setAutoCommit (false); // line 18 // Create a Statement Statement stmt = conn.createStatement (); // Create the example table try { stmt.execute ("drop table streamexample"); } catch (SQLException e) { // An exception would be raised if the table did not exist // We just ignore it } // Create the table // line 34 stmt.execute ("create table streamexample (NAME varchar2 (256), DATA long)"); File file = new File ("StreamExample.java"); // line 37 InputStream is = new FileInputStream ("StreamExample.java"); PreparedStatement pstmt = conn.prepareStatement ("insert into streamexample (name, data) values (?, ?)"); pstmt.setString (1, "StreamExample"); pstmt.setAsciiStream (2, is, (int)file.length ()); pstmt.execute (); // line 44 // Do a query to get the row with NAME 'StreamExample' ResultSet rset = stmt.executeQuery ("select DATA from streamexample where NAME='StreamExample'"); // Get the first row // line 51 if (rset.next ()) { // Get the data as a Stream from Oracle to the client InputStream gif_data = rset.getAsciiStream (1); // Open a file to store the gif data FileOutputStream os = new FileOutputStream ("example.out"); // Loop, reading from the gif stream and writing to the file int c; while ((c = gif_data.read ()) != -1) os.write (c); // Close the file os.close (); // line 66 } } }
Import the necessary classes. Load the JDBC OCI driver with the DriverManager.registerDriver() method. Connect to the database with the getConnection(), as user scott with password tiger. Use the database URL jdbc:oracle:oci8:@. You can optionally enter a database name after the @ symbol. Disable AUTOCOMMIT to enhance performance. If you do not, the driver will issue execute and commit commands after every SQL statement.
Create a table STREAMEXAMPLE with a NAME column of type VARCHAR and a DATA column of type LONG.
Insert the contents of the StreamExample.java into the table. To do this, create an input stream object for the Java file. Then, prepare a statement to insert character data into the NAME column and the stream data into the DATA column. Insert the NAME data with the setString(); insert the stream data with setAsciiStream().
Query the table to get the contents of the DATA column into a result set.
Get the data from the first row of the result set into the InputStream object gif_data. Create a FileOutputStream to write to the specified file object. Then, read the contents of the gif stream and write it to the file example.out.
This section contains sample code for the following Oracle extensions:
This sample demonstrates basic support for LOBs in the OCI 8 driver. It illustrates how to create a table containing LOB columns, and includes utility programs to read from a LOB, write to a LOB, and dump the LOB contents. For more information on LOBs, see "Working with LOBs".
Except for some changes to the comments, the following sample is similar to the LobExample.java program in the Demo/samples/oci8/object-samples directory.
import java.sql.*; // line 1 import java.io.*; import java.util.*; // Importing the Oracle Jdbc driver package // makes the code more readable import oracle.jdbc.driver.*; // Import this to get CLOB and BLOB classes import oracle.sql.*; public class NewLobExample1 { public static void main (String args []) throws Exception { // Load the Oracle JDBC driver DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database. You can put a database // name after the @ sign in the connection URL. Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger"); // It's faster when auto commit is off conn.setAutoCommit (false); // line 26 // Create a Statement Statement stmt = conn.createStatement (); try { stmt.execute ("DROP TABLE basic_lob_table"); } catch (SQLException e) { // An exception could be raised here if the table did // not exist already but we gleefully ignore it } // line 38 // Create a table containing a BLOB and a CLOB line 40 stmt.execute ("CREATE TABLE basic_lob_table (x varchar2 (30), b blob, c clob)"); // Populate the table stmt.execute ("INSERT INTO basic_lob_table VALUES ('one', '010101010101010101010101010101', 'onetwothreefour')"); stmt.execute ("INSERT INTO basic_lob_table VALUES ('two', '0202020202020202020202020202', 'twothreefourfivesix')"); // line 49 System.out.println ("Dumping lobs"); // Select the lobs ResultSet rset = stmt.executeQuery ("SELECT * FROM basic_lob_table"); while (rset.next ()) { // Get the lobs BLOB blob = ((OracleResultSet)rset).getBLOB (2); CLOB clob = ((OracleResultSet)rset).getCLOB (3); // Print the lob contents dumpBlob (conn, blob); dumpClob (conn, clob); // Change the lob contents fillClob (conn, clob, 2000); fillBlob (conn, blob, 4000); } // line 68 System.out.println ("Dumping lobs again"); rset = stmt.executeQuery ("SELECT * FROM basic_lob_table"); while (rset.next ()) { // Get the lobs BLOB blob = ((OracleResultSet)rset).getBLOB (2); CLOB clob = ((OracleResultSet)rset).getCLOB (3); // Print the lobs contents dumpBlob (conn, blob); dumpClob (conn, clob); } } // line 82 // Utility function to dump Clob contents static void dumpClob (Connection conn, CLOB clob) throws Exception { // get character stream to retrieve clob data Reader instream = clob.getCharacterStream(); // create temporary buffer for read line 91 char[] buffer = new char[10]; // length of characters read int length = 0; // fetch data line 98 while ((length = instream.read(buffer)) != -1) { System.out.print("Read " + length + " chars: "); for (int i=0; i<length; i++) System.out.print(buffer[i]); System.out.println(); } // Close input stream instream.close(); } // line 108 // Utility function to dump Blob contents static void dumpBlob (Connection conn, BLOB blob) throws Exception { // Get binary output stream to retrieve blob data InputStream instream = blob.getBinaryStream(); // Create temporary buffer for read byte[] buffer = new byte[10]; // length of bytes read line 120 int length = 0; // Fetch data while ((length = instream.read(buffer)) != -1) { System.out.print("Read " + length + " bytes: "); for (int i=0; i<length; i++) System.out.print(buffer[i] + " "); System.out.println(); } // Close input stream instream.close(); } // line 135 // Utility function to put data in a Clob static void fillClob (Connection conn, CLOB clob, long length) throws Exception { Writer outstream = clob.getCharacterOutputStream(); int i = 0; int chunk = 10; while (i < length) { outstream.write(i + "hello world", 0, chunk); // line 147 i += chunk; if (length - i < chunk) chunk = (int) length - i; } outstream.close(); } // line 154 // Utility function to write data to a Blob static void fillBlob (Connection conn, BLOB blob, long length) throws Exception { OutputStream outstream = blob.getBinaryOutputStream(); int i = 0; byte [] data = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }; // line 165 int chunk = data.length; while (i < length) { data [0] = (byte)i; outstream.write(data, 0, chunk); i += chunk; if (length - i < chunk) chunk = (int) length - i; } outstream.close(); } } // line 175
Import the necessary java.* and oracle.* classes. Register the driver with the DriverManager.registerDriver() method and connect to the database with DriverManager.getConnection(). Use the database URL jdbc:oracle:oci8:@ and connect as user scott with password tiger. You can optionally enter a database name following the @ symbol.
Use setAutoCommit(false) to disable the AUTOCOMMIT feature and enhance performance. If you do not, the driver will issue execute and commit commands after every SQL statement.
Create a statement object. Drop any pre-existing table named basic_lob_table. Then, create a new basic_lob_table directory to store the LOBs in-line.
Use SQL statements to create a table with three columns: a column to store the row number as a VARCHAR2, a BLOB column, and a CLOB column. Then insert data into two rows of the table.
SELECT the contents of the table into a result set.
Retrieve the LOBs. The getBLOB() and getCLOB() methods return locators to the LOB data; to retrieve the LOB contents, you must write additional code (which is defined later in this program). To use the getBLOB() and getCLOB() methods, cast the result set to an OracleResultSet object. Then call the "dump" functions to display the contents of the LOBs, and the "fill" functions to change the contents of the LOBs. The dump and fill functions are defined later in this program.
Display the LOBs again, after their contents have been changed. SELECT the contents of the table into a result set, and then apply the dump functions. The dump functions are defined later in this program.
Define the utility function dumpClob to display the contents of a CLOB. Read the CLOB contents as a character stream. Use the getCharacterStream() method to get a READER stream object. Set up the temporary character array to read the character data in 10-character chunks.
Set up a loop to read and display the contents of the CLOB. The length of the CLOB is displayed as well. Close the input stream when you are done.
Define the utility function dumpBlob to display the contents of a BLOB. Read the BLOB contents as a binary stream. Use the getBinaryStream() method to get an InputStream stream object. Set up the temporary byte array to read the binary data in 10-byte chunks.
Set up a loop to read and display the contents of the BLOB. The length of the BLOB is displayed as well. Close the input stream when you are done.
Define the utility function fillClob to write data to a CLOB. The fillClob function needs the CLOB locator and the length of the CLOB. To write to the CLOB, use the getCharacterOutputStream() method to get a WRITER object.
Set up a loop to write an index value and part of the string Hello World to the CLOB. Close the WRITER stream when you are done.
Define the utility function fillBlob to write data to a BLOB. The fillBlob function needs the BLOB locator and the length of the BLOB. To write to the BLOB, use the getBinaryOutputStream() method to get an OutputStream object.
Define the byte array of data that you want to write to the BLOB. The while loop causes a variation of the data to be written to the BLOB. Close the OutputStream object when you are done.
This sample demonstrates basic BFILE support in the OCI 8 driver. It illustrates filling a table with BFILEs and includes a utility for dumping the contents of a BFILE. For more information on BFILEs, see "Working with LOBs".
Except for some changes to the comments, the following sample is similar to the FileExample.java program in the Demo/samples/oci8/object-samples directory.
import java.sql.*; // line 1 import java.io.*; import java.util.*; //including this import makes the code easier to read import oracle.jdbc.driver.*; // needed for new BFILE class import oracle.sql.*; // line 10 public class NewFileExample1 { public static void main (String args []) throws Exception { // Load the Oracle JDBC driver line 16 DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database // You can put a database name after the @ sign in the connection URL. // // The example creates a DIRECTORY and you have to be connected as // "system" to be able to run the test. // If you can't connect as "system" have your system manager // create the directory for you, grant you the rights to it, and // remove the portion of this program that drops and creates the directory. Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "system", "manager"); // It's faster when auto commit is off conn.setAutoCommit (false); // line 32 // Create a Statement Statement stmt = conn.createStatement (); try // line 36 { stmt.execute ("DROP DIRECTORY TEST_DIR"); } catch (SQLException e) { // An error is raised if the directory does not exist. Just ignore it. } // line 43 stmt.execute ("CREATE DIRECTORY TEST_DIR AS '/temp/filetest'"); try // line 46 { stmt.execute ("drop table test_dir_table"); } catch (SQLException e) { // An error is raised if the table does not exist. Just ignore it. } // line 54 // Create and populate a table with files // The files file1 and file2 must exist in the directory TEST_DIR created // above as symbolic name for /private/local/filetest. stmt.execute ("CREATE TABLE test_dir_table (x varchar2 (30), b bfile)"); stmt.execute ("INSERT INTO test_dir_table VALUES ('one', bfilename ('TEST_DIR', 'file1'))"); stmt.execute ("INSERT INTO test_dir_table VALUES ('two', bfilename ('TEST_DIR', 'file2'))"); // Select the file from the table // line 64 ResultSet rset = stmt.executeQuery ("SELECT * FROM test_dir_table"); while (rset.next ()) { String x = rset.getString (1); BFILE bfile = ((OracleResultSet)rset).getBFILE (2); System.out.println (x + " " + bfile); // Dump the file contents dumpBfile (conn, bfile); } } //line 75 // Utility function to dump the contents of a Bfile line 77 static void dumpBfile (Connection conn, BFILE bfile) throws Exception { // line 80 System.out.println ("Dumping file " + bfile.getName()); System.out.println ("File exists: " + bfile.fileExists()); System.out.println ("File open: " + bfile.isFileOpen()); System.out.println ("Opening File: "); // line 84 bfile.openFile(); System.out.println ("File open: " + bfile.isFileOpen()); long length = bfile.length(); System.out.println ("File length: " + length); int chunk = 10; InputStream instream = bfile.getBinaryStream(); // Create temporary buffer for read byte[] buffer = new byte[chunk]; // Fetch data while ((length = instream.read(buffer)) != -1) { System.out.print("Read " + length + " bytes: "); for (int i=0; i<length; i++) System.out.print(buffer[i] + " "); System.out.println(); } // line 108 // Close input stream instream.close(); // close file handler bfile.closeFile(); } // line 115 }
Import the necessary java.* and oracle.* classes. Register the driver with the DriverManager.registerDriver() method and connect to the database with the getConnection() method. Use the database URL jdbc:oracle:oci8:@ and connect as user system with password manager. You can optionally enter a database name following the @ symbol.
Use setAutoCommit(false) to disable the AUTOCOMMIT feature and enhance performance. If you do not, the driver will issue execute and commit commands after every SQL statement.
Create a statement object. Drop any pre-existing directory named TEST_DIR. Then, create a new TEST_DIR directory to store the BFILE. You or your System Administrator can use whatever file name you wish.
Drop any pre-existing table named test_dir_table.
Create and populate a table with files. Use SQL statements to create a table, test_dir_table, with two columns: one column to indicate the row number as a VARCHAR2 (for example, "one" or "two"), and one column to hold the BFILE locator.
Use SQL statements to insert some data into the table. For the first row, insert a row number in the first column, and use the BFILENAME keyword to insert a BFILE, file1, located in TEST_DIR, in the second column. Do the same thing for the second row.
SELECT the contents of the table into a result set. Set up a loop to retrieve the contents of the table. Use getString() to retrieve the row number data, and use getBFILE() to retrieve the BFILE locator. Since BFILE is an Oracle-specific datatype, and getBFILE() is an Oracle extension, cast the result set object to an OracleResultSet object.
Use the dumpBfile() method (defined later in the program) to display the BFILE contents and various statistics about the BFILE.
Define the dumpBfile() method to display the BFILE contents and various statistics about the BFILE. The dumpBfile() method takes the BFILE locator as input.
Use the getName(), fileExists(), and isFileOpen() methods to return the name of the BFILE, and whether the BFILE exists and is open. Note that the BFILE does not have to be open to apply these methods to it.
Read and display the BFILE contents. First open the BFILE. You can read the BFILE contents as a binary stream. Use the getBinaryStream() method to get an input stream object. Determine the size of the "chunk" in which the stream will read the BFILE data, and set up the temporary byte array to store the data.
Set up a loop to read and display the contents of the BFILE. The length of the BFILE is displayed as well.
When you are finished, close the input stream and the BFILE.
This section contains sample code for these Oracle extensions:
Following is a complete sample program that uses JDBC to create a stored package in the data server and uses a get on the REF CURSOR type category to obtain the results of a query. For more information on REF CURSORs, see "Oracle REF CURSOR Type Category".
Except for some changes to the comments, the following sample is similar to the RefCursorExample.java program in the Demo/samples/oci8/object-samples directory.
import java.sql.*; // line 1 import java.io.*; import oracle.jdbc.driver.*; class RefCursorExample { public static void main(String args[]) throws SQLException { //Load the driver. DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database. // You can put a database name after the @ sign in the connection URL. Connection conn = DriverManager.getConnection("jdbc:oracle:oci8:@", "scott", "tiger"); // line 16 // Create the stored procedure. init(conn); // Prepare a PL/SQL call. line 20 CallableStatement call = conn.prepareCall("{ ? = call java_refcursor.job_listing (?) }"); // Find out who all the sales people are. line 24 call.registerOutParameter(1, OracleTypes.CURSOR); call.setString(2, "SALESMAN"); call.execute(); ResultSet rset = (ResultSet)call.getObject(1); // Output the information in the cursor. line 30 while (rset.next()) System.out.println(rset.getString("ENAME")); } // Utility function to create the stored procedure // line 36 static void init(Connection conn) throws SQLException { Statement stmt = conn.createStatement(); // line 40 stmt.execute("CREATE OR REPLACE PACKAGE java_refcursor AS " + " type myrctype is ref cursor return EMP%ROWTYPE; " + " function job_listing(j varchar2) return myrctype; " + "end java_refcursor;"); // line 45 stmt.execute("CREATE OR REPLACE PACKAGE BODY java_refcursor AS " + " function job_listing(j varchar2) return myrctype is " + " rc myrctype; " + " begin " + " open rc for select * from emp where job = j; " + " return rc; " + " end; " + "end java_cursor;"); // line 53 } }
Import the necessary java.* and oracle.* classes. Register the driver with the DriverManager.registerDriver() method and connect to the database with the getConnection() method. Use the database URL jdbc:oracle:oci8:@ and connect as user scott with password tiger. You can optionally enter a database name following the @ symbol.
Prepare a callable statement to the job_listing function of the java_refcursor PL/SQL procedure. The callable statement returns a cursor to the rows of information where job=SALESMAN. Register OracleTypes.CURSOR as the output parameter. The setObject() method passes the value SALESMAN to the callable statement. After the callable statement is executed, the result set contains a cursor to the rows of the table where job=SALESMAN.
Iterate through the result set and print the employee name part of the employee object.
Define the package header for the java_refcursor package. The package header defines the return types and function signatures.
Define the package body for the java_refcursor package. The package body defines the implementation which selects rows based on the value for job.
Following is a complete sample program that uses JDBC to create a table with a VARRAY. It inserts a new array object into the table, then prints the contents of the table. For more information on arrays, see "Working with Arrays".
Except for some changes to the comments, the following sample is similar to the ArrayExample.java program in the Demo/samples/oci8/object-samples directory.
import java.sql.*; // line 1 import oracle.sql.*; import oracle.jdbc.oracore.Util; import oracle.jdbc.driver.*; import java.math.BigDecimal; public class ArrayExample { public static void main (String args[]) throws Exception { // Register the Oracle JDBC driver DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database // You need to put your database name after the @ symbol in // the connection URL. // // The sample retrieves an varray of type "NUM_VARRAY" and // materializes the object as an object of type ARRAY. // A new ARRAY is then inserted into the database. // Please replace hostname, port_number and sid_name with // the appropriate values Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@(description=(address=(host=hostname)(protocol=tcp)(port=por t_number))(connect_data=(sid=sid_name)))", "scott", "tiger"); // It's faster when auto commit is off conn.setLines (false); // line 32 // Create a Statement Statement stmt = conn.createStatement (); // line 35 try { stmt.execute ("DROP TABLE varray_table"); stmt.execute ("DROP TYPE num_varray"); } catch (SQLException e) { // the above drop statements will throw exceptions // if the types and tables did not exist before } // line 47 stmt.execute ("CREATE TYPE num_varray AS VARRAY(10) OF NUMBER(12, 2)"); stmt.execute ("CREATE TABLE varray_table (col1 num_varray)"); stmt.execute ("INSERT INTO varray_table VALUES (num_varray(100, 200))"); ResultSet rs = stmt.executeQuery("SELECT * FROM varray_table"); showResultSet (rs); // line 54 //now insert a new row // create a new ARRAY object int elements[] = { 300, 400, 500, 600 }; // line 59 ArrayDescriptor desc = ArrayDescriptor.createDescriptor("NUM_VARRAY", conn); ARRAY newArray = new ARRAY(desc, conn, elements); // line 62 PreparedStatement ps = conn.prepareStatement ("INSERT INTO varray_table VALUES (?)"); ((OraclePreparedStatement)ps).setARRAY (1, newArray); ps.execute (); rs = stmt.executeQuery("SELECT * FROM varray_table"); showResultSet (rs); } // line 70 public static void showResultSet (ResultSet rs) // line 72 throws SQLException { int line = 0; while (rs.next()) { line++; System.out.println("Row " + line + " : "); ARRAY array = ((OracleResultSet)rs).getARRAY (1); System.out.println ("Array is of type " + array.getSQLTypeName()); System.out.println ("Array element is of type code " + array.getBaseType()); System.out.println ("Array is of length " + array.length()); // line 86 // get Array elements BigDecimal[] values = (BigDecimal[]) array.getArray(); for (int i=0; i<values.length; i++) { BigDecimal value = values[i]; System.out.println(">> index " + i + " = " + value.intValue()); } } } } // line 97
Import the necessary java.* and oracle.* classes. Register the driver with the DriverManager.registerDriver() method and connect to the database with the getConnection() method. This example of getConnection() uses Net8 name-value pairs to specify the host as hostname, protocol as tcp, port as 1521, sid as orcl, user as scott and password as tiger.
Use setAutoCommit(false) to disable the AUTOCOMMIT feature and enhance performance. If you do not, the driver will issue execute and commit commands after every SQL statement.
Create a Statement object and delete any previously defined tables or types named varray_table or num_varray.
Create the type num_varray as a varray containing NUMBER data. Create a 1-column table, varray_table, to contain the num_varray type data. Insert into the table two rows of data. The values 100 and 200 are both of type num_varray. Use the showResultSet() method (defined later in the program) to display information about the arrays contained in the table.
First, define an array of integer elements to insert into the varray_table. Next, create an array descriptor object that will be used to create new ARRAY objects. To create an array descriptor object, pass the SQL type name of the array type (NUM_ARRAY) and the connection object to the createDescriptor() method. Then create the new array object by passing to it the array descriptor, the connection object, and the array of integer elements.
Prepare a statement to insert the new array object into varray_table. Cast the prepared statement object to an OraclePreparedStatement object to take advantage of the setARRAY() method.
To retrieve the array contents of the table, write and execute a SQL SELECT statement. Again, use the showResultSet method (defined later in the program) to display information about the arrays contained in the table.
Define the showResultSet() method. This method loops through a result set and returns information about the arrays it contains. This method uses the result set getARRAY() method to return an array into an oracle.sql.ARRAY object. To do this, cast the result set to an OracleResultSet object. Once you have the ARRAY object, you can apply Oracle extensions getSQLTypeName(), getBaseType(), as well as length(), to return and display the SQL type name of the array, the SQL type code of the array elements, and the array length.
You can access the varray elements by using the ARRAY object's getArray() method. Since the varray contains SQL numbers, cast the result of getArray() to a java.math.BigDecimal array. Then, iterate through the value array and pull out individual elements.
This section contains the following subsections:
This section contains examples of the code you must provide to create custom Java classes for Oracle objects. You create the custom classes by implementing either the SQLData or CustomDatum interface. These interfaces provide a way to create and populate the custom Java class for the Oracle object and its attributes.
Although both SQLData and CustomDatum both populate a Java object from a SQL object, the CustomDatum interface is far more powerful. In addition to letting you populate Java objects, CustomDatum lets you materialize objects from SQL types that are not necessarily objects. Thus, you can create a CustomDatum object from any datatype found in an Oracle database. This is particularly useful in the case of RAW data that can be a serialized object.
The SQLData interface is a JDBC standard. For more information on this interface, see "Understanding the SQLData Interface".
The CustomDatum interface is provided by Oracle. You can write your own code to create custom Java classes that implement this interface, but you will find it easier to let the Oracle utility JPublisher create the custom classes for you. The custom classes created by JPublisher implement the CustomDatum interface.
For more information on the CustomDatum interface, see "Understanding the CustomDatum Interface". See the Oracle8i JPublisher User's Guide for more information on the JPublisher utility.
This section contains a code sample that illustrates how you can create a custom Java type to correspond to a given SQL type. It then demonstrates how you can use the custom Java class in the context of a sample program. The sample also contains the code to map the SQL type to the custom Java type.
Following is the SQL definition of an EMPLOYEE object. The object has two attributes: a string EmpName (employee name) attribute and an integer EmpNo (employee number) attribute.
-- SQL definition CREATE TYPE EMPLOYEE AS OBJECT ( EmpName VARCHAR2(50), EmpNo INTEGER, );
The following program implements the custom Java class EmployeeObj to correspond to the SQL type EMPLOYEE. Notice that the implementation of EmployeeObj contains a string EmpName (employee name) attribute and an integer EmpNo (employee number) attribute. Also notice that the Java definition of the EmployeeObj custom Java class implements the SQLData interface and includes the implementations of a get method and the required readSQL() and writeSQL() methods.
import java.sql.*; import oracle.jdbc2.*; public class EmployeeObj implements SQLData { private String sql_type; public String empName; public int empNo; public EmployeeObj() { } // line 14 public EmployeeObj (String sql_type, String empName, int empNo) { this.sql_type = sql_type; this.empName = empName; this.empNo = empNo; } // line 20 ////// implements SQLData ////// // define a get method to return the SQL type of the object line 24 public String getSQLTypeName() throws SQLException { return sql_type; } // line 28 // define the required readSQL() method line 30 public void readSQL(SQLInput stream, String typeName) throws SQLException { sql_type = typeName; empName = stream.readString(); empNo = stream.readInt(); } // define the required writeSQL() method line 39 public void writeSQL(SQLOutput stream) throws SQLException { stream.writeString(empName); stream.writeInt(empNo); } }
Import the needed java.* and oracle.* packages. Define the custom Java class EmployeeObj to implement the SQLData interface. EmployeeObj is the class to which you will later map your EMPLOYEE SQL object type. The EmployeeObj object has three attributes: a SQL type name, an employee name, and an employee number. The SQL type name is a Java string that represents the fully qualified SQL type name (schema.sql_type_name) of the Oracle object that the custom Java class represents.
Define a getSqlType() method to return the SQL type of the custom Java object.
Define a readSQL() method as required by the definition of the SQLData interface. The readSQL() method takes a stream SQLInput object and the SQL type name of the object data that it is reading.
Define a writeSQL() method as required by the definition of the SQLData interface. The writeSQL() method takes a stream SQLOutput object and the SQL type name of the object data that it is reading.
After you create your EmployeeObj Java class, you can use it in a program. The following program creates a table that stores employee name and number data. The program uses the EmployeeObj object to create a new employee object and insert it in the table. It then applies a SELECT statement to get the contents of the table and prints its contents.
Except for some changes to the comments, the following sample is similar to the SQLDataExample.java program in the Demo/samples/oci8/object-samples directory.
import java.sql.*; // line 1 import oracle.jdbc.driver.*; import oracle.sql.*; import java.math.BigDecimal; import java.util.Dictionary; public class SQLDataExample { public static void main(String args []) throws Exception { // Connect to the database DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver ()); OracleConnection conn = (OracleConnection) DriverManager.getConnection("jdbc:oracle:oci8:@", "scott", "tiger"); // line 16 // in the type map, add the mapping of EMPLOYEE SQL // line 18 // type to the EmployeeObj custom Java type Dictionary map = conn.getTypeMap(); map.put("EMPLOYEE", Class.forName("EmployeeObj")); // line 21 // Create a Statement line 23 Statement stmt = conn.createStatement (); try { stmt.execute ("drop table EMPLOYEE_TABLE"); stmt.execute ("drop type EMPLOYEE"); } catch (SQLException e) { // An error is raised if the table/type does not exist. Just ignore it. } // Create and populate tables // line 35 stmt.execute ("CREATE TYPE EMPLOYEE AS OBJECT(EmpName VARCHAR2(50), EmpNo INTEGER)"); stmt.execute ("CREATE TABLE EMPLOYEE_TABLE (ATTR1 EMPLOYEE)"); stmt.execute ("INSERT INTO EMPLOYEE_TABLE VALUES (EMPLOYEE('Susan Smith', 123))"); // line 40 // Create a SQLData object EmployeeObj in the SCOTT schema EmployeeObj e = new EmployeeObj("SCOTT.EMPLOYEE", "George Jones", 456); // Insert the SQLData object into the database // line 45 PreparedStatement pstmt = conn.prepareStatement ("INSERT INTO employee_table VALUES (?)"); pstmt.setObject(1, e, OracleTypes.STRUCT); pstmt.executeQuery(); System.out.println("insert done"); pstmt.close(); // line 52 // Select the contents of the employee_table // line 54 Statement s = conn.createStatement(); OracleResultSet rs = (OracleResultSet) s.executeQuery("SELECT * FROM employee_table"); // line 57 // print the contents of the table // line 59 while(rs.next()) { EmployeeObj ee = (EmployeeObj) rs.getObject(1); System.out.println("EmpName: " + ee.empName + " EmpNo: " + ee.empNo); } // line 64 // close the result set, statement, and connection // line 66 rs.close(); s.close(); if (conn != null) { conn.close(); // line 72 } } }
Import needed java.* and oracle.* packages. Register the driver with the DriverManager.registerDriver() method and connect to the database with the getConnection() method. Use the database URL jdbc:oracle:oci8:@ and connect as user scott with password tiger. You can optionally enter a database name following the @ symbol.
Use the getTypeMap() method to get the type map associated with this connection. Use the map object's put() method to add the mapping of the SQL EMPLOYEE object to the EmployeeObj custom Java type.
Create a statement object and drop any pre-existing tables and types named EMPLOYEE_TABLE and EMPLOYEE.
Use SQL statements to:
EMPLOYEE object with employee name and employee number attributes
EMPLOYEE_TABLE) having a single EMPLOYEE column
Create a new EmployeeObj object (which is a SQLData object). Identify the schema name (SCOTT), SQL type name (EMPLOYEE), an employee name (George Jones) and an employee number (456). Note that the schema name is the same as the user name in the getConnection() call. If you change the user name, you must also change the schema name.
Prepare a statement to insert the new EMPLOYEE object into the employee table. The setObject() method indicates that the object will be inserted into the first index position and that the underlying type of the EMPLOYEE object is oracle.sql.STRUCT.
Select the contents of the EMPLOYEE_TABLE. Cast the results to an OracleResultSet so that you can retrieve the custom Java object data from it.
Iterate through the result set, getting the contents of the EMPLOYEE objects and printing the employee names and employee numbers.
Close the result set, statement, and connection objects.
This section describes a Java class, written by a user, that implements the CustomDatum and CustomDatumFactory interfaces. The custom Java class of type CustomDatum has a static getFactory() method that returns a CustomDatumFactory object. The JDBC driver uses the CustomDatumFactory object's create() method to return a CustomDatum instance. Note that instead of writing the custom Java class yourself, you can use the JPublisher utility to generate class definitions that implement the CustomDatum and CustomDatumFactory interfaces.
The following example illustrates a Java class definition that can be written by a user, given the SQL definition of an EMPLOYEE object.
The following SQL code defines the EMPLOYEE object. The EMPLOYEE object consists of the employee's name (EmpName) and the employee's associated number (EmpNo).
create type EMPLOYEE as object ( EmpName VARCHAR2(50), EmpNo INTEGER );
Below are the contents of the Employee.java file.
import java.math.BigDecimal; import java.sql.SQLException; import oracle.jdbc.driver.OracleConnection; import oracle.sql.CustomDatum; import oracle.sql.CustomDatumFactory; import oracle.sql.Datum; import oracle.sql.STRUCT; import oracle.sql.StructDescriptor; public class Employee implements CustomDatum, CustomDatumFactory // line 10 { static final Employee _employeeFactory = new Employee(null, null); //line 13 public static CustomDatumFactory getFactory() { return _employeeFactory; } // line 18 /* constructor */ // line 20 public Employee(String empName, BigDecimal empNo) { this.empName = empName; this.empNo = empNo; } // line 25 /* CustomDatum interface */ // line 27 public Datum toDatum(OracleConnection c) throws SQLException { StructDescriptor sd = StructDescriptor.createDescriptor("SCOTT.EMPLOYEE", c); Object [] attributes = { empName, empNo }; return new STRUCT(sd, c, attributes); } // line 36 /* CustomDatumFactory interface */ // line 38 public CustomDatum create(Datum d, int sqlType) throws SQLException { if (d == null) return null; System.out.println(d); Object [] attributes = ((STRUCT) d).getAttributes(); return new Employee((String) attributes[0], (BigDecimal) attributes[1]); } // line 49 /* fields */ public String empName; public BigDecimal empNo; }
As required, the Employee class implements the CustomDatum and CustomDatumFactory interfaces.
JPublisher defines a _employeeFactory object of class Employee, which will be returned by the getFactory() method and used to create new Employee objects. The getFactory() method returns an empty Employee object that you can use to create new Employee objects.
JPublisher defines the Employee Java class to correspond to the SQL EMPLOYEE object. JPublisher creates the Employee class with two attributes: an employee name of type java.lang.String and an employee number of type java.math.BigDecimal.
The toDatum() method of the CustomDatum interface transforms the EMPLOYEE SQL data into oracle.sql.* representation. To do this, toDatum() uses:
STRUCT descriptor that takes the schema name, the SQL object or "type" name, and the connection object as arguments
The toDatum() returns a STRUCT containing the STRUCT descriptor, the connection object and the object attributes into an oracle.sql.Datum.
The CustomDatumFactory interface specifies a create() method that is analogous to the constructor of your Employee custom Java class. The create() method takes the Datum object and the SQL type code of the Datum object and returns a CustomDatum instance.
According to the definition, the create() method returns null if the value of the Datum object is null. Otherwise, it returns an instance of the Employee object with the employee name and employee number attributes.
This code snippet presents a simple example of how you can use the Employee class that you created with JPublisher. The sample code creates a new Employee object, fills it with data, then inserts it into the database. The sample code then retrieves the Employee data from the database.
Except for some changes to the comments, the following sample is similar to the CustomDatumExample.java program in the Demo/samples/oci8/object-samples directory.
import java.sql.*; // line 1 import oracle.jdbc.driver.*; import oracle.sql.*; import java.math.BigDecimal; public class CustomDatumExample { public static void main(String args []) throws Exception { // Connect DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver ()); OracleConnection conn = (OracleConnection) DriverManager.getConnection("jdbc:oracle:oci8:@", "scott", "tiger"); // Create a Statement // line 18 Statement stmt = conn.createStatement (); try { stmt.execute ("drop table EMPLOYEE_TABLE"); stmt.execute ("drop type EMPLOYEE"); } catch (SQLException e) { // An error is raised if the table/type does not exist. Just ignore it. } // line 28 // Create and populate tables // line 30 stmt.execute ("CREATE TYPE EMPLOYEE AS " + " OBJECT(EmpName VARCHAR2(50),EmpNo INTEGER)"); stmt.execute ("CREATE TABLE EMPLOYEE_TABLE (ATTR1 EMPLOYEE)"); stmt.execute ("INSERT INTO EMPLOYEE_TABLE " + " VALUES (EMPLOYEE('Susan Smith', 123))"); // line 35 // Create a CustomDatum object // line 37 Employee e = new Employee("George Jones", new BigDecimal("456")); // Insert the CustomDatum object // line 40 PreparedStatement pstmt = conn.prepareStatement ("INSERT INTO employee_table VALUES (?)"); pstmt.setObject(1, e, OracleTypes.STRUCT); pstmt.executeQuery(); System.out.println("insert done"); pstmt.close(); // line 47 // Select now // line 49 Statement s = conn.createStatement(); OracleResultSet rs = (OracleResultSet) s.executeQuery("SELECT * FROM employee_table"); while(rs.next()) // line 54 { Employee ee = (Employee) rs.getCustomDatum(1, Employee.getFactory()); System.out.println("EmpName: " + ee.empName + " EmpNo: " + ee.empNo); } // line 58 rs.close(); s.close(); if (conn != null) { conn.close(); } } }
Import needed java.* and oracle.* packages. Register the driver with the DriverManager.registerDriver() method and connect to the database with the getConnection() method. Use the database URL jdbc:oracle:oci8:@ and connect as user system with password manager. You can optionally enter a database name following the @ symbol.
Create a statement object and drop any pre-existing tables and types named EMPLOYEE_TABLE and EMPLOYEE.
Use SQL statements to:
Employee object with employee name and employee number attributes
EMPLOYEE column
Create a new Employee object (which is a CustomDatum object) and define an employee name and employee number for it.
Prepare a statement to insert the new Employee object into the database. The setObject() method indicates that the object will be inserted into the first index position and that the underlying type of the Employee object is oracle.sql.STRUCT.
Select the contents of the employee_table. Cast the results to an OracleResultSet so that the getCustomDatum() method can be used on it.
Iterate through the result set, getting the contents of the Employee objects and printing the employee names and employee numbers.
Close the result set, statement, and connection objects.
This section presents an example of a signed applet which uses the JDBC Thin driver to connect to and query a database. The code used in the applet was created with Oracle JDeveloper and complies with JDK 1.1.2 and JDBC 1.22. Signed applets are also browser-specific; the applet defined in this section works with the Netscape 4.x browser.
The applet displays a user interface that lets you connect to a local or a remote database, depending on whether you press the "Local" or "Remote" button. The applet queries the selected database for the contents of a specified row and displays the results.
If you want to try this example on your own system, you must provide this information:
http://developer.netscape.com/docs/manuals/signedobj/capabilities/contents.htm
Follow the instructions for obtaining a certificate and downloading the classes. The example in this section requires the Capabilities classes Principle.class, Privilege.class, PrivilegeManager.class, and PrivilegeTable.class.
In the applet code, replace the following strings:
local database connect string> with the connect string for the local database. For example:
"jdbc:oracle:thin:@myServer.us.oracle.com:1521:orcl", "scott","tiger"
select on row of local table> with a SQL SELECT statement on a row in a table in the local database. For example:
SELECT * FROM EMP WHERE ENAME = 'Mary'
remote database connect string> with the connect string for the remote database. For example:
"jdbc:oracle:thin:@yourServer.us.oracle.com:1521:orcl", "scott","tiger"
select on row of remote table> with a SQL SELECT statement on a table in the remote database. For example:
SELECT * FROM EMP WHERE ENAME = 'Bob'
This applet uses only the Java AWT components and JDBC.
// Title: JDBC Test Applet // line 1 // Description:Sample JDK 1.1 Applet using the // ORACLE JDBC Thin Driver package JDBCApplet; import java.awt.*; // line 6 import java.awt.event.*; import java.applet.*; import java.sql.*; import borland.jbcl.control.*; import netscape.security.*; // line 12 public class MainApplet extends Applet { boolean isStandalone = false; BorderLayout borderLayout1 = new BorderLayout(); Panel panel1 = new Panel(); Label labelTitle = new Label(); Panel panel2 = new Panel(); BorderLayout borderLayout2 = new BorderLayout(); TextArea txtArResults = new TextArea(); Button button1 = new Button(); BorderLayout borderLayout3 = new BorderLayout(); Panel panel3 = new Panel(); BorderLayout borderLayout4 = new BorderLayout(); Label statusBar1 = new Label(); Button button2 = new Button(); // Get a parameter value // line 28 public String getParameter(String key, String def) { return isStandalone ? System.getProperty(key, def) : (getParameter(key) != null ? getParameter(key) : def); } // line 32 // Construct the applet public MainApplet() { } // Initialize the applet line 37 public void init() { try { jbInit(); } catch (Exception e) { e.printStackTrace(); } try { PrivilegeManager.enablePrivilege("UniversalConnect"); PrivilegeManager.enablePrivilege("UniversalListen"); PrivilegeManager.enablePrivilege("UniversalAccept"); } catch (Exception e) { e.printStackTrace(); } } // Component initialization line 49 public void jbInit() throws Exception{ this.setBounds(new Rectangle(0, 0, 400, 400)); panel1.setBackground(Color.lightGray); panel1.setLayout(borderLayout3); this.setSize(new Dimension(372, 373)); labelTitle.setBackground(Color.lightGray); labelTitle.setFont(new Font("Dialog", 0, 12)); labelTitle.setAlignment(1); labelTitle.setText("Oracle Thin JDBC Driver Sample Applet"); button1.setLabel("Local"); panel3.setBackground(Color.lightGray); statusBar1.setBackground(Color.lightGray); statusBar1.setText("Ready"); button2.setLabel("Remote"); button2.addActionListener(new MainApplet_button2_actionAdapter(this)); panel3.setLayout(borderLayout4); button1.addActionListener(new MainApplet_button1_actionAdapter(this)); panel2.setLayout(borderLayout2); this.setLayout(borderLayout1); this.add(panel1, BorderLayout.NORTH); panel1.add(button1, BorderLayout.WEST); panel1.add(labelTitle, BorderLayout.CENTER); panel1.add(button2, BorderLayout.EAST); this.add(panel2, BorderLayout.CENTER); panel2.add(txtArResults, BorderLayout.CENTER); this.add(panel3, BorderLayout.SOUTH); panel3.add(statusBar1, BorderLayout.NORTH); } //Start the applet line 79 public void start() { } //Stop the applet public void stop() { } //Destroy the applet public void destroy() { } //Get Applet information public String getAppletInfo() { return "Applet Information"; } //Get parameter info public String[][] getParameterInfo() { return null; } //Main method static public void main(String[] args) { MainApplet applet = new MainApplet(); applet.isStandalone = true; Frame frame = new Frame(); frame.setTitle("Applet Frame"); frame.add(applet, BorderLayout.CENTER); applet.init(); applet.start(); frame.pack(); Dimension d = Toolkit.getDefaultToolkit().getScreenSize(); frame.setLocation((d.width - frame.getSize().width) / 2, (d.height - frame.getSize().height) / 2); frame.setVisible(true); } void button1_actionPerformed(ActionEvent e) { // // Handler for "Local" Button. // // Here is where we connect to local database line 121 StringBuffer b = new StringBuffer (); try { DriverManager.registerDriver ( new oracle.jdbc.driver.OracleDriver ()); b.append ("DriverManager.registerDriver\r\n"); } catch (SQLException oe) { statusBar1.setText("registerDriver: Caught SQLException"); } catch (ClassNotFoundException oe) { statusBar1.setText("registerDriver: Caught ClassNotFoundException"); } int numRows = 0; try { statusBar1.setText("Executing Query on Local Database ..."); Connection conn = DriverManager.getConnection ( "jdbc:oracle:thin:<local database connect string>"); b.append ("[DriverManager.getConnection] \r\n"); Statement stmt = conn.createStatement (); b.append ("[conn.createStatement] \r\n"); ResultSet rset = stmt.executeQuery ("<select on row oflocal table>"); b.append ("[stmt.executeQuery] \r\n"); b.append("SQL> <select on row of local table>\r\n\n"); b.append("DSCr\n--------------------------------------\r\n"); while (rset.next ()) { String ename = rset.getString (1); b.append (ename); b.append ("\r\n"); numRows++; } // [end while rset.next() loop] statusBar1.setText("Query Done."); } catch (SQLException SQLE) { statusBar1.setText ("Caught SQLException!"); SQLE.printStackTrace(); } finally { b.append("\r\n"); b.append(String.valueOf(numRows) + " rows selected.\r\n"); txtArResults.setText( b.toString ()); } // End JDBC Code line 165 } void button2_actionPerformed(ActionEvent e) { // // Handler for the "Remote" Button line 170 // StringBuffer b = new StringBuffer (); try { DriverManager.registerDriver ( new oracle.jdbc.driver.OracleDriver ()); b.append ("DriverManager.registerDriver\r\n"); } catch (SQLException oe) { statusBar1.setText("registerDriver: Caught SQLException"); } catch (ClassNotFoundException oe) { statusBar1.setText("registerDriver: Caught ClassNotFoundException"); } int numRows = 0; // line 183 try { statusBar1.setText("Executing Query on Remote Database ..."); try { PrivilegeManager.enablePrivilege("UniversalConnect"); b.append ("enablePrivilege(UniversalConnect)\r\n"); PrivilegeManager.enablePrivilege("UniversalListen"); b.append ("enablePrivilege(UniversalListen)\r\n"); PrivilegeManager.enablePrivilege("UniversalAccept"); b.append ("enablePrivilege(UniversalAccept)\r\n"); Connection conn = DriverManager.getConnection ( "jdbc:oracle:thin:<remote database connect string>" ); b.append ("DriverManager.getConnection\r\n"); Statement stmt = conn.createStatement (); b.append ("conn.createStatement\r\n"); ResultSet rset = stmt.executeQuery ("<select on rowof remote table>"); b.append ("stmt.executeQuery\r\n"); b.append("SQL> <select on row of remote table>\r\n\n"); b.append("ENAME\r\n----------\r\n"); while (rset.next ()) { String ename = rset.getString (1); b.append (ename); b.append ("\r\n"); numRows++; } // [end while rset.next() loop] statusBar1.setText("Query Done."); } catch (Exception oe) { oe.printStackTrace(); } } catch (SQLException SQLE) { statusBar1.setText("Caught SQLException!"); SQLE.printStackTrace(); } finally { b.append("\r\n"); b.append(String.valueOf(numRows) + " rows selected.\r\n"); txtArResults.setText( b.toString ()); } // End JDBC Code for Button2 line 256 } } // line 260 class MainApplet_button1_actionAdapter implements java.awt.event.ActionListener { MainApplet adaptee; MainApplet_button1_actionAdapter(MainApplet adaptee) { this.adaptee = adaptee; } public void actionPerformed(ActionEvent e) { adaptee.button1_actionPerformed(e); } } // line 273 class MainApplet_button2_actionAdapter implements java.awt.event.ActionListener { MainApplet adaptee; MainApplet_button2_actionAdapter(MainApplet adaptee) { this.adaptee = adaptee; } public void actionPerformed(ActionEvent e) { adaptee.button2_actionPerformed(e); } }
Import the needed files.
Set up the graphics for the GUI which will include two buttons and a text area to display the output.
Request privileges to connect to the host other than the one from which the applet was downloaded.
Initialize the components of the applet. These components include the format and layout of the GUI and the GUI buttons and text area.
Connect to the local database. To do this, register the driver with the DriverManager.registerDriver() method and connect to the database with DriverManager.getConnection(). Connect with the server URL, port number, SID, user name, and password.
Connect to the remote database.
Test that the applet has privileges on the remote database. If it does, then connect to the database and execute SQL statements.
Code to set up events and callbacks for the buttons.
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 SELECTs 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 DOs" 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 DOs" 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.
