Sample demonstrating the usage of Java Oracle object mapping

Table Of Contents 

• Overview of the Sample Application
• Notations used
  
• Required Software
• Application Set-up and Configuration
• Generating Custom Java using JPublisher
• 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 Oracle 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 of an Object Type using a java class generated by JPublisher, while retrieval using weakly typed objects (oracle.sql.STRUCT) is illustrated in Object Oracle 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 (code and name) 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 are retrieved.
• When the user presses INSERT button, first the textfields are checked to see if they have a valid value. CITY 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 CODE field should be the same as that of the selected row, as the CODE is not updateable. If the code is different from the selected row, then an error is given saying CODE is non-updateable, and the CODE 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 java classes generated by JPublisher. You can find more details of the code in ObjectJavaSample.java file under src/oracle/otnsamples/jdbc/javaobjects folder. Look into Description of Sample Files section for folder and file details.

Selecting Oracle object using Java classes

private void selectRecord(String name, String density, String state, String code){ //The SQL query for selecting the rows from the TABLE_CITY_OBJ table 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 = "%"; if(name.equals("")) name = "%"; //Bind the PreparedStatement with corresponding values pst.setString(1,code); pst.setString(2,name); //Execute the PreparedStatement OracleResultSet resultSet = (OracleResultSet)pst.executeQuery(); // create a type map using java.util.Hashtable object for specifying // the mapping between the Oracle Object-Type and the generated // java class representing the Object-Type // Instantiate a HashTable to specify mapping java.util.Hashtable mymap = new java.util.Hashtable(); // Load Java Class Class obj = Class.forName( "oracle.otnsamples.jdbc.javaobjects.CityObject"); // Map the loaded java class to the object-type mymap.put ("OBJ_TYPE_CITY", obj); // loop through the resultset and retrieve all rows while(resultSet.next()) { // specify the type map while calling getObject method CityObject city; // Retrieve the column from the ResultSet into the CityObject // class. This class represents the OBJ_TYPE_CITIES column in the // database, and hence the retrieved object is cast to CityObject city = (CityObject)resultSet.getObject(1, mymap); // add the row values to the JTable gui.addToJTable(city_object.getName(), (city.getProvince()==null)?"":city.getProvince(), city.getCityCode(),city.getPopulationDensity()); } ..................... ......................

Inserting Oracle object using Java classes

private void insertRecord(String name, String density, String state,  String code) { // Construct an instance of the cityObject class for // insertion. This object represents the object type OBJ_TYPE_CITIES // in the database. CityObject row = new CityObject(); row.setCityCode(code); row.setName(name); row.setPopulationDensity(densityint); row.setProvince(state); // Prepare the SQL statement for insertion OraclePreparedStatement ps = (OraclePreparedStatement)connection.prepareStatement( "INSERT INTO TABLE_CITY_OBJ VALUES (?)"); // Bind the Java Object and City code to the above PreparedStatement ps.setObject(1,row); // Bind the Java Object to the above Statement ps.executeUpdate(); // execute the insert statement ..................... ......................

Updating Oracle object using Java classes

private void updateRecord(String name, String density, String state,  String code){ // Construct an instance of the cityObject class for // updation. This object represents the object type OBJ_TYPE_CITIES // in the database. CityObject row = new CityObject(); row.setCityCode(code); row.setName(name); row.setPopulationDensity(densityint); row.setProvince(state); // prepare the SQL statement for updation OraclePreparedStatement ps = (OraclePreparedStatement)connection.prepareStatement( "UPDATE TABLE_CITY_OBJ a SET a.OBJCITY = ?"+ "WHERE a.OBJCITY.CITY_CODE = ?"); // Bind the Java Object and City code to the above PreparedStatement ps.setObject(1,row); 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 ObjectJavaSample.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 ObjectJavaSample.jar using the following command 

> jar xvf ObjectJavaSample.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 ObjectJavaSample with all the source files

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

• Edit ObjectJavaSample/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 ObjectJavaSample.sql in your database.

This section describes the steps required to create custom Java classes for object type and REF types using JPublisher. The Java classes are already provided for you along with the sample. So this is an optional step.

• JPublisher utility is available at ORACLE_HOME/bin directory of your database or client installation. Make sure ORACLE_HOME/bin and JAVA_HOME/bin are in the PATH.
• Add translator.jar and runtime12.jar (available in ORACLE_HOME/sqlj/lib directory)  and classes12.jar (available in ORACLE_HOME/jdbc/lib directory )  to the CLASSPATH.
• To generate the Java classes use the following command from the command prompt:

  >jpub -user=<UserName>/<Password> -sql=obj_type_city:CityObject -url=jdbc:oracle:<DriverType>:<ConnectString> -builtintypes=jdbc -package=oracle.otnsamples.jdbc.javaobjects -omit_schema_names

  Replace <UserName>, <Password>, <DriverType>, <ConnectString> with
  appropriate values.

  Example:

  >jpub -user=scott/tiger -sql=obj_type_city:CityObject -url=jdbc:oracle:thin:@localhost:1521:ORCL -builtintypes=jdbc -package=oracle.otnsamples.jdbc.javaobjects -omit_schema_names

  This command generates CityObject.java & CityObjectRef.java for the object type
  OBJ_TYPE_CITY. You can replace these files that are present at ObjectJavaSample\src\oracle\otnsamples\jdbc\javaobjects with the generated files.
  

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 ObjectJavaSample.jws from the ObjectJavaSample directory.
• Next, select Project/ObjectJavaSample.jpr from main menu.
• Now, select Run/Run ObjectJavaSample.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 ObjectJavaSampledirectory. Environmental variables JAVA_HOME and JDBC_HOME have to be set before running run.bat file.
Example:

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

Running the application manually:

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

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 ObjectJavaSample directory. Environmental variables JAVA_HOME and JDBC_HOME have to be set, else the user will be prompted to enter values.

• Go to ObjectJavaSample directory and from the $ prompt use the command below to give execute permission to the file.
  $chmod 777 run.sh
• Now run the file:
  $sh run.sh

Running the application manually:

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