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

Image result for 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;

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

    class BlogRowMapper implements RowMapper {
        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:

public class BlogController {

    private BlogRepo repo;

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

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

    public ResponseEntity<Iterable> findBlogsByContent(@PathVariable String content) {
        return ResponseEntity.ok(repo.findBlogsByContent(content));



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.

Wiring Multiple Datasources in a Spring Boot and Spring Data JPA Application


Having to wire a Spring Boot application to talk to multiple datasources is a requirement you come along every once awhile. The good news is this can be done reasonably easily within Spring Boot and in this article we will walk through how to accomplish this.

Wiring Multiple Datasources Step-by-Step


To run the demo application the following tools are needed:

  • Docker
  • Java

There are also some shell scripts available for building, running, testing, and tearing down the application for *nix OS users.

Application Structure

The example application used in this article has two domain models, Doctor and Clinic, each of which are persisted to their own separate datastores. This application overall is a very standard implementation of Spring Boot and Spring Data. Because there is already a lot of great documentation on how to implement such an application, those steps will be skipped. However for clarity, here is what the overall structure of the application looks like:\

The full application can be seen here:

Configuring Spring Data

The first step would be to define the @Configuration classes that add the DataSource, PlatformTransactionManager, and LocalContainerEntityManagerFactoryBean to the application context which will be used by Spring Data when communicating with the databases. Both of the configuration classes look essentially identical, with one exception which will be covered in detail below. Let’s step through some of the key elements in these configuration classes:

@EnableJpaRepositories(entityManagerFactoryRef = "clinicEntityManagerFactory", transactionManagerRef = "clinicTransactionManager")
public class ClinicsDatasourceConfiguration {

    @ConfigurationProperties(prefix = "clinics.datasource")
    public DataSource clinicsDataSource() {
        return DataSourceBuilder.create().build();

    PlatformTransactionManager clinicTransactionManager(
            @Qualifier("clinicEntityManagerFactory") LocalContainerEntityManagerFactoryBean clinicEntityManagerFactory) {
        return new JpaTransactionManager(clinicEntityManagerFactory.getObject());

    LocalContainerEntityManagerFactoryBean clinicEntityManagerFactory(
            @Qualifier("clinicsDataSource") DataSource clinicsDatasource, EntityManagerFactoryBuilder builder) {
        return builder.dataSource(clinicsDatasource).packages(Clinic.class).build();


Outside of name differences, the @Primary added to clinicsDataSource is the only functional difference between ClinicsDatasourceConfiguration and DoctorsDatasourceConfiguration. Adding @Primary to clinicsDataSource is necessary as some of the autoconfiguring behavior within Spring Data depends upon a DataSource being available in the application context. However in our situation there will be two DataSources, available in the application context so adding @Primary to one gives Spring the information it needs on which bean to choose. For this applications purposes, making clinicsDataSource the primary DataSource was an arbitrary decision. However deciding which DataSource should be the primary one might be something worth thinking about depending on the requirements and behavior of your application.


Automatically maps the jdbc-url, password, and username properties prefixed with clinics.datasource available in the environment, in this case defined in (source), and maps them to the DataSource being created in clinicsDataSource. If this feels too “magical” DataSourceBuilder (javadoc) also has standard builder methods; url(String), password(String), username(String) available among others.

Using @ConfigurationProperties helps to keep the behavior, from a developer’s perspective, more consistent with how a Spring Boot application would work if it had only a single DataSource. @ConfigurationProperties could also be useful in other scenarios. Here is an example of using @ConfigurationProperties to map to fields within a configuration class from an earlier version of the example project.


The arguments for the @Bean methods of clinicTransactionManager and clinicEntityManagerFactory are annotated with @Qualifier. Like with @Primary, @Qualifier tells the Spring which instance of class to use when there are multiple available in the application context. By default the name of a bean is the same name as the method that created the bean.

Defining the Properties

Next we need to provide Spring with the values to connect to both our databases. In this example we are connecting to containerized instances of MySQL and Postgres. We will go into a little more detail on this properties file below:




When defining a datasource using the spring.datasource properties the property of url would be used. However with Spring Boot 2, HirkariDataSource became the standard DataSource implementation, so to use @ConfigurationProperties like in the @Configuration classes above, the property needs to be jdbc-url.

More info can be found here, as well as a workaround if you’d prefer to keep using url.

(I plan on going into more depth about this in a future article, as this change caused me a lot of pain while putting together the example application)

This is ultimately an optional addition. By default Spring Boot sets this property to true which is arguably an anti-pattern, which you can read more about here. Why this is relevant in an article about configuring multiple datasources is that when is set to true, Spring MVC will look for an instance of PlatformTransactionManager and LocalContainerEntityManagerFactoryBean in the application context.

This could had been alternatively resolved by adding @Primary to one of the @Bean definitions of both PlatformTransactionManager and LocalContainerEntityManagerFactoryBean as was done with clinicsDataSource, however disabling should generally be done anyways, so that is a better resolution.

Running the Application

There are several scripts available for running the demo application, added for convenience and experimentation.

  • – builds the docker images and Java artifact
  • – starts up the Docker containers and Spring Boot application (note: there is a 15 second sleep between starting the containers and starting the app, to give the containers time to startup)
  • – curl commands for POSTing and GETting to the Spring Boot application
  • – stops and removes the Docker containers

The application by default runs at http://localhost:8080 with GET/POST endpoints residing at /clinics and /doctors.


With a little work a Spring Boot application can be setup to handle multiple datasources and still have an overall pretty familiar look and feel to it.

The code used in this article can be found here: