Passing Arrays to SP

Calling a SP (Stored procedure) by passing primitive types such as int, strings is not a big deal..
but for some scenarios we might need to pass a collection to a SP to get our work done,,,

for example say we need to persist a collection of users list in our system to the DB table, assume the list size is 100+ , simple program can be used to persist a single user data record in to the DB and we might loop this function to persist that kind of a user lot...

how about passing a user list directly to the SP and using a single DB connection(JDBC connection) to get this work done,,so that a single DB connection is used to persist 100+ data records..or even 1000+ ..

for this we can use Oracle StructDescriptor and ArrayDescriptor ..

1. Create struct descriptor and Array descriptor for the passing object type and Array.

Note: we have to get OracleConnection for this, below code sample is for a Jboss deployed Data access class,
T_USER_LOGIN and T_USER_LOGIN_TABLE are required at oracle DB level as Oracle Object type & oracle collection type respectively.


StructDescriptor userLoginsDesc = StructDescriptor.createDescriptor("T_USER_LOGIN",
(OracleConnection) ((WrappedConnection) conn).getUnderlyingConnection());

ArrayDescriptor userLoginsTableDesc = ArrayDescriptor.createDescriptor(
"T_USER_LOGIN_TABLE", (OracleConnection) ((WrappedConnection) conn)
.getUnderlyingConnection());



2. Create STRUCT array to pass in,

if (userLogins != null && userLogins.size() > 0) {
for (Login bean : userLogins) {

Object[] recUsrLogin = new Object[3];

recUsrLogin[0] = 1;
recUsrLogin[1] = bean.getLoginName();
recUsrLogin[2] = bean.getPassword();

STRUCT loginsStruct = new STRUCT(
userLoginsDesc,
(OracleConnection) ((WrappedConnection) conn).getUnderlyingConnection(),
recUsrLogin);

userLoginsArr[index] = loginsStruct;

++index;

}

}


ARRAY pUsrLoginsarr = new ARRAY(userLoginsTableDesc,
(OracleConnection) ((WrappedConnection) conn).getUnderlyingConnection(),
userLoginsArr);


3.Set the Array to the collable statement.

ocs = (CallableStatement) conn
.prepareCall("YOUR_SP_NAME");

ocs.setArray(3, pUsrLoginsarr);

ocs.registerOutParameter(3, OracleTypes.ARRAY, "T_USER_LOGIN_TABLE");


Note : you have to register this array type as a out parameter to the Collable statement..


ocs.execute(); // execute it,, that's All..

wasNull () ?

Java SQL resultset that we use to get values has a wasNull() method to check whether the last retrieved value is actually null or not...

for instance we use simply,
rs.getXXX("column_name") to get DB column values for our Results,.

but for getByte, getShort, getInt, getLong, getFloat, and getDouble these will return by default a 0 value if the actual resulted column value is a JDBC NULL. and for boolean, false for getBoolean().

this is a point where the developer has to concern about the real outcome of the results, given by a JDBC resultset..

in my case i had a kind of web service to execute a scheduled JOB . where this Webservice call will initiate some sort of Data persistence in the oracle DB. at the end of the service call we have to create a validation XML file, to log all the data string we sent to the DB and the resulted DB values. this file is used to compare the input data set and resulted out put data set from the DB.

so each time when the original web service call contains null values for some of NULL allowed columns , we save those NULL values correctly to our local DB and DB values are 100% OK with the original data set we used. but at the time of data verification it turns into a failure.....

it took some times to identify that composing the result XML string, we are using rs.getXXX 's to retrieve values for those columns and due to that all the NULL integer fields are returning as 0 for the result.

SQL result set has a method called rs.wasNull() to handle this kind of scenarios. so that the said method will return true or false according to the last retrieved value is actual a NULL or not.

Note: we have to call this wasNull before any other getXXX() is called for the resultset.


int lotSize = rs.getInt("lot_size");
if(rs.wasNull()){
// it is Null
}else{
// is not NUll
}


i really wonder how EJB3 queries handle this, worth to have a look on this ..
is it the EJB3 - entity bean's

@Column(name = "column_name",nullable= true)

annotation doing the magic..?