SpringBoot系列--Mysql、Sqlserver 双数据源配置

2023-02-22 16:45:42

在近的项目开发中，需要用到Mysql和Sqlserverl两种数据库，也就是要进行双数据源的配置。网上看了下，大多比较繁琐，且不够明确。今天分享一个在SpringBoot 中简洁高效配置双数据源的方案。项目结构如下：

application.properties配置文件

spring
.
datasource
.
mysql
.
username
=
root



spring
.
datasource
.
mysql
.
password
=
123456



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



spring
.
datasource
.
mysql
.
url
=
jdbc
:
mysql
:
//localhost:3306/test






spring
.
datasource
.
sqlserver
.
username
=
root



spring
.
datasource
.
sqlserver
.
password
=
123456



spring
.
datasource
.
sqlserver
.
driver
-
class
-
name
=
com
.
microsoft
.
sqlserver
.
jdbc
.
SQLServerDriver



spring
.
datasource
.
sqlserver
.
url
=
jdbc
:
sqlserver
:
//localhost:1433;DatabaseName=test

连接池配置

package
 com
.
tcwong
.
demo
.
config
;






import
 com
.
alibaba
.
druid
.
spring
.
boot
.
autoconfigure
.
DruidDataSourceBuilder
;



import
 org
.
springframework
.
boot
.
autoconfigure
.
condition
.
ConditionalOnProperty
;



import
 org
.
springframework
.
context
.
annotation
.
Bean
;



import
 org
.
springframework
.
context
.
annotation
.
Configuration
;



import
 org
.
springframework
.
context
.
annotation
.
Primary
;






import
 javax
.
sql
.
DataSource
;






@Configuration



public
 
class
 
DataSourceConfig
 
{






    
@Primary



    
@Bean



    
@ConditionalOnProperty
(
prefix 
=
 
"spring.datasource.mysql"
)



    
public
 
DataSource
 mysqlDataSource
()
 
{



        
return
 
DruidDataSourceBuilder
.
create
().
build
();



    
}






    
@Bean



    
@ConditionalOnProperty
(
prefix 
=
 
"spring.datasource.sqlserver"
)



    
public
 
DataSource
 sqlserverDataSource
()
 
{



        
return
 
DruidDataSourceBuilder
.
create
().
build
();



    
}






}

或者

package
 com
.
tcwong
.
demo
.
config
;






import
 com
.
alibaba
.
druid
.
pool
.
DruidDataSource
;



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
 javax
.
sql
.
DataSource
;






@Configuration



public
 
class
 
DataSourceConfig1
 
{






    
@Value
(
"${spring.datasource.mysql.username}"
)



    
private
 
String
 mysqlUserName
;



    
@Value
(
"${spring.datasource.mysql.password}"
)



    
private
 
String
 mysqlPassword
;



    
@Value
(
"${spring.datasource.mysql.url}"
)



    
private
 
String
 mysqlUrl
;



    
@Value
(
"${spring.datasource.mysql.driver-class-name}"
)



    
private
 
String
 mysqlDriverClass
;






    
@Value
(
"${spring.datasource.sqlserver.username}"
)



    
private
 
String
 sqlserverPassword
;



    
@Value
(
"${spring.datasource.sqlserver.password}"
)



    
private
 
String
 sqlserverUserName
;



    
@Value
(
"${spring.datasource.sqlserver.url}"
)



    
private
 
String
 sqlserverUrl
;



    
@Value
(
"${spring.datasource.sqlserver.driver-class-name}"
)



    
private
 
String
 sqlserverDriverClass
;






    
@Primary



    
@Bean



    
public
 
DataSource
 mysqlDataSource
()
 
{



        
DruidDataSource
 druidDataSource 
=
 
new
 
DruidDataSource
();



        druidDataSource
.
setUsername
(
mysqlUserName
);



        druidDataSource
.
setPassword
(
mysqlPassword
);



        druidDataSource
.
setUrl
(
mysqlUrl
);



        druidDataSource
.
setDriverClassName
(
mysqlDriverClass
);



        
return
 druidDataSource
;



    
}






    
@Bean



    
public
 
DataSource
 sqlserverDataSource
()
 
{



        
DruidDataSource
 druidDataSource 
=
 
new
 
DruidDataSource
();



        druidDataSource
.
setUsername
(
sqlserverUserName
);



        druidDataSource
.
setPassword
(
sqlserverPassword
);



        druidDataSource
.
setUrl
(
sqlserverUrl
);



        druidDataSource
.
setDriverClassName
(
sqlserverDriverClass
);



        
return
 druidDataSource
;



    
}






}

MyBatis配置

Mysql配置

package
 com
.
tcwong
.
demo
.
config
;






import
 org
.
apache
.
ibatis
.
session
.
SqlSessionFactory
;



import
 org
.
mybatis
.
spring
.
SqlSessionFactoryBean
;



import
 org
.
mybatis
.
spring
.
SqlSessionTemplate
;



import
 org
.
mybatis
.
spring
.
annotation
.
MapperScan
;



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
 javax
.
annotation
.
Resource
;



import
 javax
.
sql
.
DataSource
;






@Configuration



@MapperScan
(
basePackages 
=
 
"com.tcwong.demo.dao.mysql"



        
,
sqlSessionFactoryRef 
=
 
"mysqlSqlSessionFactory"
,
sqlSessionTemplateRef 
=
 
"mysqlSqlSessionTemplate"
)



public
 
class
 
MysqlMapperConfig
 
{






    
@Resource



    
private
 
DataSource
 mysqlDataSource
;






    
@Primary



    
@Bean



    
SqlSessionFactory
 mysqlSqlSessionFactory
()
 
{



        
SqlSessionFactory
 sqlSessionFactory 
=
 
null
;



        
try
 
{



            
SqlSessionFactoryBean
 sqlSessionFactoryBean 
=
 
new
 
SqlSessionFactoryBean
();



            sqlSessionFactoryBean
.
setDataSource
(
mysqlDataSource
);



            sqlSessionFactoryBean
.
setTypeAliasesPackage
(
"com.tcwong.demo.bean"
);



            sqlSessionFactoryBean
.
setMapperLocations
(
new
 
PathMatchingResourcePatternResolver
()



                    
.
getResource
(
"classpath*:mapper/**/*.xml"
));



            sqlSessionFactory 
=
 sqlSessionFactoryBean
.
getObject
();



        
}
 
catch
 
(
Exception
 e
)
 
{



            e
.
printStackTrace
();



        
}



        
return
 sqlSessionFactory
;



    
}






    
@Primary



    
@Bean



    
SqlSessionTemplate
 mysqlSqlSessionTemplate
()
 
{



        
return
 
new
 
SqlSessionTemplate
(
mysqlSqlSessionFactory
());



    
}



}

Sqlserver配置

package
 com
.
tcwong
.
demo
.
config
;






import
 org
.
apache
.
ibatis
.
session
.
SqlSessionFactory
;



import
 org
.
mybatis
.
spring
.
SqlSessionFactoryBean
;



import
 org
.
mybatis
.
spring
.
SqlSessionTemplate
;



import
 org
.
mybatis
.
spring
.
annotation
.
MapperScan
;



import
 org
.
springframework
.
context
.
annotation
.
Bean
;



import
 org
.
springframework
.
context
.
annotation
.
Configuration
;



import
 org
.
springframework
.
core
.
io
.
support
.
PathMatchingResourcePatternResolver
;






import
 javax
.
annotation
.
Resource
;



import
 javax
.
sql
.
DataSource
;






@Configuration



@MapperScan
(
basePackages 
=
 
"com.tcwong.demo.dao.sqlserver"



        
,
sqlSessionFactoryRef 
=
 
"sqlserverSqlSessionFactory"
,
 sqlSessionTemplateRef 
=
 
"sqlserverSqlSessionTemplate"
)



public
 
class
 sqlserverMapperConfig 
{






    
@Resource



    
private
 
DataSource
 sqlserverDataSource
;






    
@Bean



    
SqlSessionFactory
 sqlserverSqlSessionFactory
()
 
{



        
SqlSessionFactory
 sqlSessionFactory 
=
 
null
;



        
try
 
{



            
SqlSessionFactoryBean
 sqlSessionFactoryBean 
=
 
new
 
SqlSessionFactoryBean
();



            sqlSessionFactoryBean
.
setDataSource
(
sqlserverDataSource
);



            sqlSessionFactoryBean
.
setTypeAliasesPackage
(
"com.tcwong.demo.bean"
);



            sqlSessionFactoryBean
.
setMapperLocations
(
new
 
PathMatchingResourcePatternResolver
()



                    
.
getResource
(
"classpath*:mapper/**/*.xml"
));



            sqlSessionFactory 
=
 sqlSessionFactoryBean
.
getObject
();



        
}
 
catch
 
(
Exception
 e
)
 
{



            e
.
printStackTrace
();



        
}



        
return
 sqlSessionFactory
;



    
}






    
@Bean



    
SqlSessionTemplate
 sqlserverSqlSessionTemplate
()
 
{



        
return
 
new
 
SqlSessionTemplate
(
sqlserverSqlSessionFactory
());



    
}



}