DEV Community

Cover image for Accessing Multiple Databases From a Spring Boot Application
Davey
Davey

Posted on • Updated on • Originally published at davidsalter.com

Accessing Multiple Databases From a Spring Boot Application

When developing any application, it’s quite common to have to access multiple databases. Out of the box, Spring Boot provides easy access to a single datasource, in the simplest case just by specifying the JDBC driver on the class path!

Accessing multiple databases however, is still straightforward with Spring Boot. This article shows how to connect to two different MySql datasources from a Spring Boot application.

To showcase how to connect to to different databases, consider a products database and a customer database, with the following simplistic schema and data.

Database One - Products Database

Schema

create table PRODUCT(id integer, name varchar(255));
Enter fullscreen mode Exit fullscreen mode

Data

insert into PRODUCT(id, name) values (1, XBox');
Enter fullscreen mode Exit fullscreen mode

Database Two - Customer Database

Schema

create table CUSTOMER(id integer, name varchar(255));
Enter fullscreen mode Exit fullscreen mode

Data

insert into CUSTOMER(id, name) values (1, 'Daphne Jefferson’);
Enter fullscreen mode Exit fullscreen mode

To access the databases, we need to declare a JdbcTemplate for each database. In Spring, JdbcTemplates are created from a DataSource which has a set of connection properties (url, username, password etc.)

@Configuration
public class DataSourceConfig {

  Bean
  @Qualifier("customerDataSource")
  @Primary
  @ConfigurationProperties(prefix="customer.datasource")
  DataSource customerDataSource() {
    return DataSourceBuilder.create().build();
  }

  @Bean
  @Qualifier("productDataSource")
  @ConfigurationProperties(prefix="product.datasource")
  DataSource productDataSource() {
    return DataSourceBuilder.create().build();
  }

  @Bean
  @Qualifier("customerJdbcTemplate")
  JdbcTemplate customerJdbcTemplate(@Qualifier("customerDataSource")DataSource customerDataSource) {
    return new JdbcTemplate(customerDataSource);
  }

  @Bean
  @Qualifier("productJdbcTemplate")
  JdbcTemplate productJdbcTemplate(@Qualifier("productDataSource")DataSource productDataSource) {
    return new JdbcTemplate(productDataSource);
  }
}
Enter fullscreen mode Exit fullscreen mode

In the above code we can see that a @Configuration bean has been declared that defines a customerDatasource and a customerJdbcTemplate. Each of these beans are annotated with the @Qualifier('customer...') to identify them as relating to the customer database.

Similarly, the above code defines a productDataSource and a productJdbcTemplate. Again these are annotated with @Qualifier('product...') to identify them as relating to the product database.

Finally, each DataSource Bean is annotated with the @ConfigurationProperties(prefix="...datasource") annotation. This tells Spring Boot what properties within the application.properties file should be used for connecting to each database. The application.properties file therefore looks like the following:

product.datasource.url = jdbc:mysql://localhost:3306/dbOne
product.datasource.username = user1
product.datasource.password = password
product.datasource.driverClassName = com.mysql.jdbc.Driver

customer.datasource.url = jdbc:mysql://localhost:3306/dbTwo
customer.datasource.username = user2
customer.datasource.password = password
customer.datasource.driverClassName = com.mysql.jdbc.Driver
Enter fullscreen mode Exit fullscreen mode

Now that we've seen how to create a DataSource and JdbcTemplate, the JdbcTemplate can be injected into a @Repository for use, e.g.

@Repository
public class CustomerRepository {

  private static final String SELECT_SQL = "select NAME from CUSTOMER where ID=?"; 
  @Autowired
  @Qualifier("customerJdbcTemplate")
  JdbcTemplate customerJdbcTemplate;

  public String getCustomerName(int id) {
    String name = customerJdbcTemplate.queryForObject(SELECT_SQL, new Object[] {id}, String.class);

    return name;
  }
}
Enter fullscreen mode Exit fullscreen mode

Again, note the use of the @Qualifier annotation to specify which JdbcTemplate is required for the different repositories.

The ProductRepository is similarly written to access the productJdbcTemplate

@Repository
public class ProductRepository {

  private static final String SELECT_SQL = "select NAME from PRODUCT where ID=?"; 
  @Autowired
  @Qualifier("productJdbcTemplate")
  JdbcTemplate productJdbcTemplate;

  public String getProductName(int id) {
    String name = productJdbcTemplate.queryForObject(SELECT_SQL, new Object[] {id}, String.class);

    return name;
  }
}
Enter fullscreen mode Exit fullscreen mode

With a few simple steps, Spring Boot allows us to easily connect to multiple databases when using JdbcTemplates.

Credits

Photo by Jan Antonin Kolar on Unsplash

Top comments (0)