项目Git地址:SpringBoot 配置多数据源:Jacob-multi-data-source
#本地库
CREATE TABLE `username` (
`id` bigint(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
#远程库
CREATE TABLE `user` (
`id` bigint NOT NULL COMMENT '主键ID',
`name` varchar(30) DEFAULT NULL COMMENT '姓名',
`age` int DEFAULT NULL COMMENT '年龄',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.1.0</version>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>com.github.xiaoymin</groupId>
<artifactId>swagger-bootstrap-ui</artifactId>
<version>1.9.6</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.18</version>
</dependency>
server:
port: 8083
swagger:
enabled: true
mybatis:
mapper-locations: classpath:mapper/*/*.xml,classpath:mapper/*.xml
## 主数据源
master:
package: cn.jacob.datasource.mapper.master
datasource:
url: jdbc:mysql://localhost:3306/tst?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=UTC
username: root
password:
driverClassName: com.mysql.cj.jdbc.Driver
## 从数据源
cluster:
datasource:
url: jdbc:mysql://121.36.9.198:3306/local?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=UTC
username: root
password:
driverClassName: com.mysql.cj.jdbc.Driver
# -------------------------------------这一块配置不加会导致项目启动很慢-------------------------------------
# 连接池的配置信息
# 初始化大小,最小,最大
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
initialSize: 5
minIdle: 5
maxActive: 20
# 配置获取连接等待超时的时间
maxWait: 60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
# 打开PSCache,并且指定每个连接上PSCache的大小
poolPreparedStatements: true
maxPoolPreparedStatementPerConnectionSize: 20
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
filters: stat,wall,log4j
# 通过connectProperties属性来打开mergeSql功能;慢SQL记录
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# -------------------------------------------------------------------------------------------------------
@Configuration
@EnableSwagger2
public class SwaggerConfiguration {
@Bean
public Docket createRestApi() {
return new Docket(DocumentationType.SWAGGER_2)
.apiInfo(apiInfo())
.select()
.apis(RequestHandlerSelectors.basePackage("cn.jacob"))
.paths(PathSelectors.any())
.build();
}
private ApiInfo apiInfo() {
return new ApiInfoBuilder()
.title("swagger-bootstrap-ui RESTful APIs")
.description("swagger-bootstrap-ui")
.termsOfServiceUrl("http://localhost:8083/")
.contact("m15870979735@qq.com")
.version("1.0")
.build();
}
}
主数据源配置类
@Configuration
//扫描 Mapper 接口并容器管理
@MapperScan(basePackages = {"cn.jacob.datasource.mapper.master"}, sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourceConfig {
// 精确到 master 目录,以便跟其他数据源隔离
static final String PACKAGE = "cn.jacob.datasource.mapper.master";
static final String MAPPER_LOCATION = "classpath:mapper/**/*.xml";
@Value("${master.datasource.url}")
private String url;
@Value("${master.datasource.username}")
private String user;
@Value("${master.datasource.password}")
private String password;
@Value("${master.datasource.driverClassName}")
private String driverClass;
@Primary
@Bean(name = "masterDataSource")
public DataSource masterDataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClass);
dataSource.setUrl(url);
dataSource.setUsername(user);
dataSource.setPassword(password);
return dataSource;
}
@Bean(name = "masterTransactionManager")
@Primary
public DataSourceTransactionManager masterTransactionManager() {
return new DataSourceTransactionManager(masterDataSource());
}
@Bean(name = "masterSqlSessionFactory")
@Primary
public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(masterDataSource);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources(MAPPER_LOCATION));
return sessionFactory.getObject();
}
}
从数据源配置类
@Configuration
//扫描 Mapper 接口并容器管理
@MapperScan(basePackages = {"cn.jacob.datasource.mapper.cluster"}, sqlSessionFactoryRef = "clusterSqlSessionFactory")
public class ClusterDataSourceConfig {
// 精确到 cluster 目录,以便跟其他数据源隔离
static final String PACKAGE = "cn.jacob.datasource.mapper.cluster";
static final String MAPPER_LOCATION = "classpath:mapper/**/*.xml";
@Value("${cluster.datasource.url}")
private String url;
@Value("${cluster.datasource.username}")
private String user;
@Value("${cluster.datasource.password}")
private String password;
@Value("${cluster.datasource.driverClassName}")
private String driverClass;
@Bean(name = "clusterDataSource")
public DataSource clusterDataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClass);
dataSource.setUrl(url);
dataSource.setUsername(user);
dataSource.setPassword(password);
return dataSource;
}
@Bean(name = "clusterTransactionManager")
public DataSourceTransactionManager clusterTransactionManager() {
return new DataSourceTransactionManager(clusterDataSource());
}
@Bean(name = "clusterSqlSessionFactory")
public SqlSessionFactory clusterSqlSessionFactory(@Qualifier("clusterDataSource") DataSource clusterDataSource)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(clusterDataSource);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources(MAPPER_LOCATION));
return sessionFactory.getObject();
}
}
@RestController
@Slf4j
@Api(value = "主数据源", tags = "主数据源")
@RequestMapping("/username")
@AllArgsConstructor
public class UsernameController {
private UsernameService usernameService;
@GetMapping("/getList")
@ApiOperation(value = "用户列表",notes = "用户列表")
public List<Username> getList(){
return usernameService.getList();
}
}
-----------------------------------------------------------------------------------------------------------------
@RestController
@Slf4j
@Api(value = "从数据源", tags = "从数据源")
@RequestMapping("/user")
@AllArgsConstructor
public class UserController {
private UserService userService;
@GetMapping("/getList")
@ApiOperation(value = "用户列表",notes = "用户列表")
public List<User> getList(){
return userService.getList();
}
}