springboot mybatis连接多数据库

转载

mob64ca13fd559d 2024-07-14 06:34:44

文章标签 spring boot java spring mybatis 多数据源 文章分类 架构后端开发

1.配置多数据源后需要注意的地方

2.配置说明

1.配置多数据源后需要注意的地方

2.配置说明

多数据源的实现方法比较多，基于springboot和mybatis的情况下，有基于AOP进行动态数据源配置的实现，也有直接通过配置不同的SqlSessionFactory去扫描不同Mapper.xml目录去实现，也有通过dynamic-datasource-spring-boot-starter去实现。此外，还有通过各种第三方开源组件例如ShardingSphere实现的多数据源配置。本文着重介绍通过不同的SqlSessionFactory去实现多数据源配置。

1.配置多数据源后需要注意的地方

Springboot配置MybatisPlus(或Mybaits)多数据源后，常见的功能使用可能需要关注几个地方。

1.1数据源配置变化

eg:

	单数据源	多数据源
数据源url	spring.datasource.url	spring.datasource.one.jdbc-url

...

1.2 分页问题

使用多数据源后，分页可能会失效，这个时候需要重新往SqlSessionFactory注册分页拦截器。从MyBatis-Plus 3.4.0开始，不再使用旧版本的PaginationInterceptor ，而是使用MybatisPlusInterceptor。

首先配置分页拦截器Bean

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * 
 * @Description // 多数据源配置时，多种配置会失效。
 * 例如mybatisplus分页插件、数据库字段下换线自动转驼峰格式失效
 * @Date 2023/5/10
 **/
@Configuration
public class MyBatisPlusConfig {

    /**
     * mybatis-plus分页插件
     * @return
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        // 向MyBatis-Plus的过滤器链中添加分页拦截器，需要设置数据库类型（主要用于分页方言）
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }
}

然后将分页拦截器配置到SqlSessionFactory

import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
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;

/**
 * @Author wengguochao@163.com
 * @Description // 配置mybatis的SessionFactory和SessionTemplate
 * @Date 2023/5/10
 **/
@Configuration
@MapperScan(value = "com.wgc.springboottest.dao.db1",sqlSessionFactoryRef = "sqlSessionFactoryBeanOne")
public class MybatisConfigOne {

    @Resource
    @Qualifier("dsOne")
    DataSource dsOne;

    @Resource
    private MybatisPlusInterceptor mybatisPlusInterceptor;

    @Bean
    SqlSessionFactory sqlSessionFactoryBeanOne() throws Exception {

        MybatisSqlSessionFactoryBean mybatisSqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
        mybatisSqlSessionFactoryBean.setDataSource(dsOne);
        mybatisSqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/db1/**/*.xml"));

        // 多数据源控制台打印sql，配置任意一个数据源即可
        MybatisConfiguration mybatisConfiguration = new MybatisConfiguration();
        mybatisConfiguration.setLogImpl(org.apache.ibatis.logging.stdout.StdOutImpl.class);
        mybatisSqlSessionFactoryBean.setConfiguration(mybatisConfiguration);

        // 设置分页插件
        mybatisSqlSessionFactoryBean.setPlugins(mybatisPlusInterceptor);

        return mybatisSqlSessionFactoryBean.getObject();
    }

    @Bean
    SqlSessionTemplate sqlSessionTemplate1() throws Exception {
        return new SqlSessionTemplate(sqlSessionFactoryBeanOne());
    }
}

1.3 IDEA控制台不打印Sql日志

多数据源需要手动配置SQL打印，选择任意一个SqlSessionFactoryBean注册日志打印即可。代码请参考1.2代码块

2.配置说明

Maven配置部分说明：

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.7.11</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.wgc</groupId>
	<artifactId>springboottest</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>springboottest</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>8</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
	

		<!--MybatisPlus引入-->
		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>mybatis-plus-boot-starter</artifactId>
			<version>3.5.3</version>
		</dependency>

		<dependency>
			<groupId>com.mysql</groupId>
			<artifactId>mysql-connector-j</artifactId>
			<version>8.0.31</version>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>

		<dependency>
			<groupId>cn.hutool</groupId>
			<artifactId>hutool-all</artifactId>
			<version>5.8.16</version>
		</dependency>

	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
				<configuration>
					<excludes>
						<exclude>
							<groupId>org.projectlombok</groupId>
							<artifactId>lombok</artifactId>
						</exclude>
					</excludes>
				</configuration>
			</plugin>
		</plugins>

	</build>


</project>

2.1配置DataSource

import com.zaxxer.hikari.HikariDataSource;
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;

/**
 * @Author wengguochao@163.com
 * @Description //
 * @Date 2023/5/10
 **/
@Configuration
public class DataSourceConfig {

    @Bean
    @ConfigurationProperties("spring.datasource.one")
    public DataSource dsOne(){
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
//        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.two")
    public DataSource dsTwo(){
//        return DataSourceBuilder.create().type(HikariDataSource.class).build();
        return DataSourceBuilder.create().build();
    }


}

2.2 Springboot配置文件配置数据源信息

#mybatis多数据源
spring.datasource.one.jdbc-url=jdbc:mysql://10.xx.12.xx:3339/testSys?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowMultiQueries=true
spring.datasource.one.username=root
spring.datasource.one.password=123456
spring.datasource.one.driver-class-name=com.mysql.cj.jdbc.Driver

#mybatis多数据源[springboot2.x 多数据源源需要注意url和driver-class与单数据源的区别]
spring.datasource.two.jdbc-url=jdbc:mysql://localhost:3306/mytest?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowMultiQueries=true
#spring.datasource.two.jdbc-url=jdbc:mysql://10.xx.12.xx:3340/testSys?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowMultiQueries=true
spring.datasource.two.username=root
spring.datasource.two.password=123456
spring.datasource.two.driver-class-name=com.mysql.cj.jdbc.Driver

2.3配置SqlSessionFactoryBean、分页插件

import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
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;

/**
 * @Author wengguochao@163.com
 * @Description // 配置mybatis的SessionFactory和SessionTemplate
 * @Date 2023/5/10
 **/
@Configuration
@MapperScan(value = "com.wgc.springboottest.dao.db1",sqlSessionFactoryRef = "sqlSessionFactoryBeanOne")
public class MybatisConfigOne {

    @Resource
    @Qualifier("dsOne")
    DataSource dsOne;

    @Resource
    private MybatisPlusInterceptor mybatisPlusInterceptor;

    @Bean
    SqlSessionFactory sqlSessionFactoryBeanOne() throws Exception {

        MybatisSqlSessionFactoryBean mybatisSqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
        mybatisSqlSessionFactoryBean.setDataSource(dsOne);
        mybatisSqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/db1/**/*.xml"));

        // 多数据源控制台打印sql，配置任意一个数据源即可
        MybatisConfiguration mybatisConfiguration = new MybatisConfiguration();
        mybatisConfiguration.setLogImpl(org.apache.ibatis.logging.stdout.StdOutImpl.class);
        mybatisSqlSessionFactoryBean.setConfiguration(mybatisConfiguration);

        // 设置分页插件
        mybatisSqlSessionFactoryBean.setPlugins(mybatisPlusInterceptor);

        return mybatisSqlSessionFactoryBean.getObject();
    }

    @Bean
    SqlSessionTemplate sqlSessionTemplate1() throws Exception {
        return new SqlSessionTemplate(sqlSessionFactoryBeanOne());
    }
}



import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
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;

/**
 * @Author wengguochao@163.com
 * @Description // 配置mybatis的SessionFactory和SessionTemplate
 * @Date 2023/5/10
 **/
@Configuration
@MapperScan(value = "com.wgc.springboottest.dao.db2",sqlSessionFactoryRef = "sqlSessionFactoryBeanTwo")
public class MybatisConfigTwo {

    @Resource
    @Qualifier("dsTwo")
    DataSource dsTwo;

    @Resource
    private MybatisPlusInterceptor mybatisPlusInterceptor;

    @Bean
    SqlSessionFactory sqlSessionFactoryBeanTwo() throws Exception {
        MybatisSqlSessionFactoryBean mybatisSqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
        mybatisSqlSessionFactoryBean.setDataSource(dsTwo);
        mybatisSqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/db2/**/*.xml"));

        // 设置分页插件
        mybatisSqlSessionFactoryBean.setPlugins(mybatisPlusInterceptor);
        return mybatisSqlSessionFactoryBean.getObject();
    }

    @Bean
    SqlSessionTemplate sqlSessionTemplate2() throws Exception {
        return new SqlSessionTemplate(sqlSessionFactoryBeanTwo());
    }
}



import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * @Author wengguochao@163.com
 * @Description // 多数据源配置时，多种配置会失效。
 * 例如mybatisplus分页插件、数据库字段下换线自动转驼峰格式失效
 * @Date 2023/5/10
 **/
@Configuration
public class MyBatisPlusConfig {

    /**
     * mybatis-plus分页插件
     * @return
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        // 向MyBatis-Plus的过滤器链中添加分页拦截器，需要设置数据库类型（主要用于分页方言）
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }
}

至此，基本配置就完成了。需要注意的是，SqlSessionFactoryBean创建的时候，指定了扫描指定的Mapper.xml目录，这个需要自行调整即可。至于Controller、Service、Dao层，就跟平时单数据源那样使用即可，比较简单，此处不做进一步论述。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。