通过springboot+mybatis+druid配置动态数据源

通过springboot+mybatis+druid配置动态数据源

一、建数据库和表

1.数据库demo1放一张user表

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------

-- Table structure for user

-- ----------------------------

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (

`id` int(11) NOT NULL,

`name` varchar(255) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of user

-- ----------------------------

INSERT INTO `user` VALUES ('1', 'aa');

INSERT INTO `user` VALUES ('2', 'bb');

2.数据库demo2放一张role表

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------

-- Table structure for role

-- ----------------------------

DROP TABLE IF EXISTS `role`;

CREATE TABLE `role` (

`id` int(11) NOT NULL,

`name` varchar(255) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of role

-- ----------------------------

INSERT INTO `role` VALUES ('1', 'CC');

INSERT INTO `role` VALUES ('2', 'DD');

二、pom.xml引入包

org.springframework.boot

spring-boot-starter-web

org.springframework.boot

spring-boot-starter-test

test

org.springframework.boot

spring-boot-starter-thymeleaf

mysql

mysql-connector-java

runtime

mysql

mysql-connector-java

org.springframework.boot

spring-boot-starter-jdbc

org.mybatis.spring.boot

&lhttp://t;artifactId>mybatis-spring-boot-starter

2.0.1

org.aspectj

aspectjweaver

com.alibaba

druid-spring-boot-starter

1.1.10

com.typesafe.dynamicdatasource

dynamic-data-source_2.11

三、用generator插件生成user、role两张表的实体类、mapper.java、mapper.xml

User.java

Role.java

UserMapper.java

RoleMapper.java

UserMapper.xml

RoleMapper.xml

四、配置application.yml

server:

port: 8088

mybatis:

mapper-locations: classpath:mapper/*.xml

spring:

datasource:

db1:

url: jdbc:mysql://localhost:3306/demo1?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT

username: root

password: root

type: com.alibaba.druid.pool.DruidDataSource

#驱动包

driver-class-name: com.mysql.cj.jdbc.Driver

#初始连接数

initial-size: 5

#最小空闲数

min-idle: 5

#最大活动数

max-active: 20

#等待超时时间

max-wait: 60000

#配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒

time-between-eviction-runs-millis: 60000

# 配置一个连接在池中最小生存的时间，单位是毫秒

min-evictable-idle-time-millis: 300000

#验证数据库连接的查询语句,MYSQL是select 1

validation-query: SELECT 1 FROM DUAL

#空闲时测试，testOnBorrow和testOnReturn在生产环境一般是不开启的，主要是性能考虑。失效连接主要通过testWhileIdle保证

test-while-idle: true

test-on-borrow: false

test-on-return: false

#打开PSCache，并指定每个链接上的PSCache大小

pool-prepared-statements: true

max-pool-prepared-statement-per-connection-size: 20

#配置监控统计拦截的filters,去掉后监控界面NvBdIsql无法统计，‘wall'用于防火墙，此处是filter修改的地方

filters: stat,wall

#通过connectproperties属性来打开mergesql功能：慢sql记录

connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

#合并多个DruidDataSource

useGlobalDataSourceStat: true

db2:

url: jdbc:mysql://localhost:3306/demo2?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT

username: root

password: root

type: com.alibaba.druid.pool.DruidDataSource

#驱动包

driver-class-name: com.mysql.cj.jdbc.Driver

#初始连接数

initial-size: 5

#最小空闲数

min-idle: 5

#最大活动数

max-active: 20

#等待超时时间

max-wait: 60000

#配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒

time-between-eviction-runs-millis: 60000

# 配置一个连接在池中最小生存的时间，单位是毫秒

min-evictable-idle-time-millis: 300000

#验证数据库连接的查询语句,MYSQL是select 1

validation-query: SELECT 1 FROM DUAL

#空闲时测试，testOnBorrow和testOnReturn在生产环境一般是不开启的，主要是性能考虑。失效连接主要通过testWhileIdle保证

test-while-idle: true

test-on-borrow: false

test-on-return: false

#打开PSCache，并指定每个链接上的PSCache大小

pool-prepared-statements: true

max-pool-prepared-statement-per-connection-size: 20

#配置监控统计拦截的filters,去掉后监控界面sql无法统计，‘wall'用于防火墙，此处是filter修改的地方

filters: stat,wall

#通过connectproperties属性来打开mergesql功能：慢sql记录

connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

#合并多个DruidDataSource

useGlobalDataSourceStat: true

五、启动类扫描mapper.java文件

@SpringBootApplication

@MapperScan("com.example.demo.dao")

public class DemoApplication {

public static void main(String[] args) {

SpringApplication.run(DemoApplication.class, args);

}

}

六、定义DataSourceConfig， 将application.yml中的配置导入DataSource中，并注入到bean

@Configuration

public class DataSourceConfig {

//从配置文件配置数据源

@Primary

@Bean(name="datasource1")

@ConfigurationProperties("spring.datasource.db1")

public DataSource dataSource1(){

return new DruidDataSource();

}

//从配置文件配置数据源

@Bean(name="datasource2")

@ConfigurationProperties("spring.datasource.db2")

public DataSource dataSource2(){

return new DruidDataSource();

}

//动态数据源 进行数据源切换

@Bean(name="dynamicDataSource")

public DataSource dynamicDataSource(){

DynamicDataSource dynamicDatasource=new DynamicDataSource();

//设置默认数据源

dynamicDatasource.setDefaultTargetDataSource(dataSource1());

//配置多数据源

Map

dsMap.put("datasource1",dataSource1());

dsMap.put("datasource2",dataSource2());

//将多数据源放到数据源池中

dynamicDatasource.setTargetDataSources(dsMap);

return dynamicDatasource;

}

}

七、定义动态数据源切换类DynamicDataSourceContextHolder

public class DynamicDataSourceContextHolder {

private static final ThreadLocal contextHolder=new ThreadLocal<>();

//设置数据源名称

public static void setDB(String dbType){

contextHolder.set(dbType);

}

//获取数据源名称

public static String getDB(){

return contextHolder.get();

}

//清除数据源名

public static void clearDB(){

contextHolder.remove();

}

}

八、定义获取动态数据源类DynamicDataSource

public class DynamicDataSource extends AbstractRoutingDataSource {

@Override

protected Object determineCurrentLookupKey() {

return DynamicDataSourceContextHolder.getDB();

}

}

九、定义mybatis配置类，将DynamicDataSource放入SqlSessionFactoryBean中

@EnableTransactionManagement

@Configuration

public class MyBatisConfig {

@Resource(name = "dynamicDataSource")

private DataSource dynamicDataSource;

@Bean

public SqlSessionFactory sqlSessionFactory() throws Exception {

SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();

sqlSessionFactoryBean.setDataSource(dynamicDataSource);//将动态数据源bean配置到sqlsessionfactory

sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));

return sqlSessionFactoryBean.getObject();

}

@Bean

public PlatformTransactionManager platformTransactionManager() {

return new DataSourceTransactionManager(dynamicDataSource);

}

}

十、定义用于切换数据源的注解TargetDataSource

@Target({ElementType.METHOD,ElementType.TYPE})

@Retention(RetentionPolicy.RUNTIME)

@Documented

public @interface TargetDataSource {

String value() default "datasource1";

}

十一、定义切面DynamicDataSourceAspect，用于拦截注解，并执行数据源切换功能

@Aspect

@Component

public class DynamicDataSourceAspect {

@Before("@annotation(targetDataSource)")

public void beforeSwitchDS(JoinPoint point,TargetDataSource targetDataSource){

DynamicDataSourceContextHolder.setDB(targetDataSource.value());

}

@After("@annotation(targetDataSource)")

public void afterSwitchDS(JoinPoint point,TargetDataSource targetDataSource){

DynamicDataSourceContextHolder.clearDB();

}

}

十二、测试类Test

@RestController

public class Test {

@Autowired

private RoleMapper roleMapper;

@Autowired

private UserMapper userMapper;

//未使用TargetDataSource注解，则使用默认数据源，即datasource1

@RequestMapping("/ds1")

public String selectDataSource1(){

return userMapper.selectByPrimaryKey(1).toString();

}

//使用了注解，则数据源为注解中指定的datasource2

@RequestMapping("/ds2")

@TargetDataSource("datasource2")

public String selectDataSource2(){

return roleMapper.selectByPrimaryKey(1).toString();

}

}

测试

1.输入

http://localhost:8088/ds1

返回

↓

2.输入

http://localhost:8088/ds2

返回

↓

结论：两次请求分别从不同的数据库获取到了数据，多数据源配置成功！

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。