基于SpringBoot和Mybatis配置多个数据库的数据源

zhangbeizhen18

已于 2022-03-24 23:41:03 修改

阅读量3.1k

点赞数

分类专栏： L02-SpringBoot 文章标签： java

于 2022-03-24 19:31:05 首次发布

本文链接：https://blog.csdn.net/zhangbeizhen18/article/details/123719048

版权

L02-SpringBoot 专栏收录该内容

65 篇文章 2 订阅

订阅专栏

本文详细介绍了如何在Spring Boot项目中配置和使用两个不同的数据库，包括数据源配置、MyBatisPlus配置、接口与XML映射文件的设定，以及通过Service实现对不同数据库的操作。同时，文章还涵盖了可能出现的问题及解决方案，如字段转换、全局配置设置等。

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

记录：268

场景：一个应用中完成一个业务需操作两个不同的数据库。

版本：spring-boot 2.4.5，mybatis-plus 3.5.0

数据库：fjdb，fzdb

数据表：sys_user（在fjdb库中），sys_role（在fzdb）

一、说明

1.配置说明

(1)在application.yml文件中，有fjdb，fzdb各自独立的数据源配置信息。

(2)在配置属性类DataSourceProperties，各个属性对应application.yml数据源属性。

(3)在FjdbDataSourceProperties类中，加载fjdb数据库数据源信息。

(4)在FzdbDataSourceProperties类中，加载fzdb数据库数据源信息。

(5)在FjdbMybatisConfig类中，生成Mybatis访问Fjdb数据库相关配置信息。

(6)在FzdbMybatisConfig类中，生成Mybatis访问Fzdb数据库相关配置信息。

2.扫描包和xml路径说明

(1)针对fjdb数据库，Mybatis扫描包和xml路径说明

com.hub.dao.fjdb.SysUserDao

resources\mapper\fjdb\SysUserDao.xml

(1)针对fzdb数据库，Mybatis扫描包和xml路径说明

com.hub.dao.fzdb.SysRoleDao

resources\mapper\fzdb\SysRoleDao.xml

3.调用说明

(1)前台发起http请求，

(2)WorkController处理post请求

(3)WorkServiceImpl处理任务，通过SysUserDao操作fjdb数据库；通过SysRoleDao操作fzdb数据库。

二、代码

1.配置类

1.1 数据配置基类-DataSourceProperties

DataSourceProperties属性值，直接从yml文件中以注解方式加载。

@Data
public class DataSourceProperties {
    private String driverClassName;
    private String url;
    private String username;
    private String password;
    private int initialSize = 2;
    private int maxActive = 10;
    private int minIdle = -1;
    private long maxWait = 60 * 1000L;
    private boolean poolPreparedStatements = false;
    private long maxPoolPreparedStatementPerConnectionSize = 20;
    private long timeBetweenEvictionRunsMillis = 60 * 1000L;
    private long minEvictableIdleTimeMillis = 1000L * 60L * 30L;
    private long maxEvictableIdleTimeMillis = 1000L * 60L * 60L * 7;
    private String validationQuery = "select 1";
    private boolean testWhileIdle = true;
    private boolean testOnBorrow = false;
    private boolean testOnReturn = false;
    private int validationQueryTimeout = -1;
    private int maxOpenPreparedStatements = -1;
    private boolean sharePreparedStatements = false;
    private String filters = "stat,wall";
}

1.2 fjdb数据源属性,FjdbDataSourceProperties

注解说明：

@Component：生成Bean

@ConfigurationProperties：加载yml配置文件指定前缀的属性。

@PostConstruct：对已经加载的属性进行修改，比如某些属性在配置文件中是加密的，在此处可以解密。

InitializingBean接口：实现接口，即Bean初始化完成后执行afterPropertiesSet。

操作Fjdb数据源相关属性：

@Component
@ConfigurationProperties(prefix = "fjdb.datasource.druid", ignoreUnknownFields = true)
public class FjdbDataSourceProperties extends DataSourceProperties implements InitializingBean {

    @PostConstruct
    public void init() {
    }
    @Override
    public void afterPropertiesSet() throws Exception {
    }
}

1.3 fzdb数据源属性,FzdbDataSourceProperties

操作fzdb数据源相关属性：

@Component
@ConfigurationProperties(prefix = "fzdb.datasource.druid", ignoreUnknownFields = true)
public class FzdbDataSourceProperties extends DataSourceProperties implements InitializingBean {

    @PostConstruct
    public void init() {
    }
    @Override
    public void afterPropertiesSet() throws Exception {
    }
}

1.4 fjdb数据源对应Mybatis配置,FjdbMybatisConfig

注解说明

@Configuration：定义配置类

@MapperScan：属性basePackages定义Mybatis扫描的包；属性sqlSessionFactoryRef定义Mybatis要引用的会话工厂SqlSessionFactory的实例。

@Bean(name="fjdbDataSource")：定义数据源。

@Bean(name = "fjdbTransactionManager")：定义事务管理器。

@Bean(name = "fjdbSqlSessionFactory")：定义会话工厂。在会话工厂中配合Mybatis的各种属性，设置到会话工厂中。GlobalConfig相关的配置，通过GlobalConfigUtils的setGlobalConfig方法，关联MybatisConfiguration,GlobalConfig进行设置。

@Qualifier("fjdbDataSource")：指定使用命名的Bean实例。

操作fjdb数据源相关属性：

@Configuration
@MapperScan(basePackages = FjdbMybatisConfig.PACKAGE,
        sqlSessionFactoryRef = "fjdbSqlSessionFactory")
public class FjdbMybatisConfig {

    public static final String PACKAGE = "com.hub.dao.fjdb";
    public static final String MAPPER_LOCATION = "classpath:mapper/fjdb/*.xml";

    @Autowired
    private FjdbDataSourceProperties fjdbProperties;

    @Bean(name="fjdbDataSource")
    @Primary
    public DataSource fjdbDataSource(){
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(fjdbProperties.getDriverClassName());
        dataSource.setUrl(fjdbProperties.getUrl());
        dataSource.setUsername(fjdbProperties.getUsername());
        dataSource.setPassword(fjdbProperties.getPassword());
        return dataSource;
    }

    @Bean(name = "fjdbTransactionManager")
    @Primary
    public DataSourceTransactionManager fjdbTransactionManager() {
        return new DataSourceTransactionManager(fjdbDataSource());
    }

    @Bean(name = "fjdbSqlSessionFactory")
    @Primary
    public SqlSessionFactory fjdbSqlSessionFactory(@Qualifier("fjdbDataSource") DataSource dataSource)
            throws Exception {

        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(FjdbMybatisConfig.MAPPER_LOCATION));

        MybatisConfiguration mc = new MybatisConfiguration();
        mc.setMapUnderscoreToCamelCase(true);
        mc.setCacheEnabled(false);
        mc.setCallSettersOnNulls(true);
        mc.setJdbcTypeForNull(JdbcType.NULL);
        sessionFactory.setConfiguration(mc);
        GlobalConfig gConfig = new GlobalConfig();
        gConfig.setBanner(false);
        GlobalConfig.DbConfig gDb = new GlobalConfig.DbConfig();
        gDb.setIdType(IdType.AUTO);
        gDb.setLogicDeleteValue("-1");
        gDb.setLogicNotDeleteValue("0");
        gConfig.setDbConfig(gDb);
        GlobalConfigUtils.setGlobalConfig(mc, gConfig);
        return sessionFactory.getObject();
    }

}

1.5 fzdb数据源对应Mybatis配置,FzdbMybatisConfig

操作fzdb数据源相关属性：

@Configuration
@MapperScan(basePackages = FzdbMybatisConfig.PACKAGE,
        sqlSessionFactoryRef = "fzdbSqlSessionFactory")
public class FzdbMybatisConfig {
    public static final String PACKAGE = "com.hub.dao.fzdb";
    public static final String MAPPER_LOCATION = "classpath:mapper/fzdb/*.xml";

    @Autowired
    private FzdbDataSourceProperties fzdbProperties;

    @Bean(name = "fzdbDataSource")
    public DataSource fzdbDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(fzdbProperties.getDriverClassName());
        dataSource.setUrl(fzdbProperties.getUrl());
        dataSource.setUsername(fzdbProperties.getUsername());
        dataSource.setPassword(fzdbProperties.getPassword());
        return dataSource;
    }

    @Bean(name = "fzdbTransactionManager")
    public DataSourceTransactionManager fzdbTransactionManager() {
        return new DataSourceTransactionManager(fzdbDataSource());
    }

    @Bean(name = "fzdbSqlSessionFactory")
    public SqlSessionFactory fzdbSqlSessionFactory(@Qualifier("fzdbDataSource") DataSource dataSource)
            throws Exception {

        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(FzdbMybatisConfig.MAPPER_LOCATION));

        MybatisConfiguration mc = new MybatisConfiguration();
        mc.setMapUnderscoreToCamelCase(true);
        mc.setCacheEnabled(false);
        mc.setCallSettersOnNulls(true);
        mc.setJdbcTypeForNull(JdbcType.NULL);
        sessionFactory.setConfiguration(mc);
        GlobalConfig gConfig = new GlobalConfig();
        gConfig.setBanner(false);
        GlobalConfig.DbConfig gDb = new GlobalConfig.DbConfig();
        gDb.setIdType(IdType.AUTO);
        gDb.setLogicDeleteValue("-1");
        gDb.setLogicNotDeleteValue("0");
        gConfig.setDbConfig(gDb);
        GlobalConfigUtils.setGlobalConfig(mc, gConfig);
        return sessionFactory.getObject();
    }
}

1.6 yml文件

application.yml如下：

server:
  port: 18082

fjdb:
  datasource:
    druid:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://localhost:3306/fjdb?useUnicode=true&characterEncoding=UTF-8
      username: fjdb
      password: 12345678
      initial-size: 10
      max-active: 100
      min-idle: 10
      max-wait: 60000
      pool-prepared-statements: true
      max-pool-prepared-statement-per-connection-size: 20
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 300000
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false

fzdb:
  datasource:
    druid:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://localhost:3306/fzdb?useUnicode=true&characterEncoding=UTF-8
      username: fzdb
      password: 87654321
      initial-size: 20
      max-active: 150
      min-idle: 15
      max-wait: 80000
      pool-prepared-statements: true
      max-pool-prepared-statement-per-connection-size: 30
      time-between-eviction-runs-millis: 70000
      min-evictable-idle-time-millis: 320000
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false

2.Mybatis扫描的接口

2.1 扫描操作fjdb数据源的接口,SysUserDao

@Mapper：注解标识为Mybatis映射的接口。

import com.hub.model.SysUserModel;
import org.apache.ibatis.annotations.Mapper;
/**
 * @author:
 * @date: 2022-03-23 23:27
 */
@Mapper
public interface SysUserDao {

    SysUserModel queryByUserName(String username);
}

2.2 扫描操作fzdb数据源的接口,SysRoleDao

@Mapper：注解标识为Mybatis映射的接口。

import com.hub.model.SysRoleModel;
import org.apache.ibatis.annotations.Mapper;

/**
 * @author:
 * @date: 2022-03-23 23:28
 */
@Mapper
public interface SysRoleDao {

    SysRoleModel queryByCreateId(String createUserId);

}

3.Mybatis扫描的xml配置

3.1 扫描操作fjdb数据源的配置,SysUserDao.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.hub.dao.fjdb.SysUserDao">

    <select id="queryByUserName" resultType="com.hub.model.SysUserModel">
        SELECT *
        FROM sys_user
        WHERE username = #{username}
    </select>
</mapper>

3.2 扫描操作fzdb数据源的配置,SysRoleDao.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.hub.dao.fzdb.SysRoleDao">

    <select id="queryByCreateId" resultType="com.hub.model.SysRoleModel">
        SELECT *
        FROM sys_role
        WHERE create_user_id = #{createUserId}
    </select>
</mapper>

4.实体类

4.1 入参FormModel类

@Data
public class FormModel implements Serializable {

    private String username;
    private String password;
}

4.2 表对应实体类SysUserModel

@Data
@TableName("sys_user")
public class SysUserModel implements Serializable {

    @TableId
    private Long userId;
    private String username;
    private String password;
    private Long createUserId;
    private Date createTime;
}

4.3 表对应实体类SysRoleModel

@Data
@TableName("sys_role")
public class SysRoleModel implements Serializable {

    @TableId
    private Long roleId;
    private String roleName;
    private Long createUserId;
    private Date createTime;
}

5.service类

5.1 接口IWorkService

public interface IWorkService {

    Map<String, Object> wokr(FormModel form);
}

5.2 实现WorkServiceImpl

@Service
public class WorkServiceImpl implements IWorkService {


    @Autowired
    private SysUserDao user;
    @Autowired
    private SysRoleDao role;

    @Override
    public Map<String, Object> wokr(FormModel form) {
        SysUserModel userModel = user.queryByUserName(form.getUsername());
        SysRoleModel roleModel = role.queryByCreateId(String.valueOf(userModel.getCreateUserId()));
        Map<String, Object> map = new HashMap<>();
        map.put("userName",form.getUsername());
        map.put("roleName",roleModel.getRoleName());
        return map;
    }
}

6.Controller类

如下：

@RestController
@RequestMapping("/sys")
public class WorkController {

    private static final Logger logger = LoggerFactory.getLogger(WorkController.class);
    @Autowired
    private IWorkService workService;

    @PostMapping("/work")
    public Map<String, Object> work(@RequestBody  FormModel formModel){

        Map map  = workService.wokr(formModel);
        logger.info("返回数据: " + map.toString());
        return map ;
    }

}

7.启动类

如下：

@SpringBootApplication
public class ExamplesApplication {
    public static void main(String[] args) {
        SpringApplication.run(ExamplesApplication.class);
    }
}

8.本例pom.xml文件

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-actuator</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
    </dependencies>

9.建表语句

9.1 sys_user

CREATE TABLE sys_user (
  user_id BIGINT(16) NOT NULL AUTO_INCREMENT,
  username VARCHAR(64) COLLATE utf8_bin NOT NULL COMMENT '用户名',
  PASSWORD VARCHAR(64) COLLATE utf8_bin DEFAULT NULL COMMENT '密码',
  create_user_id BIGINT(16) DEFAULT NULL COMMENT '创建者ID',
  create_time DATETIME DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (user_id),
  UNIQUE KEY username (username)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='用户';

INSERT INTO sys_user(user_id,username,PASSWORD,create_user_id,create_time)
       VALUE(20220101,'admin','123456789',2022010156,'2022-3-23 23:58:59');

9.2 sys_role

CREATE TABLE sys_role (
  role_id BIGINT(16) NOT NULL AUTO_INCREMENT,
  role_name VARCHAR(64) DEFAULT NULL COMMENT '角色名称',
  create_user_id BIGINT(16) DEFAULT NULL COMMENT '创建者ID',
  create_time DATETIME DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (role_id)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='角色';

INSERT INTO sys_role(role_id,role_name,create_user_id,create_time)
       VALUE(10000001,'首席执行官',2022010156,'2022-3-23 22:58:59');

10.测试

使用Postman测试如下：