Sample demonstrating the usage of Oracle object mapping

Table Of Contents 

• Overview of the Sample Application
• Notations Used
  
• Required Software
• Application Set-up and Configuration
• Running the Application 
• From Oracle9i JDeveloper 
• From JDK for Windows
• From JDK for Linux
• Description of Sample Files

This sample illustrates retrieval and manipulation of Objects from an Oracle9i database using JDBC. Objects can be accessed either using oracle.sql.STRUCT or by defining custom Java Classes to represent the Oracle Object Type. This sample illustrates access using the weakly typed objects (oracle.sql.STRUCT), while retrieval using strongly typed objects is illustrated in Object Java Sample.

In this sample, the user can perform DML operations like select, insert update and delete on a table containing a Object Type Column.

Working of the Sample

• When the user invokes the application, it connects to the database. The status and errors, if any, will be displayed in the status bar.
• When the user presses the SELECT button the rows are retrieved from the TABLE_CITY_OBJ table. If the textfields (city name and state)  are empty, all rows are retrieved. If there exists some value in these fields then they are used as a restriction criterion and only rows matching this criteria areretrieved.
• When the user presses INSERT button, first the textfields are checked to see if they have a valid value. City Name and state textfields are mandatory for inserts. On successful validation the row will be inserted and the JTable will also be updated.
• When the user presses the UPDATE button, the values in the textfields are used to update an existing row. The JTable will be updated to show the new values.The City name field should be the same as that of the selected row, as the City name is not updateable. If the City name is different from the selected row, then an error is given saying City name is non-updateable, and the City name is reset.
• When the user presses DELETE then the selected row is deleted from the TABLE_CITY_OBJ table, and also from the displayed Jtable.

Here is the code where the Oracle objects are accessed using oracle.sql.STRUCT. You can find more details of the code in ObjectOracleSample.java file under src/oracle/otnsamples/jdbc/oraobjects folder. Look into Description of Sample Files section for folder and file details.

Selecting Oracle object using oracle.sql.STRUCT

private void selectRecord(String name, String density, String state,  String code) { // The SQL query for selecting the rows from the TABLE_CITY_OBJ table // Query can only be done on two fields, CITY CODE and CITY NAME String query = "SELECT * from TABLE_CITY_OBJ a "+ "WHERE a.OBJCITY.CITY_CODE LIKE ? AND a.OBJCITY.NAME LIKE ?"; try { // Create a PreparedStatement based on the query in query PreparedStatement pst = connection.prepareStatement(query); // If no query condition has been entered, change the bind values // to select all records if(code.equals("")) code = "%"; else code = "%"+code+"%"; if(name.equals("")) name = "%"; else name = "%"+name+"%"; // Bind the PreparedStatement with corresponding values pst.setString(1, code); pst.setString(2, name); // Execute the PreparedStatement and retrieve into an OracleResultSet, // as we need to retrieve Oracle extension datatypes like STRUCT OracleResultSet resultSet = (OracleResultSet)pst.executeQuery(); // Loop through the resultset and retrieve the objects while(resultSet.next()) { // Use getObject to get the row into oracle.sql.STRUCT oracle.sql.STRUCT mystruct = (oracle.sql.STRUCT)(resultSet.getObject(1)); // Call getAttributes method of oracle.sql.STRUCT to get the individual // column values Object cols[] = mystruct.getAttributes(); // If column in STRUCT is null set it to an empty string for the JTable cols[1] = (cols[1] == null) ? "" : cols[1]; cols[2] = (cols[2] == null) ? "" : cols[2]; // Add the row values to the JTable gui.addToJTable(cols[0], cols[1], cols[2], cols[3]); .......... ..........

Inserting Oracle object using oracle.sql.STRUCT

private void insertRecord(String name, String density, String state,  String code) { // construct the object array containing the attribute values for the // Object to be inserted Object objArray[] = new Object[4]; objArray[0] = name; objArray[1] = state; objArray[2] = code; objArray[3] = densityint; // Create the StructDescriptor from the connection StructDescriptor colStructDesc = StructDescriptor.createDescriptor("OBJ_TYPE_CITY", connection); // Construct the Struct from the StructDescriptor and objects oracle.sql.STRUCT colStruct = new STRUCT(colStructDesc, connection, objArray); // Prepare the insert SQL statement using Preparedstatement. We need to // use OraclePreparedStatement here because we need to bind in an Oracle // extension type, STRUCT OraclePreparedStatement ps = (OraclePreparedStatement)connection.prepareStatement( "INSERT INTO TABLE_CITY_OBJ VALUES (?)"); // Bind in the STRUCT representing the object to be inserted ps.setSTRUCT(1, colStruct); ps.executeUpdate(); // execute the insert statement .......... ..........

Updating Oracle object using oracle.sql.STRUCT

private void updateRecord(String name, String density, String state, String code) { // construct the object array containing the values to be updated Object objArray[] = new Object[4]; objArray[0] = name; objArray[1] = state; objArray[2] = code; objArray[3] = densityint; // Create the StructDescriptor from the connection StructDescriptor colStructDesc = StructDescriptor.createDescriptor("OBJ_TYPE_CITY", connection); // Construct the Struct from the StructDescriptor and objects oracle.sql.STRUCT colStruct = new STRUCT(colStructDesc, connection, objArray); // Prepare the update statement using Prepared statement call OraclePreparedStatement ps = (OraclePreparedStatement)connection.prepareStatement( "UPDATE TABLE_CITY_OBJ a SET a.OBJCITY = ? " + "WHERE a.OBJCITY.CITY_CODE = ?"); // Bind the Object STRUCT and CITY CODE to preparedstatement ps.setSTRUCT(1, colStruct); ps.setString(2, code); ps.executeUpdate(); // Execute the update statement .......... ..........

Deleting a row of Oracle object

private void deleteRecord(String code){ // Prepare the SQL statement for deletion PreparedStatement ps = connection.prepareStatement( "DELETE FROM TABLE_CITY_OBJ a WHERE a.OBJCITY.CITY_CODE =?"); ps.setString(1,code); // set the values in the statement ps.executeUpdate(); // execute the delete statement ..................... ......................

The following notations are used through out this document

Notation	Description
<SAMPLE_HOME>	Folder where the ObjectOracleSample.jar is unzipped.
<JAVA_HOME>	Folder where JAVA is installed.
<ORACLE_HOME>	Folder where the Oracle is installed.

Required Software

Back To Top

• Oracle9i JDeveloper ( Note: Oracle9i JDeveloper is Oracle's Visual Java Development Tool and can be downloaded from here )
  or JDK1.2.x or above This can be downloaded from here .
• Oracle9i Database or higher running SQL*Net TCP/IP listener. This can be downloaded from here .
• Oracle9i JDBC Driver. The JDBC driver is available at ORACLE_HOME/jdbc/lib. Or it could be downloaded from here.

• Unjar the provided ObjectOracleSample.jar using the following command 

> jar xvf ObjectOracleSample.jar

Note: You will find jar.exe in JAVA_HOME\bin. Ensure JAVA_HOME\bin is present in your system path. 
This creates a folder ObjectOracleSample with all the source files

• Database Setup
  Connect to your database as any user and execute the sql script located at ObjectOracleSample\config using the following command
  SQL>@<SAMPLE_HOME>\ObjectOracleSample\config\ObjectOracleSample.sql

• Edit ObjectOracleSample/Connection.properties file in your favorite editor. Change the HostName, Port, SID, UserName and 
Password to connect to schema where you have run the sql script ObjectOracleSample.sql in your database.

This sample application can be run in 3 different ways listed below.

• From Oracle9i JDeveloper
• From JDK for Windows
• From JDK for Linux

From Oracle9i JDeveloper

• Open Oracle9i JDeveloper and use File/Open option to select the ObjectOracleSample.jws from the ObjectOracleSample directory.
• Next, select Project/ObjectOracleSample.jpr from main menu.
• Now, select Run/Run ObjectOracleSample.jpr from main menu to run the application.

From JDK for Windows

This section will describe steps to run the application from console using JDK on Windows. The sample can be run either manually or using a script file .

Run application using batch File: run.bat provided:

By setting few environment variables, the sample application could be directly run by just executing the batch file: run.bat from the command prompt, from ObjectOracleSample directory. Environmental variables JAVA_HOME and JDBC_HOME have to be set before running run.bat file.
Example:

D:\ObjectOracleSample> set JDBC_HOME=d:\oracle9i\jdbc\lib
D:\ObjectOracleSample> set JAVA_HOME=d:\jdk1.3.1
D:\ObjectOracleSample> run

Running the application manually:

• Set CLASSPATH to include Oracle9i JDBC Driver file: classes12.zip or classes12.jar or ojdbc14.jar. 
• Also add ObjectOracleSample directory where Connection.properties exists and the current directory to the CLASSPATH
  Example: 
  D:\ObjectOracleSample>set CLASSPATH=D:\oracle9i\jdbc\lib\classes12.zip;D:\ObjectOracleSample;.
  
• From the directory ObjectOracleSample\src\oracle\otnsamples\jdbc\oraobjects, compile all the java files using javac:
  Example: 
  D:\ObjectOracleSample\src\oracle\otnsamples\jdbc\oraobjects>javac -d . *.java
  
• Run the class file using java from  ObjectOracleSample\src\oracle\otnsamples\jdbc\oraobjects directory
  Example:
  D:\ObjectOracleSample\src\oracle\otnsamples\jdbc\oraobjects>
  java oracle.otnsamples.jdbc.oraobjects.ObjectOracleSample

From JDK for Red Hat Linux Advanced Server release 2.1

This section will describe steps to run the application from console using JDK on Linux. The sample can be run either manually or using a script file .

Run application using batch File: run.sh provided:

By setting few environment variables, the sample application could be directly run by just executing the batch file: run.sh from the command prompt, from ObjectOracleSample directory. Environmental variables JAVA_HOME and JDBC_HOME have to be set, else the user will be prompted to enter values.

• Go to ObjectOracleSample directory and from the $ prompt use the command below to run the script:
  $sh run.sh

Running the application manually:

• Set CLASSPATH to include Oracle9i JDBC Driver file: classes12.zip or classes12.jar or ojdbc14.jar.
• ObjectOracleSample  directory where Connection.properties exists and current directory are also added to the CLASSPATH.
  Example:
  $export CLASSPATH=/home1/jdbc/lib/classes12.zip:/home1/ObjectOracleSample:.
• From the directory ObjectOracleSample/src/oracle/otnsamples/jdbc/oraobjects, compile all the java files using javac:
  Example:
  $javac -d . *.java
• Run the class file using java from the same ObjectOracleSample/src/oracle/otnsamples/jdbc/oraobjects directory.
  Example:
  $java oracle.otnsamples.jdbc.oraobjects.ObjectOracleSample