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));
Data
insert into PRODUCT(id, name) values (1, ‘XBox');
Database Two - Customer Database
Schema
create table CUSTOMER(id integer, name varchar(255));
Data
insert into CUSTOMER(id, name) values (1, 'Daphne Jefferson’);
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);
}
}
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
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;
}
}
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;
}
}
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)