SpringBoot+Mybatis配置多源数据库
方法一
配置多个数据库
spring: datasource: master: driver-class-name: com.mysql.jdbc.Driver jdbc-url: jdbc:mysql://localhost:3305/se?useSSL=false&characterEncoding=utf8&serverTimezone=GMT%2B8 username: root password: root movies: driver-class-name: com.mysql.jdbc.Driver jdbc-url: jdbc:mysql://localhost:3307/se_movies?useSSL=false&characterEncoding=utf8&serverTimezone=GMT%2B8 username: root password: root 复制代码
配置数据库连接
package com.my.equipment.config.oldConfig; 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.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.jdbc.datasource.DataSourceTransactionManager; import javax.sql.DataSource; @Configuration @MapperScan(basePackages = "com.my.equipment.web.seMoviesDao", sqlSessionTemplateRef = "seMoviesSqlSessionTemplate") public class SeMoviesDatasourceConfig { @Bean(name = "seMoviesDataSource") @ConfigurationProperties(prefix = "spring.datasource.movies") public DataSource testDataSource() { return DataSourceBuilder.create().build(); } @Bean(name = "seMoviesSqlSessionFactory") public SqlSessionFactory testSqlSessionFactory(@Qualifier("seMoviesDataSource") DataSource dataSource) throws Exception { SqlSessionFactoryBean bean = new SqlSessionFactoryBean(); bean.setDataSource(dataSource); bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml")); return bean.getObject(); } @Bean(name = "seMoviesTransactionManager") public DataSourceTransactionManager testTransactionManager(@Qualifier("seMoviesDataSource") DataSource dataSource) { return new DataSourceTransactionManager(dataSource); } @Bean(name = "seMoviesSqlSessionTemplate") public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("seMoviesSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception { return new SqlSessionTemplate(sqlSessionFactory); } } 复制代码package com.my.equipment.config.oldConfig; 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.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.jdbc.datasource.DataSourceTransactionManager; import javax.sql.DataSource; @Configuration @MapperScan(basePackages = "com.my.equipment.web.dao",sqlSessionTemplateRef = "seSqlSessionTemplate") public class SeDatasourceConfig { @Bean(name = "seDataSource") @ConfigurationProperties(prefix = "spring.datasource.master") @Primary public DataSource testDataSource() { return DataSourceBuilder.create().build(); } @Bean(name = "seSqlSessionFactory") @Primary public SqlSessionFactory testSqlSessionFactory(@Qualifier("seDataSource") DataSource dataSource) throws Exception { SqlSessionFactoryBean bean = new SqlSessionFactoryBean(); bean.setDataSource(dataSource); bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml")); return bean.getObject(); } @Bean(name = "seTransactionManager") @Primary public DataSourceTransactionManager testTransactionManager(@Qualifier("seDataSource") DataSource dataSource) { return new DataSourceTransactionManager(dataSource); } @Bean(name = "seSqlSessionTemplate") @Primary public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("seSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception { return new SqlSessionTemplate(sqlSessionFactory); } } 复制代码方法2
1.配置多个数据库
spring: datasource: master: driver-class-name: com.mysql.jdbc.Driver jdbc-url: jdbc:mysql://localhost:3305/se?useSSL=false&characterEncoding=utf8&serverTimezone=GMT%2B8 username: root password: root slave: driver-class-name: com.mysql.jdbc.Driver jdbc-url: jdbc:mysql://localhost:3310/se?useSSL=false&characterEncoding=utf8&serverTimezone=GMT%2B8 username: root password: root movies: driver-class-name: com.mysql.jdbc.Driver jdbc-url: jdbc:mysql://localhost:3307/se_movies?useSSL=false&characterEncoding=utf8&serverTimezone=GMT%2B8 username: root password: root 复制代码
2.配置数据库连接
定义多元数据库
package com.my.equipment.utils; public enum DBTypeEnum { MASTER,SLAVE,MOVIES; } 复制代码定义数据源切换
package com.my.equipment.utils; public class DBContextHolder { private static final ThreadLocal<DBTypeEnum> contextHolder=new ThreadLocal<>(); public static void set(DBTypeEnum dbTypeEnum){ contextHolder.set(dbTypeEnum); } public static DBTypeEnum get(){ return contextHolder.get(); } public static void master(){ set(DBTypeEnum.MASTER); System.out.println("写"); } public static void slave(){ set(DBTypeEnum.SLAVE); System.out.println("读"); } public static void movies(){ set(DBTypeEnum.MOVIES); System.out.println("movies"); } public static void clear(){ contextHolder.remove(); } } 复制代码重写路由选择类
package com.my.equipment.utils; import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource; import org.springframework.lang.Nullable; public class MyRoutingDataSource extends AbstractRoutingDataSource { @Nullable @Override protected Object determineCurrentLookupKey() { return DBContextHolder.get(); } } 复制代码配置Mybatis SqlSessionFactory 和事务管理器
package com.my.equipment.config; import org.apache.ibatis.jdbc.SQL; import org.apache.ibatis.session.SqlSessionFactory; import org.mybatis.spring.SqlSessionFactoryBean; import org.springframework.beans.factory.annotation.Value; 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.datasource.DataSourceTransactionManager; import org.springframework.transaction.PlatformTransactionManager; import org.springframework.transaction.annotation.EnableTransactionManagement; import javax.annotation.Resource; import javax.sql.DataSource; @Configuration @EnableTransactionManagement public class MyBatisConfig { @Value("${mybatis.mapper-locations}") private String mapperLocation; @Resource(name = "myRoutingDataSource") private DataSource myRoutingDataSource; @Bean public SqlSessionFactory sqlSessionFactory() throws Exception { SqlSessionFactoryBean sqlSessionFactoryBean=new SqlSessionFactoryBean(); sqlSessionFactoryBean.setDataSource(myRoutingDataSource); ResourcePatternResolver resolver=new PathMatchingResourcePatternResolver(); sqlSessionFactoryBean.setMapperLocations(resolver.getResources(mapperLocation)); return sqlSessionFactoryBean.getObject(); } @Bean public PlatformTransactionManager platformTransactionManager(){ return new DataSourceTransactionManager(myRoutingDataSource); } } 复制代码配置数据源
package com.my.equipment.config; import com.my.equipment.utils.DBTypeEnum; import com.my.equipment.utils.MyRoutingDataSource; 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 javax.sql.DataSource; import java.util.HashMap; import java.util.Map; @Configuration public class DatasourceConfig { /** * 配置从数据库 * @return */ @Bean(name = "slaveDataSource") @ConfigurationProperties("spring.datasource.slave") public DataSource slaveDataSource(){ return DataSourceBuilder.create().build(); } /** * 配置主数据库 * @return */ @Bean(name = "masterDataSource") @ConfigurationProperties("spring.datasource.master") public DataSource masterDataSource(){ return DataSourceBuilder.create().build(); } @Bean(name = "moviesDataSource") @ConfigurationProperties("spring.datasource.movies") public DataSource moviesDataSource(){ return DataSourceBuilder.create().build(); } @Bean public DataSource myRoutingDataSource(@Qualifier("slaveDataSource") DataSource slaveDataSource, @Qualifier("masterDataSource") DataSource masterDataSource, @Qualifier("moviesDataSource") DataSource moviesDataSource){ Map<Object,Object> targetDataSource=new HashMap<>(); targetDataSource.put(DBTypeEnum.MASTER,masterDataSource); targetDataSource.put(DBTypeEnum.SLAVE,slaveDataSource); targetDataSource.put(DBTypeEnum.MOVIES,moviesDataSource); MyRoutingDataSource myRoutingDataSource=new MyRoutingDataSource(); //找不到用默认数据源 myRoutingDataSource.setDefaultTargetDataSource(masterDataSource); //可选择的目标数据源 myRoutingDataSource.setTargetDataSources(targetDataSource); return myRoutingDataSource; } } 复制代码切面实现数据源切换
package com.my.equipment.aspect; import com.my.equipment.utils.DBContextHolder; import org.aspectj.lang.annotation.Aspect; import org.aspectj.lang.annotation.Before; import org.aspectj.lang.annotation.Pointcut; import org.springframework.stereotype.Component; @Aspect @Component public class DataSourceAop { //com.my.equipment.web.dao及其子包下所有的方法 @Pointcut("execution(* com.my.equipment.web.dao..*.*(..))") public void writePoint(){ } //com.my.equipment.web.seSlaveDao及其子包下所有的方法(..*代表当前及其子目录) @Pointcut("execution(* com.my.equipment.web.seSlaveDao..*.*(..))") public void readPoint(){ } @Pointcut("execution(* com.my.equipment.web.seMoviesDao..*.*(..))") public void moviesPoint(){ } @Before("readPoint()") public void read(){ DBContextHolder.slave(); } @Before("writePoint()") public void write(){ DBContextHolder.master(); } @Before("moviesPoint()") public void movies(){ DBContextHolder.movies(); } }
作者：JJS
链接：https://juejin.cn/post/7017643660372082724