springboot+mybatis配置多个数据源

本项目主要讲解通过 SpringBoot 配置多个数据源。

cys6736873

1422人浏览 · 2025-04-19 11:26:43

cys6736873 · 2025-04-19 11:26:43 发布

本项目主要讲解通过 SpringBoot 配置多个数据源。

1、准备数据库和数据

1）创建第一个数据库，库名demo-crm，创建表并插入测试数据。

CREATE TABLE `user_info` (
  `id` varchar(32) NOT NULL COMMENT '标识',
  `name` varchar(32) NOT NULL COMMENT '姓名',
	age int(3) NOT NULL COMMENT '年龄',
  `sex` char(1) NOT NULL COMMENT '性别：M-男；F-女；U-未知',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='用户信息';

-- 插入数据
insert into user_info values ('U01', '路飞', 18, 'M');
insert into user_info values ('U02', '娜美', 20, 'F');
insert into user_info values ('U03', '索隆', 22, 'M');
insert into user_info values ('U04', '布鲁克', 150, 'M');
insert into user_info values ('U05', '乔巴', 10, 'U');

2）创建第二个数据库，库名demo-trade，创建表并插入测试数据。

CREATE TABLE `account_info` (
  `id` varchar(32) NOT NULL COMMENT '标识',
  `account_no` varchar(32) NOT NULL COMMENT '账号',
	user_id varchar(32) NOT NULL COMMENT '用户标识',
  `amount` float(12,2) DEFAULT 0 COMMENT '金额',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='账户信息';

-- 插入数据
insert into account_info values ('A01', '88888888', 'U01', 1800);
insert into account_info values ('A02', '66666666', 'U02', 2000);
insert into account_info values ('A03', '12312345', 'U03', 2200);
insert into account_info values ('A04', '45645678', 'U04', 15000);
insert into account_info values ('A05', '67867890', 'U05', 10);

2、创建springboot项目工程

工程目录大致如下：

pom.xml文件依赖如下：

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

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>junit-jupiter</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.8</version>
        </dependency>
        <!-- MySQL数据驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.32</version>
        </dependency>
    </dependencies>

3、添加工程配置文件

配置文件 aplication.yml相关配置（注意：如果是单数据源使用 url ，多数据源要使用 jdbc-url ）。

server:
  port: 8080

spring:
  application:
    name: ethan-mybatis2
  # 配置数据源
  datasource:
    crm:
      jdbc-url: jdbc:mysql://localhost:3306/demo-crm?useUnicode=true&characterEncoding=UTF-8&useSSL=false
      username: root
      password: 123456
      driver-class-name: com.mysql.cj.jdbc.Driver
    trade:
      jdbc-url: jdbc:mysql://localhost:3306/demo-trade?useSSL=false&serverTimezone=UTC
      username: root
      password: 123456
      driver-class-name: com.mysql.cj.jdbc.Driver

# MyBatis配置
mybatis:
  # 搜索指定包别名
  # (如果 typeAliasesPackage不进行配置，resultType就得写全名resultType="com.my.example.pojo.User"，
  # 但是如果配置了别名,就可以不用写全路径直接写resultType="User"即可)
  type-aliases-package: com.my.example.pojo
  # 配置mapper的扫描，找到所有的mapper.xml映射文件
  mapper-locations: classpath*:mapper/**/*Mapper.xml
  # 使用驼峰命名
  # 数据库表列：user_name
  # 实体类属性：userName
  configuration:
    map-underscore-to-camel-case: true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

4、添加数据库配置类

1）数据库demo-crm配置类

package com.my.example.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
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 org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = {"com.my.example.mapper.crm"}, sqlSessionFactoryRef = "crmSqlSessionFactory")
public class CrmDbConfig {

    @Bean(name = "crmDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.crm")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 配置事务管理器，不然事务不起作用
     *
     * @return
     */
    @Bean
    public PlatformTransactionManager transactionManager() {
        return new DataSourceTransactionManager(this.dataSource());
    }

    @Primary
    @Bean(name = "crmSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("crmDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath*:mapper/crm/*.xml"));
        sqlSessionFactoryBean.setTypeAliasesPackage("com.my.example.pojo.crm");
        sqlSessionFactoryBean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
        return sqlSessionFactoryBean.getObject();
    }


}

2）数据库demo-trade配置类

package com.my.example.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
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 org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.my.example.mapper.trade", sqlSessionFactoryRef = "tradeSqlSessionFactory")
public class TradeDbConfig {

    @Bean(name = "tradeDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.trade")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean("tradeSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("tradeDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean ();
        sqlSessionFactoryBean.setDataSource(dataSource);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath*:mapper/trade/*.xml"));
        sqlSessionFactoryBean.setTypeAliasesPackage("com.my.example.pojo.trade");
        sqlSessionFactoryBean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
        return sqlSessionFactoryBean.getObject();

    }

}

5、数据库demo-crm表相关类

User实体类

package com.my.example.pojo.crm;

public class User {
    private String id;
    private String name;
    private Integer age;
    private String sex;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }
}

mapper接口类

package com.my.example.mapper.crm;

import com.my.example.pojo.crm.User;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface UserMapper {

    List<User> getAll();

    User getUserById(String id);

}

mapper文件

<?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">

<!-- namespace扫描mapper层的接口 -->
<mapper namespace="com.my.example.mapper.crm.UserMapper">
    <!-- id与接口中的方法名一致，resultType是结果返回 -->
    <select id="getAll" resultType="User">
        SELECT * FROM user_info t order by t.id
    </select>

    <select id="getUserById" parameterType="java.lang.String" resultType="User">
        SELECT * FROM user_info t where t.id = #{id}
    </select>

</mapper>

6、数据库demo-trade表相关类

Account实体类

package com.my.example.pojo.trade;

public class Account {
    private String id;
    private String accountNo;
    private String userId;
    private Double amount;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getAccountNo() {
        return accountNo;
    }

    public void setAccountNo(String accountNo) {
        this.accountNo = accountNo;
    }

    public String getUserId() {
        return userId;
    }

    public void setUserId(String userId) {
        this.userId = userId;
    }

    public Double getAmount() {
        return amount;
    }

    public void setAmount(Double amount) {
        this.amount = amount;
    }
}

mapper接口类

package com.my.example.mapper.trade;

import com.my.example.pojo.trade.Account;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface AccountMapper {

    List<Account> getAll();

    Account getAccountByUserId(String userId);
}

mapper文件

<?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">

<!-- namespace扫描mapper层的接口 -->
<mapper namespace="com.my.example.mapper.trade.AccountMapper">
    <!-- id与接口中的方法名一致，resultType是结果返回 -->
    <select id="getAll" resultType="Account">
        SELECT * FROM account_info t order by t.id
    </select>

    <select id="getAccountByUserId" parameterType="java.lang.String" resultType="Account">
        SELECT * FROM account_info t where t.user_id = #{userId}
    </select>

</mapper>

7、构造Service业务类

编写一些简单的查询方法。

package com.my.example.service;

import com.my.example.mapper.crm.UserMapper;
import com.my.example.mapper.trade.AccountMapper;
import com.my.example.pojo.crm.User;
import com.my.example.pojo.trade.Account;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

@Service
public class BusinessService {
    @Resource
    UserMapper userMapper;
    @Resource
    AccountMapper accountMapper;

    public List<User> getUserAll() {
        return userMapper.getAll();
    }

    public List<Account> getAccountAll() {
        return accountMapper.getAll();
    }

    public String getUserBalance(String userId) {
        User user = userMapper.getUserById(userId);
        Account account = accountMapper.getAccountByUserId(userId);
        return user.getName() + "的余额为" + account.getAmount() + "。";
    }

}

8、编写测试类进行测试

package com.my.example;

import com.my.example.service.BusinessService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;


@SpringBootTest
//@RunWith(SpringRunner.class)
class EthanMybatis2ApplicationTests {
    @Autowired
    BusinessService businessService;

    @Test
    void contextLoads() {
    }

    @Test
    void getUserAll() {
        businessService.getUserAll().forEach(user -> {
            System.out.println(user.getId() + "|" + user.getName() + "|" + user.getAge()  + "|"+ user.getSex());
        });
    }

    @Test
    void getAccountAll() {
        businessService.getAccountAll().forEach(account -> {
            System.out.println(account.getId() + "|" + account.getAccountNo() + "|" + account.getUserId()  + "|"+ account.getAmount());
        });
    }

    @Test
    void getUserBalance() {
        String userId = "U01";
        String msg = businessService.getUserBalance(userId);
        System.out.println(msg);
    }

}

getUserAll()测试结果：

U01|路飞|18|M
U02|娜美|20|F
U03|索隆|22|M
U04|布鲁克|150|M
U05|乔巴|10|U

getAccountAll()测试结果：

A01|88888888|U01|1800.0
A02|66666666|U02|2000.0
A03|12312345|U03|2200.0
A04|45645678|U04|15000.0
A05|67867890|U05|10.0

getUserBalance()测试结果：

路飞的余额为1800.0。

DAMO开发者矩阵

DAMO开发者矩阵，由阿里巴巴达摩院和中国互联网协会联合发起，致力于探讨最前沿的技术趋势与应用成果，搭建高质量的交流与分享平台，推动技术创新与产业应用链接，围绕“人工智能与新型计算”构建开放共享的开发者生态。

更多推荐

KingbaseES数据库：医疗信创潮涌沅江，国产化信创打造医疗新质生产力

DAMO开发者矩阵

告别迁移焦虑：金仓KDMS V4带你轻松搞定数据库国产化替代

DAMO开发者矩阵

KingbaseES数据库在常德二院全栈国产化信创中产生新质生产力,医疗信创的部署如何实现，如何操作？

DAMO开发者矩阵

所有评论(0)

查看更多评论

cys6736873

@cys6736873

已为社区贡献1条内容