(Originally posted on the “old” Jason Bennett’s Developer Corner, Sunday, August 17, 2008)
I recently had the challenge of creating several complex query search screens using ADF Faces. These new search screens had to integrate seamlessly into our current application’s (not a J2EE application …) search screens (look the same, act the same, feel the same …). One of the challenges that presented itself was to determine how to pass multiple search parameters with various operators (=, <, LIKE,SOUNDEX, etc …) and the values associated with them and map them to their associated columns in a dynamic where clause. Since I was creating multiple screens with similar functionality, I needed a generic method of parameter passing. The obvious answer was to use some sort of array or combination of arrays as the parameter transport mechanism. The not so obvious part was how the heck I was going to pass an array of Java Objects to a PL/SQL function that was expecting a PL/SQL Collection object. Luckily, this turned out to be less complicated than I thought thanks to Oracle and the developers of JDBC. The Java to PL/SQL user-defined type mapping is accomplished using the java.sql.SQLData interface (http://java.sun.com/j2se/1.5.0/docs/api/java/sql/SQLData.html). This interface was designed to allow for the mapping between a SQL UDT (user defined type) and a Java class. The mapping of an array of these objects to an Oracle collection (or array) is accomplished using two Oracle JDBC classes: oracle.sql.ARRAY (http://www.oracle.com/technology/docs/tech/java/sqlj_jdbc/doc_library/javadoc/oracle.sql.ARRAY.html) and oracle.sql.ArrayDescriptor (http://www.oracle.com/technology/docs/tech/java/sqlj_jdbc/doc_library/javadoc/oracle.sql.ArrayDescriptor.html). Next problem … how to create a custom (generic) Programmatic ViewObjectImpl class that will accept a Collection object as the parameter passing mechanism. This turned out to be another simple task with the help of some Oracle ADF documentation: (http://download.oracle.com/docs/cd/B31017_01/web.1013/b25947/bcadvvo008.htm). After that, it’s elementary! All of the View Objects defined for each of my custom search screens were extended from this new custom ViewObjectImpl class. The rest of this article details the steps and code I used to implement the solution. For the sake of brevity, I will limit the code to just the generic ViewObjectImpl and PL/SQL collection mapping. (The PL/SQL package used to implement the searches has some interesting bits of code, and I will publish that in a separate article.)
Step 1. Create SQL User Defined Types
Step one is to create the SQL user defined types. In this instance I created two types. The base type SEARCH_PARAM_TYPE and a collection of SEARCH_PARAM_TYPE called SEARCH_PARAM_ARRAY. These two user defined types are defined by the following commands:
CREATE OR REPLACE TYPE SEARCH_PARAM_TYPE AS OBJECT(
PARAM_NAME VARCHAR2(100),
PARAM_OPERATOR VARCHAR2(4),
PARAM_VALUE VARCHAR2(1000)
)
/
CREATE OR REPLACE TYPE SEARCH_PARAM_ARRAY AS TABLE OF SEARCH_PARAM_TYPE
/
The SEARCH_PARAM_TYPE contains a parameter name (or label or identifier), a parameter query operator code, and the value for the particular parameter. The SEARCH_PARAM_ARRAY is just a simple array (PL/SQL Table) that may hold 0 or more SEARCH_PARAM_TYPES. Each of these SQL types gets mapped in the Java code.
Step 2. Create Java Class to Match SQL Type SEARCH_PARAM_TYPE
Step two is to create a Java class to match the SQL user defined type SEARCH_PARAM_TYPE. This class must implement java.sql.SQLData and java.io.Serializable. The code below demonstrates how to implement the class that matches the SQL type SEARCH_PARAM_TYPE:
import java.io.Serializable;
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;
/***
* This class maps to an Oracle User Defined Type called
* SEARCH_PARAM_TYPE. It hold search parameter data
* consisting of a parameter name, operator code,
* and parameter value.
*
*/
public class SearchParamBean implements SQLData, Serializable{
/* Oracle User Defined Type Name */
private String sql_type = ”SEARCH_PARAM_TYPE”;
/* These String represent the attributes in the Oracle user defined type. */
Private String param_name = null;
private String param_operator = null;
private String param_value = null;
public SearchParamBean() {
}
public void setParamName(String p_param_name){
param_name = p_param_name;
}
public String getParamName(){
return param_name;
}
public void setParamOperator(String p_param_operator){
param_operator = p_param_operator;
}
public String getParamOperator(){
return param_operator;
}
public void setParamValue(String p_param_value){
param_value = p_param_value;
}
public String getParamValue(){
return param_value;
}
public String getSQLTypeName()
throws SQLException
{
return sql_type;
}
public void readSQL(SQLInput stream, String typeName)
{
/* No need to implement this */
}
public void writeSQL(SQLOutput stream) throws SQLException
{
/*
the order of values matters! The order must match the structure
of the user defined type exactly!
*/
stream.writeString(param_name);
stream.writeString(param_operator);
stream.writeString(param_value);
}
}
As you can see, this is a pretty straight forward class. It is important to note that in the writeSQL method above, the order attribute assignements defined with the stream.writeString statements must match the order of attributes as defined in the SQL user defined type exactly (under the covers I’m sure it has to do with marshalling and unmarshalling the serialized objects and not knowing how to intuitively identify what value goes where).
Step 3. Create the Custom ViewObjectImpl Class
The third step is to create the (generic) custom ViewObjectImpl class that your actual ViewObjects will extend. As stated at the start of this article, I made use of the example in the Oracle ADF documentation that details how to create a View Object using alternate data sources. The key piece of code is the callStoredFunction method:
/**
* Mechanism to execute PL/SQL functions that populate rows and data
* for the ViewObject
* @param sqlReturnType
* @param stmt
* @param params
* @return
*/
protected Object callStoredFunction(int sqlReturnType,
String stmt,
SearchParamBean[] params) {
CallableStatement plsqlStmt = null;
StringBuffer sb_plsqlBlock = new StringBuffer();
Object dataSet = new Object();
try {
sb_plsqlBlock.append(”begin ? := ”).append(stmt).append(”; end;”);
plsqlStmt = getDBTransaction().createCallableStatement(
sb_plsqlBlock.toString(),0);
/* Register the first bind variable for the return value */
plsqlStmt.registerOutParameter(1, sqlReturnType);
/*This is where we map the Java Object Array to a PL/SQL Array */
ArrayDescriptor desc = ArrayDescriptor.createDescriptor(arrayDescriptor,plsqlStmt.getConnection());
ARRAY objectArray = new ARRAY (desc, plsqlStmt.getConnection(), params);
((OraclePreparedStatement) plsqlStmt).setARRAY(2,objectArray);
plsqlStmt.executeUpdate();
dataSet = plsqlStmt.getObject(1);
}
catch (SQLException e) {
throw new JboException(e);
}
finally {
if (plsqlStmt != null) {
try {
plsqlStmt.close();
}
catch (SQLException e) {System.err.println(e.getMessage());}
}
}
return dataSet;
}
The custom ViewObjectImpl also has methods that construct the ArrayList containing SearchParamBeans. There are getter and setter methods provided in the class for adding and retrieving SearchParamBean objects. The SearchParamBean[] array you see in the code above is generated internally by the method:
/**
* Constructs the SearchParamBean array from the searchParams ArrayList
* @return
*/
protected SearchParamBean[] getParamBeanArray(){
return (SearchParamBean[])searchParams.toArray(new SearchParamBean[searchParams.size()]);
}
Rather than explain each method in detail, I’ll provide the code with appropriate comments. It’s fairly straight forward code:
import adf.custom.beans.SearchParamBean;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import oracle.jbo.JboException;
import oracle.jbo.server.ViewObjectImpl;
import oracle.jbo.server.ViewRowImpl;
import oracle.jbo.server.ViewRowSetImpl;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.driver.OraclePreparedStatement;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
public class ObjectArrayVOImpl extends ViewObjectImpl{
/**
* Number of columns in result set returned by this VO.
*/
private int numberOfVOColumns = 0;
/**
* Name of PL/SQL function that will execute the query
* for the VO implemented by this code.
*/
private String queryFunction = null;
/**
* Name of PL/SQL function that will return the
* number of rows returned by this VO.
*/
private String countFunction = null;
/**
* Name of SQL User Defined Type/ PLSQL Collection Type
*/
private String arrayDescriptor = null;
/**
* ArrayList that holds SearchParamBean objects.
*/
private ArrayList searchParams = new ArrayList();
public ObjectArrayVOImpl() {
}
/**
* This method adds a SearchParamBean object to the ArrayList that
* is mapped to the PL/SQL Collection
* @param p_param_bean
*/
public void addSearchParamBean(SearchParamBean p_param_bean){
searchParams.add(p_param_bean);
}
public void clearSearchParamArray(){
searchParams.clear();
}
/**
* This method is used to set the name of the PL/SQL (packaged) Function that
* will accept the collection of parameters and execute a query returning the
* desired results.
* @param p_value
*/
public void setQueryFunction(String p_value){
queryFunction = p_value;
}
/**
* This method is used to set the name of the PL/SQL (packaged) Function that
* will return the number of rows that will be returned to the View Object from
* the PL/SQL package result set.
* @param p_value
*/
public void setCountFunction(String p_value){
countFunction = p_value;
}
/**
* The array descriptor is is used by the method callStoredFunction
* to map the SearchParamBean[] to the correct PL/SQL Array or SQL
* Type in the backing database.
* @param p_value
*/
public void setArrayDescriptor(String p_value){
arrayDescriptor = p_value;
}
public void setNumberOfVOColumns(int p_value){
numberOfVOColumns = p_value;
}
/**
* Returns the most current ArrayList containing search parameter beans.
* @return ArrayList
*/
public ArrayList getSearchParamArrayList(){
return searchParams;
}
/**
* Constructs the SearchParamBean array from the searchParams ArrayList
* @return
*/
protected SearchParamBean[] getParamBeanArray(){
return (SearchParamBean[])searchParams.toArray(new SearchParamBean[searchParams.size()]);
}
/**
* Overridden framework method.
*
* The role of this method is to ”fetch”, populate, and return a single row
* from the datasource by calling createNewRowForCollection() and populating
* its attributes using populateAttributeForRow().
*/
protected ViewRowImpl createRowFromResultSet(Object qc, ResultSet rs) {
/*
* We ignore the JDBC ResultSet passed by the framework (null anyway) and
* use the resultset that we’ve stored in the query-collection-private
* user data storage
*/
rs = getResultSet(qc);
/*
* Create a new row to populate
*/
ViewRowImpl r = createNewRowForCollection(qc);
try {
/*
* Populate new row by attribute slot number for current row in Result Set
*/
for (int x=0; x
populateAttributeForRow(r,x,rs.getString(x+1));
}
}
catch (SQLException s) {
throw new JboException(s);
}
return r;
}
/**
* Overridden framework method.
*
* Return true if the datasource has at least one more record to fetch.
*/
protected boolean hasNextForCollection(Object qc) {
ResultSet rs = getResultSet(qc);
boolean nextOne = false;
try {
nextOne = rs.next();
/*
* When were at the end of the result set, mark the query collection
* as “FetchComplete”.
*/
if (!nextOne) {
setFetchCompleteForCollection(qc, true);
/*
* Close the result set, we’re done with it
*/
rs.close();
}
}
catch (SQLException s) {
throw new JboException(s);
}
return nextOne;
}
/**
* Overridden framework method.
*
* The framework gives us a chance to clean up any resources related
* to the datasource when a query collection is done being used.
*/
protected void releaseUserDataForCollection(Object qc, Object rs) {
/*
* Ignore the ResultSet passed in since we’ve created our own.
* Fetch the ResultSet from the User-Data context instead
*/
ResultSet userDataRS = getResultSet(qc);
if (userDataRS != null) {
try {
userDataRS.close();
}
catch (SQLException s) {
/* Ignore */
}
}
super.releaseUserDataForCollection(qc, rs);
}
/**
* Mechanism to execute PL/SQL functions that populate rows and data
* for the ViewObject
* @param sqlReturnType
* @param stmt
* @param params
* @return
*/
protected Object callStoredFunction(int sqlReturnType,
String stmt,
SearchParamBean[] params) {
CallableStatement plsqlStmt = null;
StringBuffer sb_plsqlBlock = new StringBuffer();
Object dataSet = new Object();
try {
sb_plsqlBlock.append(”begin ? := ”).append(stmt).append(”; end;”);
plsqlStmt = getDBTransaction().createCallableStatement(
sb_plsqlBlock.toString(),0);
/* Register the first bind variable for the return value */
plsqlStmt.registerOutParameter(1, sqlReturnType);
/*This is where we map the Java Object Array to a PL/SQL Array */
ArrayDescriptor desc = ArrayDescriptor.createDescriptor(arrayDescriptor,plsqlStmt.getConnection());
ARRAY objectArray = new ARRAY (desc, plsqlStmt.getConnection(), params);
((OraclePreparedStatement) plsqlStmt).setARRAY(2,objectArray);
plsqlStmt.executeUpdate();
dataSet = plsqlStmt.getObject(1);
}
catch (SQLException e) {
throw new JboException(e);
}
finally {
if (plsqlStmt != null) {
try {
plsqlStmt.close();
}
catch (SQLException e) {System.err.println(e.getMessage());}
}
}
return dataSet;
}
/**
* Return a JDBC ResultSet representing the REF CURSOR return
* value from our stored package function.
*/
private ResultSet retrieveRefCursor(Object qc, SearchParamBean[] params) {
ResultSet rs = (ResultSet)callStoredFunction(OracleTypes.CURSOR,
queryFunction,
params);
return rs ;
}
/**
* Retrieve the result set wrapper from the query-collection user-data
*/
private ResultSet getResultSet(Object qc) {
return (ResultSet)getUserDataForCollection(qc);
}
/**
* Store a new result set in the query-collection-private user-data context
*/
private void storeNewResultSet(Object qc, ResultSet rs) {
ResultSet existingRs = getResultSet(qc);
/* If this query collection is getting reused, close out any previous rowset */
if (existingRs != null) {
try {existingRs.close();} catch (SQLException s) {}
}
setUserDataForCollection(qc,rs);
hasNextForCollection(qc); // Prime the pump with the first row.
}
/**
* Overridden framework method.
*/
protected void create() {
getViewDef().setQuery(null);
getViewDef().setSelectClause(null);
setQuery(null);
}
/**
* Overridden framework method.
*/
public long getQueryHitCount(ViewRowSetImpl viewRowSet) {
Long count = Long.valueOf(“0″);
if (!searchParams.isEmpty()){
count= (Long)callStoredFunction(OracleTypes.BIGINT,
countFunction,
getParamBeanArray());
}
return count.longValue();
}
/**
* Overridden framework method.
*/
protected void executeQueryForCollection(Object qc,Object[] params,
int numUserParams) {
if (!searchParams.isEmpty()){
storeNewResultSet(qc,retrieveRefCursor(qc,getParamBeanArray()));
super.executeQueryForCollection(qc, params, numUserParams);
}
}
}
Step 4. Create a View Object based on the Custom ViewObjectImpl
The last step is to create a new ViewObject based on the custom ViewObjectImpl that was created in step three. The easiest way to do this is to use the JDeveloper View Object creation wizard. Select the ”Rows populated programmatically” option. Add any attributes (return columns) you need. Under the Java section of the View Object definition, and set the VO to extend the new ViewObjectImpl class. Edit the new View Object class and configure it to use the correct PL/SQL (packaged) functions. Here is an example:
import adf.custom.model.customViewObjectImpl.ObjectArrayVOImpl;
/* ———————————————————————
— File generated by Oracle ADF Business Components Design Time.
— Custom code may be added to this class.
— Warning: Do not modify method signatures of generated methods.
———————————————————————*/
public class EmployeeQueryVOImpl extends ObjectArrayVOImpl {
/**This is the default constructor (do not remove)
*/
public EmployeeQueryVOImpl() {
/*
Set the name of the PL/SQL function that will act as the
query execution agent. This function will also return
a result set.
*/
this.setQueryFunction(”EmployeeSearch.executeQuery”);
/*
Set the name of the PL/SQL function that will returns the
row count of the query result set for this VO.
*/
this.setCountFunction(”EmployeeSearch.getRowReturnCount”);
/*
This sets the name of the SQL Type representing the PL/SQL collection
or PL/SQL table.
*/
this.setArrayDescriptor(”SEARCH_PARAM_ARRAY”);
/*
The VO needs to know how many columns of data will be returned when
constructing the VO’s result set.
*/
this.setNumberOfVOColumns(5);
}
}
Wrapping it Up …
This article illustrated how to create a custom ViewObjectImpl class that passes an array of Java objects to a PL/SQL Collection based upon a user defined type. The article touched on a lot of peripheral topics: Creating custom ViewObjectImpl’s, mapping Java objects to SQL user defined types, and converting a Java Object Array into an Oracle Collection/Array. Basically, it covered a lot of ground in a very brief text. If you have questions (or suggestions) please feel to shoot me an email.
