retrieve a Java object from stored procedure

Question

I'm a student and new to Java and stored procedure. I'm trying to write a stored procedure which is supposed to return a table row as java object.

I've this table: USER_TABLE(USERID, USERNAME, DOB)

& Procedure is :

 create or replace procedure "USER_OUT" (USERID in varchar2,UserObj out User)
 is 
 begin
 select * from user_table where 
 USERID = UserObj.USERID;

 end USER_OUT;

In Java I'm trying to call this procedure and retrieve the object as :-

CallableStatement callableStatement = dh.con.prepareCall("{call USER_OUT(?)}");
User usr = new User();
callableStatement .setInt (1,"123");
usr1 = (User)callableStatement.getResultSet();
callableStatement.registerOutParameter(1, OracleTypes.CURSOR);
callableStatement.executeUpdate();//getting exception as Object is invalid.

Is the procedure wrongly implemented or am I missing something ? Any help is really appreciated!! Thanks.

I think this will be helpfull: http://stackoverflow.com/questions/21956042/mapping-a-jdbc-resultset-to-an-object — Florian Schaetz, Jul 20 '15 at 12:21
sorry, I was just trying that out, I used ("{call USER_OUT(?,?)}"); also. — zy31, Jul 20 '15 at 12:21
How does one even use the Java 'User' type in SQL like that? And why is the USERID an accepted parameter (but then not used)? The SP itself is .. questionable. — user2864740, Jul 20 '15 at 12:25

SatyaTNV · Answer 1 · 2015-07-20T12:37:43.927

I think you suppose to trying to get a value by passing user id.

CallableStatement callableStatement = dh.con.prepareCall("{call USER_OUT(?,?)}");
callableStatement.setString(1, "123");
callableStatement.setString(2, OracleTypes.CURSOR);//use OracleTypes.CURSOR
callableStatement.executeUpdate();//execute USER_OUT store procedure
//read the OUT parameter now
// get cursor and cast it to ResultSet
ResultSet rs = (ResultSet) callableStatement.getObject(2);
while (rs.next()) {
String userid = rs.getString(1);
...............................
}

HLP · Answer 2 · 2015-07-20T12:42:59.300

You probably don't need that line:

callableStatement.registerOutParameter(1, OracleTypes.CURSOR);

I am not an Oracle guys, but I would think of something like:

create or replace procedure "USER_OUT" (pUSERID in varchar2)
is 
begin
select USERID, USERNAME, DOB from user_table
where USERID = pUSERID;
end

and

CallableStatement callableStatement = dh.con.prepareCall("{call USER_OUT(?)}");
callableStatement.setString("pUSERID","123");

ResultSet rs = callableStatement.executeQuery();
rs.next();
// Assuming you have a constructor that takes all parameter in
User usr = new User(rs.getInt("USERID"), rs.getString("USERNAME"), rs.getString("DOB"));

retrieve a Java object from stored procedure

2 Answers2