21 November, 2013

Spring-boot JDBC with multiple DataSources sample

Spring-Boot's auto-configurer seems good for simple applications. For example it automatically creates DataSource and JdbcTemplate, when you need to connect to the database. But what about less trivial configuration, when you have several different databases?

I found the way to have multiple DataSources for Spring-Boot-based application.

In the sample below I have two special (db-related) Configurations, one properties file with connections' parameters and two Repositories.

Each @Repository connects with appropriate database through separate DataSource.
application.properties
spring.ds_items.driverClassName=org.postgresql.Driver 
spring.ds_items.url=jdbc:postgresql://srv0/test 
spring.ds_items.username=test0 
spring.ds_items.password=test0 
 
 
spring.ds_users.driverClassName=org.postgresql.Driver 
spring.ds_users.url=jdbc:postgresql://srv1/test 
spring.ds_users.username=test1 
spring.ds_users.password=test1 



DatabaseItemsConfig.java
package sb; 
 
import org.springframework.boot.autoconfigure.jdbc.TomcatDataSourceConfiguration; 
import org.springframework.boot.context.properties.ConfigurationProperties; 
import org.springframework.context.annotation.Bean; 
import org.springframework.context.annotation.Configuration; 
import org.springframework.jdbc.core.JdbcTemplate; 
 
import javax.sql.DataSource; 
 
@Configuration 
@ConfigurationProperties(name = "spring.ds_items") 
public class DatabaseItemsConfig extends TomcatDataSourceConfiguration { 
 
    @Bean(name = "dsItems") 
    public DataSource dataSource() { 
        return super.dataSource(); 
    } 
 
    @Bean(name = "jdbcItems") 
    public JdbcTemplate jdbcTemplate(DataSource dsItems) { 
        return new JdbcTemplate(dsItems); 
    } 
} 


DatabaseUsersConfig.java
package sb; 
 
import org.springframework.boot.autoconfigure.jdbc.TomcatDataSourceConfiguration; 
import org.springframework.boot.context.properties.ConfigurationProperties; 
import org.springframework.context.annotation.Bean; 
import org.springframework.context.annotation.Configuration; 
import org.springframework.jdbc.core.JdbcTemplate; 
 
import javax.sql.DataSource; 
 
@Configuration 
@ConfigurationProperties(name = "spring.ds_users") 
public class DatabaseUsersConfig extends TomcatDataSourceConfiguration { 
 
    @Bean(name = "dsUsers") 
    public DataSource dataSource() { 
        return super.dataSource(); 
    } 
 
    @Bean(name = "jdbcUsers") 
    public JdbcTemplate jdbcTemplate(DataSource dsUsers) { 
        return new JdbcTemplate(dsUsers); 
    } 
 
} 


ItemRepository.java
package sb; 
 
import org.slf4j.Logger; 
import org.slf4j.LoggerFactory; 
import org.springframework.beans.factory.annotation.Autowired; 
import org.springframework.beans.factory.annotation.Qualifier; 
import org.springframework.jdbc.core.JdbcTemplate; 
import org.springframework.jdbc.core.RowMapper; 
import org.springframework.stereotype.Repository; 
 
import java.sql.ResultSet; 
import java.sql.SQLException; 
 
@Repository 
public class ItemRepository { 
    protected final Logger log = LoggerFactory.getLogger(getClass()); 
 
    @Autowired 
    @Qualifier("jdbcItems") 
    protected JdbcTemplate jdbc; 
 
    public Item getItem(long id) { 
        return jdbc.queryForObject("SELECT * FROM sb_item WHERE id=?", itemMapper, id); 
    } 
 
    private static final RowMapper<Item> itemMapper = new RowMapper<Item>() {
        public Item mapRow(ResultSet rs, int rowNum) throws SQLException { 
            Item item = new Item(rs.getLong("id"), rs.getString("title")); 
            item.price = rs.getDouble("id"); 
            return item; 
        } 
    }; 
} 


UserRepository.java
package sb; 
 
import org.slf4j.Logger; 
import org.slf4j.LoggerFactory; 
import org.springframework.beans.factory.annotation.Autowired; 
import org.springframework.beans.factory.annotation.Qualifier; 
import org.springframework.jdbc.core.JdbcTemplate; 
import org.springframework.jdbc.core.RowMapper; 
import org.springframework.stereotype.Repository; 
 
import java.sql.ResultSet; 
import java.sql.SQLException; 
 
@Repository 
public class UserRepository { 
    protected final Logger log = LoggerFactory.getLogger(getClass()); 
 
    @Autowired 
    @Qualifier("jdbcUsers") 
    protected JdbcTemplate jdbc; 
 
    public User getUser(long id) { 
        return jdbc.queryForObject("SELECT * FROM sb_user WHERE id=?", userMapper, id); 
    } 
 
    private static final RowMapper<User> userMapper = new RowMapper<User>() {
        public User mapRow(ResultSet rs, int rowNum) throws SQLException { 
            User user = new User(rs.getLong("id"), rs.getString("name")); 
            user.alias = rs.getString("alias"); 
            return user; 
        } 
    }; 
} 


Controller.java
package sb; 
 
import org.slf4j.Logger; 
import org.slf4j.LoggerFactory; 
import org.springframework.beans.factory.annotation.Autowired; 
import org.springframework.web.bind.annotation.RequestMapping; 
import org.springframework.web.bind.annotation.RequestParam; 
import org.springframework.web.bind.annotation.RestController; 
 
@RestController 
public class Controller { 
    protected final Logger log = LoggerFactory.getLogger(getClass()); 
 
    @Autowired 
    private UserRepository users; 
 
    @Autowired 
    private ItemRepository items; 
 
    @RequestMapping("test") 
    public String test() { 
        log.info("Test"); 
        return "OK"; 
    } 
 
    @RequestMapping("user") 
    public User getUser(@RequestParam("id") long id) { 
        log.info("Get user"); 
        return users.getUser(id); 
    } 
 
    @RequestMapping("item") 
    public Item getItem(@RequestParam("id") long id) { 
        log.info("Get item"); 
        return items.getItem(id); 
    } 
 
} 


Application.java
package sb; 
 
import org.springframework.boot.SpringApplication; 
import org.springframework.boot.autoconfigure.EnableAutoConfiguration; 
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration; 
import org.springframework.context.annotation.ComponentScan; 
import org.springframework.context.annotation.Configuration; 
 
@EnableAutoConfiguration(exclude = DataSourceAutoConfiguration.class) 
@Configuration 
@ComponentScan(basePackages = "sb") 
public class Application { 
 
    public static void main(String[] args) throws Throwable { 
        SpringApplication app = new SpringApplication(Application.class); 
        app.run(); 
    } 
} 

16 comments:

Vince Scialdone said...

Thanks this was very helpful for me!

Vince Scialdone said...

Thanks! This was very helpful for me.

sahil mohindroo said...

Thanks

sahil mohindroo said...

Thanks

TSM Ltd said...

Thanks very much, got me back on the road again...

TSM Ltd said...

Thanks very much!

Chien Hao Huang said...

After Spring Boot 1.1.0.Release, there are no more
org.springframework.boot.autoconfigure.jdbc.TomcatDataSourceConfiguration

this is basic change. any suggestions?

Chien Hao Huang said...

After Spring Boot 1.1.0.Release no more
org.springframework.boot.autoconfigure.jdbc.TomcatDataSourceConfiguration

Please advice any other way to implement multiple database?

Chien Hao Huang said...

After Spring Boot 1.1.0.Release no more
org.springframework.boot.autoconfigure.jdbc.TomcatDataSourceConfiguration

Please advice any other way to implement multiple database?

Chien Hao Huang said...

Thank your blog,
but, there are one big issue:
After Spring Boot 1.1.0.RELEASE, no more TomcatDataSourceConfiguration
How could I do for handling two different database?

Andy Hardy said...

Awesome - thank you very much for this example.

/A

Andy Hardy said...

Awesome - thank you for this, it helped a lot.

/A

Stephan Beal said...

Very helpful :). One minor typo:

item.price = rs.getDouble("id");

note the field names.

nitin chopade said...

Hello,

It's still not working for me. I am not using controller , but making call to repository directly from mail class.
I even tried using - @Primary as some of the posts suggested -

---------------
@Configuration
@ConfigurationProperties(prefix = "spring.ds_items")
public class DatabaseItemsConfig {

@Primary
@Bean(name = "dsItems")
public DataSource dataSource() {
return DataSourceBuilder.create().build();
}

@Bean(name = "jdbcItems")
public JdbcTemplate jdbcTemplate(DataSource dsItems) {
return new JdbcTemplate(dsItems);
}
------------------

But I am getting this erro - java.sql.SQLException: The url cannot be null

Elchuri said...

HI all,

I am trying implement Multi-Datasource. I'am getting below error. Please advice. Thanks

Error :NoSuchBeanDefinitionException: No qualifying bean of type [com.bns.mcac.config.DatabaseConfig] found for dependency

Elchuri said...

Here is the code...

@Configuration
public class DatabaseConfig {

@Bean(name = "ds-mx")
@Primary
@ConfigurationProperties(prefix="spring.datasource")
public DataSource dataSourceMX() {
return DataSourceBuilder.create().build();
}

@Bean(name = "ds-cl")
@ConfigurationProperties(prefix="spring.datasource.cl")
public DataSource dataSourceCL() {
return DataSourceBuilder.create().build();
}

Service
--------
@Service
public class McacMapperService implements IMcacMapperService {

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

@Autowired
private JdbcTemplate jdbcTemplate;

@Autowired(required=true)
private DatabaseConfig databaseConfig;