在spring或者springboot中使用多数据源切换,一般的切换技术说到底最终都是使用了spring的aop技术,对底层的mapper接口类进行监测,在调用不同的mapper之前进行数据源的切换;而本次我将以spring-mybatis的@MapperScan注解来实现对不同的mapper进行数据源的指定;它和aop有点类似,都要需要监测底层mapper以指定不同的数据源,但是相对来说,它会比aop更加简单直接;
以下是我实现的具体的步骤,但是这种方法是有一个缺陷的,那就是你必须要有与mapper对应的xml文件,不然会报错,说找不到方法名
第一步,你要准备三个数据库,此处省略;
第二步,pom.xml中引入druid包的依赖,为注入生成DataSource数据源做准备,依赖如下:
<!-- https://mvnrepository.com/artifact/com.alibaba/druid --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.2.6</version> </dependency> <dependency> <groupId>com.louislivi.fastdep</groupId> <artifactId>fastdep-datasource</artifactId> <version>1.0.7</version> </dependency>
第三步,在properties中写好三个数据源的配置,即前面准备的三个数据库,例如:
#第一个数据源 spring.datasource.name=master spring.datasource.url=jdbc:mysql://xx.xx.xx.xx:3306/data3?serverTimezone=GMT spring.datasource.username=xxx spring.datasource.password=xxxxxx spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#配置从数据源 slave.datasource.names=slave1,slave2 slave.datasource.slave1.url=jdbc:mysql://xx.xx.xx.xx:3306/data1?serverTimezone=GMT slave.datasource.slave1.username=xxx slave.datasource.slave1.password=xxxxxx slave.datasource.slave1.driver-class-name=com.mysql.cj.jdbc.Driver slave.datasource.slave2.url=jdbc:mysql://xx.xx.xx.xx:3306/data2?serverTimezone=GMT slave.datasource.slave2.username=xxx slave.datasource.slave2.password=xxxxxx slave.datasource.slave2.driver-class-name=com.mysql.cj.jdbc.Driver
第四步 数据源配置前的准备,我们要自己写好mapper的目录,方便在配置的时候根据mapper的目录来指定数据源,例如:
还有它的xml文件对应的目录我们也准备一下,例如:
第五步,关键点来了,写数据源的配置类,关键字是@MapperScan注解及它的basePackages和sqlSessionFactoryRef
basepackages : 基于包下面的扫描MyBatis的接口。注意是,只有是接口的将会被扫描注册,如果是具体的类将会被忽略。
sqlSessionFactoryRef : 在指定使用sqlSessionFactoryRef的情况下,这里有一个或多个的Spring的容器。经常我们会使用一个或多个的数据库.
通俗来说这里这两个属性分别指定了mapper的目录以及它要使用的数据源,代码如下:
package com.example.demo.datasource; import com.alibaba.druid.pool.DruidDataSource; 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.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; /** * 这是一个用来配置主数据源的配置类,主要是注入数据源DataSource和根据注入好的DataSource重新创建返回sqlSessionFactory连接 * (1)注入数据源 * (2)根据注入的数据源重新创建DataSourceTransactionManager事物管理类和获取sqlSessionFactory连接工厂类; */ @Configuration @MapperScan(basePackages = MasterDataSourceConfig.PACKAGE,sqlSessionFactoryRef = "masterSqlSessionFactory") public class MasterDataSourceConfig { static final String PACKAGE = "com.example.demo.mapper.master"; static final String MAPPER_LOCATION = "classpath:mapper/master/*.xml"; @Value("${spring.datasource.url}") private String url; @Value("${spring.datasource.username}") private String username; @Value("${spring.datasource.password}") private String password; @Value("${spring.datasource.driver-class-name}") private String driverClass; @Bean(name="masterDataSource") @Primary public DataSource masterDataSource(){ DruidDataSource dataSource = new DruidDataSource(); dataSource.setDriverClassName(driverClass); dataSource.setUrl(url); dataSource.setUsername(username); 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(MasterDataSourceConfig.MAPPER_LOCATION)); return sessionFactory.getObject(); } }
第二个数据源的配置代码如下:
package com.example.demo.datasource; import com.alibaba.druid.pool.DruidDataSource; 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.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.core.io.support.PathMatchingResourcePatternResolver; import org.springframework.jdbc.datasource.DataSourceTransactionManager; import javax.sql.DataSource; @Configuration @MapperScan(basePackages = Slave1DataSourceConfig.PACKAGE,sqlSessionFactoryRef = "slave1SqlSessionFactory") public class Slave1DataSourceConfig { static final String PACKAGE = "com.example.demo.mapper.slave1"; static final String MAPPER_LOCATION = "classpath:/mapper/slave1/*.xml"; @Value("${slave.datasource.slave1.url}") private String url; @Value("${slave.datasource.slave1.username}") private String username; @Value("${slave.datasource.slave1.password}") private String password; @Value("${slave.datasource.slave1.driver-class-name}") private String driverClass; @Bean(name="slave1DataSource") public DataSource slave1DataSource(){ DruidDataSource dataSource = new DruidDataSource(); dataSource.setUrl(url); dataSource.setUsername(username); dataSource.setPassword(password); dataSource.setDriverClassName(driverClass); return dataSource; } @Bean(name = "slaveTransactionManager") public DataSourceTransactionManager slaveTransactionManager() { return new DataSourceTransactionManager(slave1DataSource()); } @Bean(name = "slave1SqlSessionFactory") public SqlSessionFactory slave1SqlSessionFactory(@Qualifier("slave1DataSource") DataSource dataSource) throws Exception { final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean(); sessionFactory.setDataSource(dataSource); sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(Slave1DataSourceConfig.MAPPER_LOCATION)); return sessionFactory.getObject(); } }
第三个数据源配置省略,此处要注意的地方是:PathMatchingResourcePatternResolver()的getResource()和getResources()方法,一个是从类路径下开始读,一个是从跟目录下开始读,所以我们要使用getResources()方法,一定不要搞混了,不然它会说找不到指定的xml的文件路径
第六步:在mapper类中都写一个selectAll的方法,查询三个mapper对应的表的所有数据,例如:
对应的xml如下
其他两个省略
第七步,测试
代码如下:
package com.example.demo.controller.OrDataSourceController; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.example.demo.mapper.master.CateGoryMapper; import com.example.demo.mapper.slave1.User1Mapper; import com.example.demo.mapper.slave2.TeacherMapper; import com.example.demo.pojo.master.CateGory; import com.example.demo.pojo.slave1.User1; import com.example.demo.pojo.slave2.Teacher; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import java.util.HashMap; import java.util.List; import java.util.Map; @RestController public class OrDataSourceTest { @Autowired CateGoryMapper cateGoryMapper; @Autowired User1Mapper user1Mapper; @Autowired TeacherMapper teacherMapper; @RequestMapping("/test1") public String returnSus(){ return "success"; } @RequestMapping("/getAll") public Map<String,String> getAllMsg(){ Map<String,String> map = new HashMap<>(); List<CateGory> cateGoryList = cateGoryMapper.selectList(); map.put("CateGory",cateGoryList.toString()); List<User1> user1List = user1Mapper.selectList(); map.put("User1",user1List.toString()); List<Teacher> teacherList = teacherMapper.selectList(); map.put("Teacher",teacherList.toString()); return map; } }