I'm dealing with an Oracle 10g database and the following stored procedure is provided:
procedure get_synopsis (
p_id in my_schema.products.p_id%type,
p_synopses out sys_refcursor); -- cursor of - synopsis_type, synopsis_text
In my Java code I prepare the statement in this way:
String idForDb = fromIdUrlToIdDb(prodIdUrl);
statement.registerOutParameter(1, OracleTypes.VARCHAR);
statement.setString(1, idForDb );
statement.registerOutParameter(2, OracleTypes.CURSOR);
And I get the data I need in this way:
String defaultSyn, nonDefSyn;
String returnedId = ((OracleCallableStatement)stm).getString(1);
try ( ResultSet synopses = ((OracleCallableStatement)stm).getCursor(2) ){ // p_synopses - cursor of: synopsis_type, synopsis_text
while( synopses!=null && synopses.next() ){
String type = synopses.getString(1) != null ? synopses.getString(1).toUpperCase() : null;
if( type != null ){
StringBuilder sb = new StringBuilder();
BufferedReader br = new BufferedReader( synopses.getClob(2).getCharacterStream() );
String line;
while ((line = br.readLine()) != null) {
sb.append(line).append("\n");
}
if("DEFAULT".equals(type)){
defaultSyn = sb.toString();
}else if("NONDEFAULT".equals(type)){
nonDefSyn = sb.toString();
}
// ...
}
}
}
In my tests how can I mock (OracleCallableStatement)stm.getCursor(2)?
I'm trying with org.jmock.Mockery but without success:
Mockery mockery_inner = new Mockery();
final ResultSet mocked_resultset = mockery_inner.mock(ResultSet.class);
mockery_inner.checking(new Expectations() {{
allowing(mocked_resultset).getString(1); will(returnValue("TEST_SYNOPSES-TYPE"));
allowing(mocked_resultset).getClob(2); will(returnValue("TEST_CLOooooooB"));
}});
Mockery mockery = new Mockery();
final CallableStatement statement = mockery.mock(CallableStatement.class);
mockery.checking(new Expectations() {{
allowing(statement).getString(1); will(returnValue("TEST_RETURNED-PROD-ID"));
allowing(statement).getCursor(2); will(returnValue(mocked_resultset)); // cannot find symbol getCursor(int). Location: interface java.sql.CallableStatement
}});
Reason clearly is: cannot find symbol getCursor(int). Location: interface java.sql.CallableStatement.
If I try allowing((OracleCallableStatement)statement).getCursor(2) I get "java.lang.ClassCastException: com.sun.proxy.$Proxy6 cannot be cast to oracle.jdbc.driver.OracleCallableStatement". Note: OracleCallableStatement is not an interface and thus cannot be mocked with Mockery.
I'm trying to use a "manual" mock but I'm having problems creating an instance..
class MockOracleCallableStatement implements OracleCallableStatement {
ResultSet mocked_resultset;
public MockOracleCallableStatement(){
Mockery mockery_inner = new Mockery();
mocked_resultset = mockery_inner.mock(ResultSet.class);
mockery_inner.checking(new Expectations() {{
allowing(mocked_resultset).getString(1); will(returnValue("DEFAULT")); // will pick value from an array
allowing(mocked_resultset).getClob(2); will(returnValue("TEST_CLOooooooooooB"));
}});
}
@Override
ResultSet getCursor(int paramIndex) throws SQLException{
return mocked_resultset;
}
@Override
String getString(int paramIndex) throws SQLException{
return "mockedGetString1--test";
}
}
In a nutshell, DON'T.
Mocking JDBC (along with a lot of other things) is a fools errand and will not test the things you think it's testing but will cost you a huge amount of time.
You should really write an integration test that actually goes to your database. This is the only way to verify your database code is correct. If you can, use the exact same version of the database as you do in production, if not use an in-memory database like H2*.
I wrote an article for JAX magazine on this exact subject which will go into much more detail.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments