spring boot mybatis 多数据源配置（mysql、Oracle、clickhouse）

spring boot mybatis 多数据源配置（mysql、Oracle、clickhouse）

spring boot mybatis 多数据源配置（mysql、Oracle、clickhouse）

• 项目结构
• maven pom文件
• application.properties配置文件
• mysql数据源配置
• mapper接口
• mapper xml
• Oracle数据源配置
• mapper接口
• mapper xml
• clickhouse数据源配置
• mapper接口
• mapper xml
• 测试

项目结构

maven pom文件

    4.0.0
    per.lp.study.demo
    mybatis-study
    0.0.1-SNAPSHOT
    mybatis-study
    Demo project for Spring Boot

    
        1.8
        UTF-8
        UTF-8
        2.4.9
    

    
        
            org.springframework.boot
            spring-boot-starter-web
        

        
        
            mysql
            mysql-connector-java
            runtime
        
        
        
            com.oracle.database.jdbc
            ojdbc8
            21.4.0.0.1
        
        
        
            com.oracle.database.nls
            orai18n
            21.4.0.0.1
        

        
        
            ru.yandex.clickhouse
            clickhouse-jdbc
            0.3.2
        

        
        
            com.alibaba
            druid
            1.2.8
        

        
        
            org.mybatis.spring.boot
            mybatis-spring-boot-starter
            2.2.1
        
        
            org.springframework.boot
            spring-boot-starter-test
            test
            
                
                    org.junit.vintage
                    junit-vintage-engine
                
            
        
    

    
        
            
                org.springframework.boot
                spring-boot-dependencies
                ${spring-boot.version}
                pom
                import
            
        
    

    
        
            
                org.apache.maven.plugins
                maven-compiler-plugin
                3.8.1
                
                    1.8
                    1.8
                    UTF-8
                
            
            
                org.springframework.boot
                spring-boot-maven-plugin
                2.3.7.RELEASE
                
                    per.lp.study.demo.MybatisStudyApplication
                
                
                    
                        repackage
                        
                            repackage
                        
                    
                
            
        
    

application.properties配置文件

# 应用名称
spring.application.name=mybatis-study
# 应用服务 WEB 访问端口
server.port=8080

#mysql
# 数据库驱动：
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
# 数据源名称
spring.datasource.name=defaultDataSource
# 数据库连接地址
spring.datasource.url=jdbc:mysql://localhost:3306/test?serverTimezone=UTC
# 数据库用户名&密码：
spring.datasource.username=root
spring.datasource.password=123

#oracl
# 数据库驱动：
spring.datasource.oracle.driver-class-name=oracle.jdbc.driver.OracleDriver
# 数据源名称
spring.datasource.oracle.name=defaultDataSource
# 数据库连接地址
spring.datasource.oracle.url=jdbc:oracle:thin:@localhost:1521/orcl
# 数据库用户名&密码：
spring.datasource.oracle.username=test
spring.datasource.oracle.password=test


#clickhouse
# 数据库驱动：
spring.datasource.clickhouse.driver-class-name=com.clickhouse.jdbc.ClickHouseDriver
# 数据源名称
spring.datasource.clickhouse.name=defaultDataSource
# 数据库连接地址
spring.datasource.clickhouse.url=jdbc:clickhouse://192.168.10.100:8123
# 数据库用户名&密码：
spring.datasource.clickhouse.username=default
spring.datasource.clickhouse.password=123456

mysql数据源配置

package per.lp.study.demo.config;

import com.zaxxer.hikari.HikariDataSource;
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.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;


@Configuration
@MapperScan(basePackages = "per.lp.study.demo.mapper", sqlSessionTemplateRef = "primarySqlSessionTemplate")
public class DataSourceConfig {

    @Value("${spring.datasource.driver-class-name}")
    private String driverName;

    @Value("${spring.datasource.url}")
    private String url;

    @Value("${spring.datasource.username}")
    private String userName;

    @Value("${spring.datasource.password}")
    private String password;

    @Bean("dataSource1")
    public HikariDataSource getDataSource() {
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setDriverClassName(driverName);
        dataSource.setJdbcUrl(url);
        dataSource.setUsername(userName);
        dataSource.setPassword(password + "456");
        return dataSource;
    }

    @Bean(name = "primaryTransactionManager")
    public DataSourceTransactionManager dataSourceTransactionManager(HikariDataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "primarySqlSessionFactory")
    public SqlSessionFactory sqlSessionFactoryBean(@Qualifier("dataSource1") HikariDataSource dataSource) throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        // 指定数据源
        factoryBean.setDataSource(dataSource);
        // 指定mapper xml路径
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        Resource[] mapperXml = resolver.getResources("classpath:mapper/*Mapper.xml");
        factoryBean.setTypeAliasesPackage("per.lp.study.demo.mapper");
        factoryBean.setMapperLocations(mapperXml);
        return factoryBean.getObject();
    }

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

}

mapper接口

package per.lp.study.demo.mapper;

import java.util.List;
import java.util.Map;

public interface TestMapper {
    List> getList();
}

mapper xml

    
        select * from demo
    

Oracle数据源配置

package per.lp.study.demo.config;

import com.zaxxer.hikari.HikariDataSource;
import org.apache.ibatis.datasource.pooled.PooledDataSource;
import org.apache.ibatis.datasource.unpooled.UnpooledDataSource;
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.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.io.IOException;

@Configuration
@MapperScan(basePackages = "per.lp.study.demo.mapper1", sqlSessionTemplateRef = "secondarySqlSessionTemplate")
public class DataSource2Config {

    @Value("${spring.datasource.oracle.driver-class-name}")
    private String driverName1;

    @Value("${spring.datasource.oracle.url}")
    private String url1;

    @Value("${spring.datasource.oracle.username}")
    private String userName1;

    @Value("${spring.datasource.oracle.password}")
    private String password1;

    @Bean("secondaryDataSource")
    public PooledDataSource getDataSource1() {
        UnpooledDataSource source = new UnpooledDataSource();
        source.setDriver(driverName1);
        source.setUrl(url1);
        source.setUsername(userName1);
        source.setPassword(password1);
        PooledDataSource pooledDataSource = new PooledDataSource(source);
        return pooledDataSource;
    }

    @Bean(name = "secondarySqlSessionFactory")
    public SqlSessionFactory sqlSessionFactoryBean1(@Qualifier("secondaryDataSource") PooledDataSource dataSource) throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        // 指定数据源
        factoryBean.setDataSource(dataSource);
        // 指定mapper xml路径
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        Resource[] mapperXml = resolver.getResources("classpath:mapper1/*Mapper.xml");
        factoryBean.setTypeAliasesPackage("per.lp.study.demo.mapper1");
        factoryBean.setMapperLocations(mapperXml);
        return factoryBean.getObject();
    }

    @Bean(name = "secondaryTransactionManager")
    public DataSourceTransactionManager testTransactionManager(@Qualifier("secondaryDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "secondarySqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("secondarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

mapper接口

package per.lp.study.demo.mapper1;

import java.util.List;
import java.util.Map;

public interface TestMapper1 {
    List> getList();
}

mapper xml

    
        select * from STUDY
    

 
clickhouse数据源配置 
package per.lp.study.demo.config;

import org.apache.ibatis.datasource.pooled.PooledDataSource;
import org.apache.ibatis.datasource.unpooled.UnpooledDataSource;
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.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "per.lp.study.demo.mapper2", sqlSessionTemplateRef = "thirdSqlSessionTemplate")
public class DataSource3Config {

    @Value("${spring.datasource.clickhouse.driver-class-name}")
    private String driverName1;

    @Value("${spring.datasource.clickhouse.url}")
    private String url1;

    @Value("${spring.datasource.clickhouse.username}")
    private String userName1;

    @Value("${spring.datasource.clickhouse.password}")
    private String password1;

    @Bean("thirdDataSource")
    public PooledDataSource getDataSource1() {
        UnpooledDataSource source = new UnpooledDataSource();
        source.setDriver(driverName1);
        source.setUrl(url1);
        source.setUsername(userName1);
        source.setPassword(password1);
        PooledDataSource pooledDataSource = new PooledDataSource(source);
        return pooledDataSource;
    }

    @Bean(name = "thirdSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactoryBean1(@Qualifier("thirdDataSource") PooledDataSource dataSource) throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        // 指定数据源
        factoryBean.setDataSource(dataSource);
        // 指定mapper xml路径
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        Resource[] mapperXml = resolver.getResources("classpath:mapper2/*Mapper.xml");
        factoryBean.setTypeAliasesPackage("per.lp.study.demo.mapper2");
        factoryBean.setMapperLocations(mapperXml);
        return factoryBean.getObject();
    }

    @Bean(name = "thirdTransactionManager")
    public DataSourceTransactionManager testTransactionManager(@Qualifier("thirdDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "thirdSqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("thirdSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

 
mapper接口 
package per.lp.study.demo.mapper2;

import java.util.List;
import java.util.Map;

public interface TestMapper2 {
    List> getList();
}

 
mapper xml 




    
        select * from test
    

测试

package per.lp.study.demo;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import per.lp.study.demo.mapper.TestMapper;
import per.lp.study.demo.mapper1.TestMapper1;
import per.lp.study.demo.mapper2.TestMapper2;

import java.util.List;
import java.util.Map;

@SpringBootTest
class MybatisStudyApplicationTests {

    @Autowired
    private TestMapper testMapper;

    @Autowired
    private TestMapper1 testMapper1;

    @Autowired
    private TestMapper2 testMapper2;

    @Test
    void test() {
        List> mapList = testMapper.getList();
        for(Map map : mapList) {
            for(String key : map.keySet()) {
                System.out.println(key + "---->" + map.get(key));
            }
        }
    }

    @Test
    void test1() {
        List> mapList = testMapper1.getList();
        for(Map map : mapList) {
            for(String key : map.keySet()) {
                System.out.println(key + "---->" + map.get(key));
            }
        }
    }

    @Test
    void test2() {
        List> list = testMapper2.getList();
        for (Map map : list) {
            for (String key : map.keySet()) {
                System.out.println(key + "---->" + map.get(key));
            }
        }
    }

    @Test
    void contextLoads() {

    }

}