springboot mybatis 多数据源测试

 

 

1.先看一下项目结构

2.pom文件

<?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.2.4.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.wxd.etl</groupId>
    <artifactId>dozjkandapi</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>dozjkandapi</name>
    <description></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>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>
        <!-- mybatis -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.1</version>
        </dependency>
        <!-- 阿里巴巴druid连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.10</version>
        </dependency>
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.3</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

3.配置数据源

server:
  port: 80
spring:
  datasource:
    zjk:
      type: com.alibaba.druid.pool.DruidDataSource
      url: jdbc:oracle:thin:@192.168.0.189:1521:orcl
      username: user1
      password: 123456
      driver-class-name: oracle.jdbc.driver.OracleDriver
    jhk:
      type: com.alibaba.druid.pool.DruidDataSource
      url: jdbc:oracle:thin:@192.168.0.189:1521:orcl
      username: user2
      password: 123456
      driver-class-name: oracle.jdbc.driver.OracleDriver

4.创建两个数据源的DataSource

package com.wxd.etl.dozjkandapi.config;

import com.alibaba.druid.pool.DruidDataSource;
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.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;

/**
 * @Author: Wxd
 * @Date: 2020/3/12 14:39
 */
@Configuration
@MapperScan(basePackages = "com.wxd.etl.dozjkandapi.mapper.zjk", sqlSessionTemplateRef  = "zjkSqlSessionTemplate")
public class MybatisDbZJKConfig {
    @Bean(name="zjkDataSource")
    @Qualifier("zjkDataSource")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.zjk")
    public DataSource primaryDataSource() {
        DruidDataSource druidDataSource = new DruidDataSource();
        return druidDataSource;
    }

    @Bean(name = "zjkTransactionManager")
    @Primary
    public DataSourceTransactionManager setTransactionManager(@Qualifier("zjkDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "zjkSqlSessionFactory")
    @Primary
    public SqlSessionFactory setSqlSessionFactory(@Qualifier("zjkDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapperxml/zjk/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "zjkSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate setSqlSessionTemplate(@Qualifier("zjkSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

package com.wxd.etl.dozjkandapi.config;

import com.alibaba.druid.pool.DruidDataSource;
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.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;

/**
 * @Author: Wxd
 * @Date: 2020/3/12 14:45
 */
@Configuration
@MapperScan(basePackages = "com.wxd.etl.dozjkandapi.mapper.jhk", sqlSessionTemplateRef  = "jhkSqlSessionTemplate")
public class MybatisDbJHKConfig {

    @Bean(name="jhkDataSource")
    @Qualifier("jhkDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.jhk")
    public DataSource secondaryDataSource() {
        DruidDataSource druidDataSource = new DruidDataSource();
        return druidDataSource;
    }

    @Bean(name = "jhkTransactionManager")
    public DataSourceTransactionManager setTransactionManager(@Qualifier("jhkDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "jhkSqlSessionFactory")
    public SqlSessionFactory setSqlSessionFactory(@Qualifier("jhkDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapperxml/jhk/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "jhkSqlSessionTemplate")
    public SqlSessionTemplate setSqlSessionTemplate(@Qualifier("jhkSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

坑点1：在创建SqlSessionFactory的时候需要指定mapper.xml文件的路径，原因：在我们使用多数据源的时候，因为关闭了springboot的默认数据源配置，使用我们自己定义的数据源，此时我们在配置文件中配置的路径是不会生效的，需要我们在定义数据源代码的时候，手动指向一下mapper.xml的位置

坑点2：

bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResource("classpath:mapperxml/jhk/*.xml"));设置的路径是绝对路径bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapperxml/jhk/*.xml"));才是相对路径

一个是getResource，一个是getResources

5.测试类中调用

@RunWith(SpringRunner.class)
@SpringBootTest(classes = DozjkandapiApplication.class)
class DozjkandapiApplicationTests {
    @Autowired
    private PsEtlCredentialsinfoMapper psEtlCredentialsinfoMapper;

    @Test
    void contextLoads() {
        PsEtlCredentialsinfo psEtlCredentialsinfo = psEtlCredentialsinfoMapper.selectByPrimaryKey("19010900010", (short) 1, "00");
        System.out.println(psEtlCredentialsinfo);
    }

}

 

执行结果：