Java-SpringBoot使用基于JDBC配置多个数据源
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();
}
}