How can I unit test a class that uses SimpleJdbcCall

Question

I have a class that looks like this:

public class MyClass {

    private final SimpleJdbcCall simpleJdbcCall;

    public MyClass(final DataSource dataSource) {
        this(new JdbcTemplate(dataSource));
    }

    public MyClass(final JdbcTemplate template) {
        simpleJdbcCall = new SimpleJdbcCall(template)
            .withoutProcedureColumnMetaDataAccess()
            .withCatalogName("MY_ORACLE_PACKAGE")
            .withFunctionName("GET_VALUE")
            .withReturnValue()
            .declareParameters(
                new SqlOutParameter("RESULT", Types.VARCHAR))
            .declareParameters(
                new SqlParameter("P_VAR1_NAME", Types.VARCHAR))
            .declareParameters(
                new SqlParameter("P_VAR2_NAME", Types.VARCHAR))
            .useInParameterNames("P_VAR1_NAME", "P_VAR2_NAME");
    }

    private String getValue(final String input) {
        final SqlParameterSource params = new MapSqlParameterSource()
            .addValue("P_VAR1_NAME", input, Types.VARCHAR)
            .addValue("P_VAR2_NAME", null, Types.VARCHAR);
        return simpleJdbcCall.executeFunction(String.class, params);
    }
}

It works as expected, but I want to write a unit test for it and it's driving me crazy. I've tried mocking the JdbcTemplate (Mockito), but that leads to mocking connections, metadata, etc, and I get lost about the time callable statement factories come into play.

I guess I could write it so that the SimpleJdbcCall is passed as a parameter to a new constructor and then mock that, but that feels hackish. I'd prefer the test not affect the class unless it's to improve it.

I'd like to keep using this SimpleJdbcCall API. It writes the SQL for me so I don't have to mix SQL and Java, but I would also really like to test this thing without having to write 1000 lines of code. Can anyone see a good way to test this?

Stopgap

Answer 1

I too prefer not to inject 15 different SimpleJdbcCalls into my repository, so I bite the bullet and add this to my test setup method:

DatabaseMetaData metaData = mock(DatabaseMetaData.class);
Connection con = mock(Connection.class);
when(con.getMetaData()).thenReturn(metaData);
DataSource ds = mock(DataSource.class);
when(ds.getConnection()).thenReturn(con);
jdbcTemplate = mock(JdbcTemplate.class);
when(jdbcTemplate.getDataSource()).thenReturn(ds);

Apprehend

Answer 2

I would definitely take the approach of adding a constructor to allow the SimpleJdbcCall to be injected directly.

MyClass(SimpleJdbcCall simpleJdbcCall) {
  this.simpleJdbcCall = simpleJdbcCall;
}

(and probably invoke that constructor from the one which currently invokes new).

This isn't "hackish", it is simply Dependency Injection. I'd argue that making the class testable without needing to test the workings of SimpleJdbcCall is a definite improvement.

Invoking new in the constructor makes testing harder because it is a tight static coupling to the class being instantiated.

I found Miško Hevery's blog post on this topic very interesting.

Responser

DI is really meant for configurable dependencies, and not a good fit to replace stateful objects like a SimpleJdbcCall. And a dependency like this can be mocked just fine, so the code is easily testable already. – Verbose

"a dependency like this can be mocked just fine" How? (Genuine question) – Responser

Note that you can keep the configuration of SimpleJdbcCall inside the constructor; just allow the test to create the instance outside so the test can override executeFunction(). – Allemande

By using a mocking library (as opposed to a more basic "mock objects" library). For Java, we have PowerMockito and JMockit. – Verbose

@Rogério it sounds like you have a valid answer, would you consider writing it up more fully? – Responser

I added an example test in my answer. – Verbose

Answer 3

My first recommendation would be to not unit test it; write an integration test, which actually executes the stored function in the Oracle database (but rollback the transaction).

Otherwise, you can mock the SimpleJdbcCall class, with the code under test as is, by using PowerMockito or JMockit.

Example test with JMockit:

@Mocked DataSource ds;
@Mocked SimpleJdbcCall dbCall;

@Test
public void verifyDbCall() {
    String value = new MyClass(ds).getValue("some input");

    // assert on value

    new Verifications() {{
        SqlParameterSource params;
        dbCall.executeFunction(String.class, params = withCapture());
        // JUnit asserts on `params`
    }};
}

Liftoff

General comment: Integration tests are often a code smell ("don't know what they are doing") - If they knew, they could write a simple, small and efficient unit test. Since they don't, they just test "something" and lots of it but in the end, no one can really say what is tested and what isn't. – Allemande

I must profess unfamiliarity with JMockit: how is the dbCall in your example related to MyClass? – Responser

@AaronDigulla That's absurd. Nobody advocates doing only unit tests as you suggests, since they are not very good at actually finding bugs. The general consensus, AFAIK, is to have both unit tests and some kind of integration tests. Though, personally, I prefer to have only integration tests, since it has worked very well for me in several projects. – Verbose

@AndyTurner The dbCall instance assigned to the mock field is a "representative" of all SimpleJdbcCall instances used during the test; as such, it can be used when recording and/or verifying expectations that will match invocations on other instances. – Verbose

@Rogério: Different people make different experiences. I've seen a lot of projects where the production data was copied into the test and management thought this was enough. So I try to warn people that you need to understand what you're doing; just running a lot of tests on a lot of data isn't enough. – Allemande

Answer 4

Add an extra constructor:

/*test*/ MyClass(final SimpleJdbcCall call) {
    simpleJdbcCall = call
        .withoutProcedureColumnMetaDataAccess()
        .withCatalogName("MY_ORACLE_PACKAGE")
        .withFunctionName("GET_VALUE")
        .withReturnValue()
        .declareParameters(
            new SqlOutParameter("RESULT", Types.VARCHAR))
        .declareParameters(
            new SqlParameter("P_VAR1_NAME", Types.VARCHAR))
        .declareParameters(
            new SqlParameter("P_VAR2_NAME", Types.VARCHAR))
        .useInParameterNames("P_VAR1_NAME", "P_VAR2_NAME");
}

This one is package private, so other classes in the same package (=tests) can call it. This way, a test can create an instance that has executeFunction() overridden. You can return fake results in the method or test the state of the object.

That means your code still configures the object; the test just passes a "POJO" which the code under test fills out.

This way, you don't have to write a lot of code - the default implementation does most of the work for you.

Alternatively, allow to call a constructor with the interface SimpleJdbcCallOperations which means you need a powerful mocking framework or write a lot of boiler plate code.

Other alternatives: Use a mock JDBC driver. These are usually hard to set up, cause spurious test failures, when a test fails, you often don't really know why, ...

Or an in-memory database. They come with a whole bunch of problems (you need to load test data which you need to manufacture and maintain).

That's why I try to avoid the round-trip through the JDBC layer when I can. Assume that JDBC and the database works - other people have tested this code. If you do it again, you're just wasting your time.

Related:

• http://blog.pdark.de/2015/07/15/replacing-integration-with-unit-tests/
• http://blog.pdark.de/2009/06/05/testing-the-impossible-inserting-into-database/
• http://blog.pdark.de/2008/07/26/testing-with-databases/

Allemande

Answer 5

I did it using http://www.jmock.org/

XML configuration -

<bean id="simpleJDBCCall" class="org.springframework.jdbc.core.simple.SimpleJdbcCall">
    <property name="jdbcTemplate" ref="jdbcTemplate" />
</bean>

Java File -

@Autowired
private SimpleJdbcCall jdbcCall;

Test Class -

simpleJDBCCall = mockingContext.mock(SimpleJdbcCall.class);
mockingContext.checking(new Expectations() {
        { 
            oneOf(simpleJDBCCall).withSchemaName("test");
            will(returnValue(simpleJDBCCall));
            oneOf(simpleJDBCCall).withCatalogName("test");
            will(returnValue(simpleJDBCCall));
            oneOf(simpleJDBCCall).withProcedureName(ProcedureNames.TEST);
            will(returnValue(simpleJDBCCall));
            oneOf(simpleJDBCCall).execute(5);
            will(returnValue(testMap));
        }

Centering

Answer 6

Just wanted to share after scouring Google for some help and only finding partial solution and more questions than answers. I was able to test with this code. The bean for my repository which is the class under test injects a SimpleJDBCCall into the constructor. Inside the bean when it creates the SimpleJDBCCall it uses a JDBCTemplate as the argument. In my test I found you can just pass a datasource directly to the SimpleJdbcCall. I also had to use a spy SimpleJDBCCall instead of a mock. It would error on the withSchemaName in my repo code without that. Probbaly something I was doing wrong but I have not figured it out yet. (Names have been changed to protect the innocent.)

public void multiAccountSearchReport_test() throws SQLException {
    DatabaseMetaData databaseMetaData = mock(DatabaseMetaData.class);
    Connection con = mock(Connection.class);
    DataSource datasource = mock(DataSource.class);
    SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(datasource);
    SimpleJdbcCall simpleJdbcCallSpy = spy(simpleJdbcCall);

    
    when(datasource.getConnection()).thenReturn(con);
    when(con.getMetaData()).thenReturn(databaseMetaData);
    when(simpleJdbcCallSpy.withCatalogName(any())).thenReturn(simpleJdbcCallSpy);
    when(simpleJdbcCallSpy.withProcedureName(any())).thenReturn(simpleJdbcCallSpy);
    when(simpleJdbcCallSpy.withSchemaName(any())).thenReturn(simpleJdbcCallSpy);

    SalesRepository repo = new SalesRepository(simpleJdbcCallSpy);
    MultiAccountSearchParameters parms = new MultiAccountSearchParameters();
    Map<String, Object> linkedList = new LinkedCaseInsensitiveMap<>();
    List listInner= new ArrayList<>();
    linkedList.put("ID", "1001");
    linkedList.put("FIRST_NAME", "first_name");
    linkedList.put("LAST_NAME", "last_name");
    linkedList.put("ADDRESS", "address");
    listInner.add(linkedList);
    Map map = new LinkedHashMap<String,Object>();
    map.put("result-list-1",listInner);


    doReturn(map).when(simpleJdbcCallSpy).execute((SqlParameterSource)any());
    parms.setApcApproval("1");
    List<Map<String,String>> response = null;
    try {
        response = repo.multiAccountSearchReport(parms);
    }catch(Exception ex) {
        String message = ex.getMessage();
    }

    assertEquals(response.get(0).get("ID"),"1001");

}

Yuletide