多数据源
大约 4 分钟
多数据源
工作中，在业务的发展或业务数据隔离的场景下，或者本身用的是不用类型的数据库，这就需要引入多数据源的配置，这里着重讲述如何进行配置，我们可能会遇到下面的情况
1）同一个项目中涉及两个或多个业务数据库，它们之间相互独立，这种情况也可以作为两个或多个项目来开发
2）两个或多个数据库之间是主从关系，主库负责写，从库负责读（建议使用数据库中间件，如sharding-sphere）
3）业务存储在不同类型的数据库里，如oracle和mysql
maven依赖
版本可根据需求添加，查找版本情况
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
</dependency>
配置文件
在配置文件application.yml中配置我们需要链接的数据库：local和quick
spring:
datasource:
local:
driver-class-name: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://127.0.0.1:3306/uu_oversea_centuryuu_vip?useUnicode=true&useSSL=false&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&autoReconnect=true&failOverReadOnly=false&characterEncoding=utf-8
username: username
password: password
type: com.alibaba.druid.pool.DruidDataSource
druid:
max-active: 3000
filters: stat
initialSize: 10
maxWait: 60000
minIdle: 10
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: select 'x'
testWhileIdle: true
testOnBorrow: true
testOnReturn: false
poolPreparedStatements: true
maxOpenPreparedStatements: 20
quick:
driver-class-name: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://hk-cdb-dre2ze7b.sql.tencentcdb.com:63982/quick_sdk_platform?useUnicode=true&useSSL=false&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&autoReconnect=true&failOverReadOnly=false&characterEncoding=utf-8
username: username
password: password
type: com.alibaba.druid.pool.DruidDataSource
druid:
max-active: 3000
filters: stat
initialSize: 10
maxWait: 60000
minIdle: 10
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: select 'x'
testWhileIdle: true
testOnBorrow: true
testOnReturn: false
poolPreparedStatements: true
maxOpenPreparedStatements: 20
注意
驱动名称根据版本配置
com.mysql.jdbc.Driver 是 mysql-connector-java 5中的，
com.mysql.cj.jdbc.Driver 是 mysql-connector-java 版本6之后的
这里有一部分单数据源的配置可以去掉
- Mybatis-Plus中Mapper的接口文件与xml文件存放位置设置
- @MapperScan(basePackages = "com.game.server.mapper")
可以去掉
配置文件
mybatis-plus:
mapper-locations: classpath:mapper/*.xml
启动类
@MapperScan(basePackages = "com.game.server.mapper")
数据源配置
配置文件配置好之后，我们创建两个配置类来加载配置信息，初始化数据源
数据源1
package com.game.server.config;
import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
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 org.springframework.transaction.PlatformTransactionManager;
import com.baomidou.mybatisplus.autoconfigure.MybatisPlusProperties;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
@Configuration
@MapperScan(basePackages = "com.game.server.mapper.local", sqlSessionTemplateRef = "localSqlSessionTemplate")
public class DataSourceLocalConfig {
@Primary
@Bean(name = "datasourcelocal")
@ConfigurationProperties(prefix = "spring.datasource.local")
public DataSource testDataSource() {
return DataSourceBuilder.create().build();
}
/**
* local数据源
*/
@Bean("localSqlSessionFactory")
@Primary
public SqlSessionFactory sqlSessionFactory(@Qualifier("datasourcelocal") DataSource dataSource,
MybatisPlusProperties mybatisProperties) throws Exception {
MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setConfiguration(mybatisProperties.getConfiguration());
// 设置指定mapper位置
bean.setMapperLocations(
new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/local/*.xml"));
return bean.getObject();
}
@Bean(name = "localTransactionManager")
@Primary
public PlatformTransactionManager transactionManager(@Qualifier("datasourcelocal") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "localSqlSessionTemplate")
@Primary
public SqlSessionTemplate testSqlSessionTemplate(
@Qualifier("localSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
数据源2
package com.game.server.config;
import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
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 org.springframework.transaction.PlatformTransactionManager;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
@Configuration
@MapperScan(basePackages = "com.game.server.mapper.quick", sqlSessionTemplateRef = "quickSqlSessionTemplate")
public class DataSourceQuickConfig {
@Bean(name = "datasourcequick")
@ConfigurationProperties(prefix = "spring.datasource.quick")
public DataSource testDataSource() {
return DataSourceBuilder.create().build();
}
/**
* quick数据源
*/
@Bean("quickSqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(@Qualifier("datasourcequick") DataSource dataSource) throws Exception {
MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
bean.setDataSource(dataSource);
// bean.setConfiguration(mybatisProperties.getConfiguration());
// 设置指定mapper位置
bean.setMapperLocations(
new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/quick/*.xml"));
return bean.getObject();
}
@Bean(name = "quickTransactionManager")
public PlatformTransactionManager transactionManager(@Qualifier("datasourcequick") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "quickSqlSessionTemplate")
public SqlSessionTemplate testSqlSessionTemplate(
@Qualifier("quickSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
说明
basePackages:指定了mapper扫描接口的位置，相当于单数据源的启动类上的@MapperScan(basePackages = "com.game.server.mapper")
@ConfigurationProperties(prefix = "spring.datasource.local"): 对应配置文件yml中的前缀
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/quick/*.xml"));对应xml文件的位置 相当于单数据源时的：
mybatis-plus: mapper-locations: classpath:mapper/*.xml
使用
使用方式和单数据源相同这里不做过多的赘述
补充批量操作
工作中有需求要进行批量新增和修改
实现了以下几种方式
- 代码中foreach insert/update：重复的IO连接与断开效率极低，性能很差，不考虑
- 多线程foreach insert/update：使用多线程进行批量插入/修改，时间会大大降低，但还会有频繁建立断开IO，性能不好
- mybatis-plus xml中foreach：其实就是在拼sql，但是不同业务要拼不同的sql，复用性很差，代码重复
- mybatis-plus扩展：通过简单的配置就可以达到不同业务的复用
原有mapper
原有mapper，不需要做任何修改
@Repository
public interface WeekPayInfoMapper extends BaseMapper<WeekPayInfo> {
List<Map<String, String>> selectPayInfoByRole();
}
增加service
@Component
public class WeekPayInfoMapperBatch extends ServiceImpl<WeekPayInfoMapper, WeekPayInfo> {
}
提示
WeekPayInfo：实体类
使用
@Autowired
private WeekPayInfoMapperBatch weekPayInfoMapperBatch;
weekPayInfoMapperBatch.saveBatch(weekPays);
- 本文作者： 景兴春
- 本文链接： https://www.jingxc.top/spring/more-mysql.html
- 版权声明： 本博客所有文章除特别声明外，均采用 Apache License 2.0 许可协议。转载请注明出处！