Multiple Database Configuration In Spring Boot Application

To access multiple database in application sprint boot makes easy way to do this. In this article, You will get idea to configure it, here we are going to configure two mysql databases for spring boot application.

Steps: -

1.Add multiple datasource configuration in to your application.properties

application.properties

spring.datasource.employee.jdbc-url=jdbc:mysql://localhost/employee?autoReconnect=true&useSSL=false
spring.datasource.employee.username=empuser
spring.datasource.employee.password=emppwd
spring.datasource.employee.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.student.jdbc-url=jdbc:mysql://localhost/student?autoReconnect=true&useSSL=false
spring.datasource.student.username=studuser
spring.datasource.student.password=studpassword
spring.datasource.student.driver-class-name=com.mysql.jdbc.Driver
`

2.Create Configuration class with providers @Bean i.e connection to database.

Create class file name as mysqlConfiguration.java for employee and student database to make connection.
mysqlConfiguration.java

mysqlConfiguration
@Configuration
public class MySqlConfiguration {
@Bean (name = “employeeDatasource”)
@ConfigurationProperties(prefix = “spring.datasource.employee”)
@Primary
public DataSource employeeDatasource()
{
return DataSourceBuilder.create().build();
}
@Bean(name = “employeeTemplate”)
@Primary
public JdbcTemplate employeeTemplate(@Qualifier(“employeeDatasource”) DataSource emp) {
return new JdbcTemplate(emp);
}
@Bean (name = “studentDatasource”)
@ConfigurationProperties(prefix = “spring.datasource.student”)
public DataSource studentDatasource()
{
return DataSourceBuilder.create().build();
}


@Bean(name = “studentTemplate”)
public JdbcTemplate studentTemplate(@Qualifier(“studentDatasource”) DataSource stud) {
return new JdbcTemplate(stud);
}
}

3.Create Base service to select datasource i.e Generate query.

Create BaseService class file to check connectionName and execeute the query which is called.
BaseService.java

base-service
//@Service
public class BaseService {
@Autowired
@Qualifier(“employeeTemplate”)
private JdbcTemplate employeeTemplate;

//get templates
@Autowired
@Qualifier(“studentTemplate”)
private JdbcTemplate studentTemplate;

public JdbcTemplate getTemplate(String connectionName) {
if(connectionName.equals(“employee”))
return employeeTemplate;
else if(connectionName.equals(“student”))
return studentTemplate;
else
return null;
};

protected List<Map<String, Object>> parseQueryResult(String query,String databaseName,String connectionName) {
List<Map<String, Object>> parsedList = new ArrayList<Map<String, Object>>();
List<Map<String, Object>> queryResult = getTemplate(connectionName).queryForList(query,databaseName);
return queryResult;
}
}

4.Call Base from DAO to get result from two database

Here we called the base service method from dao to get execute query on specific database.
recordsDaoImpl.java

public LinkedHashMap<String, Object> getTableOfdb(String databaseName,String connectionName) { 

List<Map<String, Object>> employeeList = new ArrayList<Map<String, Object>>();
List<Map<String, Object>> studentList = new ArrayList<Map<String, Object>>();
LinkedHashMap<String, Object> responseObjectsMap = new LinkedHashMap<String, Object>();

String queryEmp = “SELECT * FROM EMPLOYEE”;
EmpList = parseQueryResult(queryEmp,databaseName,connectionName);
String queryStud = “SELECT * FROM STUDENT”;
StudList = parseQueryResult(queryStud,databaseName,connectionName);

responseObjectsMap.put(“employee”, EmpList);
responseObjectsMap.put(“students”, StudList);

return responseObjectsMap;
}

Thank you for reading and feel free add comments👋🏽
Hope this helps you.✌️

Love Programming and Writing, Software Engineer, Pune