CODEWITHSUNDEEP
javaunit-testingmockingmockitospring-jdbc
Matt Malone
Matt Malone

Reputation: 361

How can I unit test a class that uses SimpleJdbcCall

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?

Upvotes: 7

Views: 8156

Answers (6)

howserss
howserss

Reputation: 1159

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");

}

Upvotes: 0

Jeff E
Jeff E

Reputation: 688

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);

Upvotes: 4

Java_Alert
Java_Alert

Reputation: 1179

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));
        }

Upvotes: 0

Rog&#233;rio
Rog&#233;rio

Reputation: 16380

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`
    }};
}

Upvotes: 0

Aaron Digulla
Aaron Digulla

Reputation: 328556

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:

Upvotes: 0

Andy Turner
Andy Turner

Reputation: 140309

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.

Upvotes: 0

Related Questions