spring boot 配置双数据源mysql、sqlServer
背景:原来一直都是使用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。下面是具体步骤以及遇到的一系列问题。
一、导入依赖
1 pom.xml文件: 2 <dependency> 3 <groupId>com.microsoft.sqlserver</groupId> 4 <artifactId>mssql-jdbc</artifactId> 5 <version>6.4.0.jre8</version> 6 <scope>runtime</scope> 7 </dependency>
二.在application.properties中配置
1 spring.datasource.second.jdbc-url=jdbc:sqlserver://xxxxx:1433;DatabaseName=test 2 spring.datasource.second.username=xxx 3 spring.datasource.second.password=xxxx 4 spring.datasource.second.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver 5 6 注意1:连接数据库的方式不一样,mysql是/test ,sqlServer是;DatabaseName=test 7 spring.datasource.url=jdbc:mysql://xxxx/test 8 spring.datasource.second.jdbc-url=jdbc:sqlserver://xxxxx:1433;DatabaseName=test
三.配置sqlServer数据源
1 package com.ieou.qmt.common; 2 import org.apache.ibatis.session.SqlSessionFactory; 3 import org.mybatis.spring.SqlSessionFactoryBean; 4 import org.springframework.beans.factory.annotation.Qualifier; 5 import org.springframework.boot.context.properties.ConfigurationProperties; 6 import org.springframework.boot.jdbc.DataSourceBuilder; 7 import org.springframework.context.annotation.Bean; 8 import org.springframework.context.annotation.Configuration; 9 import org.springframework.core.io.support.PathMatchingResourcePatternResolver; 10 import org.springframework.core.io.support.ResourcePatternResolver; 11 import org.springframework.jdbc.core.JdbcTemplate; 12 import org.springframework.jdbc.datasource.DataSourceTransactionManager; 13 import org.springframework.transaction.PlatformTransactionManager; 14 import javax.sql.DataSource; 15 16 @Configuration 17 public class SqlServerDataSourceConfig { 18 19 @Bean(name = "sqlServerDataSource") 20 @Qualifier("sqlServerDataSource") 21 @ConfigurationProperties(prefix="spring.datasource.second") 22 public DataSource getMyDataSource(){ 23 return DataSourceBuilder.create().build(); 24 } 25 26 @Bean(name = "secondaryJdbcTemplate") 27 public JdbcTemplate secondaryJdbcTemplate( 28 @Qualifier("sqlServerDataSource") DataSource dataSource) { 29 return new JdbcTemplate(dataSource); 30 } 31 }
总结:
配置到这里就可以使用JdbcTemplate来操作sqlServer了,(mysql是spring boot的自动化配置,sqlServer是我们手动配置的)只要在类中注入即可
例如:(JdbcTemplate 的用法自行百度)
public class IEOUMallServiceImpl implements IEOUMallService{
@Autowired
@Qualifier("secondaryJdbcTemplate")
protected JdbcTemplate jdbcTemplate2;
}
四.以上配置完成后在执行带有@Transactional 事务的接口时会发现报错:
1 "org.springframework.beans.factory.NoSuchBeanDefinitionException: No qualifying bean of type 2 3 'org.springframework.transaction.PlatformTransactionManager' available\n\tat 4 5 org.springframework.beans.factory.support.DefaultListableBeanFactory.getBean(DefaultListableBeanFactory.java:347)\n\tat 6 7 org.springframework.beans.factory.support.DefaultListableBeanFactory.getBean(DefaultListableBeanFactory.java:334)\n\tat 8 9 org.springframework.transaction.interceptor.TransactionAspectSupport.determineTransactionManage
所以:我们必须手动分别配置mysql与sqlServer
1.配置文件:application.properties 需要改为如下:
1 spring.datasource.first.jdbc-url=jdbc:mysql://xxxx:3306/test 2 (这里要是jdbc-url,不然会报jdbcUrl is required with driverClassName的错误) 3 spring.datasource.first.type=com.alibaba.druid.pool.DruidDataSource 4 spring.datasource.first.driver-class-name=com.mysql.jdbc.Driver 5 spring.datasource.first.username=xxx 6 spring.datasource.first.password=xxx 7 8 spring.datasource.second.jdbc-url=jdbc:sqlserver://xxxx:1433;DatabaseName=test 9 spring.datasource.second.username=xxx 10 spring.datasource.second.password=xxx 11 spring.datasource.second.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
2.原来的SqlServerDataSourceConfig改为如下:
1 package com.ieou.qmt.common; 2 3 import org.apache.ibatis.session.SqlSessionFactory; 4 import org.mybatis.spring.SqlSessionFactoryBean; 5 import org.springframework.beans.factory.annotation.Qualifier; 6 import org.springframework.boot.context.properties.ConfigurationProperties; 7 import org.springframework.boot.jdbc.DataSourceBuilder; 8 import org.springframework.context.annotation.Bean; 9 import org.springframework.context.annotation.Configuration; 10 import org.springframework.core.io.support.PathMatchingResourcePatternResolver; 11 import org.springframework.core.io.support.ResourcePatternResolver; 12 import org.springframework.jdbc.core.JdbcTemplate; 13 import org.springframework.jdbc.datasource.DataSourceTransactionManager; 14 import org.springframework.transaction.PlatformTransactionManager; 15 16 import javax.sql.DataSource; 17 18 @Configuration 19 public class SqlServerDataSourceConfig { 20 21 private static final String MAPPER_PATH = "classpath:mybatis/mapping/mapper/*.xml"; 22 23 private static final String ENTITY_PACKAGE = "com.ieou.qmt.mapper"; 24 25 @Bean(name = "sqlServerDataSource") 26 @Qualifier("sqlServerDataSource") 27 @ConfigurationProperties(prefix="spring.datasource.second") 28 public DataSource getMyDataSource(){ 29 return DataSourceBuilder.create().build(); 30 } 31 32 @Bean(name = "secondaryJdbcTemplate") 33 public JdbcTemplate secondaryJdbcTemplate( 34 @Qualifier("sqlServerDataSource") DataSource dataSource) { 35 return new JdbcTemplate(dataSource); 36 } 37 38 @Bean(name = "second.SqlSessionTemplate") 39 public SqlSessionFactory devSqlSessionFactory( 40 @Qualifier("sqlServerDataSource") DataSource ddataSource) 41 throws Exception { 42 final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean(); 43 sessionFactory.setDataSource(ddataSource); 44 ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver(); 45 sessionFactory.setMapperLocations(resolver.getResources(MAPPER_PATH)); 46 sessionFactory.setTypeAliasesPackage(ENTITY_PACKAGE); 47 return sessionFactory.getObject(); 48 } 49 50 @Bean 51 public PlatformTransactionManager sqlServerTransactionManager(@Qualifier("sqlServerDataSource") DataSource sqlServerDataSource) 52 { 53 return new DataSourceTransactionManager(sqlServerDataSource); 54 } 55 56 }
3.新建mysql配置文件如下:
1 package com.ieou.qmt.common; 2 3 import org.apache.ibatis.session.SqlSessionFactory; 4 import org.mybatis.spring.SqlSessionFactoryBean; 5 import org.springframework.beans.factory.annotation.Qualifier; 6 import org.springframework.boot.context.properties.ConfigurationProperties; 7 import org.springframework.boot.jdbc.DataSourceBuilder; 8 import org.springframework.context.annotation.Bean; 9 import org.springframework.context.annotation.Configuration; 10 import org.springframework.context.annotation.Primary; 11 import org.springframework.core.io.support.PathMatchingResourcePatternResolver; 12 import org.springframework.core.io.support.ResourcePatternResolver; 13 import org.springframework.jdbc.datasource.DataSourceTransactionManager; 14 import org.springframework.transaction.PlatformTransactionManager; 15 16 import javax.sql.DataSource; 17 18 @Configuration 19 public class MysqlDataSourceConfig { 20 21 private static final String MAPPER_PATH = "classpath:mybatis/mapping/*.xml"; 22 23 private static final String ENTITY_PACKAGE = "com.ieou.qmt.mapper"; 24 25 26 @Bean(name = "dataSource") 27 @Primary 28 @Qualifier("dataSource") 29 @ConfigurationProperties(prefix="spring.datasource.first") 30 public DataSource getMyDataSource(){ 31 return DataSourceBuilder.create().build(); 32 } 33 34 @Bean(name = "first.SqlSessionTemplate") 35 @Primary 36 public SqlSessionFactory devSqlSessionFactory( 37 @Qualifier("dataSource") DataSource ddataSource) 38 throws Exception { 39 final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean(); 40 sessionFactory.setDataSource(ddataSource); 41 ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver(); 42 sessionFactory.setMapperLocations(resolver.getResources(MAPPER_PATH)); 43 sessionFactory.setTypeAliasesPackage(ENTITY_PACKAGE); 44 return sessionFactory.getObject(); 45 } 46 47 @Bean 48 @Primary 49 public PlatformTransactionManager mysqlTransactionManager(@Qualifier("dataSource") DataSource mysqlDataSource) 50 { 51 return new DataSourceTransactionManager(mysqlDataSource); 52 } 53 }
4.从事务管理器中选择一个事务,在方法上加@Transactional(value = "mysqlTransactionManager",rollbackFor = Exception.class)
至此:配置完毕,如有不足或不对的地方,请补充。
hello world!!!