springcloud中配置多数据源
1、mybatis多数据源的使用场景： 操作同一台服务器上不同的数据库，或者多地机器上的相同或不相同数据库。 进行跨库操作、多数据源进行分离数据； 使得程序得以扩展，当一台或者部分服务器宕机，不影响整个应用的服务； 2、多数据源配置的思路： 数据隔离，把数据分别存放在test、test02这两个数据库中； 创建数据源Datasource指定使用的哪个数据源配置，并将其注入到SqlSessionFactory 会话工厂； 创建事务TransactionManager，需把数据源注入； 把SqlSessionFactory 注入，创建sqlSessionTemplate模板，使用模板操作mapper接口； 把模板放到mapper 路径下，并 @MapperScan注解 声明mapper接口位置；
1，在application.yml中配置两个数据源的信息
#配置多数据源 spring.datasource.iam.jdbc-url: jdbc:mysql://localhost:3306/iam spring.datasource.iam.username: root spring.datasource.iam.password: root spring.datasource.iam.driver-class-name: com.mysql.cj.jdbc.Driver spring.datasource.appstore.jdbc-url: jdbc:mysql://localhost:3306/appstore spring.datasource.appstore.username: root spring.datasource.appstore.password: root spring.datasource.appstore.driver-class-name: com.mysql.cj.jdbc.Driver
2，创建并配置两个数据源的datasourceConfig
2.1 iamDataSourceConfig配置
/** * 创建iam库的数据源 */ @Configuration @MapperScan(basePackages = "com.primeton.tags.mapper.iam",sqlSessionTemplateRef = "iamSqlSessionTemplate") public class IamDataSourceConfig { /** * 加载配置数据源 * @return * @Primary 是指具有默认值 */ @Bean(name="iamDataSource") //作用在方法上，注入；一般和@Qualifier配合使用 @ConfigurationProperties(prefix = "spring.datasource.iam") //指定加载哪个数据库配置 @Primary //是指默认值，多个数据源只能存在一个默认值，及仅能一个数据源使用该注解 public DataSource iamDataSource() { return DataSourceBuilder.create().build(); } /** * 创建会话工厂SqlSessionFactory * @param dataSource * @return * @throws Exception */ @Bean(name="iamSqlSessionFactory") @Primary public SqlSessionFactory iamSqlSessionFactory(@Qualifier("iamDataSource") DataSource dataSource) throws Exception { SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setDataSource(dataSource); sqlSessionFactoryBean.setTypeAliasesPackage("com.primeton.tags.entity"); //设置mybatis别名 sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/iam/*.xml")); return sqlSessionFactoryBean.getObject(); } /** * 创建事务 TransactionManager * @param dataSource * @return */ @Bean(name="iamTransactionManager") @Primary public DataSourceTransactionManager testTransactionManager(@Qualifier("iamDataSource") DataSource dataSource) { return new DataSourceTransactionManager(dataSource); } /** * 创建SqlSessionTemplate模版 * @param sqlSessionFactory * @return */ @Bean(name="iamSqlSessionTemplate") @Primary public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("iamSqlSessionFactory") SqlSessionFactory sqlSessionFactory){ return new SqlSessionTemplate(sqlSessionFactory); } }
2.2 AppstoreDataSourceConfig配置
@Configuration @MapperScan(basePackages = "com.primeton.tags.mapper.appstore",sqlSessionTemplateRef = "appstoreSqlSessionTemplate") public class AppstoreDataSourceConfig { @Bean(name="appstoreDataSource") @ConfigurationProperties(prefix = "spring.datasource.appstore") public DataSource appstoreDataSource() { return DataSourceBuilder.create().build(); } @Bean(name="appstoreSqlSessionFactory") public SqlSessionFactory appstoreSqlSessionFactory(@Qualifier("appstoreDataSource") DataSource dataSource) throws Exception { SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setDataSource(dataSource); sqlSessionFactoryBean.setTypeAliasesPackage("com.primeton.tags.entity"); sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/appstore/*.xml")); return sqlSessionFactoryBean.getObject(); } @Bean(name="appstoreTransactionManager") public DataSourceTransactionManager testTransactionManager(@Qualifier("appstoreDataSource") DataSource dataSource) { return new DataSourceTransactionManager(dataSource); } @Bean(name="appstoreSqlSessionTemplate") public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("appstoreSqlSessionFactory") SqlSessionFactory sqlSessionFactory){ return new SqlSessionTemplate(sqlSessionFactory); } }
3，编写mapper接口和对应的xml(跟单库时一样)；
@Mapper public interface TagMapper { List<Tag> selectAllTag(); }
<mapper namespace="com.primeton.tags.mapper.iam.TagMapper"> <select id="selectAllTag" resultType="tag"> select * from tag_tb t </select> </mapper>
4，单元测试
@SpringBootTest public class MultiDataSourceTest { @Autowired private TagMapper tagMapper; @Autowired private Tag2Mapper tag2Mapper; @Test public void testSelectAll() { List<Tag> tagList = tagMapper.selectAllTag(); List<Tag> tag2List = tag2Mapper.selectAllTag(); System.out.println("tagList:"+tagList); System.out.println("tag2List:"+tag2List); } }
开发中遇到的问题：
1，使用多数据源后别名失效；
博客：https://blog.csdn.net/xiaoxiao48/article/details/105725009
解决办法：在多数据源的配置中设置别名：
sqlSessionFactoryBean.setTypeAliasesPackage("com.primeton.tags.entity"); //设置mybatis别名
2，使用多数据源后mybatis日志失效：
博客：https://blog.csdn.net/qq_40791070/article/details/100665502
解决办法：
1，在yml中添加mybatis日志配置
mybatis:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
2，在某一个自定义的多数据源配置中进行配置；
注意：不能在每个多数据源配置中都添加，否则会报错。
@Bean(name="iamSqlSessionFactory") @Primary public SqlSessionFactory iamSqlSessionFactory(@Qualifier("iamDataSource") DataSource dataSource, MybatisProperties mybatisProperties) throws Exception { SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setDataSource(dataSource); sqlSessionFactoryBean.setTypeAliasesPackage("com.primeton.tags.entity"); //设置mybatis别名 sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/iam/*.xml")); sqlSessionFactoryBean.setConfiguration(mybatisProperties.getConfiguration()); return sqlSessionFactoryBean.getObject(); }
3，在配置了多数据源后，mybatis-plus分页功能失效
博客：https://blog.csdn.net/qq_22008739/article/details/88885732
解决办法：集成多数据源的时候需要手动配置分页插件
3.1 mybatis-plus分页配置
@Configuration public class MybatisPlusConfig { @Bean public PaginationInterceptor paginationInterceptor() { return new PaginationInterceptor(); } }
3.2 在多数据源配置中进行配置
@Bean(name="iamSqlSessionFactory") @Primary public SqlSessionFactory iamSqlSessionFactory(@Qualifier("iamDataSource") DataSource dataSource, MybatisProperties mybatisProperties) throws Exception { SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setDataSource(dataSource); sqlSessionFactoryBean.setTypeAliasesPackage("com.primeton.tags.entity"); //设置mybatis别名 sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/iam/*.xml")); sqlSessionFactoryBean.setConfiguration(mybatisProperties.getConfiguration()); //配置多数据源后打印日志 MybatisConfiguration config = new MybatisConfiguration(); config.addInterceptor(new PaginationInterceptor()); sqlSessionFactoryBean.setConfiguration(config); //解决配置多数据源后mybatis-plus分页失效问题 return sqlSessionFactoryBean.getObject(); }
1，在application.yml中配置两个数据源的信息
#配置多数据源spring.datasource.iam.jdbc-url: jdbc:mysql://localhost:3306/iamspring.datasource.iam.username: rootspring.datasource.iam.password: rootspring.datasource.iam.driver-class-name: com.mysql.cj.jdbc.Driver
spring.datasource.appstore.jdbc-url: jdbc:mysql://localhost:3306/appstorespring.datasource.appstore.username: rootspring.datasource.appstore.password: rootspring.datasource.appstore.driver-class-name: com.mysql.cj.jdbc.Driver123456789102，创建并配置两个数据源的datasourceConfig2.1 iamDataSourceConfig配置
/** * 创建iam库的数据源 */@Configuration@MapperScan(basePackages = "com.primeton.tags.mapper.iam",sqlSessionTemplateRef = "iamSqlSessionTemplate")public class IamDataSourceConfig {
/** * 加载配置数据源 * @return * @Primary 是指具有默认值 */ @Bean(name="iamDataSource") //作用在方法上，注入；一般和@Qualifier配合使用 @ConfigurationProperties(prefix = "spring.datasource.iam") //指定加载哪个数据库配置 @Primary //是指默认值，多个数据源只能存在一个默认值，及仅能一个数据源使用该注解 public DataSource iamDataSource() { return DataSourceBuilder.create().build(); }
/** * 创建会话工厂SqlSessionFactory * @param dataSource * @return * @throws Exception */ @Bean(name="iamSqlSessionFactory") @Primary public SqlSessionFactory iamSqlSessionFactory(@Qualifier("iamDataSource") DataSource dataSource) throws Exception { SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setDataSource(dataSource); sqlSessionFactoryBean.setTypeAliasesPackage("com.primeton.tags.entity"); //设置mybatis别名 sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/iam/*.xml")); return sqlSessionFactoryBean.getObject(); }
/** * 创建事务 TransactionManager * @param dataSource * @return */ @Bean(name="iamTransactionManager") @Primary public DataSourceTransactionManager testTransactionManager(@Qualifier("iamDataSource") DataSource dataSource) { return new DataSourceTransactionManager(dataSource); }
/** * 创建SqlSessionTemplate模版 * @param sqlSessionFactory * @return */ @Bean(name="iamSqlSessionTemplate") @Primary public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("iamSqlSessionFactory") SqlSessionFactory sqlSessionFactory){ return new SqlSessionTemplate(sqlSessionFactory); }}
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657582.2 AppstoreDataSourceConfig配置
@Configuration@MapperScan(basePackages = "com.primeton.tags.mapper.appstore",sqlSessionTemplateRef = "appstoreSqlSessionTemplate")public class AppstoreDataSourceConfig {
@Bean(name="appstoreDataSource") @ConfigurationProperties(prefix = "spring.datasource.appstore") public DataSource appstoreDataSource() { return DataSourceBuilder.create().build(); }
@Bean(name="appstoreSqlSessionFactory") public SqlSessionFactory appstoreSqlSessionFactory(@Qualifier("appstoreDataSource") DataSource dataSource) throws Exception { SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setDataSource(dataSource); sqlSessionFactoryBean.setTypeAliasesPackage("com.primeton.tags.entity"); sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/appstore/*.xml")); return sqlSessionFactoryBean.getObject(); }
@Bean(name="appstoreTransactionManager") public DataSourceTransactionManager testTransactionManager(@Qualifier("appstoreDataSource") DataSource dataSource) { return new DataSourceTransactionManager(dataSource); }
@Bean(name="appstoreSqlSessionTemplate") public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("appstoreSqlSessionFactory") SqlSessionFactory sqlSessionFactory){ return new SqlSessionTemplate(sqlSessionFactory); }}12345678910111213141516171819202122232425262728293，编写mapper接口和对应的xml(跟单库时一样)；
@Mapperpublic interface TagMapper { List<Tag> selectAllTag();}1234<mapper namespace="com.primeton.tags.mapper.iam.TagMapper"> <select id="selectAllTag" resultType="tag"> select * from tag_tb t </select></mapper>12345674，单元测试
@SpringBootTestpublic class MultiDataSourceTest { @Autowired private TagMapper tagMapper; @Autowired private Tag2Mapper tag2Mapper;
@Test public void testSelectAll() { List<Tag> tagList = tagMapper.selectAllTag(); List<Tag> tag2List = tag2Mapper.selectAllTag(); System.out.println("tagList:"+tagList); System.out.println("tag2List:"+tag2List); }}123456789101112131415开发中遇到的问题：
1，使用多数据源后别名失效；博客：https://blog.csdn.net/xiaoxiao48/article/details/105725009
解决办法：在多数据源的配置中设置别名：
sqlSessionFactoryBean.setTypeAliasesPackage("com.primeton.tags.entity"); //设置mybatis别名12，使用多数据源后mybatis日志失效：博客：https://blog.csdn.net/qq_40791070/article/details/100665502解决办法：1，在yml中添加mybatis日志配置
mybatis: configuration: log-impl: org.apache.ibatis.logging.stdout.StdOutImpl1232，在某一个自定义的多数据源配置中进行配置；注意：不能在每个多数据源配置中都添加，否则会报错。
@Bean(name="iamSqlSessionFactory") @Primary public SqlSessionFactory iamSqlSessionFactory(@Qualifier("iamDataSource") DataSource dataSource, MybatisProperties mybatisProperties) throws Exception { SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setDataSource(dataSource); sqlSessionFactoryBean.setTypeAliasesPackage("com.primeton.tags.entity"); //设置mybatis别名 sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/iam/*.xml")); sqlSessionFactoryBean.setConfiguration(mybatisProperties.getConfiguration()); return sqlSessionFactoryBean.getObject(); }123456789103，在配置了多数据源后，mybatis-plus分页功能失效
博客：https://blog.csdn.net/qq_22008739/article/details/88885732解决办法：集成多数据源的时候需要手动配置分页插件3.1 mybatis-plus分页配置
@Configurationpublic class MybatisPlusConfig { @Bean public PaginationInterceptor paginationInterceptor() { return new PaginationInterceptor(); }}12345673.2 在多数据源配置中进行配置
@Bean(name="iamSqlSessionFactory") @Primary public SqlSessionFactory iamSqlSessionFactory(@Qualifier("iamDataSource") DataSource dataSource, MybatisProperties mybatisProperties) throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setDataSource(dataSource); sqlSessionFactoryBean.setTypeAliasesPackage("com.primeton.tags.entity"); //设置mybatis别名 sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/iam/*.xml")); sqlSessionFactoryBean.setConfiguration(mybatisProperties.getConfiguration()); //配置多数据源后打印日志 MybatisConfiguration config = new MybatisConfiguration(); config.addInterceptor(new PaginationInterceptor()); sqlSessionFactoryBean.setConfiguration(config); //解决配置多数据源后mybatis-plus分页失效问题 return sqlSessionFactoryBean.getObject(); }————————————————版权声明：本文为CSDN博主「qiaosaifei」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。原文链接：https://blog.csdn.net/qiaosaifei/article/details/117743659
