Mybatis配置多数据源

前言

Spring Boot项目使用Mybatis，既要从上游系统同步数据，又要操作本系统的数据库，所以需要引入双数据源，配置Mybatis

步骤

一、配置双数据源，连接数据库

1、禁用Spring Boot数据源的自动装配，在启动类@SpringBootApplication注解添加exclude = {DataSourceAutoConfiguration.class}

@SpringBootApplication(scanBasePackages = {"com.linkus"}, exclude = {DataSourceAutoConfiguration.class, MybatisAutoConfiguration.class})
public class Application {

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }

2、application.properties配置文件添加两个数据源配置

ora.datasource.driverClassName=oracle.jdbc.OracleDriver
ora.datasource.url=jdbc:oracle:thin:@ip:port:db
ora.datasource.username=xxx
ora.datasource.password=xxx

spring.datasource.driverClassName=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://ip:port/db
spring.datasource.username=xxx
spring.datasource.password=xxx

3、添加oracle数据源配置类PrimaryDataSourceConfig，下面的pg数据源配置类DmpDataSourceConfig，添加对应数据源的bean
PrimaryDataSourceConfig

@Primary
@Bean(name = "primaryDataSource")
@ConfigurationProperties(prefix = "ora.datasource")
public DataSource oraDataSource() {
    return DruidDataSourceBuilder.create().build();
}

DmpDataSourceConfig

@Bean(name = "dmpDataSource")
@ConfigurationProperties(prefix = "spring.datasource")
public DataSource dataSource() {
    return DruidDataSourceBuilder.create().build();
}

两个数据源添加完成

二、配置两个Mybatis

1、禁用Mybatis自动装配，启动类@SpringBootApplication注解添加exclude = {DataSourceAutoConfiguration.class, MybatisAutoConfiguration.class}

2、在application.properties配置文件添加两个Mybatis的配置和对应的配置类

主数据源的mybatis配置

# mybatis mapper.xml路径
ora.datasource.extend.mybatisMapperLocations=classpath*:mapper/crm/*.xml

主数据源的配置类

@Configuration
@ConfigurationProperties(prefix = "ora.datasource.extend")
public class PrimaryMybatisProperties {

    private String mybatisMapperLocations;

    public String getMybatisMapperLocations() {
        return mybatisMapperLocations;
    }

    public void setMybatisMapperLocations(String mybatisMapperLocations) {
        this.mybatisMapperLocations = mybatisMapperLocations;
    }
}

数据源2的配置

# mybatis mapper.xml路径
spring.datasource.extend.mybatisMapperLocations=classpath*:mapper/dmp/*.xml

数据源2的配置类

@Configuration
@ConfigurationProperties(prefix = "spring.datasource.extend")
public class DmpMybatisProperties {

    private String mybatisMapperLocations;

    public String getMybatisMapperLocations() {
        return mybatisMapperLocations;
    }

    public void setMybatisMapperLocations(String mybatisMapperLocations) {
        this.mybatisMapperLocations = mybatisMapperLocations;
    }
}

3、配置两个Mybatis的SqlSessionFactory

@Configuration
@MapperScan(basePackages = {"com.linkus.abp.mapper.crm"}, sqlSessionFactoryRef = "primarySqlSessionFactory")
@Slf4j
public class PrimaryDataSourceConfig {

    @Autowired
    private PrimaryMybatisProperties properties;

    @Primary
    @Bean(name = "primaryDataSource")
    @ConfigurationProperties(prefix = "ora.datasource")
    public DataSource oraDataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean(name = "primarySqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactory(@Qualifier("primaryDataSource") DataSource dataSource) {
        try {
            SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
            sqlSessionFactoryBean.setDataSource(dataSource);
            sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                    .getResources(this.properties.getMybatisMapperLocations()));
            log.info("config primarySqlSessionFactory success.");
            return sqlSessionFactoryBean.getObject();
        } catch (Exception e) {
            log.error("config primarySqlSessionFactory failed.", e);
            e.printStackTrace();
        }
        return null;
    }

    @Bean(name = "primarySqlSessionTemplate")
    @Primary
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("primarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    @Bean(name = "primaryTransactionManager")
    @Primary
    public DataSourceTransactionManager transactionManager(@Qualifier("primaryDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

}

@Configuration
@MapperScan(basePackages = {"com.linkus.abp.mapper.dmp"}, sqlSessionFactoryRef = "dmpSqlSessionFactory")
@Slf4j
public class DmpDataSourceConfig {

    @Autowired
    private DmpMybatisProperties property;

    @Bean(name = "dmpDataSource")
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource dataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean(name = "dmpSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dmpDataSource") DataSource dataSource) {
        try {
            SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
            sessionFactory.setDataSource(dataSource);
            sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                    .getResources(this.property.getMybatisMapperLocations()));
            log.info("config dmpSqlSessionFactory success.");
            return sessionFactory.getObject();
        } catch (Exception e) {
            log.error("config dmpSqlSessionFactory failed.", e);
            e.printStackTrace();
        }
        return null;
    }

    @Bean(name = "dmpSqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("dmpSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    @Bean(name = "dmpTransactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier("dmpDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}

两个Mybatis配置完可以工作了，如果需要分页还要配置两个Mybatis的分页

【spring boot】 mybatis 配置双数据源 / 多数据源_mybatis 双数据源 - CSDN 博客

在 spring boot 项目中，使用 mybatis 操作数据库，很方便。经过亲测，比 jpa 好用。
mybatis 提供了代码生成器 mybatis generator。使用 mybatis generator 可以很方便的生成 mybatis 代码。
mybatis 分页插件 Mybatis-PageHelper，很好的解决了分页问题。
spring boot 项目默认提供 1 个数据源，多个数据源时，需要手动进行一些处理，如下。

概念

数据源：javax.sql.DataSource 类型。起到作为连接数据库的桥梁的作用。
mybatis SessionFactory：org.apache.ibatis.session.SqlSessionFactory 类型。mybatis 通过该类获取数据源。
Mybatis PageHelper：mybatis 分页插件，通过该插件可以很方便的实现分页。
mybatis generator：mybatis 代码生成工具，通过该工具可以很方便的生成 mybatis 代码。

步骤简述

先配置 2 个数据源，分别连 2 个数据库。
再配置 2 个 mybatis，使 mybatis 能够工作。
然后配置 2 个 mybatis 的分页插件，可以使用 pagehelper 处理分页。
最后，配置 2 个 mybatis generator，能够生成 2 个 mybatis 的代码。

配置 2 个数据源

spring boot AutoConfigure 提供的 DataSourceAutoConfiguration 类，自动配置数据源。因此，先禁用 DataSourceAutoConfiguration 类。

...
@SpringBootApplication(exclude = { 
		DataSourceAutoConfiguration.class})
...
public class Application {

	public static void main(String[] args) {
		SpringApplication.run(Application.class, args);
	}
}

假设，要配置两个数据源，连接两个数据库，分别为 db1、db2，需要做如下操作：
1，在 application.properties 配置文件中添加如下 db1 和 db2 的 jdbc 配置。

spring.datasource.db1.driverClassName =oracle.jdbc.OracleDriver
spring.datasource.db1.jdbcUrl=jdbc:oracle:thin:@<db1 ip>/db1
spring.datasource.db1.username=xxx
spring.datasource.db1.password=xxx
spring.datasource.db1.platform=oracle

spring.datasource.db2.driverClassName =oracle.jdbc.OracleDriver
spring.datasource.db2.jdbcUrl=jdbc:oracle:thin:@<db2 ip>/db2
spring.datasource.db2.username=xxx
spring.datasource.db2.password=xxx
spring.datasource.db2.platform=oracle

2，添加 db1 数据源配置类 PrimaryMybatisConfiguration，db2 数据源配置类 Db2MybatisConfiguration。
3，类 PrimaryMybatisConfiguration 中，添加 db1 DataSourcebean。类 Db2MybatisConfiguration 中，添加 db2 DataSourcebean。
PrimaryMybatisConfiguration

    @Bean(name = "primaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.db1")
    @Primary
    public DataSource dataSource() {
    	log.info("config primaryDataSource success.");
    	return DataSourceBuilder.create().build();
    }

Db2MybatisConfiguration

    @Bean(name = "db2DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.db2")
    public DataSource dataSource() {
    	log.info("config db2DataSource success.");
    	return DataSourceBuilder.create().build();
    }

至此，两个数据源（db1、db2），添加完成。
注：上面代码中，默认的数据源实现类：com.zaxxer.hikari.HikariDataSource。

配置 2 个 mybatis

mybatis spring boot AutoConfigure 提供的 MybatisAutoConfiguration 类，自动配置 Mybatis。因此，先禁用 MybatisAutoConfiguration 类。

...
@SpringBootApplication(exclude = { 
		DataSourceAutoConfiguration.class,
		MybatisAutoConfiguration.class})
...
public class Application {

	public static void main(String[] args) {
		SpringApplication.run(Application.class, args);
	}
}

假设，要配置两个 mybatis，分别使用 db1 数据源、db2 数据源，需要做如下操作：
1，在 application.properties 配置文件中添加如下 2 个 mybatis 配置和对应的 @Configuration 类。
使用 db1 数据源的 mybatis 的配置文件

spring.datasource.db1.extend.mybatisMapperLocations=classpath:config/mybatis/mapper/*.xml

使用 db1 数据源的 mybatis 的 @Configuration 类

...
@Configuration
@ConfigurationProperties(prefix = "spring.datasource.db1.extend")
public class PrimaryMybatisProperties {
	
	private String mybatisMapperLocations;
	...
}

使用 db2 数据源的 mybatis 的配置文件

spring.datasource.db2.extend.mybatisMapperLocations=classpath:config/mybatis/mapper2/*.xml

使用 db2 数据源的 mybatis 的 @Configuration 类

...
@Configuration
@ConfigurationProperties(prefix = "spring.datasource.db2.extend")
public class Db2MybatisProperties {
	
	private String mybatisMapperLocations;
	...
}

2，配置 2 个 mybatis 的 SqlSessionFactory
PrimaryMybatisConfiguration

@Configuration
@MapperScan(basePackages = { "xxx.db.mapper" },sqlSessionFactoryRef="primarySqlSessionFactory")
public class PrimaryMybatisConfiguration {
	private Logger log = LoggerFactory.getLogger(this.getClass());

    @Autowired
    private PrimaryMybatisProperties property;

    @Bean(name = "primaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.db1")
    @Primary
    public DataSource dataSource() {
    	log.info("config primaryDataSource success.");
    	return DataSourceBuilder.create().build();
    }

    @Bean(name = "primarySqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactory(
    		@Qualifier("primaryDataSource") DataSource dataSource) {
        try {
            SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
            sessionFactory.setDataSource(dataSource);
            sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                    .getResources(this.property.getMybatisMapperLocations()));
            log.info("config primarySqlSessionFactory success.");
            return sessionFactory.getObject();
        } catch (Exception e) {
            log.error("config primarySqlSessionFactory failure.", e);
            return null;
        }
    }
    
    @Bean(name = "primarySqlSessionTemplate")
    @Primary
    public SqlSessionTemplate testSqlSessionTemplate(
    		@Qualifier("primarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
    
    @Bean(name = "primaryTransactionManager")
    @Primary
    public DataSourceTransactionManager transactionManager(
    		@Qualifier("primaryDataSource") DataSource dataSource) {
    	return new DataSourceTransactionManager(dataSource);
    }
}

Db2MybatisConfiguration

@Configuration
@MapperScan(basePackages = { "xxx.db2.mapper" },sqlSessionFactoryRef="db2SqlSessionFactory")
public class Db2MybatisConfiguration {
	private Logger log = LoggerFactory.getLogger(this.getClass());

    @Autowired
    private Db2MybatisProperties property;

    @Bean(name = "db2DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.db2")
    public DataSource dataSource() {
    	log.info("config db2DataSource success.");
    	return DataSourceBuilder.create().build();
    }

    @Bean(name = "db2SqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(
    		@Qualifier("db2DataSource") DataSource dataSource) {
        try {
            SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
            sessionFactory.setDataSource(dataSource);
            sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                    .getResources(this.property.getMybatisMapperLocations()));
            log.info("config db2SqlSessionFactory success.");
            return sessionFactory.getObject();
        } catch (Exception e) {
            log.error("config db2SqlSessionFactory failure.", e);
            return null;
        }
    }
    
    @Bean(name = "db2SqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(
    		@Qualifier("db2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
    
    @Bean(name = "db2TransactionManager")
    public DataSourceTransactionManager transactionManager(
    		@Qualifier("db2DataSource") DataSource dataSource) {
    	return new DataSourceTransactionManager(dataSource);
    }
}

至此，2 个 mybatis 的 SqlSessionFactory 已配置完成。此时，2 个 mybatis 已经可以工作。接下来需要配置分页插件。

配置 2 个 mybatis 的分页插件

pagehelper spring boot AutoConfigure 提供的 PageHelperAutoConfiguration 类，自动配置 Mybatis 的分页插件。因此，先禁用 PageHelperAutoConfiguration 类。

...
@SpringBootApplication(exclude = { 
		DataSourceAutoConfiguration.class,
		MybatisAutoConfiguration.class, 
		PageHelperAutoConfiguration.class })
...
public class Application {

	public static void main(String[] args) {
		SpringApplication.run(Application.class, args);
	}
}

假设，要配置两个 mybatis 的分页插件，需要做如下操作：
1，在 application.properties 配置文件中添加如下 2 个 mybatis 分页插件的配置和对应的 @Configuration 类。
使用 db1 数据源的 mybatis 分页插件的配置文件

spring.datasource.db1.extend.pagehelperDialect=Oracle
spring.datasource.db1.extend.pagehelperReasonable=true
spring.datasource.db1.extend.pagehelperSupportMethodsArguments=true
spring.datasource.db1.extend.pagehelperOffsetAsPageNum=true
spring.datasource.db1.extend.pagehelperRowBoundsWithCount=true
spring.datasource.db1.extend.pagehelperParams=count=countSql;pageNum=pageNumKey;pageSize=pageSizeKey;

使用 db1 数据源的 mybatis 分页插件的 @Configuration 类

...
@Configuration
@ConfigurationProperties(prefix = "spring.datasource.db1.extend")
public class PrimaryMybatisProperties {
	
	private String mybatisMapperLocations;
	private String pagehelperDialect;
	private String pagehelperReasonable;
	private String pagehelperSupportMethodsArguments;
	private String pagehelperParams;
	private String pagehelperOffsetAsPageNum;
	private String pagehelperRowBoundsWithCount;
	...
}

使用 db2 数据源的 mybatis 分页插件的配置文件

spring.datasource.db2.extend.pagehelperDialect=Oracle
spring.datasource.db2.extend.pagehelperReasonable=true
spring.datasource.db2.extend.pagehelperSupportMethodsArguments=true
spring.datasource.db2.extend.pagehelperOffsetAsPageNum=true
spring.datasource.db2.extend.pagehelperRowBoundsWithCount=true
spring.datasource.db2.extend.pagehelperParams=count=countSql;pageNum=pageNumKey;pageSize=pageSizeKey;

使用 db2 数据源的 mybatis 分页插件的 @Configuration 类

...
@Configuration
@ConfigurationProperties(prefix = "spring.datasource.db2.extend")
public class Db2MybatisProperties {
	
	private String mybatisMapperLocations;
	private String pagehelperDialect;
	private String pagehelperReasonable;
	private String pagehelperSupportMethodsArguments;
	private String pagehelperParams;
	private String pagehelperOffsetAsPageNum;
	private String pagehelperRowBoundsWithCount;
	...
}

2，配置 2 个 mybatis 的 SqlSessionFactory，增加分页插件
PrimaryMybatisConfiguration

@Configuration
@MapperScan(basePackages = { "xxx.db.mapper" },sqlSessionFactoryRef="primarySqlSessionFactory")
public class PrimaryMybatisConfiguration {
	private Logger log = LoggerFactory.getLogger(this.getClass());

    @Autowired
    private PrimaryMybatisProperties property;

    @Bean(name = "primaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.db1")
    @Primary
    public DataSource dataSource() {
    	log.info("config primaryDataSource success.");
    	return DataSourceBuilder.create().build();
    }

    @Bean(name = "primarySqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactory(
    		@Qualifier("primaryDataSource") DataSource dataSource) {
        try {
            SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
            sessionFactory.setDataSource(dataSource);
            sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                    .getResources(this.property.getMybatisMapperLocations()));
                    
			//分页插件
			Interceptor interceptor = new PageInterceptor();
			Properties properties = new Properties();
			properties.setProperty("helperDialect", this.property.getPagehelperDialect());
			properties.setProperty("reasonable", this.property.getPagehelperReasonable());
			properties.setProperty("supportMethodsArguments",this.property.getPagehelperSupportMethodsArguments());
			properties.setProperty("params",this.property.getPagehelperParams());
			interceptor.setProperties(properties);
			sessionFactory.setPlugins(new Interceptor[] {interceptor});
         
            log.info("config primarySqlSessionFactory success.");
            return sessionFactory.getObject();
        } catch (Exception e) {
            log.error("config primarySqlSessionFactory failure.", e);
            return null;
        }
    }
    
    @Bean(name = "primarySqlSessionTemplate")
    @Primary
    public SqlSessionTemplate testSqlSessionTemplate(
    		@Qualifier("primarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
    
    @Bean(name = "primaryTransactionManager")
    @Primary
    public DataSourceTransactionManager transactionManager(
    		@Qualifier("primaryDataSource") DataSource dataSource) {
    	return new DataSourceTransactionManager(dataSource);
    }
}

Db2MybatisConfiguration

@Configuration
@MapperScan(basePackages = { "xxx.db2.mapper" },sqlSessionFactoryRef="db2SqlSessionFactory")
public class Db2MybatisConfiguration {
	private Logger log = LoggerFactory.getLogger(this.getClass());

    @Autowired
    private Db2MybatisProperties property;

    @Bean(name = "db2DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.db2")
    public DataSource dataSource() {
    	log.info("config db2DataSource success.");
    	return DataSourceBuilder.create().build();
    }

    @Bean(name = "db2SqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(
    		@Qualifier("db2DataSource") DataSource dataSource) {
        try {
            SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
            sessionFactory.setDataSource(dataSource);
            sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                    .getResources(this.property.getMybatisMapperLocations()));

			//分页插件
			Interceptor interceptor = new PageInterceptor();
			Properties properties = new Properties();
			properties.setProperty("helperDialect", this.property.getPagehelperDialect());
			properties.setProperty("reasonable", this.property.getPagehelperReasonable());
			properties.setProperty("supportMethodsArguments",this.property.getPagehelperSupportMethodsArguments());
			properties.setProperty("params",this.property.getPagehelperParams());
			interceptor.setProperties(properties);
			sessionFactory.setPlugins(new Interceptor[] {interceptor});
			
            log.info("config db2SqlSessionFactory success.");
            return sessionFactory.getObject();
        } catch (Exception e) {
            log.error("config db2SqlSessionFactory failure.", e);
            return null;
        }
    }
    
    @Bean(name = "db2SqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(
    		@Qualifier("db2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
    
    @Bean(name = "db2TransactionManager")
    public DataSourceTransactionManager transactionManager(
    		@Qualifier("db2DataSource") DataSource dataSource) {
    	return new DataSourceTransactionManager(dataSource);
    }
}

配置 2 个 mybatis generator

1，在 pom.xml 中引入 mybatis generator

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	...
	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>

			<!-- mybatis generator 自动生成代码插件 -->
			<plugin>
				<groupId>org.mybatis.generator</groupId>
				<artifactId>mybatis-generator-maven-plugin</artifactId>
				<version>1.3.2</version>
				<configuration>
					<overwrite>true</overwrite>
					<verbose>true</verbose>
				</configuration>
				<dependencies>
					<dependency>
						<groupId>com.oracle.jdbc</groupId>
						<artifactId>ojdbc7</artifactId>
						<version>12.1.0.1</version>
					</dependency>
				</dependencies>
			</plugin>
		</plugins>
	</build>
</project>

2, 配置 2 个 mybatis generator 配置文件。
使用 db1 数据源的 mybatis generator 的配置文件 generatorConfig.xml.
使用 db1 数据源的 mybatis generator 的配置文件 generatorConfig2.xml.
3, 使用 mvn 执行 mybatis generator 命令。
使用 db1 数据源的 mybatis generator 生成代码命令

mvn -Dmybatis.generator.configurationFile=src/main/resources/generatorConfig.xml mybatis-generator:generate

使用 db2 数据源的 mybatis generator 生成代码命令

mvn -Dmybatis.generator.configurationFile=src/main/resources/generatorConfig2.xml mybatis-generator:generate

posted @ 2024-02-27 11:05 CharyGao 阅读(125) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部