springboot + mybatis多数据源配置(oracle + mysql)

_超级瞄准已部署_

已于 2023-04-08 14:13:52 修改

阅读量2.9k

点赞数 3

分类专栏： mybatis 文章标签： mybatis oracle java

于 2023-04-06 22:01:41 首次发布

原文链接：http://www.bysocket.com

版权

mybatis 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

文章详细介绍了如何在SpringBoot项目中配置多个数据源，包括添加相关依赖、配置application.properties文件、编写数据源配置类、设置驼峰命名映射以及创建对应的DAO接口和mapper.xml文件。此外，还包含了单元测试用例，展示如何从不同数据源获取数据。

摘要由CSDN通过智能技术生成

1、pom文件依赖。添加springboot，mybatis依赖，以及对应的数据库驱动

    <dependencies>
        <!-- Spring Boot Web 依赖 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- Spring Boot Test 依赖 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!-- Spring Boot Mybatis 依赖 -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>
        <!-- MySQL 连接驱动依赖 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.30</version>
        </dependency>
        <!-- Oracle 连接驱动依赖 -->
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.3</version>
        </dependency>

        <!-- Druid 数据连接池依赖 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.15</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
    </dependencies>

2、配置文件 application.properties

## master 数据源配置
master.datasource.url=jdbc:mysql://localhost:3306/webtest?useUnicode=true&characterEncoding=utf8
master.datasource.username=root
master.datasource.password=123
master.datasource.driverClassName=com.mysql.cj.jdbc.Driver

## slave 数据源配置
slave.datasource.url=jdbc:oracle:thin:@192.168.119.10:1521:orcl
slave.datasource.username=wateruser
slave.datasource.password=test
slave.datasource.driverClassName=oracle.jdbc.driver.OracleDriver

## 驼峰命名法与数据库下划线字段映射配置
mybatis.configuration.map-underscore-to-camel-case=true

3、多数据源配置类

@Configuration
@MapperScan(basePackages = MasterDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourceConfig {
    static final String PACKAGE = "com.zj.dao.master";
    static final String MAPPER_LOCATION = "classpath:mapper/master/*.xml";

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

    @Value("${master.datasource.username}")
    private String user;

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

    @Value("${master.datasource.driverClassName}")
    private String driverClass;

    @Bean(name = "masterDataSource")
    @Primary
    public DataSource masterDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        return dataSource;
    }

    @Bean(name = "masterTransactionManager")
    @Primary
    public DataSourceTransactionManager masterTransactionManager() {
        return new DataSourceTransactionManager(masterDataSource());
    }

    @Bean(name = "masterSqlSessionFactory")
    @Primary
    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(masterDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(MasterDataSourceConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }
}

@Configuration
@MapperScan(basePackages = SlaveDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "slaveSqlSessionFactory")
public class SlaveDataSourceConfig {

    static final String PACKAGE = "com.zj.dao.slave";
    static final String MAPPER_LOCATION = "classpath:mapper/slave/*.xml";

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

    @Value("${slave.datasource.username}")
    private String user;

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

    @Value("${slave.datasource.driverClassName}")
    private String driverClass;

    @Bean(name = "slaveDataSource")
    public DataSource slaveDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        return dataSource;
    }

    @Bean(name = "slaveTransactionManager")
    public DataSourceTransactionManager slaveTransactionManager() {
        return new DataSourceTransactionManager(slaveDataSource());
    }

    @Bean(name = "slaveSqlSessionFactory")
    public SqlSessionFactory slaveSqlSessionFactory(@Qualifier("slaveDataSource") DataSource slaveDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(slaveDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(SlaveDataSourceConfig.MAPPER_LOCATION));

        // 传入驼峰映射配置
        sessionFactory.setConfiguration(globalConfiguration());
        return sessionFactory.getObject();
    }

    /**
     * 驼峰命名法与数据库下划线字段映射配置
     * @return
     */
    @Bean
    @ConfigurationProperties(prefix = "mybatis.configuration")
    public org.apache.ibatis.session.Configuration globalConfiguration() {
        return new org.apache.ibatis.session.Configuration();
    }
}

说明：@Primary 当spring容器中一个类型的bean有多个时，使用@Primary可以标记为主要的bean，@MapperScan 分别扫描不同的包，以及指定对应的sqlsessionFactory。

4、工程结构如下。注意dao层以及相应的mapper.xml文件路径，与配置类的扫包路径相对应！

5、dao层

public interface AdminDao {

    /**
     * 查询所有
     * @return
     */
    List<Admin> selectAll();
}

public interface TAddressDao {

    /**
     * 查询所有
     * @return
     */
    List<TAddress> selectAll();
}

6、mapper.xml

AdminDao.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="com.zj.dao.master.AdminDao">

    <resultMap type="com.zj.entity.Admin" id="AdminMap">
        <result property="username" column="username" jdbcType="VARCHAR"/>
        <result property="password" column="password" jdbcType="VARCHAR"/>
        <result property="phone" column="phone" jdbcType="VARCHAR"/>
        <result property="address" column="address" jdbcType="VARCHAR"/>
    </resultMap>

    <select id="selectAll" resultType="com.zj.entity.Admin">
        select * from admin
    </select>

</mapper>

TAddressDao.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="com.zj.dao.slave.TAddressDao">

    <resultMap type="com.zj.entity.TAddress" id="TAddressMap">
        <result property="id" column="ID" jdbcType="INTEGER"/>
        <result property="name" column="NAME" jdbcType="VARCHAR"/>
        <result property="areaid" column="AREAID" jdbcType="INTEGER"/>
        <result property="operatorid" column="OPERATORID" jdbcType="INTEGER"/>
    </resultMap>

    <select id="selectAll" resultType="com.zj.entity.TAddress">
        select * from t_address
    </select>

</mapper>

7、单元测试

@SpringBootTest
@RunWith(SpringRunner.class)
public class MultipleTest {
    @Autowired
    private AdminDao adminDao;
    @Autowired
    private TAddressDao tAddressDao;

    @Test
    public void test() {
        List<Admin> admins = adminDao.selectAll();
        System.out.println("mysql 数据 --> " + admins);

        List<TAddress> list = tAddressDao.selectAll();
        System.out.println("oracle 数据 --> " + list);
    }

}

测试结果如下：