Mock Oracle CallableStatement.getCursor()

Gabe

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";
    }
}
tddmonkey

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.

  • Although this has other issues due to compatibility

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

USe CallableStatement for Oracle Functions with ROWTYPE Parameters

From Dev

Oracle Autoincrement Functionality: Triggers or Oracle JDBC CallableStatement in 11.2?

From Dev

Oracle from Java. CallableStatement and Null for object parameter?

From Dev

Closing resultSet and callableStatement

From Dev

Performance Degradation on CallableStatement

From Dev

A CallableStatement was executed with nothing returned

From Dev

Execute a CallableStatement blocks the application

From Dev

A CallableStatement was executed with nothing returned

From Dev

Is CallableStatement really immune to SQL injection?

From Dev

Can CallableStatement be used in place of PreparedStatement?

From Dev

CallableStatement.getResultSet() giving null

From Dev

Java get output CallableStatement JDBC

From Dev

Is CallableStatement really immune to SQL injection?

From Dev

JDBC How to create a procedure using CallableStatement?

From Dev

Retrieve ResultSet using CallableStatement after executeBatch()

From Dev

Setting multiple CallableStatement variables with nested SQL queries

From Dev

Callablestatement error : Missing IN or OUT parameter at index:: 1

From Dev

Java CallableStatement registerOutParameter, what does it do?

From Dev

Callablestatement error : Missing IN or OUT parameter at index:: 1

From Dev

Java CallableStatement registerOutParameter, what does it do?

From Dev

char encoding doing mysql insert with callablestatement or jdbctemplate

From Dev

How do you get multiple resultset from a single CallableStatement?

From Dev

How to pass an NULL (or empty) array to a JDBC callableStatement (that expects an Array)

From Dev

"Parameter is not an OUT parameter" error while calling stored procedure via CallableStatement

From Dev

Invalid SQL statement with CallableStatement during Stored proc call

From Dev

CallableStatement getResultSet returns null when using output parameter

From Dev

Executing stored procedure from Java, other than with JDBC CallableStatement

From Dev

Java/PostgreSQL- CallableStatement.setBoolean null throws NPE

From Dev

java CallableStatement failed, while calling function of postgreSQL enterprise edition

Related Related

  1. 1

    USe CallableStatement for Oracle Functions with ROWTYPE Parameters

  2. 2

    Oracle Autoincrement Functionality: Triggers or Oracle JDBC CallableStatement in 11.2?

  3. 3

    Oracle from Java. CallableStatement and Null for object parameter?

  4. 4

    Closing resultSet and callableStatement

  5. 5

    Performance Degradation on CallableStatement

  6. 6

    A CallableStatement was executed with nothing returned

  7. 7

    Execute a CallableStatement blocks the application

  8. 8

    A CallableStatement was executed with nothing returned

  9. 9

    Is CallableStatement really immune to SQL injection?

  10. 10

    Can CallableStatement be used in place of PreparedStatement?

  11. 11

    CallableStatement.getResultSet() giving null

  12. 12

    Java get output CallableStatement JDBC

  13. 13

    Is CallableStatement really immune to SQL injection?

  14. 14

    JDBC How to create a procedure using CallableStatement?

  15. 15

    Retrieve ResultSet using CallableStatement after executeBatch()

  16. 16

    Setting multiple CallableStatement variables with nested SQL queries

  17. 17

    Callablestatement error : Missing IN or OUT parameter at index:: 1

  18. 18

    Java CallableStatement registerOutParameter, what does it do?

  19. 19

    Callablestatement error : Missing IN or OUT parameter at index:: 1

  20. 20

    Java CallableStatement registerOutParameter, what does it do?

  21. 21

    char encoding doing mysql insert with callablestatement or jdbctemplate

  22. 22

    How do you get multiple resultset from a single CallableStatement?

  23. 23

    How to pass an NULL (or empty) array to a JDBC callableStatement (that expects an Array)

  24. 24

    "Parameter is not an OUT parameter" error while calling stored procedure via CallableStatement

  25. 25

    Invalid SQL statement with CallableStatement during Stored proc call

  26. 26

    CallableStatement getResultSet returns null when using output parameter

  27. 27

    Executing stored procedure from Java, other than with JDBC CallableStatement

  28. 28

    Java/PostgreSQL- CallableStatement.setBoolean null throws NPE

  29. 29

    java CallableStatement failed, while calling function of postgreSQL enterprise edition

HotTag

Archive