How-to persist and retrieve user defined SQL objects using
JDBC
After completing this How-to you should be able to:
Identify different mechanisms in which user
defined SQL objects can be accessed from JDBC.
Understand how
java.sql.STRUCT class and the corresponding Oracle
extensions can be used to manipulate oracle objects.
Introduction
An object type is a user-defined composite datatype
that encapsulates a data structure along with the functions and procedures needed
to manipulate the data. The data is referred to as attributes and the set of
operations specified on the data are called the methods of the object type.
Pre-requisites for running the example
You will need the following to run this example -
Oracle9i Database. This can be downloaded
from here.
Oracle9i JDBC Driver. This can be downloaded
from here.
JDK 1.3 or above. This can be downloaded from here.
Description
There are two ways in which object types can be persisted
to and retrieved from the database -
Use a standard, generic JDBC type to map to Oracle objects - In this document
we will discuss this approach to persist and retrieve object types from the
database.
Customize the mapping by creating custom Java classes - Oracle JPublisher
utility can be used to generate the custom java classes.
For better understanding, in this document we shall consider
an employee object. An employee can have different attributes like name, designation,
department number etc. The object is created as shown below
CREATE OR REPLACE TYPE EMP_TYPE AS OBJECT (
NAME VARCHAR2(50),
TITLE VARCHAR2(30),
DEPT_NUM NUMBER(2)
);
/
We will now see how to perform DML operations like select
and insert on a table containing the above Object Type as a column. The table
is created as shown below
CREATE TABLE EMP_TABLE (
INFO EMP_TYPE
);
/
Selection from emp_tab table
Here is the code snippet for selecting values from the emp_type
object. For the sake of simplicity, assume the connection object is already
available. We now perform a simple query on the emp_table.
To fetch values from the resultset, an instance of java.sql.STRUCT
is used as shown in line 5. From the Struct instance the attribute values of
the emp_type object are retrieved.
......
Statement stmt = connection.createStatement();
ResultSet rs= stmt.executeQuery("SELECT * FROM emp_table");
System.out.println("Number of attributes "+
empType.getAttributes().length);
System.out.println("Name is " + empType.getAttributes()[0]);
System.out.println("Designation is "
+ empType.getAttributes()[1]);
System.out.println("Department is " +
empType.getAttributes()[2]);
}
stmt.close();
......
Note: The same functionality can be achieved using Oracle extension
methods as shown below :
oracle.sql.STRUCT oracleSTRUCT=((OracleResultSet)rs).getSTRUCT(1);
oracle.sql.Datum[] attrs = oracleSTRUCT.getOracleAttributes();
System.out.println("Number of attributes.." + attrs.length);
System.out.println("Name is " + attrs[0].stringValue());
System.out.println("Designation is " + attrs[1].stringValue());
System.out.println("Department is " + attrs[2].intValue());
As shown above another way to return the object as a STRUCT
object is to cast the result set to an OracleResultSet object and use the Oracle
extension getSTRUCT() method. To retrieve Oracle object attributes from a STRUCT
or Struct instance as oracle.sql types, getOracleAttributes() method of the
oracle.sql.STRUCT class is used.
The oracle.sql.STRUCT class implements the java.sql.Struct
interface and provides extended functionality beyond the JDBC 2.0 standard.
The STRUCT class includes the following methods in addition to standard Struct
functionality:
getOracleAttributes(): Retrieves the values of the values
array as oracle.sql.* objects.
getDescriptor(): Returns the StructDescriptor object for
the SQL type that corresponds to this STRUCT object.
getJavaSQLConnection(): Returns the current connection instance
(java.sql.Connection).
toJdbc(): Consults the default type map of the connection,
to determine what class to map to, and then uses toClass().
toJdbc(map): Consults the specified type map to determine
what class to map to, and then uses toClass().
Insertion to emp_tab table
Here is the code snippet for inserting to the
emp_table using STRUCT object and descriptor.
Again we assume that the connection object is already available.
.....
PreparedStatement stmt = connection.prepareStatement("INSERT
INTO emp_table VALUES(?)");
STRUCT empType= new STRUCT(structdesc, connection, attributes);
stmt.setObject(1, empType);
stmt.execute();
stmt.close();
.....
As shown above construction of a
STRUCT object requires a StructDescriptor
. The constructor for StructDescriptor requires
that you pass in the SQL type name of the Oracle object type and a connection
object as shown in line 2. The next step is to form an object array containing
the values of the attributes corresponding to the object
emp_type. Finally a the STRUCT object is
constructed using the StructDescriptor and connection
object which is used to bind the prepared statement.
Running the Sample
The complete source code for this sample is available
here. This section discusses the instruction to run the sample application
Step 1
Unjar ObjectTypes.jar
using Winzip, or using the following command:
> jar xvf ObjectTypes.jar
This creates a directory ObjectTypes.
Step 2
The application requires a database table used in the
sample to be created. To create the table and the object types, run SQL*Plus,
connect to your database as scott/tiger, and execute the ObjectTypes.sql
located in ObjectTypes directory as shown below:
sql>@ObjectTypes.sql
Step 3
Edit the file ObjectTypes.java. In the method dbConnection(),
change the following line to your database connection parameters connection = DriverManager.getConnection("jdbc:oracle:thin:@<hostname>:<port>:<SID>",
"scott","tiger");
Step 4
Set CLASSPATH environment variable to include Oracle9i
JDBC Driver file: classes12.zip or classes12.jar or ojdbc14.jarAlso ensure that PATH system variable includes the
bin directory of JDK.
Bookmark
