Sometimes user needs to create View Object based on procedure or function which returns Ref cursor. For this type of requirement ADF framework provides programmatic VO option. In this tutorial I’ll explain you how we can create View Object using Cursor.
To create programmatic View Object you have to override following methods of View Object Impl Class.
1. create()
2. executeQueryForCollection()
3. hasNextForCollection()
4. createRowFromResultSet()
5. getQueryHitCount()
6. protected void releaseUserDataForCollection()
Here I’m using a function which returns Ref Cursor
CREATE OR REPLACE FUNCTION HR.FUNC_returncursor
RETURN SYS_REFCURSOR
AS
REF_TEST SYS_REFCURSOR;
BEGIN
OPEN REF_TEST FOR
SELECT employee_id,first_name,phone_number,salary
FROM employees;
RETURN REF_TEST;
END;
To create the read-only programmatic view object:
1. In the Application Navigator, right-click the project in which you want to create the view object and choose New.
2. In the New Gallery, expand Business Tier, select ADF Business Components and then View Object, and click OK.
3. In the Create View Object wizard, in the Name page, provide a name and package for the view object. For the data source, select Rows populated programmatically, not based on a query.
4. In the Attributes page, click New one or more times to define the view object attributes your programmatic view object requires.
5. In the Attribute Settings page, adjust any setting you may need to for the attributes you defined.
6. In the Java page, select Generate View Object Class to enable a custom view object class (ViewObjImpl) to contain your code.
7. Click Finish to create the view object.
Code of ViewObject Impl class which override above define method.
package com.in.adftutorials.model;
import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import oracle.jbo.JboException;
import oracle.jbo.server.DBTransaction;
import oracle.jbo.server.ViewObjectImpl;
import oracle.jbo.server.ViewRowImpl;
import oracle.jbo.server.ViewRowSetImpl;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
// ---------------------------------------------------------------------
// --- File generated by Oracle ADF Business Components Design Time.
// --- Sat Apr 20 11:40:36 IST 2013
// --- Custom code may be added to this class.
// --- Warning: Do not modify method signatures of generated methods.
// ---------------------------------------------------------------------
public class EmpVoImpl extends ViewObjectImpl {
/**
* This is the default constructor (do not remove).
*/
public EmpVoImpl() {
}
protected void create() {
getViewDef().setQuery(null);
getViewDef().setSelectClause(null);
setQuery(null);
}
/**
* executeQueryForCollection - overridden for custom java data source support.
*/
protected void executeQueryForCollection(Object qc, Object[] params,
int noUserParams) {
storeNewResultSet(qc, retrieveParamsResultSet(qc, params));
super.executeQueryForCollection(qc, params, noUserParams);
}
private ResultSet retrieveParamsResultSet(Object qc, Object[] params) {
ResultSet rs =getStoredProcParams();
return rs;
}
public ResultSet getStoredProcParams() {
Connection conn = null;
ResultSet rs = null;
try {
String sp = "{? = call func_returncursor()}";
CallableStatement proc = null;
proc = this.getDBTransaction().createCallableStatement(sp,0);
proc.registerOutParameter(1, OracleTypes.CURSOR);
proc.executeQuery();
rs= ((OracleCallableStatement)proc).getCursor(1);
} catch (SQLException sqlerr) {
throw new JboException(sqlerr);
}
return rs;
}
private void storeNewResultSet(Object qc, ResultSet rs) {
ResultSet existingRs = (ResultSet)getUserDataForCollection(qc);
// If this query collection is getting reused, close out any previous rowset
if (existingRs != null) {
try {
existingRs.close();
} catch (SQLException e) {
throw new JboException(e);
}
}
setUserDataForCollection(qc, rs);
hasNextForCollection(qc); // Prime the pump with the first row.
}
/**
* hasNextForCollection - overridden for custom java data source support.
*/
protected boolean hasNextForCollection(Object qc) {
ResultSet rs = (ResultSet)getUserDataForCollection(qc);
boolean nextOne = false;
if (rs != null) {
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;
}
/**
* createRowFromResultSet - overridden for custom java data source support.
*/
protected ViewRowImpl createRowFromResultSet(Object qc,
ResultSet resultSet) {
resultSet = (ResultSet)getUserDataForCollection(qc);
/*
* Create a new row to populate
*/
ViewRowImpl r = createNewRowForCollection(qc);
if (resultSet != null) {
try {
/*
* Populate new row by attribute slot number for current row in Result Set
*/
populateAttributeForRow(r, 0,
resultSet.getString(1));
populateAttributeForRow(r, 1,
resultSet.getString(2));
populateAttributeForRow(r, 2,
resultSet.getString(3));
populateAttributeForRow(r, 3,
resultSet.getString(4));
} catch (SQLException s) {
throw new JboException(s);
}
}
return r;
}
protected void releaseUserDataForCollection(Object qc, Object rs) {
ResultSet userDataRS = (ResultSet)getUserDataForCollection(qc);
if (userDataRS != null) {
try {
userDataRS.close();
} catch (SQLException s) {
/* Ignore */
}
}
super.releaseUserDataForCollection(qc, rs);
}
/**
* getQueryHitCount - overridden for custom java data source support.
*/
public long getQueryHitCount(ViewRowSetImpl viewRowSet) {
/* Object[] params = viewRowSet.getParameters(true);
BigDecimal id = (BigDecimal)params[0];
CallableStatement st = null;
try {
st = getDBTransaction().createCallableStatement(COUNTSQL,
DBTransaction.DEFAULT);
st.registerOutParameter(1,Types.NUMERIC);
if (id == null) st.setNull(2,Types.NUMERIC);
else st.setBigDecimal(2,id);
st.execute();
return st.getLong(1);
}
catch (SQLException s) {
throw new JboException(s);
}
finally {try {st.close();} catch (SQLException s) {}} */
// long value = super.getQueryHitCount(viewRowSet);
//return value;
return 108;
}
}Result
Sample Code
