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

Post author:xfxia
Post published:2023年9月18日
Post category:mysql

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

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

项目结构

在这里插入图片描述

maven 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>
    <groupId>per.lp.study.demo</groupId>
    <artifactId>mybatis-study</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>mybatis-study</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <spring-boot.version>2.4.9</spring-boot.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- mysql连接驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!-- oracle连接驱动 -->
        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc8</artifactId>
            <version>21.4.0.0.1</version>
        </dependency>
        <!-- 不加此依赖，Oracle连接会报错 -->
        <dependency>
            <groupId>com.oracle.database.nls</groupId>
            <artifactId>orai18n</artifactId>
            <version>21.4.0.0.1</version>
        </dependency>

        <!-- clickhouse依赖-->
        <dependency>
            <groupId>ru.yandex.clickhouse</groupId>
            <artifactId>clickhouse-jdbc</artifactId>
            <version>0.3.2</version>
        </dependency>

        <!-- druid连接池，clickhouse使用此连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.8</version>
        </dependency>

        <!-- mybatis依赖 -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.1</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-dependencies</artifactId>
                <version>${spring-boot.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <version>2.3.7.RELEASE</version>
                <configuration>
                    <mainClass>per.lp.study.demo.MybatisStudyApplication</mainClass>
                </configuration>
                <executions>
                    <execution>
                        <id>repackage</id>
                        <goals>
                            <goal>repackage</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

</project>

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<Map<String, Object>> getList();
}

mapper xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="per.lp.study.demo.mapper.TestMapper">
    <select id="getList" resultType="map">
        select * from demo
    </select>
</mapper>

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<Map<String, Object>> getList();
}

mapper xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="per.lp.study.demo.mapper1.TestMapper1">
    <select id="getList" resultType="map">
        select * from STUDY
    </select>
</mapper>

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<Map<String, Object>> getList();
}

mapper xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="per.lp.study.demo.mapper2.TestMapper2">
    <select id="getList" resultType="map">
        select * from test
    </select>
</mapper>

测试

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<Map<String, Object>> mapList = testMapper.getList();
        for(Map<String, Object> map : mapList) {
            for(String key : map.keySet()) {
                System.out.println(key + "---->" + map.get(key));
            }
        }
    }

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

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

    @Test
    void contextLoads() {

    }

}

版权声明：本文为luopengdlc原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

原文链接：https://blog.csdn.net/luopengdlc/article/details/122279028

Tags: mysql