JPA or SQL in a Spring Boot Application? Why Not Both?

JPA, Java Persistence API, was designed with the goal of making database interaction easier for Java developers. When used with a library like Spring Data JPA, getting basic database communication setup can be accomplished in mere minutes.

Spring Data JPA works great when performing relatively simple queries against one or two tables, but can become unwieldy once you start moving beyond this basic level of complexity. In real-world applications querying against three, four, or more tables at a time, along with performing other complex behavior within a query, is often necessary. Databases are, unsurprisingly, optimized around querying, filtering, and parsing large amounts of data, to say nothing of the transport time between the database and an application. So even if the logic of a complex query could be broken down and implemented with JPA, performance requirements might necessitate keeping that behavior as a single query.

Spring Data JPA makes handling a lot of mundane database activity a lot easier, but runs into some pain with more complex behavior. SQL can handle really complex behavior, but requires writing a decent amount of boilerplate code for even simple selects and inserts. Surely there must be a way to leverage the strengths of both JPA and SQL behind a consistent interface?

Custom Interfaces, Consistent Behavior

Spring Data JPA offers the ability to create custom repository interfaces and implementations and combine them with the default Repository interfaces Spring Data JPA offers, creating a composite repository. This allows applications to interact with the database through a single consistent interface. Let’s step through creating our own composite repository and then reference it in our application.

The first step is to define a new interface and method signature within. Here I am creating the ‘CustomBlogRepo’ interface:

public interface CustomBlogRepo {
    Iterable findBlogsByContent(String content);
}

Next we need to implement the interface. In this example I am using JdbcTemplate to handle querying, but there isn’t any constraints around how you implement database behavior in a custom repository class.

public class CustomBlogRepoImpl implements CustomBlogRepo {

    private JdbcTemplate jdbcTemplate;

    protected CustomBlogRepoImpl(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public Iterable findBlogsByContent(String content) {
        return jdbcTemplate.query("SELECT * FROM Blogs WHERE body LIKE ?", new Object[] { "%" + content + "%" },
                new BlogRowMapper());
    }

    class BlogRowMapper implements RowMapper {
        @Override
        public Blog mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new Blog(rs.getLong("id"), rs.getString("title"), rs.getString("body"));
        }
    }
}

Note: The above isn’t a good use case of when to use SQL, Spring Data JPA is easily capable of implementing a LIKE.

A couple of other important points, by default Spring Data JPA will scan for classes that match the name of your custom interface CustomBlogRepo with the post fix of “Impl”, thus why the implementation class is named CustomBlogRepoImpl. This behavior can be customized if needed. Additionally the implementation class must also be located in package where Spring Data JPA is scanning for repositories. This too is configurable, as can be seen in the previous link.

Finally we will need to extend the appropriate Repository interface with the customer interface we created. Here is an example of what this would look like:

public interface BlogRepo extends CrudRepository, CustomBlogRepo {

}

With this, we can interact both with the standard Spring Data JPA methods and the more complex database queries implemented in CustomBlogRepoImpl all through the single repository BlogRepo. We can see this in action in BlogController:

@RestController
@RequestMapping("/api/v1/blogs")
public class BlogController {

    private BlogRepo repo;

    protected BlogController(BlogRepo repo) {
        this.repo = repo;
    }

    @GetMapping
    public ResponseEntity<Iterable> getAllBlogs() {
        return ResponseEntity.ok(repo.findAll());
    }

    @GetMapping("/search-by/content/{content}")
    public ResponseEntity<Iterable> findBlogsByContent(@PathVariable String content) {
        return ResponseEntity.ok(repo.findBlogsByContent(content));
    }
}

Conclusion

Being able to reference either JPA or more complex SQL queries from a single consistent Repository interface makes it a lot easier and more consistent to interact with JPA and SQL within your Spring Data JPA projects. With it you can largely side step questions or rather you should use JPA or SQL in your next project, the answer now can simply be, why not both?

The code used in this article can be found on my Github.