Pranav E K

Using Multiple DataSource In Spring Boot App

Jun 23, 2016
1 minute read

1. Overview

Spring Boot will load properties from the application.properties file which resides in the application class path and add them to the Spring Environment.

This post shows how to create multiple JdbcTemplate instances which are bounded to different databases specified in the properties files.

2. The application.properties

Sample properties file located at src/main/resources

#DB
spring.db_mysql.driverClassName=com.mysql.jdbc.Driver
spring.db_mysql.url=jdbc:mysql://localhost:3306/test
spring.db_mysql.username=root_branch_leaf
spring.db_mysql.password=hard_to_guess
 
spring.db_sqlite.driverClassName=org.sqlite.JDBC
spring.db_sqlite.url=jdbc:sqlite:test.db

3. Configuration for DB

Configure DataSource bean in the Spring configuration class named DbConfig. Using @ConfigurationProperties we could bind the namespace properties to the given DataSource bean.

@Configuration
public class DbConfig {

	@Bean(name = "mysqlDb")
	@Primary
	@ConfigurationProperties(prefix="spring.db_mysql") 
	public DataSource mysqlDataSource() {
		return DataSourceBuilder.create().build();
	}
	
	@Bean(name = "sqliteDb")
	@ConfigurationProperties(prefix="spring.db_sqlite") 
	public DataSource sqliteDataSource() {
		return DataSourceBuilder.create().build();
	}
	
	@Bean(name = "mysql")
    public JdbcTemplate mysqlJdbcTemplate(@Qualifier("mysqlDb") DataSource mysqlDb) { 
		return new JdbcTemplate(mysqlDb); 
    } 
		
	@Bean(name = "sqlite") 
    public JdbcTemplate sqliteJdbcTemplate(@Qualifier("sqliteDb") DataSource sqliteDb) { 
        return new JdbcTemplate(sqliteDb); 
    } 
	
}

5. Create an interface for the DAO

Write an abstract method inside the MedicaidDao interface

public interface MedicaidDao {
	Map<String, String> findMetaDataOfTemplateBxp();
}

6. Implement the DAO interface

Autowire the JdbcTemplate to a field and use @Qualifier annotation to specify which bean should be injected to it.

@Repository
public class MedicaidDaoImpl implements MedicaidDao {

	static final Logger LOG = LoggerFactory.getLogger(MedicaidDaoImpl.class);

	@Autowired
	@Qualifier("mysql")
	private JdbcTemplate jdbcTemplate;

	@Autowired
	@Qualifier("sqlite")
	private JdbcTemplate sqliteJdbcTemplate;

	@Override
	public Map<String,String> findMetaDataOfTemplateBxp() {
		Map<String,String> bxpMetaDataMap =  new HashMap<String,String>();
		jdbcTemplate.query("SELECT column_name, column_type FROM INFORMATION_SCHEMA.columns where table_schema=? and table_name=?", (ps) -> {
			ps.setString(1,"test");
			ps.setString(2,"bxps");
		},(rs) -> {
			while(rs.next()){
				bxpMetaDataMap.put(rs.getString("column_name"),rs.getString("column_type")); 				
			}
		});
		
		return bxpMetaDataMap;
	}
}

7. Conclusion

DataSourceBuilder builds data source from the values specified in the properties file.

Using the @Qualifier annotation we could specify the JdbcTemplate bean which should be injected in the DAO. Data will be fetched from the data source bounded to the bean.