前言
最近的工作中要做数据的迁移,用的数据库有 两个mysql库,两个oracle库,两个mongodb库。需要把一个主mysql库的数据迁移到oracel,中途需要关联查询 mysql库,oralce库,mongodb库从中取业务数据。先做个简单的demo 研究一下多数据源。
一、springboot yml文件配置
application.yml 配置如下:
test1-datasource:
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/database1?characterEncoding=utf8&useSSL=false&serverTimezone=UTC
username: root
password: 123456
test2-datasource:
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/database2?characterEncoding=utf8&useSSL=false&serverTimezone=UTC
username: root
password: 123456
test3-datasource:
driverClassName: oracle.jdbc.driver.OracleDriver
url: jdbc:oracle:thin:@ (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx)(PORT=61512))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xxx)))
username: root
password: 123456
test4-datasource:
driverClassName: oracle.jdbc.driver.OracleDriver
url: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx)(PORT=61512))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xxx)))
username: root
password: 123456
#数据库连接池
# mongodb多数据源
spring:
data:
mongodb:
one:
uri: mongodb://localhost:27017/test
database: test
two:
uri: mongodb://localhost:27017/test1
database: test1
二、mysql 和oracle多数据源的配置
主要有以下五步:
3 mapper 扫描配置 MapperScannerConfigurer
5 配置事务管理器DataSourceTransactionManager
@Configuration // 该注解类似于spring配置文件
public class MyBatisConfig implements EnvironmentAware {
private Environment environment;
@Override
public void setEnvironment(final Environment environment) {
this.environment = environment;
}
/**
* 创建数据源(数据源的名称:方法名可以取为XXXDataSource(),XXX为数据库名称,该名称也就是数据源的名称)
*/
@Bean
public DataSource test1DataSource() throws Exception {
Properties props = new Properties();
props.put("driverClassName", environment.getProperty("test1-datasource.driverClassName"));
props.put("url", environment.getProperty("test1-datasource.url"));
props.put("username", environment.getProperty("test1-datasource.username"));
props.put("password", environment.getProperty("test1-datasource.password"));
return DruidDataSourceFactory.createDataSource(props);
}
@Bean
public DataSource test2DataSource() throws Exception {
Properties props = new Properties();
props.put("driverClassName", environment.getProperty("test2-datasource.driverClassName"));
props.put("url", environment.getProperty("test2-datasource.url"));
props.put("username", environment.getProperty("test2-datasource.username"));
props.put("password", environment.getProperty("test2-datasource.password"));
return DruidDataSourceFactory.createDataSource(props);
}
@Bean
public DataSource test3DataSource() throws Exception {
Properties props = new Properties();
props.put("driverClassName", environment.getProperty("test3-datasource.driverClassName"));
props.put("url", environment.getProperty("test3-datasource.url"));
props.put("username", environment.getProperty("test3-datasource.username"));
props.put("password", environment.getProperty("test3-datasource.password"));
return DruidDataSourceFactory.createDataSource(props);
}
@Bean
public DataSource test4DataSource() throws Exception {
Properties props = new Properties();
props.put("driverClassName", environment.getProperty("test4-datasource.driverClassName"));
props.put("url", environment.getProperty("test4-datasource.url"));
props.put("username", environment.getProperty("test4-datasource.username"));
props.put("password", environment.getProperty("test4-datasource.password"));
return DruidDataSourceFactory.createDataSource(props);
}
/**
* @Primary 该注解表示在同一个接口有多个实现类可以注入的时候,默认选择哪一个,而不是让@autowire注解报错
* @Qualifier 根据名称进行注入,通常是在具有相同的多个类型的实例的一个注入(例如有多个DataSource类型的实例)
*/
@Bean
@Primary
public DynamicDataSource dataSource(@Qualifier("test1DataSource") DataSource test1DataSource,
@Qualifier("test2DataSource") DataSource test2DataSource,
@Qualifier("test3DataSource") DataSource test3DataSource,
@Qualifier("test4DataSource") DataSource test4DataSource
) {
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put(DatabaseType.test1, test1DataSource);
targetDataSources.put(DatabaseType.test2, test2DataSource);
targetDataSources.put(DatabaseType.test3, test3DataSource);
targetDataSources.put(DatabaseType.test4, test4DataSource);
DynamicDataSource dataSource = new DynamicDataSource();
dataSource.setTargetDataSources(targetDataSources);// 该方法是AbstractRoutingDataSource的方法
dataSource.setDefaultTargetDataSource(test2DataSource);// 默认的datasource设置为myTestDbDataSource
return dataSource;
}
@Bean
public MapperScannerConfigurer mapperScannerConfigurer() {
MapperScannerConfigurer scannerConfigurer = new MapperScannerConfigurer();
scannerConfigurer.setBasePackage("com.xing.mapper");
Properties props = new Properties();
props.setProperty("mappers", "tk.mybatis.mapper.common.Mapper");
// props.setProperty("IDENTITY", "MYSQL"); 既有mysql 又有oracle
props.setProperty("notEmpty", "true");
scannerConfigurer.setProperties(props);
return scannerConfigurer;
}
/**
* 根据数据源创建SqlSessionFactory
*/
@Bean
public SqlSessionFactory sqlSessionFactory(DynamicDataSource ds) throws Exception {
PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
SqlSessionFactoryBean fb = new SqlSessionFactoryBean();
fb.setDataSource(ds);// 指定数据源(这个必须有,否则报错)
// 下边两句仅仅用于*.xml文件,如果整个持久层操作不需要使用到xml文件的话(只用注解就可以搞定),则不加
fb.setTypeAliasesPackage("com.xing.model");// 指定基包
fb.setMapperLocations(resolver.getResources("classpath:mapper/**/*.xml"));//
return fb.getObject();
}
/**
* 配置事务管理器
*/
@Bean
public DataSourceTransactionManager transactionManager(DynamicDataSource dataSource) throws Exception {
return new DataSourceTransactionManager(dataSource);
}
}
三、mysql 和oracle 多数据源的切换
这里定义一个切面,根据不同的数据源的对应service动态切换。
@Aspect
@Component
public class DataSourceAspect {
/**
* 使用空方法定义切点表达式
*/
@Pointcut("execution(* com.xing.service.**.*(..))")
public void declareJointPointExpression() {
}
@Before("declareJointPointExpression()")
public void setDataSourceKey(JoinPoint point){
//根据连接点所属的类实例,动态切换数据源
if (point.getTarget() instanceof Test1Service
|| point.getTarget() instanceof Test1ServiceImpl) {
DatabaseContextHolder.setDatabaseType(DatabaseType.test1);
}
else if (point.getTarget() instanceof Test3Service
|| point.getTarget() instanceof Test3ServiceImpl) {
DatabaseContextHolder.setDatabaseType(DatabaseType.test3);
}
else if (point.getTarget() instanceof Test4Service
|| point.getTarget() instanceof Test4ServiceImpl) {
DatabaseContextHolder.setDatabaseType(DatabaseType.test4);
}
else {//连接点所属的类实例是(当然,这一步也可以不写,因为defaultTargertDataSource就是该类所用的mytestdb)
DatabaseContextHolder.setDatabaseType(DatabaseType.test2);
}
}
}
四、测试方法的调用
@RestController
public class TestDataSourceController {
@Autowired
private CommonService commonService;
@RequestMapping(value = "/test1", method = RequestMethod.GET)
public List<UserInfo> selectUser() {
System.out.println("查询第一个数据源");
List<UserInfo> list = commonService.selectUserInfo();
System.out.println(JSON.toJSONString(list));
return list;
}
@RequestMapping(value = "/test2", method = RequestMethod.GET)
public List<UserDetail> userDetail() {
System.out.println("查询第二个数据源");
List<UserDetail> list = commonService.selectUserDetail();
System.out.println(JSON.toJSONString(list));
return list;
}
@RequestMapping(value = "/test3", method = RequestMethod.GET)
public List<User> user() {
System.out.println("查询第三个数据源");
List<User> list = commonService.selectUser();
System.out.println(JSON.toJSONString(list));
return list;
}
@RequestMapping(value = "/test4", method = RequestMethod.GET)
public List<User> userOrT1() {
System.out.println("查询第四个数据源");
List<User> list = commonService.selectUserOrT1();
System.out.println(JSON.toJSONString(list));
return list;
}
}
五、mongodb多数据源配置
分两个MongoTemplateConf配置,OneMongoTemplateConf配置第一个mongodb数据源,TwoMongoTemplateConf 配置第二个mongodb数据源。主要分两步: 第一步获取yml配置后创建MongoDatabaseFactory ,然后在创建MongoTemplate ,给bean 取个名。
@Configuration
@EnableMongoRepositories(mongoTemplateRef = "oneMongo")
public class OneMongoTemplateConf implements EnvironmentAware{
private Environment environment;
@Override
public void setEnvironment(final Environment environment) {
this.environment = environment;
}
@Bean(name = "oneMongo")
@Primary
public MongoTemplate mongoTemplate() {
return new MongoTemplate(mongoDatabaseFactory());
}
@Bean(name = "oneMongoFactory")
@Primary
public MongoDatabaseFactory mongoDatabaseFactory() {
MongoClient client = MongoClients.create(environment.getProperty("spring.data.mongodb.one.uri"));
return new SimpleMongoClientDatabaseFactory(client, environment.getProperty("spring.data.mongodb.one.database"));
}
}
TwoMongoTemplateConf 代码如下:
@Configuration
@EnableMongoRepositories(mongoTemplateRef = "twoMongo")
public class TwoMongoTemplateConf implements EnvironmentAware{
private Environment environment;
@Override
public void setEnvironment(final Environment environment) {
this.environment = environment;
}
@Bean(name = "twoMongo")
public MongoTemplate mongoTemplate() {
return new MongoTemplate(mongoDatabaseFactory());
}
@Bean(name = "twoMongoFactory")
public MongoDatabaseFactory mongoDatabaseFactory() {
MongoClient client = MongoClients.create(environment.getProperty("spring.data.mongodb.two.uri"));
return new SimpleMongoClientDatabaseFactory(client, environment.getProperty("spring.data.mongodb.two.database"));
}
}
六、mongodb 测试方法的调用
注入MongoTemplate,注入时 需要用@Qualifier 注解指定用哪个MongoTemplate 实例。
代码如下:
@Autowired
@Qualifier("oneMongo")
private MongoTemplate oneMongoTemplate;
@Autowired
@Qualifier("twoMongo")
private MongoTemplate twoMongoTemplate;
/**
* 向 localhost:27017/mytest插入数据
*/
@RequestMapping("/mongo1")
public String testOneMongoInsert() {
Map<String, Object> data = Maps.newHashMap();
Map<String, Object> info = Maps.newHashMap();
data.put("name", "cgg");
data.put("attr", "one");
info.put("age" , 18);
data.put("info", info);
// oneMongoTemplate.insert(data, "inventory_27017");
List<Map> list = oneMongoTemplate.findAll(Map.class, "inventory_27017");
System.out.println(JSON.toJSONString(list));
return JSON.toJSONString(list);
}
/**
* 向 localhost:27018/mytest插入数据
*/
@RequestMapping("/mongo2")
public String testTwoMongoInsert() {
Map<String, Object> data = Maps.newHashMap();
Map<String, Object> info = Maps.newHashMap();
data.put("name", "cgg");
data.put("attr", "two");
info.put("age" , 18);
data.put("info", info);
// twoMongoTemplate.insert(data, "inventory_27018");
List<Map> list = twoMongoTemplate.findAll(Map.class, "inventory_27018");
System.out.println(JSON.toJSONString(list));
return JSON.toJSONString(list);
}
总结
以上就是今天要讲的内容,具体完整的项目代码已放到github上。
原文地址:https://blog.csdn.net/weixin_43171875/article/details/128834266
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。
如若转载,请注明出处:http://www.7code.cn/show_27368.html
如若内容造成侵权/违法违规/事实不符,请联系代码007邮箱:suwngjj01@126.com进行投诉反馈,一经查实,立即删除!