介绍
版本说明
spring boot版本:2.0.2.RELEASE
数据源:druid
数据库:mysql
ORM映射:MyBatis,JPA(Hibernate)
需求说明
因为需要在同一个项目中连接多个数据库,而且后期可能还回继续新增新的数据库连接。所以除了实现多数据源之外,还需要实现多个数据源之间动态的进行切换。多数据源的话,声明出来就好了,动态切换就需要用到AbstractRoutingDataSource跟AOP切面来实现。在示例中只实现数据源切换不实现AOP。关于AOP的部分结合自己的业务来写就好了。
POM
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.0</version>
</dependency>
<dependency>
<groupId>javax.activation</groupId>
<artifactId>activation</artifactId>
<version>1.1.1</version>
</dependency>
<dependency>
<groupId>net.sf.json-lib</groupId>
<artifactId>json-lib</artifactId>
<version>2.4</version>
<classifier>jdk15</classifier>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
</dependencies>
配置文件
# 数据源相关配置
ds:
# 数据库1
basic:
datasource:
url: jdbc:mysql://192.168.31.203:3306/ktwlsoft_framework_basic_pl?useUnicode=true&characterEncoding=utf8&useSSL=false&useAffectedRows=true&serverTimezone=GMT
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
# 数据库2
base:
datasource:
url: jdbc:mysql://192.168.31.203:3306/tensquare_base?useUnicode=true&characterEncoding=utf8&useSSL=false&useAffectedRows=true&serverTimezone=GMT
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
# 数据库3
article:
datasource:
url: jdbc:mysql://192.168.31.203:3306/tensquare_article?useUnicode=true&characterEncoding=utf8&useSSL=false&useAffectedRows=true&serverTimezone=GMT
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
# 数据库4
friend:
datasource:
url: jdbc:mysql://192.168.31.203:3306/tensquare_friend?useUnicode=true&characterEncoding=utf8&useSSL=false&useAffectedRows=true&serverTimezone=GMT
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
# 连接池配置
datasource:
initial_size: 20
min_idle: 20
max_active: 200
max_wait: 60000
time_between_eviction_runs_millis: 60000
min_evictable_idle_time_millis: 300000
test_while_idle: true
test_on_borrow: false
test_on_return: false
pool_prepared_statements: true
max_pool_prepared_statement_per_connection_size: 20
# JPA 相关配置
spring:
jpa:
database-platform: org.hibernate.dialect.MySQL5InnoDBDialect
show-sql: true
# mybatis 打印sql
logging:
level:
com.hzw.mapper : debug
数据源的代码实现
自定义数据源切换类
/**
* 自定义数据源切换类
*/
public class DatabaseContextHolder {
private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();
public static void setDBKey(String dataSourceKey) {
contextHolder.set(dataSourceKey);
}
public static String getDBKey() {
return contextHolder.get();
}
public static void clearDBKey() {
contextHolder.remove();
}
}
动态数据源
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import java.util.HashMap;
import java.util.Map;
/**
* 动态数据源
*
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
private static DynamicDataSource instance;
private static byte[] lock=new byte[0];
private static Map<Object,Object> dataSourceMap=new HashMap<Object, Object>();
@Override
public void setTargetDataSources(Map<Object, Object> targetDataSources) {
super.setTargetDataSources(targetDataSources);
dataSourceMap.putAll(targetDataSources);
// 必须添加该句,否则新添加数据源无法识别到
super.afterPropertiesSet();
}
public Map<Object, Object> getDataSourceMap() {
return dataSourceMap;
}
@Override
protected Object determineCurrentLookupKey() {
String dbKey = DatabaseContextHolder.getDBKey();
return dbKey;
}
private DynamicDataSource() {}
public static synchronized DynamicDataSource getInstance(){
if(instance==null){
synchronized (lock){
if(instance==null){
instance=new DynamicDataSource();
}
}
}
return instance;
}
}
数据源KEY
/**
* 数据库数据源名称
*/
public class DbUtil {
/**数据库basic**/
public static final String DB_BASIC = "ds_basic";
/**数据库base**/
public static final String DB_BASE = "ds_base";
/**数据库article**/
public static final String DB_ARTICLE = "ds_article";
/**数据库friend**/
public static final String DB_FRIEND = "ds_friend";
}
数据源配置
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import com.hzw.util.DbUtil;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
/**
* 数据源配置
*/
@Configuration
// 扫描 Mapper 接口并容器管理
@MapperScan(basePackages = DatasourceConfig.PACKAGE, sqlSessionFactoryRef = "sqlSessionFactory")
public class DatasourceConfig {
// mapper扫描
static final String PACKAGE = "com.hzw.mapper";
static final String MAPPER_LOCATION = "classpath:mapper/*.xml";
@Value("${ds.basic.datasource.url}")
private String urlBasic;
@Value("${ds.basic.datasource.username}")
private String userBasic;
@Value("${ds.basic.datasource.password}")
private String passwordBasic;
@Value("${ds.basic.datasource.driver-class-name}")
private String driverClassBasic;
@Value("${ds.base.datasource.url}")
private String urlBase;
@Value("${ds.base.datasource.username}")
private String userBase;
@Value("${ds.base.datasource.password}")
private String passwordBase;
@Value("${ds.base.datasource.driver-class-name}")
private String driverClassBase;
@Value("${ds.article.datasource.url}")
private String urlArticle;
@Value("${ds.article.datasource.username}")
private String userArticle;
@Value("${ds.article.datasource.password}")
private String passwordArticle;
@Value("${ds.article.datasource.driver-class-name}")
private String driverClassArticle;
@Value("${ds.friend.datasource.url}")
private String urlFriend;
@Value("${ds.friend.datasource.username}")
private String userFriend;
@Value("${ds.friend.datasource.password}")
private String passwordFriend;
@Value("${ds.friend.datasource.driver-class-name}")
private String driverClassFriend;
@Value("${ds.datasource.max_active}")
private Integer maxActive;
@Value("${ds.datasource.min_idle}")
private Integer minIdle;
@Value("${ds.datasource.initial_size}")
private Integer initialSize;
@Value("${ds.datasource.max_wait}")
private Long maxWait;
@Value("${ds.datasource.time_between_eviction_runs_millis}")
private Long timeBetweenEvictionRunsMillis;
@Value("${ds.datasource.min_evictable_idle_time_millis}")
private Long minEvictableIdleTimeMillis;
@Value("${ds.datasource.test_while_idle}")
private Boolean testWhileIdle;
@Value("${ds.datasource.test_while_idle}")
private Boolean testOnBorrow;
@Value("${ds.datasource.test_on_borrow}")
private Boolean testOnReturn;
@Bean(name = "dynamicDataSource")
@Primary
public DynamicDataSource dynamicDataSource() {
DynamicDataSource dynamicDataSource = DynamicDataSource.getInstance();
// basic数据源
DruidDataSource dataSourceBasic = initDataSource(driverClassBasic,urlBasic,userBasic,passwordBasic);
// base数据源
DruidDataSource dataSourceBase = initDataSource(driverClassBase,urlBase,userBase,passwordBase);
// article数据源
DruidDataSource dataSourceArticle = initDataSource(driverClassArticle,urlArticle,userArticle,passwordArticle);
// friend数据源
DruidDataSource dataSourceFriend = initDataSource(driverClassFriend,urlFriend,userFriend,passwordFriend);
Map<Object,Object> map = new HashMap<>();
map.put(DbUtil.DB_BASIC, dataSourceBasic);
map.put(DbUtil.DB_BASE, dataSourceBase);
map.put(DbUtil.DB_ARTICLE, dataSourceArticle);
map.put(DbUtil.DB_FRIEND, dataSourceFriend);
dynamicDataSource.setTargetDataSources(map);
// 默认数据源
dynamicDataSource.setDefaultTargetDataSource(dataSourceBasic);
return dynamicDataSource;
}
/**
* 初始数据源
* @param driver 驱动
* @param url 数据库连接
* @param username 用户名
* @param password 密码
* @return
*/
public DruidDataSource initDataSource(String driver,String url,String username,String password){
//jdbc配置
DruidDataSource rdataSource = new DruidDataSource();
rdataSource.setDriverClassName(driver);
rdataSource.setUrl(url);
rdataSource.setUsername(username);
rdataSource.setPassword(password);
setPool(rdataSource);
return rdataSource;
}
/**
* 连接池配置
* @param rdataSource
*/
private void setPool(DruidDataSource rdataSource){
//连接池配置
rdataSource.setMaxActive(maxActive);
rdataSource.setMinIdle(minIdle);
rdataSource.setInitialSize(initialSize);
rdataSource.setMaxWait(maxWait);
rdataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
rdataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
rdataSource.setTestWhileIdle(testWhileIdle);
rdataSource.setTestOnBorrow(testOnBorrow);
rdataSource.setTestOnReturn(testOnReturn);
rdataSource.setValidationQuery("SELECT 'x'");
rdataSource.setPoolPreparedStatements(true);
rdataSource.setMaxPoolPreparedStatementPerConnectionSize(20);
try {
rdataSource.setFilters("stat");
} catch (SQLException e) {
e.printStackTrace();
}
}
@Bean(name = "transactionManager")
@Primary
public DataSourceTransactionManager transactionManager() {
return new DataSourceTransactionManager(dynamicDataSource());
}
@Bean(name = "sqlSessionFactory")
@Primary
public SqlSessionFactory sqlSessionFactory(@Qualifier("dynamicDataSource") DataSource dynamicDataSource)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dynamicDataSource);
sessionFactory.setTypeAliasesPackage("com.hzw.model");
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources(DatasourceConfig.MAPPER_LOCATION));
return sessionFactory.getObject();
}
@Bean
public ServletRegistrationBean druidServlet() {
ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean();
servletRegistrationBean.setServlet(new StatViewServlet());
servletRegistrationBean.addUrlMappings("/druid/*");
Map<String, String> initParameters = new HashMap<String, String>();
// 用户名
initParameters.put("loginUsername", "admin");
// 密码
initParameters.put("loginPassword", "admin");
// 禁用HTML页面上的“Reset All”功能
initParameters.put("resetEnable", "false");
// IP白名单 (没有配置或者为空,则允许所有访问)
initParameters.put("allow", "");
servletRegistrationBean.setInitParameters(initParameters);
return servletRegistrationBean;
}
@Bean
public FilterRegistrationBean filterRegistrationBean() {
FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
filterRegistrationBean.setFilter(new WebStatFilter());
filterRegistrationBean.addUrlPatterns("/*");
filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
return filterRegistrationBean;
}
}
如此这般,多数据源,动态切换的功能就有了,那么下面来验证是否成功。
验证
启动类
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
/**
* 启动类
*/
@SpringBootApplication
public class Application{
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}
下面可以写几个mybatis的例子跟JPA(Hibernate)的例子,保证我们的mybatis跟JPA都是可用的,数据源都是可以切换成功的。这里就不具体写了,只把在service层怎么来动态切换的代码贴出来
article数据库
@Override
public List<Channel> findChannel() {
// 指定数据源
DatabaseContextHolder.setDBKey(DbUtil.DB_ARTICLE);
return channelRepository.findAll();
}
base数据库
@Override
public List<City> findCity() {
// 指定数据源
DatabaseContextHolder.setDBKey(DbUtil.DB_BASE);
return cityMapper.findCity();
}
basic数据库
@Override
public List<Users> findUser() {
// 指定数据源
DatabaseContextHolder.setDBKey(DbUtil.DB_BASIC);
return usersMapper.findUser();
}
这里是在代码中手动指定当前方法使用的数据库,我们可以根据自己的业务来进行改造
场景一
需求是不同的用户,根据区域或其他属性来进行分库,某些用户访问某个数据库。
这种场景,只需要在用户登录的时候就把用户能访问的数据库存储起来,在AOP中获取当前用户能访问的数据库并调用DatabaseContextHolder.setDBKey()方法来设置。AOP的切面可以定义到service或者是action层。
场景二
需求是指定的某个AOP切面能访问某个数据库。
这种场景就更简单了,只需要在切面中去设置就行了
结语
我们的场景是根据不同的用户来进行数据库的动态切换,本来想的是可以在线上新增数据库连接并且能够切换。目前只实现了在项目中把数据源全部配置出来,然后动态切换,后面在慢慢实现动态新增的事情吧。