山石彦 | 作者
urlify.cn/vQzIne | 来源
最近工作中有用到双数据源,一个项目(中台)中需要操作两个不同的数据库。当时考虑到了两种方式,
1.通过http请求访问(A项目访问d1数据源,B项目访问d2数据源,B通过http访问A的接口)
2.配置双数据源(A项目访问d1数据源,B项目配置d1,d2数据源)
两种方式各有利弊,http简单,但是效率不如双数据源。双数据源相对复杂一些,但效率比较好,最后决定使用双数据源。
步骤如下:
1.applicationContext.properties配置文件中配置两个数据库连接信息
#db1数据库连接spring.datasource.d1.url=jdbc:oracle:thin:@localhost:1521:db1spring.datasource.d1.username=user1spring.datasource.d1.password=123456spring.datasource.d1.driver-class-name=oracle.jdbc.OracleDriver#验证连接的有效性spring.datasource.d1.test-while-idle=true#获取连接时候验证,会影响性能spring.datasource.d1.test-on-borrow=true#在连接归还到连接池时是否测试该连接spring.datasource.d1.test-on-return=truespring.datasource.d1.validation-query=SELECT 1 FROM DUAL#空闲连接回收的时间间隔,与test-while-idle一起使用,设置5分钟spring.datasource.d1.time-between-eviction-runs-millis=300000#连接池空闲连接的有效时间 ,设置30分钟spring.datasource.d1.min-evictable-idle-time-millis=1800000spring.datasource.d1.initial-size=5#指定连接池中最大的活跃连接数.spring.datasource.d1.max-active=50#指定连接池等待连接返回的最大等待时间,毫秒单位.spring.datasource.d1.max-wait=60000#指定必须保持连接的最小值spring.datasource.d1.min-idle=5#db2数据库连接spring.datasource.d2.url=jdbc:oracle:thin:@localhost:1521/db2spring.datasource.d2.username=user2spring.datasource.d2.password=123456spring.datasource.d2.driver-class-name=oracle.jdbc.OracleDriver#验证连接的有效性spring.datasource.d2.test-while-idle=true#获取连接时候验证,会影响性能spring.datasource.d2.test-on-borrow=true#在连接归还到连接池时是否测试该连接spring.datasource.d2.test-on-return=truespring.datasource.d2.validation-query=SELECT 1 FROM DUAL#空闲连接回收的时间间隔,与test-while-idle一起使用,设置5分钟spring.datasource.d2.time-between-eviction-runs-millis=300000#连接池空闲连接的有效时间 ,设置30分钟spring.datasource.d2.min-evictable-idle-time-millis=1800000spring.datasource.d2.initial-size=5#指定连接池中最大的活跃连接数.spring.datasource.d2.max-active=50#指定连接池等待连接返回的最大等待时间,毫秒单位.spring.datasource.d2.max-wait=60000#指定必须保持连接的最小值spring.datasource.d2.min-idle=5
2.配置文件Ds1MybatisConfig.java,Ds2MybatisConfig.java文件,配置数据源,sqlSessionFactory等信息。
@Configuration @MapperScan(value = {"com.test.d1.mapper"}, sqlSessionFactoryRef = "d1SqlSessionFactory") public class Ds1MybatisConfig{ @Primary @Bean(name = "d1DataSource") @ConfigurationProperties(prefix = "spring.datasource.d1") public DataSource d1DataSource() { return new org.apache.tomcat.jdbc.pool.DataSource(); } @Bean(name = "d1SqlSessionFactory") public SqlSessionFactory d1SqlSessionFactoryBean() throws Exception { SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setDataSource(d1DataSource()); sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath*:/mybatis/d1/*.xml")); return sqlSessionFactoryBean.getObject(); } @Bean public PlatformTransactionManager d1TransactionManager() { return new DataSourceTransactionManager(d1DataSource()); } }
@Configuration @MapperScan(value = {"com.test.d2.mapper"}, sqlSessionFactoryRef = "d2SqlSessionFactory") public class Ds2MybatisConfig{ @Bean(name = "d2DataSource") @ConfigurationProperties(prefix = "spring.datasource.d2") public DataSource d2DataSource() { return new org.apache.tomcat.jdbc.pool.DataSource(); } @Bean(name = "d2SqlSessionFactory") public SqlSessionFactory d2SqlSessionFactory() throws Exception { SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setDataSource(d2DataSource()); sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath*:/mybatis/d2/*Mapper.xml")); return sqlSessionFactoryBean.getObject(); } @Bean public PlatformTransactionManager d2TransactionManager() { return new DataSourceTransactionManager(d2DataSource()); } }
@Configuration @MapperScan(value = {"com.test.d2.mapper"}, sqlSessionFactoryRef = "d2SqlSessionFactory") public class Ds2MybatisConfig{ @Bean(name = "d2DataSource") @ConfigurationProperties(prefix = "spring.datasource.d2") public DataSource d2DataSource() { return new org.apache.tomcat.jdbc.pool.DataSource(); } @Bean(name = "d2SqlSessionFactory") public SqlSessionFactory d2SqlSessionFactory() throws Exception { SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setDataSource(d2DataSource()); sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath*:/mybatis/d2/*Mapper.xml")); return sqlSessionFactoryBean.getObject(); } @Bean public PlatformTransactionManager d2TransactionManager() { return new DataSourceTransactionManager(d2DataSource()); } }
注意:1.只能有一个数据源是主数据源,即只能有一个数据源上可以标注@Primary注解。
2.d1和d2分别是存放不同的数据mapper的文件夹,不同的数据源可以访问不同的文件夹下的数据
3.在启动类上加上@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class}) 排除Springboot的数据源自动配置类。
粉丝福利:108本java从入门到大神精选电子书领取