How can I test a Spring controller that uses JDBC?

Question

I have a Spring controller that looks like this:

@RestController
@RequestMapping("/foo")
public class FooController {
    @Autowired
    private NamedParameterJdbcTemplate template;

    @GetMapping("/{name}")
    public List<Foo> byName(@PathVariable String name) {
        Map<String, String> params = new HashMap<>();
        params.put("name", name);
        List<Foo> result = template.query("SELECT * FROM FOOS WHERE FOO_NM = :name", params, new FooRowMapper());
        if (result.size() == 0) {
            throw new ResponseStatusException(HttpStatus.NOT_FOUND, String.format("foo %s not found", name));
        }
        return result;
    }
}

However, I'm not sure how to test this. I can do a basic "Spring can set it up" test:

@SpringBootTest
public class FooControllerTest {
    @Autowired
    private FooController controller;

    @Test
    public void canCreate() {
        Assertions.assertNotNull(controller);
    }
}

But I'm not certain what the right way to test, for example, the byName method is. Do I need to mock something? Can I just test it like a plain Java method (call it with whatever parameters and assert the output)?

Answer 1

After a bit of research and help from the posted answers, I moved the retrieval code into a service:

@Service
public class FooService implements IFooService {
    @Autowired
    private NamedParameterJdbcTemplate template;

    // the only method of IFooService
    @Override
    public List<FormulaFunction> getAllByName(String name) {
        Map<String, String> params = new HashMap<>();
        params.put("name", name);
        return template.query("SELECT * FROM FOOS WHERE FOO_NM = :name", params, new FooRowMapper());
    }
}

In testing the controller, that service is mocked using Mockito:

@WebMvcTest(FooController.class)
public class FooControllerTest {
    @MockBean
    private IFooService service;

    @Autowired
    private FooController controller;

    @Autowired
    private MockMvc mvc;

    @Test
    public void canCreate() {
        Assertions.assertNotNull(controller);
    }

    /**
     * Test that controller properly returns 404 when no results are found
     */
    @Test
    public void test404() throws Exception {
        doReturn(new ArrayList<>()).when(service).getAllByName("bars");
        this.mvc.perform(get("/foo/bars")).andExpect(status().isNotFound());
    }
}