During one of my recent Spring and Spring Boot training course, I talked about the three major ways to access relational data from Spring:
- Send already-worked-out SQL using the
JdbcTemplate, - Work with Hibernate by injecting the JPA
EntityManagerusing the@PersistenceContextannotation, and - Simply extend one of the Spring Data interfaces, like
CrudRepository.
I told my students how I always struggle to implement the findById method using the JdbcTemplate. The method signature is:
Optional<Officer> findById(Integer id)
If the id exists in the database, I want to return that Officer, wrapped in an Optional. If the id does not exist, I want to return an empty Optional. My original hope was that I could call the queryForObject method, and wrap the result in an Optional:
jdbcTemplate.queryForObject("SELECT * FROM officers WHERE id=?", officerMapper, id);
The problem is that when you call queryForObject with an id that is not in the database, the method doesn’t return null, it throws an exception (specifically, an IncorrectResultSizeDataAccessException).
That means I have two choices. I can either catch that exception and return the empty Optional from the catch block:
This works, but makes the exception handling part of the normal flow of execution.
The other alternative is to check first to see if the id exists, and then make the call:
This works, but at the cost of an additional database query. I was describing the dilemma and trade-offs with the students, when one person suggested an alternative: use queryForStream combined with findFirst or findAny. After checking the documentation, I realized I need to wrap any call to queryForStream in a try-with-resources block.
Here is the result:
Perfect, and it never occurred to me. I knew Spring had added the queryForStream method, but I never realized I had the ideal opportunity to use it. I’ve been using this example for about five years or so, but now, at last, I think I know how to handle it properly. Good thing I went to class that week.
Leave a Reply