springboot 2.1 mysql 多数据源集成配置，踩过的坑

ゝ一纸荒年。 2022-03-08 04:52 118阅读 0赞

最近有看到springboot多数据源，所以看了一下如何配置，看了一些博客，还是有写坑，记录一下自己配置的过程

以下是pom.xml ,有些包没有用到，但是不影响使用

<?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>
    	<parent>
    		<groupId>org.springframework.boot</groupId>
    		<artifactId>spring-boot-starter-parent</artifactId>
    		<version>2.1.1.RELEASE</version>
    		<relativePath/> 
    	</parent>
    	<groupId>com.hqk</groupId>
    	<artifactId>biz</artifactId>
    	<version>0.0.1-SNAPSHOT</version>
    	<name>biz</name>
    	<description>Demo project for Spring Boot</description>
    
    	<properties>
    		<java.version>1.8</java.version>
    	</properties>
    
    	<dependencies>
    		
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-web</artifactId>
    		</dependency>
    
    		
    		<dependency>
    			<groupId>com.alibaba</groupId>
    			<artifactId>druid-spring-boot-starter</artifactId>
    			<version>1.1.9</version>
    		</dependency>
    
    		<dependency>
    			<groupId>org.mybatis.spring.boot</groupId>
    			<artifactId>mybatis-spring-boot-starter</artifactId>
    			<version>1.3.2</version>
    		</dependency>
    
    		<dependency>
    			<groupId>mysql</groupId>
    			<artifactId>mysql-connector-java</artifactId>
    			<scope>runtime</scope>
    		</dependency>
    
    		
    		<dependency>
    			<groupId>com.github.pagehelper</groupId>
    			<artifactId>pagehelper-spring-boot-starter</artifactId>
    			<version>1.2.5</version>
    		</dependency>
    
            
            <dependency>
                <groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-mail</artifactId>
            </dependency>
    
    		
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-activemq</artifactId>
    		</dependency>
    		<dependency>
    			<groupId>org.apache.activemq</groupId>
    			<artifactId>activemq-pool</artifactId>
    		</dependency>
    
    		
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-data-redis</artifactId>
    		</dependency>
    		
    		<dependency>
    			<groupId>org.apache.commons</groupId>
    			<artifactId>commons-pool2</artifactId>
    			<version>2.4.2</version>
    		</dependency>
    
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-test</artifactId>
    			<scope>test</scope>
    		</dependency>
    	</dependencies>
    
    	<build>
    		<plugins>
    			<plugin>
    				<groupId>org.springframework.boot</groupId>
    				<artifactId>spring-boot-maven-plugin</artifactId>
    			</plugin>
    		</plugins>
    	</build>
    
    </project>

接下来是数据源的配置，我用的是 xml方式： 坑就在这里，**这里必须使用 ‘’jdbc-url‘’，不能使用“url”，不然会报错**

spring.datasource.test1.jdbc-url=jdbc:mysql://192.168.1.12:3306/loan_market?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull
    spring.datasource.test1.username=dev
    spring.datasource.test1.password=3edc$RFV
    spring.datasource.test1.driver-class-name=com.mysql.cj.jdbc.Driver
    
    spring.datasource.test2.jdbc-url=jdbc:mysql://172.31.1.232:3306/loan_market?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull
    spring.datasource.test2.username=test_rd_user
    spring.datasource.test2.password=P6p7AcZeBj4IKZC2FC98
    spring.datasource.test2.driver-class-name=com.mysql.cj.jdbc.Driver

接下来是数据源的配置，主数据库需要加上 @Primary 注解，数据源里面需要指定mapper Java类文件位置和 xml文件位置

主库配置：

package com.hqk.biz.datasource;
    
    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;
    
    /**
     * mysql 主库配置类
     */
    @Configuration
    @MapperScan(basePackages = "com.hqk.biz.mapper.master", sqlSessionTemplateRef  = "test1SqlSessionTemplate")
    public class MasterDataSourceConfig {
        /**
         * 创建数据源
         * @return
         */
        @Bean(name = "test1DataSource")
        @ConfigurationProperties(prefix = "spring.datasource.test1")
        @Primary
        public DataSource testDataSource() {
            return DataSourceBuilder.create().build();
        }
    
        /**
         * 创建工厂
         * @param dataSource
         * @return
         * @throws Exception
         */
        @Bean(name = "test1SqlSessionFactory")
        @Primary
        public SqlSessionFactory testSqlSessionFactory(@Qualifier("test1DataSource") DataSource dataSource) throws Exception {
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dataSource);
            // xml文件位置 如果用注解，则不需要这个
            bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:/mapper/master/*.xml"));
            return bean.getObject();
        }
    
        /**
         * 创建事物
         * @param dataSource
         * @return
         */
        @Bean(name = "test1TransactionManager")
        @Primary
        public DataSourceTransactionManager testTransactionManager(@Qualifier("test1DataSource") DataSource dataSource) {
            return new DataSourceTransactionManager(dataSource);
        }
    
        /**
         * 创建模板
         * @param sqlSessionFactory
         * @return
         * @throws Exception
         */
        @Bean(name = "test1SqlSessionTemplate")
        @Primary
        public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
            return new SqlSessionTemplate(sqlSessionFactory);
        }
    
    }

从库也是一样的配置，但是不需要 @primary注解

package com.hqk.biz.datasource;
    
    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;
    
    /**
     * mysql从库配置类
     */
    @Configuration
    @MapperScan(basePackages = "com.hqk.biz.mapper.cluster", sqlSessionTemplateRef  = "test2SqlSessionTemplate")
    public class ClusterDataSourceConfig {
    
        @Bean(name = "test2DataSource")
        @ConfigurationProperties(prefix = "spring.datasource.test2")
        public DataSource testDataSource() {
            return DataSourceBuilder.create().build();
        }
    
        @Bean(name = "test2SqlSessionFactory")
        public SqlSessionFactory testSqlSessionFactory(@Qualifier("test2DataSource") DataSource dataSource) throws Exception {
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dataSource);
            bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:/mapper/cluster/*.xml"));
            return bean.getObject();
        }
    
        @Bean(name = "test2TransactionManager")
        public DataSourceTransactionManager testTransactionManager(@Qualifier("test2DataSource") DataSource dataSource) {
            return new DataSourceTransactionManager(dataSource);
        }
    
        @Bean(name = "test2SqlSessionTemplate")
        public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
            return new SqlSessionTemplate(sqlSessionFactory);
        }
    
    }

主要的配置已经完成了，接下来下个 mapper测试一下，主数据源mapper

package com.hqk.biz.mapper.master;
    
    import org.apache.ibatis.annotations.Mapper;
    
    @Mapper
    public interface BumenMapper {
    
        public void addBumen();
    }

从数据源mapper，这两个mapper不在同一个包下，上面数据源配置已经指明了两个mapper的位置

package com.hqk.biz.mapper.cluster;
    
    import org.apache.ibatis.annotations.Mapper;
    
    @Mapper
    public interface StudentMapper {
    
        public void addBumen();
    }

接下来是 mapper.xml  主数据源和从数据源的 mapper.xml，mapper.xml 比较简单，贴下图片，主要是位置要区分

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h1cWlhbmt1bmxvbA_size_16_color_FFFFFF_t_70][]

接下来直接测试两个 mapper 即可

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h1cWlhbmt1bmxvbA_size_16_color_FFFFFF_t_70]: /images/20220308/415158b8ac2e4dca9bd360b1528b2c28.png