spring boot 配置双数据源mysql、sqlServer

时间:2021-10-12 21:00:11

背景:原来一直都是使用mysql数据库,在application.properties 中配置数据库信息

spring.datasource.url=jdbc:mysql://xxxx/test

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

spring.datasource.driver-class-name=com.mysql.jdbc.Driver

spring.datasource.username=xxx

spring.datasource.password=xxx

这样spring boot会自动化配置,我们用spring boot 约定的配置,现在由于业务的需要,另加一个数据源sqlServer。下面是具体步骤以及遇到的一系列问题。

一、导入依赖
 pom.xml文件:
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<version>6.4.0.jre8</version>
<scope>runtime</scope>
</dependency>
二.在application.properties中配置
 spring.datasource.second.jdbc-url=jdbc:sqlserver://xxxxx:1433;DatabaseName=test
spring.datasource.second.username=xxx
spring.datasource.second.password=xxxx
spring.datasource.second.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver 注意1:连接数据库的方式不一样,mysql是/test ,sqlServer是;DatabaseName=test
spring.datasource.url=jdbc:mysql://xxxx/test
spring.datasource.second.jdbc-url=jdbc:sqlserver://xxxxx:1433;DatabaseName=test
三.配置sqlServer数据源
 package com.ieou.qmt.common;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
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.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import javax.sql.DataSource; @Configuration
public class SqlServerDataSourceConfig { @Bean(name = "sqlServerDataSource")
@Qualifier("sqlServerDataSource")
@ConfigurationProperties(prefix="spring.datasource.second")
public DataSource getMyDataSource(){
return DataSourceBuilder.create().build();
} @Bean(name = "secondaryJdbcTemplate")
public JdbcTemplate secondaryJdbcTemplate(
@Qualifier("sqlServerDataSource") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
}

总结:

配置到这里就可以使用JdbcTemplate来操作sqlServer了,(mysql是spring boot的自动化配置,sqlServer是我们手动配置的)只要在类中注入即可
例如:(JdbcTemplate 的用法自行百度)
public class IEOUMallServiceImpl implements IEOUMallService{
@Autowired
@Qualifier("secondaryJdbcTemplate")
protected JdbcTemplate jdbcTemplate2;
}

四.以上配置完成后在执行带有@Transactional 事务的接口时会发现报错:
 "org.springframework.beans.factory.NoSuchBeanDefinitionException: No qualifying bean of type 

 'org.springframework.transaction.PlatformTransactionManager' available\n\tat 

 org.springframework.beans.factory.support.DefaultListableBeanFactory.getBean(DefaultListableBeanFactory.java:347)\n\tat 

 org.springframework.beans.factory.support.DefaultListableBeanFactory.getBean(DefaultListableBeanFactory.java:334)\n\tat 

 org.springframework.transaction.interceptor.TransactionAspectSupport.determineTransactionManage
所以:我们必须手动分别配置mysql与sqlServer
1.配置文件:application.properties 需要改为如下:
 spring.datasource.first.jdbc-url=jdbc:mysql://xxxx:3306/test
(这里要是jdbc-url,不然会报jdbcUrl is required with driverClassName的错误)
spring.datasource.first.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.first.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.first.username=xxx
spring.datasource.first.password=xxx spring.datasource.second.jdbc-url=jdbc:sqlserver://xxxx:1433;DatabaseName=test
spring.datasource.second.username=xxx
spring.datasource.second.password=xxx
spring.datasource.second.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
2.原来的SqlServerDataSourceConfig改为如下:
 package com.ieou.qmt.common;

 import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
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.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager; import javax.sql.DataSource; @Configuration
public class SqlServerDataSourceConfig { private static final String MAPPER_PATH = "classpath:mybatis/mapping/mapper/*.xml"; private static final String ENTITY_PACKAGE = "com.ieou.qmt.mapper"; @Bean(name = "sqlServerDataSource")
@Qualifier("sqlServerDataSource")
@ConfigurationProperties(prefix="spring.datasource.second")
public DataSource getMyDataSource(){
return DataSourceBuilder.create().build();
} @Bean(name = "secondaryJdbcTemplate")
public JdbcTemplate secondaryJdbcTemplate(
@Qualifier("sqlServerDataSource") DataSource dataSource) {
return new JdbcTemplate(dataSource);
} @Bean(name = "second.SqlSessionTemplate")
public SqlSessionFactory devSqlSessionFactory(
@Qualifier("sqlServerDataSource") DataSource ddataSource)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(ddataSource);
ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
sessionFactory.setMapperLocations(resolver.getResources(MAPPER_PATH));
sessionFactory.setTypeAliasesPackage(ENTITY_PACKAGE);
return sessionFactory.getObject();
} @Bean
public PlatformTransactionManager sqlServerTransactionManager(@Qualifier("sqlServerDataSource") DataSource sqlServerDataSource)
{
return new DataSourceTransactionManager(sqlServerDataSource);
} }
3.新建mysql配置文件如下:
 package com.ieou.qmt.common;

 import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
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.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager; import javax.sql.DataSource; @Configuration
public class MysqlDataSourceConfig { private static final String MAPPER_PATH = "classpath:mybatis/mapping/*.xml"; private static final String ENTITY_PACKAGE = "com.ieou.qmt.mapper"; @Bean(name = "dataSource")
@Primary
@Qualifier("dataSource")
@ConfigurationProperties(prefix="spring.datasource.first")
public DataSource getMyDataSource(){
return DataSourceBuilder.create().build();
} @Bean(name = "first.SqlSessionTemplate")
@Primary
public SqlSessionFactory devSqlSessionFactory(
@Qualifier("dataSource") DataSource ddataSource)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(ddataSource);
ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
sessionFactory.setMapperLocations(resolver.getResources(MAPPER_PATH));
sessionFactory.setTypeAliasesPackage(ENTITY_PACKAGE);
return sessionFactory.getObject();
} @Bean
@Primary
public PlatformTransactionManager mysqlTransactionManager(@Qualifier("dataSource") DataSource mysqlDataSource)
{
return new DataSourceTransactionManager(mysqlDataSource);
}
}
4.从事务管理器中选择一个事务,在方法上加@Transactional(value = "mysqlTransactionManager",rollbackFor = Exception.class)
 
至此:配置完毕,如有不足或不对的地方,请补充。